正文
MYSQL主从复制--传统方式
提示：扫一扫查出行【扫一扫了解最新限行尾号】
复制提示
我叫张贺，贪财好色。一名合格的LINUX运维工程师，专注于LINUX的学习和研究，曾负责某中型企业的网站运维工作，爱好佛学和跑步。
个人博客：传送阵
笔者微信：zhanghe15069028807
目录
-
0、MYSQL主从复制概述
- 1、主从复制的作用
- 2、主从复制存在的问题
- 3、主从复制问题的解决办法
- 4、主从复制的原理（面试必问）
-
1、MYSQL主从复制-传统方式
- 1、环境准备
- 2、Master配置
- 3、Slave配置
- 4、报错&解决
0、MYSQL主从复制概述
1、主从复制的作用
-
用于备份，避免影响业务
-
实时灾备，用于切换故障
-
读写分离，提供查询服务
2、主从复制存在的问题
-
当主压力大时，复制会延时
-
主库宕机后，数据可能会丢失
3、主从复制问题的解决办法
-
并行复制，解决延时问题
-
全同步复制，要求5.7版本以上
4、主从复制的原理（面试必问）
- 用于备份，避免影响业务
- 实时灾备，用于切换故障
- 读写分离，提供查询服务
2、主从复制存在的问题
-
当主压力大时，复制会延时
-
主库宕机后，数据可能会丢失
3、主从复制问题的解决办法
-
并行复制，解决延时问题
-
全同步复制，要求5.7版本以上
4、主从复制的原理（面试必问）
- 并行复制，解决延时问题
- 全同步复制，要求5.7版本以上
4、主从复制的原理（面试必问）
我们在上一节做逻辑备份恢复时，其实就是手动将故障前的操作通过 二进制日志再执行一次，就是这么简单，而主从复制的原理就是将这个过程给自动化了，不用我们手工参与了。
1、主数据库必须要将二进制日志打开，这样主的操作才会被记录下来。
2、从数据库实时侦测主数据库二进制文件的变化，一旦变动之后，就会立马将操作下载到本地的中继日志里面。
3、从数据库进程然后再读取中继日志，将其重放备库的数据库之上。
1、MYSQL主从复制-传统方式
1、环境准备
数据库主从复制如下，如需实现多从，将以下实验从案例再配置一次即可。
| 操作系统 | MYSQL版本 | IP | 角色 |
|---|---|---|---|
| centos7 | 5.7 | 192.168.70.160 | Master |
| centos7 | 5.7 | 192.168.70.161 | Slave |
/etc/hosts
文件解析（两台机器一样）
192.168.70.160 master1
192.168.80.161 slave1
2、
Master
配置
//打开二进制日志
[root@master1 ~]# cat /etc/my.cnf
[mysqld]
datadir=/mysql
socket=/var/lib/mysql/mysql.sock
log-bin #打开二进制日志
server-id=160 #给一个server-id
//重启数据库
[root@master1 ~]# systemctl restart mysqld
//准备数据
[root@master1 ~]# mysql -pcba-123
mysql> create database linuxdb;
mysql> use linuxdb;
mysql> create table t1(id int,name varchar(20));
mysql> insert into t1 values (1,'tt');
mysql> insert into t1 values (2,'ttt');
mysql> select * from t1;
+------+------+
| id | name |
+------+------+
| 1 | tt |
| 2 | ttt |
+------+------+
//确认二进制日志有没有开启
[root@master1 ~]# mysql -pcba-123 -e "show variables like 'log_bin%';"
+---------------------------------+--------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------+
| log_bin | ON |
| log_bin_basename | /mysql/master1-bin |
| log_bin_index | /mysql/master1-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
+---------------------------------+--------------------------+
//授权，允许能够远程连接的主机
mysql> grant replication slave,replication client on *.* to 'rep'@'192.168.70.%' identified by 'Rep123.com';
//导出当前数据，主从复制也要基于全备，这次备份是准备复制给slave主机的
[root@master1 ~]# mysqldump -uroot -pcba-123 --all-databases --single-transaction --master-data=1 --flush-logs >/root/db-$(date +%F)-all.sql;
//传输给slave主机
[root@master1 ~]# scp /root/db-2019-11-30-all.sql root@slave1:/root
replication是响应的意思
3、
Slave
配置
//检测是否能登录master的数据库
[root@slave1 ~]# mysql -hmaster1 -urep -p'Rep123.com'
//给一个server-id，不用配置bin-log
[root@slave1 ~]# cat /etc/my.cnf
[mysqld]
datadir=/mysql
socket=/var/lib/mysql/mysql.sock
server-id=161
//重启mysql服务
[root@slave1 ~]# systemctl restart mysqld
//导入数据
[root@slave1 ~]# mysql -uroot -p'cba-123' -e "source /root/db-2019-11-30-all.sql;"
//连接master
mysql> change master to master_host='master1',
master_user='rep',
master_password='Rep123.com';
//启动slave角色
mysql> start slave;
//查看角色是否同步
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: master1
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master1-bin.000001 主的binlog日志名字
Read_Master_Log_Pos: 154 主当前的节点，在主上show master status 先查看节点号，然后现来这查看节点，就可以确定主从复制是否成功
Relay_Log_File: slave1-relay-bin.000003 保存到本地的哪个relay文件当中
Relay_Log_Pos: 371 保存到最后哪个节点了
Relay_Master_Log_File: master1-bin.000001
Slave_IO_Running: Yes #这两个线程一定要是YES才可以
Slave_SQL_Running: Yes
只能在从上执行查询操作，如果执行更改类的操作，主从立马就会断开，注意，注意。
4、报错&解决
报错一：UUID冲突
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.
解决办法：
将从上面的/mysql/auto.cnf更改名字，然后一下数据库
mv /mysql/auto.cnf /mysql/auto.cnf.bak
报错二：数据库重复，跳过即可。
Last_SQL_Error: Error 'Can't create database 'linuxdb'; database exists' on query. Default database: 'linuxdb'. Query: 'create database linuxdb'
解决办法：
mysql> stop slave;
mysql> set global sql_slave_skip_counter = 1;
mysql> start slave;
报错三：
Slave_SQL_Running: NO
Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'ANONYMOUS' at master log db1-bin.000001, end_log_pos 507. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
MariaDB [(none)]> stop slave;
MariaDB [(none)]> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;
MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave status\G
//打开二进制日志
[root@master1 ~]# cat /etc/my.cnf
[mysqld]
datadir=/mysql
socket=/var/lib/mysql/mysql.sock
log-bin #打开二进制日志
server-id=160 #给一个server-id
//重启数据库
[root@master1 ~]# systemctl restart mysqld
//准备数据
[root@master1 ~]# mysql -pcba-123
mysql> create database linuxdb;
mysql> use linuxdb;
mysql> create table t1(id int,name varchar(20));
mysql> insert into t1 values (1,'tt');
mysql> insert into t1 values (2,'ttt');
mysql> select * from t1;
+------+------+
| id | name |
+------+------+
| 1 | tt |
| 2 | ttt |
+------+------+
//确认二进制日志有没有开启
[root@master1 ~]# mysql -pcba-123 -e "show variables like 'log_bin%';"
+---------------------------------+--------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------+
| log_bin | ON |
| log_bin_basename | /mysql/master1-bin |
| log_bin_index | /mysql/master1-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
+---------------------------------+--------------------------+
//授权，允许能够远程连接的主机
mysql> grant replication slave,replication client on *.* to 'rep'@'192.168.70.%' identified by 'Rep123.com';
//导出当前数据，主从复制也要基于全备，这次备份是准备复制给slave主机的
[root@master1 ~]# mysqldump -uroot -pcba-123 --all-databases --single-transaction --master-data=1 --flush-logs >/root/db-$(date +%F)-all.sql;
//传输给slave主机
[root@master1 ~]# scp /root/db-2019-11-30-all.sql root@slave1:/root
replication是响应的意思
Slave
配置
//检测是否能登录master的数据库
[root@slave1 ~]# mysql -hmaster1 -urep -p'Rep123.com'
//给一个server-id，不用配置bin-log
[root@slave1 ~]# cat /etc/my.cnf
[mysqld]
datadir=/mysql
socket=/var/lib/mysql/mysql.sock
server-id=161
//重启mysql服务
[root@slave1 ~]# systemctl restart mysqld
//导入数据
[root@slave1 ~]# mysql -uroot -p'cba-123' -e "source /root/db-2019-11-30-all.sql;"
//连接master
mysql> change master to master_host='master1',
master_user='rep',
master_password='Rep123.com';
//启动slave角色
mysql> start slave;
//查看角色是否同步
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: master1
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master1-bin.000001 主的binlog日志名字
Read_Master_Log_Pos: 154 主当前的节点，在主上show master status 先查看节点号，然后现来这查看节点，就可以确定主从复制是否成功
Relay_Log_File: slave1-relay-bin.000003 保存到本地的哪个relay文件当中
Relay_Log_Pos: 371 保存到最后哪个节点了
Relay_Master_Log_File: master1-bin.000001
Slave_IO_Running: Yes #这两个线程一定要是YES才可以
Slave_SQL_Running: Yes
只能在从上执行查询操作，如果执行更改类的操作，主从立马就会断开，注意，注意。
4、报错&解决
报错一：UUID冲突
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.
解决办法：
将从上面的/mysql/auto.cnf更改名字，然后一下数据库
mv /mysql/auto.cnf /mysql/auto.cnf.bak
报错二：数据库重复，跳过即可。
Last_SQL_Error: Error 'Can't create database 'linuxdb'; database exists' on query. Default database: 'linuxdb'. Query: 'create database linuxdb'
解决办法：
mysql> stop slave;
mysql> set global sql_slave_skip_counter = 1;
mysql> start slave;
报错三：
Slave_SQL_Running: NO
Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'ANONYMOUS' at master log db1-bin.000001, end_log_pos 507. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
MariaDB [(none)]> stop slave;
MariaDB [(none)]> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;
MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave status\G
报错一：UUID冲突
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.
解决办法：
将从上面的/mysql/auto.cnf更改名字，然后一下数据库
mv /mysql/auto.cnf /mysql/auto.cnf.bak
报错二：数据库重复，跳过即可。
Last_SQL_Error: Error 'Can't create database 'linuxdb'; database exists' on query. Default database: 'linuxdb'. Query: 'create database linuxdb'
解决办法：
mysql> stop slave;
mysql> set global sql_slave_skip_counter = 1;
mysql> start slave;
报错三：
Slave_SQL_Running: NO
Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'ANONYMOUS' at master log db1-bin.000001, end_log_pos 507. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
MariaDB [(none)]> stop slave;
MariaDB [(none)]> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;
MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave status\G
