路由器

电脑

手机

网络

MySql主从复制（初学笔记）

来源：www.45fan.com  2019-04-19 08:32:35 

笔记有部分前辈的经验，如有冒犯请联系 servicewy@163.com 会尽快删除。
           如有转载请标明出处

MySQL8 数据库的主从备份

5.5.* 版本 ： 单线程
5.7.* 版本： 多线程

大多用的都是基数版本。其中出名的为 5系列的 5.5 版本 和 5.7版本。
MySQL主从复制
MySQL数据库自身提供的主从复制功能可以方便的实现数据的多处自动备份，实现数据库的拓展。多个数据备份不仅可以加强数据的安全性，通过实现读写分离还能进一步提升数据库的负载性能。
下图就描述了一个多个数据库间主从复制与读写分离的模型：
MySql主从复制（初学笔记）
在一主多从的数据库体系中，多个从服务器采用异步的方式更新主数据库的变化，业务服务器在执行写或者相关修改数据库的操作是在主服务器上进行的，读操作则是在各从服务器上进行。如果配置了多个从服务器或者多个主服务器又涉及到相应的负载均衡问题，关于负载均衡具体的技术细节还没有研究过，今天就先简单的实现一主一从的主从复制功能。
Mysql主从复制的实现原理图大致如下(来源网络)：
MySql主从复制（初学笔记）
MySQL之间数据复制的基础是二进制日志文件（binary log file）。一台MySQL数据库一旦启用二进制日志后，其作为master，它的数据库中所有操作都会以“事件”的方式记录在二进制日志中，其他数据库作为slave通过一个I/O线程与主服务器保持通信，并监控master的二进制日志文件的变化，如果发现master二进制日志文件发生变化，则会把变化复制到自己的中继日志中，然后slave的一个SQL线程会把相关的“事件”执行到自己的数据库中，以此实现从数据库和主数据库的一致性，也就实现了主从复制。

主从实践：

实现MySQL主从复制需要进行的配置：
主服务器：
开启二进制日志
配置唯一的server-id
获得master二进制日志文件名及位置
创建一个用于slave和master通信的用户账号

从服务器：

配置唯一的server-id
使用master分配的用户账号读取master二进制日志
启用slave服务

具体实现过程如下：

一、准备工作：

主从两数据库版本一致！

*二、主数据库master修改：

1.修改mysql配置
找到主数据库的配置文件my.cnf，在/etc/mysql/my.cnf,在[mysqld]部分插入如下两行：

[mysqld]
log-bin=binlog #开启二进制日志
server-id=1 #设置server-id（可随意输入但必须存在必须保证唯一）
#service mysqld restart #修改配置文件后 需重启MySQL服务。

2.重启mysql，创建用于同步的用户账号
进入MySQL数据库 mysql -u root -pRedhat123@
创建用户并授权：

用户：tom   密码：Redhat123@
mysql> CREATE USER 'tom'@'%' IDENTIFIED BY 'Redhat123@';  //创建用户
mysql> GRANT REPLICATION SLAVE ON *.* TO 'tom'@'%'; //给予复制权限(with grant option 给予授权权限 可不写)
mysql>flush privileges;  //刷新权限

3.查看master状态，记录二进制文件名(mysql-bin.000003)和位置(73)：

mysql > SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File       | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| binlog.000005  | 115   |       |         |
+------------------+----------+--------------+------------------+

三、从服务器slave修改：

1.修改mysql配置

同样找到my.cnf配置文件，添加server-id

[mysqld]
server-id=2  #设置server-id，必须唯一不可与master中的server-id相同
#service mysqld restart   //修改配置文件后 需重启MySQL服务

2.重启mysql，进入MySQL中，执行同步SQL语句(需要主服务器主机名，登陆凭据，二进制文件的名称和位置------>名称和位置可以通过在master中执行show master status进行查看)：

mysql> CHANGE MASTER TO
  ->   MASTER_HOST='192.168.89.151',
  ->   MASTER_USER='tom',
  ->   MASTER_PASSWORD=‘Redhat123@',
  ->   MASTER_LOG_FILE='binlog.000005',
  ->   MASTER_LOG_POS=115;

3.启动slave同步进程：

mysql>start slave;

4.查看slave状态：

mysql> show slave status \G;
*************************** 1. row ***************************
      Slave_IO_State: Waiting for master to send event
       Master_Host: 192.168.89.151
       Master_User: rep1
       Master_Port: 3306
      Connect_Retry: 60
     Master_Log_File: binlog.000008
   Read_Master_Log_Pos: 671
      Relay_Log_File: slave1-relay-bin.000004
      Relay_Log_Pos: 4
  Relay_Master_Log_File: binlog.000008
     Slave_IO_Running: Yes 
    Slave_SQL_Running: Yes
     Replicate_Do_DB: 
   Replicate_Ignore_DB: 
  ...
 当Slave_IO_Running和Slave_SQL_Running都为YES的时候就表示主从同步设置成功了。进行验证，在主master中添加一个数据库，然后回到slave上，登陆数据库查看数据库，如果可以看到master中新创建的数据库，那么表示基本的主从复制实现成功。
如果失败可以关闭slave（mysql>stop slave;）,然后根据slave的状态来进行修改。

排错：

