[TOC] # 主从 读写分离可以看这个: [https://help.aliyun.com/document\_detail/85143.html?spm=a2c4g.11186623.2.14.6ab869abTLXJBz](https://help.aliyun.com/document_detail/85143.html?spm=a2c4g.11186623.2.14.6ab869abTLXJBz) ## 简介  mysql复制过程分成三步: 1. master将改变记录到二进制日志(binary log).这些记录过程叫做二进制日志事件,`binary log events` 2. slave将master的`binary log events`拷贝到他的中继日志 3. slave重做中继日志中的事件,将改变应用到自己的数据库中.mysql复制是异步的而且串行化的 **复制的基本原则** * 每个slave只有一个master * 每个slave只能有一个唯一的服务器ID * 每个master可以有多个slave **异步半同步区别** 1. 异步复制 简单的说就是master把binlog发送过去，不管slave是否接收完，也不管是否执行完，这一动作就结束了. 2. 半同步复制 简单的说就是master把binlog发送过去，slave确认接收完，但不管它是否执行完，给master一个信号我这边收到了，这一动作就结束了。（谷歌写的代码，5.5上正式应用。） 3. 异步的劣势 当master上写操作繁忙时，当前POS点例如是10，而slave上IO\_THREAD线程接收过来的是3，此时master宕机，会造成相差7个点未传送到slave上而数据丢失。 **binlog作用** 记录数据库的增删改查sql语句,二进制文件. 参数说明 -F: 备份后立即刷新binlog日志 `--master-date`=1或者2.备份语句中添加`change master`语句,记录binlog位置点 开启binlog,创建rep用户,授权`replication slave` binlog转储线程 当从服务器与主服务器连接时，主服务器会创建一个线程将二进制日志内容发送到从服务器。 该线程可以使用 语句`SHOW PROCESSLIST`(下面有示例介绍) 在服务器 sql 控制台输出中标识为Binlog Dump线程。 二进制日志转储线程获取服务器上二进制日志上的锁，用于读取要发送到从服务器的每个事件。一旦事件被读取，即使在将事件发送到从服务器之前，锁会被释放。 **master.info** 记录主库的binlog信息 **relay log** 将slaveIO线程读取到的binlog数据写入relaylog,由relaylog.info管理. 等待sql线程读取内容并转为sql语句并写入到slave ## 常见配置 **注意创建文件夹的时候权限问题** * 主从都配置在`[mysqld]`节点下,而且都是小写 * 主机修改my.ini配置文件(必须主服务器唯一id) * 从修改my.ini(必须: 从服务器唯一ID, 可选: 启用二进制日志) * 必须启用二进制配置文件 * 停止从服务复制功能`stop slave;` ### 主节点配置 配置文件`[mysqld]` 配置唯一id ~~~ server-id=1 ~~~ 配置mysqlbin和错误日志 没有data文件夹,那就自己创建一个 log-bin=自己本地路径/mysqlbin log-err=自己本地路径/mysqlerr ~~~ log-bin=/usr/share/mysql/data/mysqlbin log-error=/usr/share/mysql/data/mysqlerr ~~~ 主机读写都可以 ~~~ read-only=0 ~~~ 可选 * 根目录,basedir="路径" * 临时目录,tmpdir="路径" * 数据目录="自己本地路径/data" * 设置不要复制的数据库 `binlog-ignore-db=mysql` * 设置需要复制的数据库 `binlog-do-db=需要复制的主数据库名` 其余配置 ~~~bash relay_log=relay-bin #开启中继日志 (日志存储位置尽量不要同数据存储同一磁盘同一目录，这里测试方便不重新指向) binlog-format=row #日志格式三种:STATEMENT，ROW，MIXED log-slave-updates=true # 配置从服务器的更新写入二进制日志 ~~~ ~~~sql mysql> show variables like 'log_%'; #查看日志是否开启 ~~~ log\_bin、relay\_log，二进制日志和中继日志尽量不要跟数据存储放在同一磁盘同一目录，防止硬盘损坏时日志也丢失 ### 从节点配置 必须:从节点id 可选: 启用二进制日志 ~~~ log-bin=mysql-bin ~~~ 其余配置 ~~~bash relay_log=relay-bin binlog-format=row log-slave-updates=true sever_id=2 #relay_log_purge=0 #禁止自动删除中继日志(slave配置文件多了下面这两条），如果是MHA开启此 ~~~ 启动从库 ~~~bash mysql -uroot -p123456 -e "set global read_only=1" #从库只读，不建议写在配置文件中 ~~~ ### 主节点备份数据 要把表结构什么的都在从库上回放,不然主从复制会报错 ~~~bash [root@node01 /]# mysqldump -uroot -p123456 -h192.168.2.70 --master-data=2 --single-transaction -R --triggers -A > /home/soft/all.sql; ~~~ 说明： \--master-data=2代表备份时刻记录master的Binlog位置和Position \--single-transaction意思是获取一致性快照 \-R意思是备份存储过程和函数 \--triggres的意思是备份触发器 \-A代表备份所有的库 查看更多信息mysqldump --help **从库要回放数据** ### 创建用户 ~~~ flush privileges; # 创建个从用户replication Grant replication slave on *.* to 'repl'@'%' identified by '123456'; flush privileges; ~~~ 查询master的状态,并记录下file和position的值(意思就是这个文件的这个位置开始复制). ~~~ mysql> show master status; +-----------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-----------------+----------+--------------+------------------+-------------------+ | mysqlbin.000002 | 154 | | mysql | | +-----------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) ~~~ **建议用这种方式,上一种,如果中间有人修改数据,复制点就变了** 查看Master主库备份时的binlog名称和位置，MASTER\_LOG\_FILE和MASTER\_LOG\_POS： ~~~bash [root@node01 soft]# head -n 30 /home/soft/all.sql | grep 'CHANGE MASTER TO' -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=154; ~~~ ### 从库配置 重启从库 连接从库,准备从当前点开始复制 ~~~ mysql> stop slave; #暂停从库 mysql> CHANGE MASTER TO MASTER_HOST='192.168.33.80', -> MASTER_USER='repl', -> MASTER_PASSWORD='123456', -> MASTER_LOG_FILE='mysqlbin.000002', -> MASTER_LOG_POS=1264; Query OK, 0 rows affected, 2 warnings (0.02 sec) mysql> start slave; Query OK, 0 rows affected (0.00 sec) ~~~ 查看下状态 ~~~ mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.33.80 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysqlbin.000002 Read_Master_Log_Pos: 1264 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 319 Relay_Master_Log_File: mysqlbin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes ~~~ 看 `Slave_IO_State`以及`Slave_IO_Running`和`Slave_SQL_Running`都为yes 出问题,就看下面的error是什么,或者看日志 ## 故障切换 确保所有主从数据库都开启二进制日志 ~~~ mysql> show variables like 'log_bin'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_bin | ON | +---------------+-------+ 1 row in set (0.01 sec) ~~~ 确保切换时数据时从库都是最新先把主库设为只读：set global read\_only=1; 并且刷新一下主库log-bin日志 ~~~sql mysql> show variables like 'read_only'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | read_only | ON | +---------------+-------+ 1 row in set (0.00 sec) mysql> flush logs; Query OK, 0 rows affected (0.02 sec) ~~~ ### 主故障 把主节点关闭 从节点 ~~~ mysql> show slave status\G; ~~~ 会发现,io也连接不上 ~~~ Last_IO_Error: error reconnecting to master ~~~ 1. 确保所有的`relay log`全部读取完毕 **在所有从库上** ~~~ mysql> stop slave io_thread; Query OK, 0 rows affected (0.00 sec) mysql> show processlist; +----+-------------+--------------+--------+---------+------+--------------------------------------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------------+--------------+--------+---------+------+--------------------------------------------------------+------------------+ | 2 | root | localhost | NULL | Query | 0 | starting | show processlist | | 4 | system user | | NULL | Connect | 2284 | Slave has read all relay log; waiting for more updates | NULL | | 5 | root | 192.168.33.5 | mytest | Sleep | 457 | | NULL | | 6 | root | 192.168.33.5 | mytest | Sleep | 458 | | NULL | +----+-------------+--------------+--------+---------+------+--------------------------------------------------------+------------------+ 4 rows in set (0.00 sec) ~~~ 直到看到`Slave has read all relay log; waitingfor more updates`，则表示从库更新都执行完毕了. 或者通过`show slave status\G;`查看 2. 选择新的主库 `show slave status\G;` 对比选择`Relay_Master_Log_File`，`Exec_Master_Log_Pos`最大的作为新的主库，这里我们选择slave1为新的主库（如果两个从IO进程一直都是正常，没有落后于主，且`relay log`都已经重放完成，两个从是一样的，选择哪个都可以） 3. 进行相应的配置 在slave1上，执行： ~~~ mysql> stop slave; ~~~ 进入datadir，删除`master.info` 和 `relay-log.info`（删除前，可以先备份） ~~~ $ rm -rf master.info $ rm -rf relay-log.info ~~~ 配置my.cnf文件，开启`log-bin`,如果有`log-slaves-updates=1`和`read-only=1`则要注释掉， 然后重启slave1或在线修改参数。 master.info：记录了mysql主服务器上的日志文件和记录位置、连接的密码。 4. 其他slave配置 slave2也要删除这2个文件,或者`reset slave;` `reset slave`将清除slave上的同步位置，删除所有旧的同步日志，使用新的日志重新开始，这正是我们想要的。需要注意的是，必须先停止slave服务（STOP SLAVE），我们已经在第一步停止了它。 5. `reset master` 在slave1上`reset master`，重新生成二进制日志。（RESET MASTER将删除所有的二进制日志，创建一个.000001的空日志。如果盲目的在主库上执行这个命令会导致slave找不到master的binlog，造成同步失败。） ~~~ mysql> reset master; Query OK, 0 rows affected (0.01 sec) mysql> show binary logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 154 | +------------------+-----------+ 1 row in set (0.00 sec) ~~~ 6. 在slave1上创建用于同步的用户 ~~~ flush privileges; # 创建个从用户replication Grant replication slave on *.* to 'repl'@'%' identified by '123456'; flush privileges; ~~~ 6. 重建主从,将slave2指向slave1 记得是slave1的ip ~~~ mysql> stop slave; Query OK, 0 rows affected (0.00 sec) mysql> CHANGE MASTER TO MASTER_HOST='192.168.33.81', -> MASTER_USER='repl', -> MASTER_PASSWORD='123456', -> MASTER_LOG_FILE='mysql-bin.000001', -> MASTER_LOG_POS=154; Query OK, 0 rows affected, 2 warnings (0.01 sec) mysql> start slave; Query OK, 0 rows affected (0.00 sec) ~~~ `show slave status\G;`查看下 ## 常见问题 常见排查问题 ~~~ mysql> show processlist; 查看下进程是否Sleep太多。发现很正常。  mysql> show master status\G; mysql> show slave status\G; ~~~ **主从复制，中继日志不断增长，如何设置中继日志自动清除?** 配置文件my.cnf,在mysqld下增添 ~~~ relay_log_purge=1 (自动清除中继日志打开) ~~~ **主从同步失败，如何快速同步?** 跳过出现指定错误的SQL. 如果要断开主从架构，应先stop slave io\_thread; 等待执行完relay log里的内容再stop slave; ~~~ #表示跳过一步错误，后面的数字可变 stop slave; set global sql_slave_skip_counter=1; start slave; # 之后再用mysql> show slave status\G 查看： ~~~