Centos Mysql主从同步搭建步骤教程分享

确保主数据库与从数据库里的数据一样

【主服务器】

1.创建供从服务器备份的账号，只需要授予REPLICATION SLAVE权限。

mysql> CREATE USER 'forslave'@'%' IDENTIFIED BY '123456';

mysql> GRANT REPLICATION SLAVE ON *.* TO 'forslave'@'%';

也可以使用phpmyadmin直接创建

可以不映射所有ip,只对内网相关服务器

可能有3306防火墙端口需要打开

2.配置my.cnf如下

[mysqld]

server-id=1

log-bin=mysql-bin#binlog名称

#binlog-do-db=mstest //要同步的mstest数据库,要同步多个数据库，就多加几个replicate-db-db=数据库名 #binlog-ignore-db=mysql //要忽略的数据库

3重启数据库

查看主服务器状态

mysql> show master status;

记录文件名和position值，后面配置从服务器使用

提示1：如果你不配置server-id或者配置值为0，那么主服务器将拒绝所有从服务器的连接。

提示2：在使用InnoDB的事务复制，为了尽可能持久和数据一致，你应该在my.cnf里配置innodb_flush_log_at_trx_commit 和 sync_binlog；

（innodb_flush_log_at_trx_commit

0：log buffer将每秒一次地写入log file中，并且log file的flush(刷到磁盘)操作同时进行。该模式下在事务提交的时候，不会主动触发写入磁盘的操作。

1：每次事务提交时MySQL都会把log buffer的数据写入log file，并且flush(刷到磁盘)中去，该模式为系统默认。

2：每次事务提交时MySQL都会把log buffer的数据写入log file，但是flush(刷到磁盘)操作并不会同时进行。该模式下，MySQL会每秒执行一次 flush(刷到磁盘)操作。

）

（sync_binlog 默认，sync_binlog=0，表示MySQL不控制binlog的刷新，由文件系统自己控制它的缓存的刷新。这时候的性能是最好的，但是风险也是最大的。因为一旦系统Crash，在binlog_cache中的所有binlog信息都会被丢失。

如果sync_binlog>0，表示每sync_binlog次事务提交，MySQL调用文件系统的刷新操作将缓存刷下去。最安全的就是sync_binlog=1了，表示每次事务提交，MySQL都会把binlog刷下去，是最安全但是性能损耗最大的设置。这样的话，在数据库所在的主机操作系统损坏或者突然掉电的情况下，系统才有可能丢失1个事务的数据。但是binlog虽然是顺序IO，但是设置sync_binlog=1，多个事务同时提交，同样很大的影响MySQL和IO性能。虽然可以通过groupcommit的补丁缓解，但是刷新的频率过高对IO的影响也非常大。对于高并发事务的系统来说，“sync_binlog”设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多。

所以很多MySQLDBA设置的sync_binlog并不是最安全的1，而是100或者是0。这样牺牲一定的一致性，可以获得更高的并发和性能。）

innodb_flush_log_at_trx_commit和sync_binlog 两个参数是控制MySQL 磁盘写入策略以及数据安全性的关键参数，当两个参数都设置为1的时候写入性能最差，推荐做法是innodb_flush_log_at_trx_commit=2，sync_binlog=500 或1000

提示3：确保主服务器里的skip-networking选项未启用，如果网络被禁用，你的从服务器将不能与主服务器通信并且复制失败。

【配置从数据库】

1.配置my.cnf如下

[mysqld]

server-id=2

#可以指定要复制的库

replicate-do-db = test #在master端不指定binlog-do-db，在slave端用replication-do-db来过滤

replicate-ignore-db = mysql #忽略的库

#网上还有下面配置

#relay-log=mysqld-relay-bin

重启服务器

提示1：如果有多个从服务器，每个服务器的server-id不能重复，跟IP一样是唯一标识，如果你没设置server-id或者设置为0，则从服务器不会连接到主服务器。

提示2：一般你不需要在从服务器上启用二进制日志，如果你在从服务器上启用二进制日志，那你可用它来做数据备份和崩溃恢复，或者做更复杂的事情（比如这个从服务器用来当作其它从服务器的主服务器）。

配置连接主服务器的信息

mysql> stop slave;

mysql> CHANGE MASTER TO

-> MASTER_HOST='192.168.1.233',

-> MASTER_USER='forslave',

-> MASTER_PASSWORD='123456',

-> MASTER_LOG_FILE='mysql-bin.000002',#主服务器文件名

-> MASTER_LOG_POS=313;#主服务器文件position

mysql> start slave;

mysql> show slave status

若Slave_IO_Rrunning:no的话，在服务器搜auto.cnf(find / -name 'auto.cnf')，重命名为auto.cnf.bak，重启数据库，show slave status，显示Slave_IO_Rrunning和Slave_SQL_Rrunning都为yes

若配置my.cnf后从服务器数据库重启失败，检查配置文件是否有字符缺失

测试：在库中修改表数据或增删表，看到从库有相关变化则成功

最后摘录一点关于主从同步延迟过高解释和解决方法

原因：我们知道， 一个服务器开放Ｎ个链接给客户端来连接的，　这样有会有大并发的更新操作, 但是从服务器的里面读取binlog 的线程仅有一个， 当某个SQL在从服务器上执行的时间稍长 或者由于某个SQL要进行锁表就会导致，主服务器的SQL大量积压，未被同步到从服务器里。这就导致了主从不一致， 也就是主从延迟。

解决：实际上主从同步延迟根本没有什么一招制敌的办法， 因为所有的SQL必须都要在从服务器里面执行一遍，但是主服务器如果不断的有更新操作源源不断的写入， 那么一旦有延迟产生， 那么延迟加重的可能性就会原来越大。 当然我们可以做一些缓解的措施。

a. 我们知道因为主服务器要负责更新操作， 他对安全性的要求比从服务器高， 所有有些设置可以修改，比如sync_binlog=1，innodb_flush_log_at_trx_commit = 1 之类的设置，而slave则不需要这么高的数据安全，完全可以讲sync_binlog设置为0或者关闭binlog，innodb_flushlog，innodb_flush_log_at_trx_commit也 可以设置为0来提高sql的执行效率 这个能很大程度上提高效率。另外就是使用比主库更好的硬件设备作为slave。

b. 就是把，一台从服务器当度作为备份使用， 而不提供查询， 那边他的负载下来了， 执行relay log 里面的SQL效率自然就高了。

c. 增加从服务器喽，这个目的还是分散读的压力， 从而降低服务器负载。