导致lave_IO_Running 为connecting 的原因主要有以下 几个方面：
网络不通
密码不对，账号授权不对，找好错误，地址错误
pos不对
Master和Slave两端防火墙
导致lave_IO_Running 为NO 的原因可能是：
在配置Slave同步时（change master to…）因为Slave访问Master没有权限导致。
Master中的/var/lib/mysql/binlog.xxxxxx误删或者配置文件中bin-log=xxx 参数设置有问题。
导致Slave_SQL_Running:NO
在配置Slave同步时master_log_file=xxxxx与master_log_pos=xxxx两参数与master不同步。
在配置slave同步时，因为slave访问master没有权限导致。(master端创建用户并授权步骤。)
uuid 相同， 删除 /var/lib/mysql/auto.cnf 文件 并重新启动MySQL。
slave 启动失败 可尝试 reset slave all （重置slave） 再启动slave
master开启二进制日志后默认记录所有库所有表的操作，可以通过配置来指定只记录指定的数据库甚至指定的表的操作，具体在mysql配置文件的[mysqld]可添加修改如下选项：

binlog-do-db=*****    // 指定，只向从服务器开放某数据库的复制，多个数据库需要一                   
             行一个‘binlog-do-db=’ 进行指定（主服务器参数）

binlog-ignore-db=mysql  // 指定主服务器上的哪个数据库不向从服务器开放复制权限，多  
             个数据库时，需要一行一个‘binlog-ignore-db=’进行指定       
                             （主服务器参数）

log-bin=binlog      // 在MySQL8中默认开启，如果想关闭，需开disable_log_bin

四、异步复制 全同步复制 半同步复制

1.异步复制（Asynchronous replication）
MySQL默认的复制即是异步的，主库在执行完客户端提交的事务后会立即将结果返给给客户端，并不关心从库是否已经接收并处理，这样就会有一个问题，主如果crash掉了，此时主上已经提交的事务可能并没有传到从上，如果此时，强行将从提升为主，可能导致新主上的数据不完整。
2.全同步复制（Fully synchronous replication）
指当主库执行完一个事务，所有的从库都执行了该事务才返回给客户端。因为需要等待所有从库执行完该事务才能返回，所以全同步复制的性能必然会收到严重的影响。

3.半同步复制（Semisynchronous replication）
介于异步复制和全同步复制之间，主库在执行完客户端提交的事务后不是立刻返回给客户端，而是等待至少一个从库接收到并写到relay log中才返回给客户端。相对于异步复制，半同步复制提高了数据的安全性，同时它也造成了一定程度的延迟，这个延迟最少是一个TCP/IP往返的时间。所以，半同步复制最好在低延时的网络中使用。

半同步复制
想要使用半同步复制，异步复制必须已经存在。
半同步复制是一个功能模块，库要能支持动态加载才能实现半同步复制！
首先加载插件：
因为需要执行install plugin , set global ,stop slave和start slave等操作，所以用户需要super权限。

Master端：

mysql>install plugin rpl_semi_sync_master soname 'demisync_master.so'; //加载semisync_master 插件
mysql>set global rpl_semi_sync_master_enable=1;   //开启半同步（默认为关闭）

mysql>select plugin_name,plugin_status from information_schema.plugins

where plugin_name_like’%semi%’; //查看插件是否加载成功。

+----------------------+---------------+
| PLUGIN_NAME     | PLUGIN_STATUS |
+----------------------+---------------+
| rpl_semi_sync_master | ACTIVE    |
+----------------------+---------------+
1 row in set (0.00 sec)

mysql>show plugins; //查看插件是否加载成功

rpl_semi_sync_master | ACTIVE  | REPLICATION semisync_master.so | GPL

mysql>show status like ‘Rpl_semi_sync_master_status’; //查看半同步是否在运行。

+-----------------------------+-------+
| Variable_name        | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_status | ON  |
+-----------------------------+-------+
1 row in set (0.00 sec)

Slave端：

mysql>install plugin  rpl_semi_sync_slave soname 'semisync_slave.so';   //加载semisync_slave插件
mysql>set global rpl_semi_sync_slave_enable=1;   //开启半同步（默认为关闭）
mysql>stop slave io_thread;
mysql>start slave io_thread;
  如果没有重启，默认还是异步复制，重启后，slave会在master上注册为半同步复制的slave角色。

验证方式：

主error.log（配置文件中：log=/log/mysql/mysql_log）中会打印如下信息：
2016-08-05T10:03:40.104327Z 5 [Note] While initializing dump thread for slave with UUID <ce9aaf22-5af6-11e6-850b-000c2988bad2>, found a zombie dump thread with the same UUID. Master is killing the zombie dump thread(4).
2016-08-05T10:03:40.111175Z 4 [Note] Stop asynchronous binlog_dump to slave (server_id: 2)
2016-08-05T10:03:40.119037Z 5 [Note] Start binlog_dump to master_thread_id(5) slave_server(2), pos(mysql-bin.000003, 621)

2016-08-05T10:03:40.119099Z 5 [Note] Start semi-sync binlog_dump to slave (server_id: 2), pos(mysql-bin.000003, 621)

mysql>show status like ‘Rpl_semi_sync_slave_status’; //查看半同步是否在运行

+----------------------------+-------+
| Variable_name       | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON  |
+----------------------------+-------+
1 row in set (0.20 sec)

两个变量常用来监控主从是否运行在半同步复制模式下。 如全部启动正常，MySQL半同步复制基本搭建完成。

 
 
推荐阅读