mysql5 和 8 可以互为主从 mysql5.7主从切换

转载

feiry 2024-08-09 19:23:14

文章标签 mysql5 和 8 可以互为主从 mysql5.7主从方案 mysql SQL sql 文章分类 MySQL 数据库

高可用架构方案

负载均衡:有一定的高可用性

LVS Nginx

主备系统:有高可用性,但是需要切换,是单活的架构

KA , MHA, MMM

真正高可用(多活系统)

NDB Cluster Oracle RAC Sysbase cluster , InnoDB Cluster(MGR),PXC , MGC

主从复制前提

1、两台以上mysql实例 ,server_id,server_uuid不同

2、主库开启二进制日志

3、专用的复制用户

4、保证主从开启之前的某个时间点,从库数据是和主库一致(补课)

5、告知从库,复制user,passwd,IP port,以及复制起点(change master to)

6、线程(三个):Dump thread IO thread SQL thread 开启(start slave)

主从复制过程描述

1.change master to 时，ip pot user password binlog position写入到master.info进行记录

2. start slave 时，从库会启动IO线程和SQL线程

3.IO_T，读取master.info信息，获取主库信息连接主库

4. 主库会生成一个准备binlog DUMP线程，来响应从库

5. IO_T根据master.info记录的binlog文件名和position号，请求主库DUMP最新日志

6. DUMP线程检查主库的binlog日志，如果有新的，TP(传送)给从从库的IO_T

7. IO_T将收到的日志存储到了TCP/IP 缓存，立即返回ACK给主库，主库工作完成

8.IO_T将缓存中的数据，存储到relay-log日志文件,更新master.info文件binlog 文件名和postion，IO_T工作完成

9.SQL_T读取relay-log.info文件，获取到上次执行到的relay-log的位置，作为起点，回放relay-log

10.SQL_T回放完成之后，会更新relay-log.info文件。

11. relay-log会有自动清理的功能。

细节：

1.主库一旦有新的日志生成，会发送“信号”给binlog dump ，IO线程再请求

过程

准备一个多实例环境

这里使用3307(主) 3308(从)

3307操作内容

1、主库授权复制用户rep

[root@db1 3307]# mysql -uroot -p -S /opt/data/3307/mysql.sock
grant replication slave on *.* to rep@'11.11.11.19' identified by 'YANqi123!';
mysql> show grants for rep@'11.11.11.19';
+-------------------------------------------------------+
| Grants for rep@11.11.11.19 |
+-------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'rep'@'11.11.11.19' |
+-------------------------------------------------------+
1 row in set (0.00 sec)
mysql> flush privileges;

2、锁表备份

mysql> flush table with read lock;

注：锁表后不能退出窗口，需要另开连接窗口。

3、导出备份

mysqldump -uroot -pv,qZo#%?q0z* -A -S /opt/data/3307/mysql.sock --master-data=2 --single-transaction -R --triggers|gzip >/data/backup/rep_bak_$(date +%F).sql.gz

如果不在同一台服务器上，备份出来的数据拷贝到3308服务器上

4、解锁，开放用户写入功能。

mysql> unlock table;

Query OK, 0 rows affected (0.00 sec)

3308操作内容

1、把主库的全备导入到从库

[root@db02 backup]# gzip -cd rep_bak_2016-08-28.sql.gz >rep.sql

2、重置从数据库

[root@db1 3308]# mysql -uroot -pv,qZo#%?q0z* -S /opt/data/3308/mysql.sock
mysql> reset slave all;
[root@db1 ~]# grep mysql-bin.00 /data/backup/rep_bak_2020-10-21.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=2034;

3、导入数据

mysql> source /data/backup/rep.sql

或

mysql -uroot -pv,qZo#%?q0z* -S /opt/data/3308/mysql.sock

4、执行change master to语句，生成master.info文件

需mysql终端执行如下语句；(主库的信息)

