MYSQL主备搭建

环境准备

1. 服务器: CENTOS7
2. 数据库: MYSQL 5.7

主库配置

先给数据库添加一个专门用于同步的用户, 这里repl是用户名,123456是密码, 且只限于同一网段

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO repl@'192.168.0.%' IDENTIFIED BY '123456';

找到MYSQL的配置文件并添加以下配置。 我的是在/etc/my.cnf, 如果你的MYSQL是用docker安装的话, 则要进入docker容器里面找

[mysqld]
##  binlog文件名
log_bin = mysql-bin
## MYSQL实例id, 不能重复
server_id = 10
## 仅将以下数据库记录binlog, 不记录的则不会同步
## binlog_do_db = dbname1,dbname2
## 以下是推荐配置, 且是默认配置
## sync_binlog = 1
## innodb_flush_log_at_trx_commit = 1
## innodb_support_xa = on
## binlog_format = row

备库配置

和主库一样, 先添加一个同步的用户

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO repl@'192.168.0.%' IDENTIFIED BY '123456';

找到配置文件, 添加以下配置

[mysqld]
log_bin = mysql-bin
server_id = 20
## 中继日志
relay_log = /var/log/mysql/mysql-relay-bin
## 允许备库将重放事件记录到自身的binlog中
log_slave_updates = 1
## 只读, 也可以不设置
read_only = 1
## 需要同步的数据库, 逗号分隔(不添加代表同步所有)
## replicate-do-db = dbname
## 不需要同步的表, 多个需要分别设置
## replicate-ignore-table = dbname.table_name1
## replicate-ignore-table = dbname.table_name2

开始同步

由于我们修改了数据库的配置 , 所以第一件事就是将两个数据库重启, 重启完成之后在主库执行命令show master status, 可以看到生成的binlog文件名

File	Position	Binlog_Do_DB	BinLog_Ignore_DB	Executed_Gtid_Set
mysql-bin.000001	98	dbname

注意: 如果主库已经运行了一段时间,而不是新安装的。那么需要先将主库克隆一份到备库，比如使用mysqldump转存一份到备库上

下一步就是告诉备库如何连接到主库并重放binlog, 这一步推荐使用CHANGE MASTER TO语句来执行, 而不是修改配置文件。在备库上执行以下语句:

CHANGE MASTER TO MASTER_HOST = 'MASTER_SERVER_HOST', MASTER_USER = 'repl', MASTER_PASSWORD = '123456', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 0;

MSTER_LOG_POS参数设置为0, 是因为要从日志的开头读起。执行完之后再通过SHOW SLAVE_STATUS检查复制是否正确执行。

Slave_IO_State	Master_Host	Master_User	Master_Port	Connect_Retry	Master_Log_File	Read_Master_Log_Pos	Relay_Log_Pos	Relay_Master_Log_File	Slave_IO_Running	Slave_Sql_Running	…	Seconds_Behind_Master
	MASTER_SERVER_HOST	repl	3306	60	mysql-bin.000001	4	mysql-relay-bin.000001	mysql-bin.000001	No	No	…	0
最后再执行START SLAVE语句开始进行同步。

没有显示错误则再次执行SHOW SLAVE_STATUS查看状态，也可以在主库及备库上通过SHOW PROCESSLIST命令查看同步线程的状态。

主库

Id	User	Host	db	Command	Time	State	Info
15	repl	MASTER_SERVER_HOST:14732		Binlog Dump	2770	Master has sent all binlog to slave; waiting for more updates
备库
Id	User	Host	db	Command	Time	State	Info
-----	-----	-----	-----	-----	-----	-----	-----
1	system user			Connect	2749	Waiting for master to send event
2	system user			Connect	2742	Slave has read all relay log; waiting for more updates