mysql8配置主从库,MySQL 8 主从搭建

1、修改配置文件

MySQL 搭建主从需要配置 my.cnf ，在主库 my.cnf 的 [mysqld] 段落下添加如下内容：

[mysqld]

# 设置server-id,唯一值，标识主机，必须与从库不一致

server-id=1

# 开启二进制日志，主库必须开启

log-bin=mysql-bin

# 自动删除2592000秒(30天)前的日志

# 8.0以前的版本中这个配置为expire_logs_days，单位为天

binlog_expire_logs_seconds=2592000

# binlog记录的模式

# statement模式不会记录每一条更改语句，节约资源但主从数据可能不一致

# row模式记录每一条更改的语句，日志量非常大

# mixed模式是前两者优点的综合，但日志结构较为复杂

binlog_format=mixed

#设置只记录binlog的库，如果全库

binlog-do-db=demo

在从库 my.cnf 的 [mysqld] 段落下添加如下内容：

[mysqld]

# 设置server-id,唯一值，标识主机，必须与主库不一致

server-id=2

#设置只同步binlog的库

replicate-do-db=demo

修改配置文件之后记得重启 MySQL 使配置文件生效。

2、导出主库并还原

在主库上使用 MySQL 自带的 mysqldump 导出需要同步的数据库。

mysqldump -uroot -p --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=2 demo > /demo.sql

参数说明：

--skip-lock-tables #不锁表

--single-transaction #设定事务级别为可重复读

--flush-logs #开启一个新的binlog文件

--hex-blob #以16进制导出blob数据

--master-data=2 #导出数据库时将binlog信息也一并导出，2表示注释binlog信息

导出之后通过任意方式传输到从库所在的服务器上，并导入该 SQL 文件，导入前记得先创建好和主库一致的数据库名。

mysql> use demo;

Database changed

mysql> source /demo.sql;

Query OK, 0 rows affected (0.00 sec)

3、创建账号(可选)

从库连接主库需要一个用户，不在乎安全性的话也可以使用 root 用户，那样的话可以跳过这段。

但如果需要一个拥有最低权限可以实现主从复制的用户则可以手动创建并授权：

mysql> create user 'username'@'ip' identified by 'password';

Query OK, 0 rows affected (0.01 sec)

mysql> grant replication slave,replication client on *.* to 'username'@'ip';

Query OK, 0 rows affected (0.00 sec)

replication slave 这个权限用于主从复制，如果没有这个权限将不能同步数据。

replication client 则用于查看从库状态，允许使用 “show slave status”命令。

4、配置主从

从导出的 SQL 中获取主库的 binlog 信息。

cat demo.sql | grep CHANGE

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=156;

由此可以得知主库正在使用 mysql-bin.000007 这个 binlog 文件，位置为 156 。

然后在从库上配置需要连接的主库信息：

mysql> change master to master_host='slave ip',

-> master_port=3306,

-> master_user='username',

-> master_password='password',

-> master_log_file='mysql-bin.000007',

-> master_log_pos=156;

Query OK, 0 rows affected, 2 warnings (0.02 sec)

最后开启主从复制：

mysql> start slave;

Query OK, 0 rows affected, 1 warning (0.00 sec)

5、主从连接状态

开启主从同步之后通过命令查看状态：

mysql> show slave status\G

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

Slave_IO_State: Waiting for master to send event

Master_Host: ip

Master_User: username

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000007

Read_Master_Log_Pos: 517

Relay_Log_File: adam-relay-bin.000010

Relay_Log_Pos: 685

Relay_Master_Log_File: mysql-bin.000007

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB: demo

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

Relay_Log_Space: 1108

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: 10d10288-32ce-11eb-8674-00163e086d97

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, 1 warning (0.00 sec)

当 Slave_IO_Running 和 Slave_SQL_Running 都为Yes的时候，表示主从同步正常。