时间戳

首页 > 技术文章 > mysql主从同步

mysql主从同步

tian880820 2018-03-20 15:02 原文

mysql复制的原理：

Mysql内建的复制功能是构建大型，高性能应用程序的基础。将Mysql的数据分布到多个系统上去，这种分布的机制，是通过将Mysql的某一台主机的数据复制到其它主机（slaves）上，并重新执行一遍来实现的。复制过程中一个服务器充当主服务器，而一个或多个其它服务器充当从服务器。主服务器将更新写入二进制日志文件，并维护文件的一个索引以跟踪日志循环。这些日志可以记录发送到从服务器的更新。当一个从服务器连接主服务器时，它通知主服务器从服务器在日志中读取的最后一次成功更新的位置。从服务器接收从那时起发生的任何更新，然后封锁并等待主服务器通知新的更新。

mysql支持哪些复制

 1--基于语句的复制： 在主服务器上执行的SQL语句，在从服务器上执行同样的语句。MySQL默认采用基于语句的复制，效率比较高。一旦发现没法精确复制时，会自动选着基于行的复制。 
 2--基于行的复制：把改变的内容复制过去，而不是把命令在从服务器上执行一遍. 从mysql5.0开始支持
 3--混合类型的复制: 默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制。

Mysql复制能解决的问题
    1--数据分布 (Data distribution )
    2--负载平衡(load balancing)
    3--数据备份(Backups) ，保证数据安全
    4--高可用性和容错行(High availability and failover)
    5--实现读写分离，缓解数据库压力

Mysql主从复制原理
     master服务器将数据的改变记录二进制binlog日志，当master上的数据发生改变时，则将其改变写入二进制日志中；salve服务器会在一定时间间隔内对master二进制日志进行探测其是否发生改变，如果发生改变，则开始一个I/OThread请求master二进制事件，同时主节点为每个I/O线程启动一个dump线程，用于向其发送二进制事件，并保存至从节点本地的中继日志中，从节点将启动SQL线程从中继日志中读取二进制日志，在本地重放，使得其数据和主节点的保持一致，最后I/OThread和SQLThread将进入睡眠状态，等待下一次被唤醒。

注意几点：
     1--master将操作语句记录到binlog日志中，然后授予slave远程连接的权限（master一定要开启binlog二进制日志功能；通常为了数据安全考虑，slave也开启binlog功能）。
     2--slave开启两个线程：IO线程和SQL线程。其中：IO线程负责读取master的binlog内容到中继日志relay log里；SQL线程负责从relay log日志里读出binlog内容，并更新到slave的数据库里，这样就能保证slave数据和master数据保持一致了。
     3--Mysql复制至少需要两个Mysql的服务，当然Mysql服务可以分布在不同的服务器上，也可以在一台服务器上启动多个服务。
     4--Mysql复制最好确保master和slave服务器上的Mysql版本相同（如果不能满足版本一致，那么要保证master主节点的版本低于slave从节点的版本）
     5--master和slave两节点间时间需同步

Mysql复制的流程图如下：

 

如上图所示：
     Mysql复制过程的第一部分就是master记录二进制日志。在每个事务更新数据完成之前，master在二日志记录这些改变。MySQL将事务串行的写入二进制日志，即使事务中的语句都是交叉执行的。在事件写入二进制日志完成后，master通知存储引擎提交事务。
    第二部分就是slave将master的binary log拷贝到它自己的中继日志。首先，slave开始一个工作线程——I/O线程。I/O线程在master上打开一个普通的连接，然后开始binlog dump process。Binlog dump process从master的二进制日志中读取事件，如果已经跟上master，它会睡眠并等待master产生新的事件。I/O线程将这些事件写入中继日志。
    SQL slave thread（SQL从线程）处理该过程的最后一步。SQL线程从中继日志读取事件，并重放其中的事件而更新slave的数据，使其与master中的数据一致。只要该线程与I/O线程保持一致，中继日志通常会位于OS的缓存中，所以中继日志的开销很小。
   此外，在master中也有一个工作线程：和其它MySQL的连接一样，slave在master中打开一个连接也会使得master开始一个线程。复制过程有一个很重要的限制——复制在slave上是串行化的，也就是说master上的并行更新操作不能在slave上并行操作。

主从复制条件

1）开启Binlog功能
2）主库要建立账号
3）从库要配置master.info（CHANGE MASTER to...相当于配置密码文件和Master的相关信息）
4）start slave 开启复制功能
  
需要了解的：
1）3个线程，主库IO，从库IO和SQL及作用
2）master.info（从库）作用
3）relay-log 作用
4）异步复制
5）binlog作用（如果需要级联需要开启Binlog）

 

master上的操作：

1）设置master数据库的my.cnf文件（在[mysqld]配置区域添加下面内容）

