概述：
MySQL 的主从复制是通过他的归档日志（binlog） 来实现的。基本的过程就是从库在一个线程中和主库建立一个长连接，告诉主库从主库同步的 binlog 的位置，然后主库将这个位置之后的 binlog 日志内容发送给从库，从库获取到主库发送过来的 binlog 内容后先写入到本地的中转日志（relaylog）中，然后从库另一个进程在从中转日志中读取内容并解析成为 sql 语句在从库中执行，从而保证了主从的同步。
要设置主从复制，有几个前提：
以mysql8为例
首先，主库和从库的数据库的大版本保持一致。
第二，主从复制集群中每个数据库实例的 server-id 的值不能重复。（线上实例的server-id 生成策略已经避免重复，无需操作）
第三，要开启归档日志并且归档日志的格式选择为 row 方式。这两个在 MySQL 8 中默认就是这样，所以不需要设置，如果早期版本默认值不同需要在配置文件中人工设置。
步骤:
一主一从复制
参数说明
repl ：进行主从复制权限的用户名
123456 ：进行主从复制权限的密码
30.40.36.86 ：slave节点的IP
10.218.251.172：master节点的IP
主库设置
首先在在 MySQL 中增加一个可以进行主从复制权限的用户。在 mysql 交互环境中执行如下命令：
mysql> create user ‘repl’@‘30.40.36.86’ identified by ‘123456’;
mysql> grant replication slave on . to ‘repl’@‘30.40.36.86’;
mysql> show master status;
±--------------±---------±-------------±-----------------±------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
±--------------±---------±-------------±-----------------±------------------+
| binlog.000001 | 2507 | | | |
±--------------±---------±-------------±-----------------±------------------+
1 row in set (0.00 sec)
最后一个命令获取到当前归档日志的名字和位置，后面从服务器设置主从复制的时候需要从这个位置开始。
从库设置
1.基本设置
得到上面这些信息后，我们就可以登录到 slave 服务器上的数据库，然后通过下面的命令将这个数据库设置为 master 数据库的从服务器。
mysql> change master to
-> master_host=‘10.218.251.172’,
-> master_port=3306,
-> master_user=‘repl’,
-> master_password=‘123456’,
-> master_log_file=‘binlog.000001’,
-> master_log_pos=2507;
这个命令将当前数据库设置为 10.218.251.172 数据库的从库，并且从归档日志 binlog.000001 的位置 2507开始进行复制(在前面已获取)
2.开启复制线程
start slave;
不带任何参数表示同时启动I/O 线程和SQL线程。
I/O线程从主库读取bin log，并存储到relay log中继日志文件中。
SQL线程读取中继日志，解析后，在从库重放。
3.查看从库的状态
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.218.251.172
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000001
Read_Master_Log_Pos: 2507
Relay_Log_File: BAB1800793-relay-bin.000002
Relay_Log_Pos: 319
Relay_Master_Log_File: binlog.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 2507
Relay_Log_Space: 532
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 11
Master_UUID: 55b5f9f9-6996-11e9-a55e-7cd30ac454c0
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set (0.00 sec)
ERROR:
No query specified
注：’\G’ 是格式化show的结果，方便查看
Slave_IO_State 的值为 Waiting for master to send event ，表示已经准备好接受主库发送过来的归档日志进行处理了。也可以看下Slave_IO_Running，Slave_SQL_Running的状态都为Yes表示I/O 线程和SQL线程都已经启动成功。
效果演示
1.主库插入数据
登录主库执行如下命令
mysql> create database copytest;
mysql> use copytest;
mysql> create table copytable(id int(11));
mysql> insert into copytable values(1);
mysql> select * from copytable;
±-----+
| id |
±-----+
| 1 |
±-----+
1 row in set (0.03 sec)
2.从库查看数据
mysql> show databases;
±-------------------+
| Database |
±-------------------+
| copytest |
| information_schema |
| mysql |
| performance_schema |
| sys |
±-------------------+
mysql> use copytest;
mysql> show tables;
±-------------------+
| Tables_in_copytest |
±-------------------+
| copytable |
±-------------------+
mysql> select * from copytable;
±-----+
| id |
±-----+
| 1 |
±-----+
1 row in set (0.00 sec)
可以看到，主库中做的操作，都同步到从库中来了。
同步指定的数据库(表)
注：下面指定数据库（表）的命令产生的效果没有保存下来，如果数据库重启了，那么就会失效。如果要生效就需要将配置写在 my.cnf 文件中。在从库中，将 my.cnf 文件内容更改为如下即可。
同步指定的数据库
上面的设置中，我们是将主库中的所有数据库都同步到从库中来了。实际上，我们还可以通过命令的方式同步指定的数据库或者数据库的数据表。在从库中执行下面的命令将会指定只同步数据库copytest。
mysql> stop slave;
mysql> change replication filter replicate_do_db=(copytest);
mysql> start slave;
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.218.251.172
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000001
Read_Master_Log_Pos: 3189
Relay_Log_File: BAB1800793-relay-bin.000003
Relay_Log_Pos: 319
Relay_Master_Log_File: binlog.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: copytest
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 3189
Relay_Log_Space: 1378
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 11
Master_UUID: 55b5f9f9-6996-11e9-a55e-7cd30ac454c0
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set (0.00 sec)
ERROR:
No query specified
注意：执行任何修改同步策略的操作前都需要先停止复制线程。命令：stop slave；
可以看到Replicate_Do_DB的值已经变成了copytest。这样，只有主库中 copytest数据库中的变化会同步到从库中来，其余的数据库的变化则不会同步。
取消同步指定的数据库
取消刚才制定的同步数据
mysql> stop slave;
mysql> change replication filter replicate_do_db=();
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 10.218.251.172
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000001
Read_Master_Log_Pos: 3189
Relay_Log_File: BAB1800793-relay-bin.000004
Relay_Log_Pos: 319
Relay_Master_Log_File: binlog.000001
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 3189
Relay_Log_Space: 696
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 11
Master_UUID: 55b5f9f9-6996-11e9-a55e-7cd30ac454c0
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set (0.00 sec)
ERROR:
No query specified
同步忽略指定的数据库
有时候存在除了某一个库别的库都需要同步，如果一一把所有需要同步的数据库设置进去太麻烦，并且新增数据库由于没有制定到需要同步的数据库项里面导致无法同步。这个时候就可以制定忽略那些库不需要同步，命令如下：
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> change replication filter Replicate_Ignore_DB=(ignoretest);
Query OK, 0 rows affected (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.07 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.218.251.172
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000001
Read_Master_Log_Pos: 3189
Relay_Log_File: BAB1800793-relay-bin.000005
Relay_Log_Pos: 319
Relay_Master_Log_File: binlog.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: ignoretest
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 3189
Relay_Log_Space: 696
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 11
Master_UUID: 55b5f9f9-6996-11e9-a55e-7cd30ac454c0
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set (0.00 sec)
ERROR:
No query specified
mysql>
同步指定的数据表
方法同上，只是需要制定的参数不同，设置值的时候可以用通配符。需要注意的是需要指定具体某库的某个（些）表
命令如下：CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE =('copytest.copytable%');
代表同步copytest库下面的所有以copytable开头的表。
取消指定的数据表
方法同取消指定的数据库基本相同，命令：CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE =();;
多主一从复制（多源复制）
数据流向：
master1 -> slave
master2 -> slave
参数说明
repl ：进行主从复制权限的用户名
123456 ：进行主从复制权限的密码
30.40.36.86 ：slave节点的IP
10.218.251.172：master1节点的IP
10.218.251.11 ：master2节点的IP
主库设置
master1设置：
首先在在 MySQL 中增加一个可以进行主从复制权限的用户。在 mysql 交互环境中执行如下命令：
mysql> create user ‘repl’@‘30.40.36.86’ identified by ‘123456’;
mysql> grant replication slave on . to ‘repl’@‘30.40.36.86’;
mysql> show master status;
±--------------±---------±-------------±-----------------±------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
±--------------±---------±-------------±-----------------±------------------+
| binlog.000001 | 3189 | | | |
±--------------±---------±-------------±-----------------±------------------+
1 row in set (0.00 sec)
最后一个命令获取到当前归档日志的名字和位置，后面从服务器设置主从复制的时候需要从这个位置开始。
master2设置：
同master1相同
mysql> create user ‘repl’@‘30.40.36.86’ identified by ‘123456’;
mysql> grant replication slave on . to ‘repl’@‘30.40.36.86’;
mysql> show master status;
±--------------±---------±-------------±-----------------±------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
±--------------±---------±-------------±-----------------±------------------+
| binlog.000095 | 7909238 | | | |
±--------------±---------±-------------±-----------------±------------------+
1 row in set (0.00 sec)
从库设置
1.基本设置
得到上面这些信息后，我们就可以登录到 slave 服务器上的数据库，然后通过下面的命令将这个数据库设置为 master 数据库的从服务器。
mysql> change master to
-> master_host=‘10.218.251.172’,
-> master_port=3306,
-> master_user=‘repl’,
-> master_password=‘123456’,
-> master_log_file=‘binlog.000001’,
-> master_log_pos=3189
-> for channel ‘100’;
Query OK, 0 rows affected, 1 warning (0.23 sec)
mysql>
mysql> change master to
-> master_host=‘10.218.251.11’,
-> master_port=3306,
-> master_user=‘repl’,
-> master_password=‘123456’,
-> master_log_file=‘binlog.000095’,
-> master_log_pos=7909238
-> for channel ‘200’;
Query OK, 0 rows affected, 1 warning (0.30 sec)
这个命令将当前数据库设置为 10.218.251.172 和10.218.251.11数据库的从库，并且分别记录归档日志 的位置。分别设置了通道，可以同时执行。
注：与一主一从相比多了for channel‘’ 用来指定复制通道。
2.开启复制线程
start slave; 开启所有频道的复制线程。
start slave for channel ‘100’; 只开启通道为100的复制线程。
stop slave; 关闭所有频道的复制线程。
stop slave for channel ‘100’ 只关闭通道为100的复制线程。
3.查看从库状态
由于有两个主库所以会分别统计状态
mysql> start slave;
Query OK, 0 rows affected (0.08 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.218.251.172
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000001
Read_Master_Log_Pos: 3189
Relay_Log_File: BAB1800793-relay-bin-100.000002
Relay_Log_Pos: 319
Relay_Master_Log_File: binlog.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 3189
Relay_Log_Space: 536
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 11
Master_UUID: 55b5f9f9-6996-11e9-a55e-7cd30ac454c0
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name: 100
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
*************************** 2. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.218.251.11
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000095
Read_Master_Log_Pos: 7909238
Relay_Log_File: BAB1800793-relay-bin-200.000002
Relay_Log_Pos: 319
Relay_Master_Log_File: binlog.000095
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 7909238
Relay_Log_Space: 536
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: b794dc7e-6958-11e9-bcb2-7cd30ac4546c
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name: 200
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
2 rows in set (0.00 sec)
ERROR:
No query specified
可以看见设置三个的主从同步通道的所有状态信息。只有【Slave_IO_Running】和【Slave_SQL_Running】都是Yes，则同步是正常的。 如果是No或者Connecting都不行，可查看mysql-error.log，以排查问题。
效果演示
1.mater1库插入数据
mysql> create database testmaster;
mysql> use testmaster;
mysql> create table tablemaster(id int(11));
mysql> insert into tablemaster values(1);
mysql> select * from tablemaster;
±-----+
| id |
±-----+
| 1 |
±-----+
1 row in set (0.01 sec)
2.mater2库插入数据
mysql> create database testmaster2;
Query OK, 1 row affected (0.07 sec)
mysql> use testmaster2;
mysql> create table tablemaster2(id int(11));
mysql> insert into tablemaster2 values(1);
mysql> select * from tablemaster2;
±-----+
| id |
±-----+
| 1 |
±-----+
1 row in set (0.86 sec)
3.查看从库变化
mysql> show databases;
±-------------------+
| Database |
±-------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| testmaster |
| testmaster2 |
±-------------------+
6 rows in set (0.00 sec)
mysql> use testmaster;
Database changed
mysql> show tables;
±---------------------+
| Tables_in_testmaster |
±---------------------+
| tablemaster |
±---------------------+
1 row in set (0.00 sec)
mysql> select * from tablemaster;
±-----+
| id |
±-----+
| 1 |
±-----+
1 row in set (0.00 sec)
mysql> use testmaster2;
Database changed
mysql> show tables;
±----------------------+
| Tables_in_testmaster2 |
±----------------------+
| tablemaster2 |
±----------------------+
1 row in set (0.00 sec)
mysql> select * from tablemaster2;
±-----+
| id |
±-----+
| 1 |
±-----+
1 row in set (0.00 sec)
master1和master2的数据都同步过来了。
注：master1和master2不能同时执行相同的DDL；不能执行有相互冲突的sql语句。所以如果需要同库的同表需要在开启同步前定义好数据库结构。
同步指定数据库数据
同一主一从类似，不同点就是可以在sql后面加上 for channel ‘channelID’ 来指定操作的通道，不指定默认全部通道一起执行。
356
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
