开篇先介绍场景:
领导:“老杨,可能哪位同事误操作,数据又不同步了,你今天弄一下吧!”
我:“好的,王总,还是11点开始弄吗”
领导:“是的,我已经给运营部和客服部打了招呼,11点让他们做好准备!”
心里万马奔腾,今晚又要加班到凌晨了!
熟悉的场景,你可曾遇到!
今天,我就记录下同步的过程,一主两从的架构
192.168.1.105 主 192.168.1.120 从1 192.168.1.130 从2
MySQL版本都是 5.7.25-0ubuntu0.16.04.2
1. 先配置主库
配置文件:
/etc/mysql/mysql.conf.d/mysqld.cnf
(不同系统,不同版本配置文件目录和名称可能不一致,自行寻找)
去掉下面两行的注释,可以根据喜好修改目录
server-id = 1 # 每台服务器都不同log_bin = /var/log/mysql/mysql-bin.logbinlog_do_db = shop #需要同步的db
server_id也可以动态修改
set global server_id=2; 这样不需要重启服务器,但重启后就会失效
2.创建复制专用用户
create user 'rep_user'@'%' identified with mysql_native_password by '123123';
此用户用于主从同步,client连接master。
查看:
mysql> select user,Host from mysql.user ;+------------------+-----------+| user | Host |+------------------+-----------+| rep_user | % || debian-sys-maint | localhost || mysql.session | localhost || mysql.sys | localhost || root | localhost |+------------------+-----------+5 rows in set (0.00 sec)
3.记录主库复制点
为了防止写操作,需要先锁定表
flush tables with read lock;
然后:
Current database: shop*************************** 1. row *************************** File: mysql-bin.000008 Position: 514 Binlog_Do_DB: shop Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.01 sec)
后续就需要从这个位置开始同步了,所以很重要,不能出错!
然后导出数据
mysqldump -hlocalhost -p3306 -uroot -p shop > shop.sql
也可以直接复制MySQL data目录的文件来同步。
4. 同步文件
复制备份文件到从库
scp shop.sql root@192.168.1.120:/data
scp shop.sql root@192.168.1.130:/data
将shop.sql导入到 数据库
需要注意的是,server-uuid不能一样,如果复制文件,需要修改。
5. 配置从库
change master to master_host='192.168.1.105',
master_user='rep_user',master_password='123123', #账号信息
master_log_file='mysql-bin.000008',master_log_pos=154; #master位置信息
change master to记录了要连接主库的信息ip、端口、用户、密码、复制bin文件、偏移量这些信息,这些信息会记录到到文件里,默认在
/var/lib/mysql/master.info里,可以通过变量 master_info_repository设置存放的位置:
mysql> show variables like "%master_info%";+------------------------+-------+| Variable_name | Value |+------------------------+-------+| master_info_repository | FILE |
从库的relay信息在/var/lib/mysql/relay-log.info这个文件里。同样可通过relay_log_info_repository来设置
start slave;
启动sql线程和IO线程,并建立到master的连接,
查看主库连接信息
show slave status ;
显示如下:
mysql> show slave statusG;*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.105 Master_User: rep_user Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000008 Read_Master_Log_Pos: 785 Relay_Log_File: ubuntu-relay-bin.000002 Relay_Log_Pos: 591 Relay_Master_Log_File: mysql-bin.000008 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: 785 Relay_Log_Space: 799 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: 0Master_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: 3fe5881b-6668-11e9-946e-000c29c762e0 Master_Info_File: /var/lib/mysql/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)
参数说明:
有几个参数比较重要哦:
Master_Log_File 当前读取的master binlog
Read_Master_Log_Pos 当前读取的master binlog位置
Relay_Log_File sql 线程正在读取执行的relay日志文件
Relay_Log_Pos sql 线程正在读取执行的relay日志位置
Slave_IO_Running: Yes IO线程的状态
Slave_SQL_Running: Yes sql线程的状态,这两个额状态必须都是yes ,否则就是出错
Seconds_Behind_Master sql线程比IO线程慢多少,在负载大,内容多的场景,这里可能延迟比较大, 一般 的都是0
6.错误记录
6.1错误1
Last_IO_Errno: 1593
Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
这是什么错呢?原来在每个server data目录下有一个auto.cnf ,里面的uuid
不能一样,在进行文件copy时容易出现这个问题,修改一下就可以了。
/var/lib/mysql/auto.cnf
[auto]server-uuid=3fe5881b-6668-11e9-946e-000c29c762e0
6.2 错误2
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
....
Last_IO_Error: Master command COM_REGISTER_SLAVE failed: Access denied for user 'rep_user'@'%' (using password: YES) (Errno: 1045)
根据错误信息应该是权限不对
GRANT REPLICATION SLAVE ON *.* TO 'rep_user'@'%'
flush privileges;
重新配置就可以了
最后,说下这种方式 同步的缺点,就是需要加锁或停服,所以对于不能中断服务的场景就不适用了,那么有没有不停服同步的方法呢,当然是有!后续在详细介绍。