社区微信群开通啦，扫一扫抢先加入社区官方微信群
社区微信群
确保俩虚拟机centos7时间是同步的；
一台虚拟机是另一台拷贝过来后、修改的固定IP；
主服务器IP：192.168.255.133
从服务器IP：192.168.255.134
背景及原理介绍：Mysql 主从复制
主服务器须启用二进制日志记录并配置唯一的服务器ID，需要重启服务端
vim /etc/my.cnf
[mysqld]
server-id=1
log-bin=mysql-bin
service mysqld restart
注意：
grant replication slave on *.* to 'back'@'192.168.255.134' identified by 'asdf';
show master status;
# 这里看到File和Position需要在从库连接时使用
exit
说明：
从服务器需使用MySQL主服务器上的用户名和密码连接到主站
例如，使用用户back可以从主机(192.168.255.134)连接到master上进行复制操作(grant replication):
创建用户back，密码asdf(需要遵循mysql的密码验证插件级别要求)
主服务器用mysqldump 将待同步的库test被分成sql，传输到从服务器/root/目录
mysqldump -uroot -p test > /root/test.sql
ls /root/test.sql
scp /root/test.sql 192.168.255.134:/root # 密码asdf
从库使用sql同步数据库test的数据
use test; # 从库进mysql的库test
source /root/test.sql # 用sql同步数据
vim /etc/my.cnf
[mysqld]
server-id=2
log_bin=mysql-bin
replicate_do_table=test.user # 需要同步的表,test库其他表不同步
binlog-ignore-db=mysql # 这个是忽略的不需要同步的库，可以不设置
change master to master_host='192.168.255.133',master_user='back',master_password='asdf',master_port=3306,MASTER_LOG_FILE='mysql-bin.000014',MASTER_LOG_POS=748;
# 文件名字和log位置是主库show master status看到的
start slave; # 开启复制功能d
show slave status G;
俩线程都保证运行中，这时候主从库的test数据库的user表就可以保持同步，测试下
6. 异步模式测试
主从test.user表：
修改主库test.user表，从库也会变化：
刷新从库表：
7. 错误的情况
从库先写一条，主库写一条，这时候就不同步了：
错误原因里面写的很清楚：俩条记录 PRIMARY key一样，这时候Slave_SQL_Running: No，从库的SQL线程就会关闭，需要人工介入处理了。
mysql> show slave status G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.255.133
Master_User: back
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000006
Read_Master_Log_Pos: 3670
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 2723
Relay_Master_Log_File: mysql-bin.000006
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table: test.user
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1062
Last_Error: Could not execute Write_rows event on table test.user; Duplicate entry '8' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000006, end_log_pos 3100
Skip_Counter: 0
Exec_Master_Log_Pos: 2854
Relay_Log_Space: 3750
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1062
Last_SQL_Error: Could not execute Write_rows event on table test.user; Duplicate entry '8' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000006, end_log_pos 3100
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 4b5e1b3a-1af8-11ea-ac00-000c29b986f2
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 191210 14:22:39
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)
ERROR:
No query specified
主从的配置在之前的基础上添加开启半同步插件的配置即可
vim /etc/my.cnf
[mysqld]
plugin-load=rpl_semi_sync_master=semisync_master.so
rpl_semi_sync_master_enabled=1
service mysqld restart
查看半同步插件是否有 rpl_semi_sync_master这一行即可
mysql> show plugins;
+----------------------------+----------+--------------------+----------------------+---------+
| Name | Status | Type | Library | License |
+----------------------------+----------+--------------------+----------------------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |
......
| validate_password | ACTIVE | VALIDATE PASSWORD | validate_password.so | GPL |
| rpl_semi_sync_master | ACTIVE | REPLICATION | semisync_master.so | GPL |
+----------------------------+----------+--------------------+----------------------+---------+
46 rows in set (0.00 sec)
查看主库半同步是否运行，ON状态即可
mysql> show status like 'Rpl_semi_sync_master_status';
+-----------------------------+-------+
| Variable_name | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_status | ON |
+-----------------------------+-------+
1 row in set (0.01 sec)
vim /etc/my.cnf
[mysqld]
plugin-load=rpl_semi_sync_slave=semisync_slave.so
rpl_semi_sync_slave_enabled=1
service mysqld restart
查看是半同步模式否在运行
show status like ‘Rpl_semi_sync_slave_status’;
mysql> show status like 'Rpl_semi_sync_slave_status';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON |
+----------------------------+-------+
1 row in set (0.01 sec)
然后主库检查master状态，从库连接即可
主库查看更新的二进制文件状态：
从库根据最新的二进制文件建立连接，开启IO和SQL线程：
tail -100f /var/log/mysql.log
看下日志
STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD;
如果觉得我的文章对您有用，请随意打赏。你的支持将鼓励我继续创作！