mysql> change master to
-> MASTER_HOST='11.11.11.19', #主库地址
-> MASTER_PORT=3307, #主库端口
-> MASTER_USER='rep', #同步的账号
-> MASTER_PASSWORD='YANqi123!', #同步账号的密码
-> MASTER_LOG_FILE='mysql-bin.000004', #全量备份后，生成的binlog文件名
-> MASTER_LOG_POS=2034 #全量备份后的位置点
-> MASTER_CONNECT_RETRY=10; #主从连接失败，间隔多久后重试。
Query OK, 0 rows affected, 2 warnings (0.02 sec)

5、开启从库开关

mysql> start slave;

测试：

主库创建数据库或表后，在从库上查是否有同步过来

查看同步状态

mysql -uroot -pv,qZo#%?q0z* -S /opt/data/3308/mysql.sock -e "show slave status\G;" |egrep "Yes|Seconds_Behind_Master"

Slave_IO_Running: Yes #客户端IO进程的工作状态

Slave_SQL_Running: Yes #客户端sql进程的工作状态

Seconds_Behind_Master: 0 #数据同步延时，0代表是实时同步的，主库比从库延迟多少秒

主从状态说明

主库

如果显示非一下信息表示主从有问题

mysql> show full processlist;
+----+------+-------------------+------+-------------+-------+---------------------------------------------------------------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-------------------+------+-------------+-------+---------------------------------------------------------------+-----------------------+
| 9 | rep | 11.11.11.19:33664 | NULL | Binlog Dump | 11232 | Master has sent all binlog to slave; waiting for more updates | NULL |
| 11 | root | localhost | NULL | Query | 0 | starting | show full processlist |
+----+------+-------------------+------+-------------+-------+---------------------------------------------------------------+-----------------------+
2 rows in set (0.00 sec)

从库

mysql> show slave status\G

*************************** 1. row ***************************

主库相关信息

Slave_IO_State: Waiting for master to send event

Master_Host: 11.11.11.19

Master_User: rep

Master_Port: 3307

Connect_Retry: 60

Master_Log_File: mysql-bin.000004

Read_Master_Log_Pos: 2626

从库中继日志应用状态

Relay_Log_File: db1-relay-bin.000002

Relay_Log_Pos: 912

从库复制线程有关状态

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

过滤复制状态

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

延时从库有关的状态(人为)

SQL_Delay: 0

SQL_Remaining_Delay: NULL

主从延时相关状态(非人为)

Seconds_Behind_Master: 0

GTID 复制有关的状态

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

FAQ:报错处理

mysql> show slave status\G

Replicate_Wild_Ignore_Table:

Last_Errno: 1007

Last_Error: Error 'Can't create database 'test'; database exists' on query. Default database: 'test'. Query: 'create database test'

Skip_Counter: 0

Exec_Master_Log_Pos: 4244

Relay_Log_Space: 18413

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 1007

Last_SQL_Error: Error 'Can't create database 'test'; database exists' on query. Default database: 'test'. Query: 'create database test'

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1

1 row in set (0.00 sec)

方法一

mysql> stop slave; # 临时关闭
Query OK, 0 rows affected (0.00 sec
# 将同步指针，向下移动一个，如果多次不同步，可以重复操作
mysql> set global sql_slave_skip_counter=1;
# 也可以像下面一样移除多个；
mysql> set global sql_slave_skip_counter=2;
Query OK, 0 rows affected (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

方法二

配置文件my.cnf修改如下，根据可以忽略的错误号事先在配置文件中添加好，跳过指定的不影响与业务数据的错误。

具体的错误代码可看官方手册。

grep slave-skip /data/3307/my.cnf

slave-skip-errros = 1032,1062,1007

常见错误代码：

1007：对象已存在

1032：无法执行DML

1062: 主键冲突或约束冲突

其他可能引起复制故障的问题：

1、mysql自身的原因及认为重复插入数据。

2、不同版本的数据库版本，引起不能同步。