mysql8 主从复制,MySQL 8 主从复制设置

前言

MySQL 有一个比较重要的功能是主从复制，也就是一个主库，可以设置 n 个从库，从库的数据能够自动和主库的数据保持一致。主从复制的架构有两个好处，一个是进行数据自动的备份，数据在主库和从库上都有一份，如果主库挂了，从库还有一份完整的数据。另一个是可以实现数据库访问的读写分离，提高数据库的吞吐量。也就是对数据的更新操作是在主库中完成的，而读取操作在多个从库中完成的，从而实现了负荷分担的效果，提高数据库的访问效率。

MySQL 的主从复制是通过他的归档日志(binlog) 来实现的。基本的过程就是从库在一个线程中和主库建立一个长连接，告诉主库从主库同步的 binlog 的位置，然后主库将这个位置之后的 binlog 日志内容发送给从库，从库获取到主库发送过来的 binlog 内容后先写入到本地的中转日志(relaylog)中，然后从库另一个进程在从中转日志中读取内容并解析成为 sql 语句在从库中执行，从而保证了主从的同步。具体的架构如下图所示。

MySQL 主从复制架构图

要设置主从复制，有几个前提：

首先，主库和从库的数据库的版本保持一致。

第二，主从复制集群中每个数据库实例的 server-id 的值不能重复。

第三，要开启归档日志并且归档日志的格式选择为 row 方式。这两个在 MySQL 8 中默认就是这样，所以不需要设置，如果早期版本默认值不同需要在配置文件中人工设置。

全新的主库设置从库

这里，我们以一个全新安装的主库为例来说明如何设置一个主从复制的MySQL 集群。以我们在前一篇文章 《MySQL 8 二进制方式安装到 CentOS 7 中》 安装的数据库作为主库，在服务器 10.110.2.72 中安装一个版本一样的 MySQL 库作为从库。特别注意，在这里的 server-id 值要设置为和主库设置的 71 不一样，这里我们把他设置为

主库设置

首先在在 MySQL 中增加一个可以进行主从复制权限的用户。在 mysql 交互环境中执行如下命令

mysql>create user 'repl'@'10.110.2.72' identified by 'reple_password';

mysql>grant replication slave on *.* to 'repl'@'10.110.2.72';

mysql> show master status;

+---------------+----------+--------------+------------------+-------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+---------------+----------+--------------+------------------+-------------------+

| binlog.000001 | 888 | | | |

+---------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

最后一个命令获取到当前归档日志的名字和位置，后面从服务器设置主从复制的时候需要从这个位置开始。

从库设置

得到这些信息后，我们就可以登录到 10.10.2.72 服务器上的数据库，然后通过下面的命令将这个数据库设置为 10.110.2.71 数据库的从服务器。

mysql> change master to

-> master_host='10.110.2.71',

-> master_port=38965,

-> master_user='repl',

-> master_password='replpassword',

-> master_log_file='binlog.000001',

-> master_log_pos=888;

mysql>start slave;

mysql>show slave slave\G;

这个命令将当前数据库设置为 10.110.2.71 数据库的从库，并且从归档日志 binlog.000001 的位置 888 开始进行复制。

查看从库的状态

mysql> show slave status\G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 10.110.2.71

Master_User: repl

Master_Port: 38965

Connect_Retry: 60

Master_Log_File: binlog.000001

Read_Master_Log_Pos: 888

Relay_Log_File: localhost-relay-bin.000001

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: 4035

Relay_Log_Space: 1431

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: 71

Master_UUID: 2980729e-23c6-11e9-aec6-005056881ba9

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

1 row in set (0.00 sec)

ERROR:

No query specified

Slave_IO_State 的值为 Waiting for master to send event ，表示已经准备好接受主库发送过来的归档日志进行处理了。

效果演示

登录主库执行如下命令

mysql>create database test1;

mysql>use test1;

mysql>create table test1(id bigint);

mysql>insert into test1 values(1);

mysql>insert into test1 values(2);

在从库查看数据库状态

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| sys |

| test1 |

+--------------------+

5 rows in set (0.00 sec)

mysql> use test1;

Database changed

mysql> show tables;

+-----------------+

| Tables_in_test1 |

+-----------------+

| test1 |

+-----------------+

1 row in set (0.01 sec)

mysql> select * from test1;

+------+

| id |

+------+

| 1 |

| 2 |

+------+

2 rows in set (0.00 sec)

mysql>

可以看到，主库中做的操作，都同步到从库中来了。

同步指定的数据库

上面的设置中，我们是将主库中的所有数据库都同步到从库中来了。实际上，我们还可以通过命令的方式同步指定的数据库或者数据库的数据表。在从库中执行下面的命令将会指定只同步数据库 test1

mysql> stop slave;

Query OK, 0 rows affected (0.16 sec)

mysql> change replication filter replicate_do_db=(test1);

Query OK, 0 rows affected (0.00 sec)

ysql> start slave;

Query OK, 0 rows affected (0.11 sec)

mysql> show slave status\G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 10.110.2.71

Master_User: repl

Master_Port: 38965

Connect_Retry: 60

Master_Log_File: binlog.000002

Read_Master_Log_Pos: 4035

Relay_Log_File: localhost-relay-bin.000010

Relay_Log_Pos: 319

Relay_Master_Log_File: binlog.000002

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB: test1

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: 4035

Relay_Log_Space: 1431

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: 71

Master_UUID: 2980729e-23c6-11e9-aec6-005056881ba9

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

1 row in set (0.00 sec)

这样，只有主库中 test1 数据库中的变化会同步到从库中来，其余的数据库的变化则不会同步。

这个命令产生的效果没有保存下来，如果数据库重启了，那么就会失效。如果要生效就需要将配置写在 my.cnf 文件中。在从库中，将 my.cnf 文件内容更改为如下即可。

[mysqld]

server-id=72

port=38965

character-set-server=utf8mb4

default-time_zone='+8:00'

lower_case_table_names=1

#max_connections=3000

#max_connect_errors=100000

default_authentication_plugin=mysql_native_password

datadir=/data/mysql/data

socket=/data/mysql/mysql.sock

log-error=/data/mysql/logs/mysqld.log

pid-file=/data/mysql/mysqld.pid

long_query_time=1

slow_query_log = 1

slow_query_log_file=/data/mysql/logs/slow.log

replicate_do_db=test1

[client]

port=38965

default-character-set=utf8mb4

socket=/data/mysql/mysql.sock

[mysql]

no-auto-rehash

default-character-set=utf8mb4

加入了新行 replicate_do_db=test1。然后重新启动数据库即可。

非空主库主从设置

很多时候，设置主从复制的时候，主库已经运行了一段时间，有了一些业务数据，那么这个时候我们首先将主库设置为只读状态，不允许新数据写入，然后查看当前的归档日志状态，记录下来后将数据库备份出来，恢复从库的可写状态，最后把备份出来的数据库恢复到从库中，再设置为从刚才查询出来的归档日志和归档日志的位置开始进行同步即可。