9 MySQL 主从同步

2019-02-12 本文已影响16人 Kokoronashi

MySQL 主从同步概述

[TOC]

MySQL 主从同步介绍

主库: 被客户端存储数据访问的库

从库: 同步主库的数据到本机

MySQL 主从同步作用

实现数据的自动备份

MySQL 主从同步原理

1549904644742

Master 记录数据更改操作

启用 binlog 日志
设置 binlog 日志格式
设置 server_id

slave 运行两个线程

线程	作用
Slave_IO_Running	复制master主机 binlog日志文件里的 SQL 到本机的 relay-log 文件里
Slave_SQL_Running	执行本机 relay-log 文件里的 SQL 语句,重现 Master 的数据操作

查看主库上 Binlog Dump 程序复制新的 binlog 给从 relay log.

#显示当前服务器上,正在执行的程序列表
mysql> show processlist;
+----+-------------+---------------------+------+-------------+------+---------------------------------------------------------------+------------------+
| Id | User        | Host                | db   | Command     | Time | State                                                         | Info             |
+----+-------------+---------------------+------+-------------+------+---------------------------------------------------------------+------------------+
|  2 | root        | localhost           | NULL | Query       |    0 | starting                                                      | show processlist |
|  9 | system user |                     | NULL | Connect     |  842 | Connecting to master                                          | NULL             |
| 11 | system user |                     | NULL | Connect     |  842 | Slave has read all relay log; waiting for more updates        | NULL             |
| 20 | repluser    | 192.168.1.102:34916 | NULL | Binlog Dump |  600 | Master has sent all binlog to slave; waiting for more updates | NULL             |
+----+-------------+---------------------+------+-------------+------+---------------------------------------------------------------+------------------+

生成的新文件

文件名	作用
master.info	主从配置信息
relay-log.info	中继日志信息
test2-relay-bin.000001	中继日志,记录主库 binlog 中的 SQL
test2-relay-bin.index	中继日志索引

如果 Slave_IO 和 Slave_SQL 进程显示NO,最简单方法 rm 删除掉这四钟文件,重启数据库后,重新配置主从.

让当前从库临时不同步主库上的数据

#暂时停止同步进程,start slave后,花费时间自动恢复同步.
mysql> stop slave;

构建主从同步

主从同步模式

一主一从
一主多从

构建步骤

确保数据相同

从库必须要有主库上的数据

#备份主库
mysqldump -root -p密码 -B 库名列表 > mytest.sql
#从库恢复保证数据一致
mysql -uroot -p < mytest.sql

配置主服务器

启用 binlog 日志

[mysqld]
server_id=id
#启用binlog日志
log-bin=日志名
#日志格式
binlog_format=mixed

授权用户

#允许 repl 用户访问主库
mysql> grant replication slave on *.* to repl@'%' identified by 'passwrod';

查看当前正使用的日志

mysql> show master status\G;
*************************** 1. row ***************************
             File: binlog-101.000001
         Position: 154

配置从服务器

设置 server_id

[mysqld]
server_id=id

指定主库信息

mysql> change master to
master_host="192.168.1.101",
master_user="repl",
master_password="123123",
master_log_file="binlog-101.000001",
master_log_pos=154;

启动slave进程

mysql> start slave;

查看 slave 状态

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.101
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog-101.000001
          Read_Master_Log_Pos: 447
               Relay_Log_File: test2-relay-bin.000002
                Relay_Log_Pos: 614
        Relay_Master_Log_File: binlog-101.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
            
            ... ...
            #线程错误信息,可以用来排错
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error:

文件名	说明
master.info	主库信息
relay-log.info	中继日志信息
主机名-relay-bin.xxxxxx	中继日志
主机名-relay-bin.index	中继日志索引文件

测试配置

客户端连接主库写入数据,从库也可查询到

示例

配置MySQL 主从同步

192.168.1.101 主库

192.168.1.102 从库

主库配置步骤:

启用binlog日志
用户授权
查看正在使用的binlog日志信息

cat /etc/my.cnf
[mysqld]
server_id=101
log-bin=master101
binlog-format=mixed

mysql> grant replication slave on *.* to repluser@"192.168.1.%" identified by "123qqq...";

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| master101.000001 |      451 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

从库配置步骤:

验证授权用户
指定server_id
指定主库信息 ( 主库ip,授权用户,用户密码,主库binlog日志,主库position )
查看从库状态信息

mysql -urepluser -p123qqq... -h192.168.1.101

cat /etc/my.cnf
[mysqld]
server_id=102

mysql> change master to 
    -> master_host="192.168.1.101",
    -> master_user="repluser",
    -> master_password="123qqq...",
    -> master_log_file="master101.000001",
    -> master_log_pos=603;

mysql> show slave status\G;
...            
Slave_IO_Running: No
Slave_SQL_Running: No
...

mysql> start slave;
mysql> show slave status\G;
...            
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...

排错方法

