Mysql5.7高可用之-基于binlog文件位置的主从同步配置
本文主要参照MySQL 5.7 Reference Manual文档,介绍在CentOS 7下,基于binlog文件的主从同步MySQL高可用方案,网上其他文章配置多掺杂其他与主从复制本身无关的配置选项,本着极简配置的原则撰写此文。
1、环境规划
本次规划采用一主一从配置
主机IP | 角色 |
---|---|
172.17.242.54 | MASTAER |
172.17.242.55 | SLAVE |
2、安装MySQL
请参照另一片文章CentOS 7下安装MySQL5.7
3、配置Master
1、配置my.cnf文件开启binlog,并设置server-id
shell > vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin
server-id=1
重启MySQL,注意:若要保证主从同步最低延迟与高一致性,需要在以上配置文件中增加如下配置,但是会牺牲一定性能
[mysqld]
log-bin=mysql-bin
server-id=1
innodb_flush_log_at_trx_commit=1
sync_binlog=1
2、创建复制账号信息
mysql> CREATE USER'repl'@'%' IDENTIFIED BY'123456';
mysql> GRANT REPLICATION SLAVE ON *.* TO'repl'@'%';
3、记录Master binlog文件同步位置信息,后面在配置Slave时需要设置相关信息
mysql> flush tables withread lock;
Query OK, 0 rows affected(0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+| File| Position| Binlog_Do_DB| Binlog_Ignore_DB| Executed_Gtid_Set|
+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000002| 154||||
+------------------+----------+--------------+------------------+-------------------+
1 rowinset(0.00 sec)
mysql> unlock tables;
其中 mysql-bin.000002为当前binlog文件名,Position 为坐标信息
4、创建Master Dump文件导入Slave数据库
如果皆为新建数据库无历史数据拷贝需要可忽略此步骤
1、执行 dump操作
shell>cd /usr/local/mysql/bin
shell> ./mysqldump -uroot -p --all-databases> /usr/common/m.sql
2、拷贝到目标主机并执行导入操作
shell>scp m.sql root@172.17.242.55:/usr/common
shell>cd /usr/local/mysql/bin/
shell> ./mysql -uroot -p< /usr/common/m.sql
5、配置 Slave
1、配置MySQL server-id 信息
shell> vim /etc/my.cnf[mysqld]
server-id=22
2、重启MySQL ,配置Master信息
mysql> CHANGE MASTER TO
-> MASTER_HOST='172.17.242.54',
-> MASTER_USER='repl',
-> MASTER_PASSWORD='123456',
-> MASTER_LOG_FILE='mysql-bin.000002',
-> MASTER_LOG_POS=154;
Query OK, 0 rows affected, 2 warnings(0.02 sec)
3、导入主库备份文件
shell>cd /usr/local/mysql/bin/
shell> ./mysql -uroot -p< /usr/common/m.sql
4、启动Slave并查看状态
mysql> START SLAVE;
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waitingfor master to send event
Master_Host: 172.17.242.54
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 154
Relay_Log_File: iZ2zedll6n6czo68sfh0mlZ-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000002
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: 154
Relay_Log_Space: 545
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: 1d968c76-ba7a-11ea-9642-00163e3428e0
Master_Info_File: /usr/local/mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave hasread all relay log; waitingformore 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:
1 rowinset(0.00 sec)
至此,MySQL主从配置完毕。
6、验证
1、Master执行插入、删除、更新等操作,至Slave库查看对应记录信息是否正确
主库执行
mysql> usetest;
mysql> show tables;
+----------------+| Tables_in_test|
+----------------+| country|| t_user|
+----------------+
2 rowsinset(0.00 sec)
mysql>select * from t_user;
+------+|id|
+------+| 1|| 2|| 3|| 4|
+------+
4 rowsinset(0.00 sec)
mysql> insert into t_user values(5),(6),(7),(8);
Query OK, 4 rows affected(0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql>select * from t_user;
+------+|id|
+------+| 1|| 2|| 3|| 4|| 5|| 6|| 7|| 8|
+------+
8 rowsinset(0.00 sec)
mysql>