mysql8做主从备份

阅读数:266 评论数:0

跳转到新版页面

分类

数据库

正文

如果主库已经投入使用了，此时如果需要主从同步，需要导出所表结构和数据，然后导入到从库中。这时需要注意修改/var/lib/mysql/auto.cnf中uuid。

master配置

0、修改配置文件 vim /etc/my.cnf

[mysqld]
# 日志文件名
log-bin = mysql-bin
# 要同步的数据库
binlog-do-db = my_db_name
# 主数据库端ID号
server-id = 1
# 跳过checksum的事件
binlog_checksum = none

systemctl restart mysqd.service

重启。

1、创建一个专门为复制服务的用户

create user 'repl'@'%' identified by '123456';
grant replication slave on *.* to 'repl'@'%';
flush privileges;

mysql8.0中默认使用caching_sha2_password插件，由于一些原因，可能我们配置的使用mysql_native_password。

create user 'repl'@'%' identified with mysql_native_password by '123456';

2、查看master 状态

flush tables with read lock; // 记得unlock tables
show master status;

slave端配置

1、配置文件 vim /etc/my.cnf

[mysqld]
# 主数据库ID号
server-id = 2
slave_sql_verify_checksum = NONE
replicate-do-db = my_db_name
#replicate-rewrite-db = my_db_name -> new_db_name

重启。

2、执行同步

change master to master_host=‘120.77.xx.xx’,master_user=‘repl’,master_password=‘111111’,master_log_file=‘mysql-bin.000013’,master_log_pos=150;

start slave;

3、查看同步状态

show slave status\G;

需要slave_io_running和slave_sql_running都是yes，表示成功。slave_sql_running负责与主机的io通信，slave_io_running负责自己的slave mysql进程。

slave_io_running: no的原因：

（1）mysql有个uuid，然而uuid是唯一的，在物理备份时，可能没有改，需要修改/var/lib/mysql/auto.cnf中的uuid。

（2）server-id是否唯一。

如果要停止同步

stop slave

问题解决：

1、主从库不同步的解决思路

方法一：忽略错误后，继续同步

该方法适用于主从库数据相差不大，或者要求数据可以不完全统一的情况，数据要求不严格的情况。
解决：

stop slave; 
#表示跳过一步错误，后面的数字可变 
set global sql_slave_skip_counter =1; 
start slave; 
之后再用mysql> show slave status\G 查看： 
Slave_IO_Running: Yes 
Slave_SQL_Running: Yes 
ok，现在主从同步状态正常了。。。

方式二：重新做主从，完全同步

（1）先进入主库，进行锁表，防止数据写入

mysql> flush tables with read lock;

（2）把数据备份到mysql.bak.sql文件

[root@server01 mysql]#mysqldump -uroot -p -hlocalhost > mysql.bak.sql

（3）把mysql备份文件传到从库机器，进行数据恢复

[root@server01 mysql]# scp mysql.bak.sql root@192.168.128.101:/tmp/ 
mysql> stop slave; 
mysql> source /tmp/mysql.bak.sql 
change master to master_host = '192.168.128.100', master_user = 'rsync', master_port=3306, master_password='', master_log_file = 'mysqld-bin.000001', master_log_pos=3260; 
mysql> start slave; 
mysql> show slave status\G 查看： 
Slave_IO_Running: Yes 
Slave_SQL_Running: Yes

2、在master上删除一条记录，而slave上找不到

Last_SQL_Error: Could not execute Delete_rows event on table hcy.t1;
Can't find record in 't1',
Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND;
the event's master log mysql-bin.000006, end_log_pos 254

解决方法：

从机可以直接跳过。

stop slave;
set global sql_slave_skip_counter=1;
start slave;

3、主键重复。在slave已经有该记录，又在master上插入同一条记录。

Last_SQL_Error: Could not execute Write_rows event on table hcy.t1;
Duplicate entry '2' for key 'PRIMARY',
Error_code: 1062;
handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000006, end_log_pos 924

解决方法：

从库中删除对应的数据。

stop slave;
delete from t1 where id=xx;
start slave;

4、master上更新一条记录，而slave上找不到，丢失了数据。

Last_SQL_Error: Could not execute Update_rows event on table hcy.t1;
Can't find record in 't1',
Error_code: 1032;
handler error HA_ERR_KEY_NOT_FOUND;
the event's master log mysql-bin.000010, end_log_pos 794

把丢失的数据在slave上填补，然后跳过报错。如果不行，可以重从做一次主从复制。

秒吧学习