出错时,可以去查看 Last_IO_Error 和 Last_SQL_Error 字段,会记录错误信息.

Slave_IO_Running: No 查看错误日志,Position配置时错误导致
Slave_IO_Running: Connecting 用户名写错导致无法连接主库

Last_IO_Error: error connecting to master 'repl@192.168.1.101:3306'

客户端验证主从同步配置

主库创建客户端用户,客户端登陆插入数据验证.

#主库创建客户端访问用户
mysql> grant all on bbsdb.* to jim@'%' identified by "123qqq...";

#客户端通过 jim账号 访问主库,创建 bbsdb.t1 插入数据
mysql> create database bbsdb;
mysql> create table bbsdb.t1(id int);
mysql> insert into bbsdb.t1 values(1),(2),(3),(4);

#查看 中继日志中 客户端在主库执行的操作
mysqlbinlog test2-relay-bin.000002 |grep -i -e create -e insert
create database bbsdb
create table bbsdb.t1(id int)
insert into bbsdb.t1 values(1),(2),(3),(4)

#连接从库 查看是否同步
mysql> select * from bbsdb.t1;
+------+
| id   |
+------+
|    0 |
|    2 |
|    3 |
|    4 |
+------+

主从配置常用参数

主库/etc/my.cnf

主库配置,对所有从库生效.

选项	说明
binlog_do_db=name	设置Master对哪些库记日志
binlog_ignore_db=name	设置Master对哪些库不记日志

#主库配置此参数,会影响
[mysqld]
binlog_do_db=db1,db2
binlog_ignore_db=db3,db4

从库 /etc/my.cnf

选项	说明
log_slave_updates	记录从库更新,允许链式复制( A-B-C )
relay_log=dbsvr2-relay-bin	指定中继日志文件名
replicate_do_db=mysql	仅复制指定库,其他库将被忽略,此选项可设置多条(省略时复制所有库)
replicate_ignore_db=test	不复制哪些库,其他库将被忽略,ignore-db与do-db只需选用其中一种

#从库配置,只对本从库生效,不影响其他从库
[mysqld]
#级联复制 
log_slave_updates
replicate_do_db=db1,db2
replicate_ignore_db=db3,db4

主从配置注意事项

数据库UUID不可相同,uuid配置在默认文件 /var/lib/mysql/auto.cnf

从库的 SQL 线程执行本机中继日志文件里的 SQL 命令,不会记录在本机的 binlog 日志文件里.

log_slave_updates 级联复制,中继日志执行 SQL 会再写入本机 binlog 日志

主从同步复制模式

模式	type	说明
异步复制	Asynchronous replication	主库执行完一次事务后,立即将结果返给客户端,并不关心从库是否已经接受并处理.
全同步复制	Fully syncharonous replication	当主库执行完一次事务,且所有从库都执行了该事务后才返回给客户端
半同步复制	Semisynchronous replication	介于异步复制和全同步复制之间,主库执行完一次事务后,等待至少一个从库接受到并写到relay log中才返回给客户端

模式配置

查看是否支持动态加载模块默认允许

#查看是否支持动态加载模块 默认允许
mysql> show variables like "have_dynamic_loading";
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| have_dynamic_loading | YES   |
+----------------------+-------+

#主库安装 semisync_master.so 插件
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

#从库安装 semisync_slave.so 插件
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

#查看已安装的插件
mysql> select plugin_name,plugin_status from information_schema.plugins where plugin_name like '%semi%';
+----------------------+---------------+
| plugin_name          | plugin_status |
+----------------------+---------------+
| rpl_semi_sync_master | ACTIVE        |
+----------------------+---------------+

启用半同步复制

查看本机半同步复制状态

mysql> show variables like "rpl_semi_sync_%_enabled";
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| rpl_semi_sync_master_enabled | OFF   |
+------------------------------+-------+

mysql> show variables like "rpl_semi_sync_%_enabled";
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| rpl_semi_sync_slave_enabled | OFF   |
+-----------------------------+-------+

启用主库的半同步复制模式

mysql> set global rpl_semi_sync_master_enabled = 1;
mysql> show variables like "rpl_semi_sync_%_enabled";
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| rpl_semi_sync_master_enabled | ON    |
+------------------------------+-------+

启用从库的半同步复制模式

mysql> set global rpl_semi_sync_slave_enabled = 1;
mysql> show variables like "rpl_semi_sync_%_enabled";
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| rpl_semi_sync_slave_enabled | ON    |
+-----------------------------+-------+

写入配置永久生效

#主库配置
[mysqld]
plugin-load=rpl_semi_sync_master=semisync_master.so
rpl_semi_sync_master_enabled=1

#从库配置
[mysqld]
plugin-load=rpl_semi_sync_slave=semisync_slave.so
rpl_semi_sync_slave_enabled=1

#高可用架构下,master和slave需同时启动,以便在主从切换后能继续使用半同步复制
[mysqld]
plugin-load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl_semi_sync_master_enabled=1
rpl_semi_sync_slave_enabled=1