mysql8做主从备份

阅读数:149 评论数: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;

+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000013 | 150 | octopus | | |
+------------------+----------+--------------+------------------+-------------------+

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上填补,然后跳过报错。如果不行,可以重从做一次主从复制。




相关推荐

备份的类型 1、根据是否需要数据库离线 (1)冷备: 需要关mysql服务,请写请求均不允许 (2)温备: 服务在线,但仅支持读请求。 <p

1、创建备份目录 cd /home mkdir backup cd backup 2

一、概述 二、JSON字段的增删改查 1、插入操作 (1)可以直接插入json格式的字符串。 mysql> create table t(c1 json); Query OK, 0 rows affe

Centos7的yum源中没有mysql,为了解决这个问题,我们需要先下载mysql的repo源。 一、下载mysql的repo源并安装 wget http://repo.mysql.com/mysq

一、事务的ACID 1、Atomicity原子性 Transactions are often composed of multiple statements. Atomicity guarantee

一、登录mysql mysql --version -查看Mysql版本 mysql -h hostname -u username -p -h:运行mysql服务器的机器,如果正在该机器上运行此命令

mysql的权限分为4级:全局、数据库、表、列。 1、grant grant privileges [

insert [into] table [(column1,column2,column3,...)] values (val

一、语法 select [optioins] items [into file_details] from tables [where conditions] [group by gr

一、语法 update [low_priority] [ignore] tablename set column1=expression1,... [where condition] [order b