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 with read 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 row in set (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: Waiting for 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 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:
1 row in set (0.00 sec)
至此,MySQL主从配置完毕。
6、验证
1、Master执行插入、删除、更新等操作,至Slave库查看对应记录信息是否正确
主库执行
mysql> use test;
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| country |
| t_user |
+----------------+
2 rows in set (0.00 sec)
mysql> select * from t_user;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
+------+
4 rows in set (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 rows in set (0.00 sec)
mysql>