[root@master ~]# vim /usr/local/mysql/my.cnf
.......
server-id=1        #数据库唯一ID，主从的标识号绝对不能重复。
log-bin=mysql-bin    　#开启bin-log，并指定文件目录和文件名前缀
binlog-do-db=******    　#需要同步的数据库。如果是多个同步库，就以此格式另写几行即可。如果不指明对某个具体库同步，就去掉此行，表示同步所有库（除了ignore忽略的库）。
binlog-ignore-db=mysql  #不同步mysql系统数据库。如果是多个不同步库，就以此格式另写几行；也可以在一行，中间逗号隔开。
sync_binlog = 1      　#确保binlog日志写入后与硬盘同步
binlog_checksum = none   #跳过现有的采用checksum的事件，mysql5.6.5以后的版本中binlog_checksum=crc32,而低版本都是binlog_checksum=none
binlog_format = mixed    #bin-log日志文件格式，设置为MIXED可以防止主键重复。
    
--------------------------------------------------------------------------------
温馨提示：在主服务器上最重要的二进制日志设置是sync_binlog，这使得mysql在每次提交事务的时候把二进制日志的内容同步到磁盘上，即使服务器崩溃也会把事件写入日志中。
sync_binlog这个参数是对于MySQL系统来说是至关重要的，他不仅影响到Binlog对MySQL所带来的性能损耗，而且还影响到MySQL中数据的完整性。对于"sync_binlog"参数的各种设置的说明如下：
sync_binlog=0，当事务提交之后，MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘，而让Filesystem自行决定什么时候来做同步，或者cache满了之后才同步到磁盘。
sync_binlog=n，当每进行n次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。
     
在MySQL中系统默认的设置是sync_binlog=0，也就是不做任何强制性的磁盘刷新指令，这时候的性能是最好的，但是风险也是最大的。因为一旦系统Crash，在binlog_cache中的所有binlog信息都会被丢失。而当设置为“1”的时候，是最安全但是性能损耗最大的设置。因为当设置为1的时候，即使系统Crash，也最多丢失binlog_cache中未完成的一个事务，对实际数据没有任何实质性影响。
     
从以往经验和相关测试来看，对于高并发事务的系统来说，“sync_binlog”设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多。
 
导出master数据库中的数据，然后导入到slave数据库中。保证双方在同步环境实现前的数据一致。
导出数据库之前先锁定数据库
mysql> flush tables with read lock;    #数据库只读锁定命令，防止导出数据库的时候有数据写入。unlock tables命令解除锁定
 
导出master数据库(master数据库的root用户登陆密码：123456)
  [root@master ~]# mysql -uroot -p123456  < /opt/backup.sql
  [root@master ~]# scp /opt/backup.sql  192.168.10.202:/opt/   ＃将导出的sql文件上传到slave机器上
    
3）设置数据同步权限
  mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'syncuser'@'192.168.10.%' IDENTIFIED BY '123456';
  Query OK, 0 rows affected (0.02 sec)
  mysql> flush privileges;
  Query OK, 0 rows affected (0.00 sec)
 
--------------------------------------------------------------------------------
温馨提示：
权限查看方式
mysql> show grants;
mysql> show grants for slave@'182.148.1115.237';
--------------------------------------------------------------------------------
 
4）查看主服务器master状态(注意File与Position项，从服务器需要这两项参数)
mysql> show master status;

 

 下面是slave数据库上的操作

1）设置slave数据库的my.cnf配置文件

[root@master ~]# vim /usr/local/mysql/my.cnf
.......
server-id=2   #设置从服务器id，必须于主服务器不同
log-bin=mysql-bin   #启动MySQ二进制日志系统
replicate-do-db=******  #需要同步的数据库名。如果不指明同步哪些库，就去掉这行，表示所有库的同步（除了ignore忽略的库）。
replicate-ignore-db=mysql  #不同步mysql系统数据库
slave-skip-errors = all   #跳过所有的错误错误，继续执行复制操作
    
-----------------------------------------------------------------------------------------------
温馨提示：
当只针对某些库的某张表进行同步时，如下，只同步huanqiu库的haha表和huanpc库的heihei表：
replicate-do-db = huanqiu
replicate-wild-do-table = huanqiu.haha       //当只同步几个或少数表时，可以这样设置。注意这要跟上面的库指定配合使用；
replicate-do-db = huanpc
replicate-wild-do-table = huanpc.heihei      //如果同步的库的表比较多时，就不能这样一一指定了，就把这个选项配置去掉，直接根据指定的库进行同步。
-----------------------------------------------------------------------------------------------
    
2）在slave数据库中导入从master传过来的数据。
　　# mysql -uroot -p123456  < /opt/backup.sql
    
3）配置主从同步指令
mysql> stop slave;   ＃执行同步前，要先关闭slave
mysql> CHANGE MASTER TO    MASTER_HOST='192.168.10.201',MASTER_USER='syncuser',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000007',MASTER_LOG_POS=120;
    
mysql> start slave;
mysql> show slave status \G;

如上，当IO和SQL线程的状态均为Yes，则表示主从已实现同步了！

 

参考：https://www.cnblogs.com/kevingrace/p/6256603.html

 

推荐阅读