1、MySql主从复制内容介绍

主从复制（也称 AB 复制）允许将来自一个MySQL数据库服务器（主服务器）的数据复制到一个或多个MySQL数据库服务器（从服务器）。复制是异步的，从机不需要永久连接以接收来自主机的更新。根据配置，您可以复制数据库中的所有数据库，所选数据库甚至选定的表。

MySQL中复制的优点包括：

1. 横向扩展解决方案 - 在多个从站之间分配负载以提高性能。在此环境中，所有写入和更新都必须在主服务器上进行。但是，读取可以在一个或多个从设备上进行。该模型可以提高写入性能（因为主设备专用于更新），同时显着提高了越来越多的从设备的读取速度。
2. 数据安全性 - 因为数据被复制到从站，并且从站可以暂停复制过程，所以可以在从站上运行备份服务而不会破坏相应的主数据。
3. 分析 - 可以在主服务器上创建实时数据，而信息分析可以在从服务器上进行，而不会影响主服务器的性能。
4. 远程数据分发 - 您可以使用复制为远程站点创建数据的本地副本，而无需永久访问主服务器。

Replication 的原理：当主库写入文件的时候，从库读取写入文件的SQL，到从库再次执行一次

前提是作为主服务器角色的数据库服务器必须开启二进制日志，由于MySQL不同版本之间的(二进制日志)bin-log格式可能会不一样，建议Master(主)的MySQL版本和Slave(从)的版本尽可能的相同或者更低。MySQL主从是基于binlog，在主机上需开启binlog才能进行主从。

主从过程步骤

1. 主服务器上面的任何修改都会通过自己的 I/O tread(I/O 线程)保存在二进制日志 Binary log 里面。
2. 从服务器上面也启动一个 I/O thread，通过配置好的用户名和密码, 连接到主服务器上面请求读取二进制日志，然后把读取到的二进制日志写到本地的一个Realy log（中继日志）里面。
3. 从服务器上面同时开启一个 SQL thread 定时检查 Realy log(这个文件也是二进制的)，如果发现有更新立即把更新的内容在本机的数据库上面执行一遍。每个从服务器都会收到主服务器二进制日志的全部内容的副本。
4. 从服务器设备负责决定应该执行二进制日志中的哪些语句。除非另行指定，否则主从二进制日志中的所有事件都在从站上执行。如果需要，您可以将从服务器配置为仅处理一些特定数据库或表的事件。

MySQL主从作用

1. 实时灾备，用于故障切换
2. 读写分离，提供查询服务
3. 备份，避免影响业务

2、主从复制操作

环境准备

注意：Centos 是mysqld， 而 Ubantu 是 mysql 服务

2.1、Master主机操作

1、Master节点配置 /etc/my.cnf (master节点执行)

注意：

1. 主节点和从节点必须配置唯一id ，其他配置是可选项
2. centos是 vim /etc/my.cnf
3. ubantu是 vim /etc/mysql/mysql.conf.d/mysqld.cnf

> vim /etc/my.cnf
# 找到[mysqld]添加下面内容
[mysqld]
## 同一局域网内注意要server-id一定要唯一
server-id=100  
## 开启二进制日志功能，可以随便取（关键）
log-bin=mysql-bin
## 复制过滤：过滤掉不需要的数据库，如过滤掉mysql数据库
binlog-ignore-db=mysql
## 为每个session 分配的内存，在事务过程中用来存储二进制日志的缓存
binlog_cache_size=1M
## 主从复制的格式（mixed,statement,row，默认格式是statement）
binlog_format=mixed

# 重启mysql服务，一般配置文件写完后，都需要重启该服务才生效
systemctl restart mysqld
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

2、重启mysql服务，使新配置生效

>  systemctl restart mysqld
1

3、mysql查看是否开启二进制日志文件
默认是OFF没有开启，只有配置了my.cnf的log_bin后，才打开，为ON

mysql> show variables like 'log_%';
1

4、在master服务器授权slave服务器的同步权限(master节点执行)

mysql > mysql -uroot -p密码
# 授予slave服务器可以同步master服务
mysql > grant replication slave, replication client on *.* to 'root'@'slave服务的ip' identified by '从机访问主机的密码';
mysql > flush privileges;
# 查看MySQL现在有哪些用户及对应的IP权限(可以不执行，只是一个查看)
mysql > select user,host from mysql.user;


#如：
mysql> grant replication slave, replication client on *.* to 'root'@'130.211.18.183' identified by '3456789';
mysql > flush privileges;
# 'root'     ：就是从机（Slave）要访问主机（Master）的用户名；
# '130.211.18.183'：就是从机（Slave）的IP地址；
# '3456789'     ：就是从机（Slave）访问主机（Master）的密码；是你自己新设置的密码，不是主机的密码。
1
2
3
4
5
6
7
8
9
10
11
12
13
14

若出现 ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

1. 数据库默认密码规则必须携带大小写字母、特殊符号，字符长度大于8否则会报错。
2. 因此设定较为简单的密码时需要首先修改set global validate_password_policy和_length参数值。

mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password_length=1;
Query OK, 0 rows affected (0.00 sec)
1
2
3
4

5、查询master服务的binlog文件名和位置(master节点执行)

mysql > show master status;
1

日志文件名：mysql-bin.000003

复制的位置：154

2.2、Slave从机操作

1、Slave节点配置 vim /etc/mysql/mysql.conf.d/mysqld.cnf

>  vim /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
## 设置server_id,注意要唯一
server-id=102
## 开启二进制日志功能，以备Slave作为其它Slave的Master时使用
log-bin=mysql-slave-bin
## relay_log配置中继日志
relay_log=edu-mysql-relay-bin
##复制过滤：不需要备份的数据库，不输出（mysql库一般不同步）
binlog-ignore-db=mysql
## 如果需要同步函数或者存储过程
log_bin_trust_function_creators=true
## 为每个session 分配的内存，在事务过程中用来存储二进制日志的缓存
binlog_cache_size=1M
## 主从复制的格式（mixed,statement,row，默认格式是statement）
binlog_format=mixed
## 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。
## 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
slave_skip_errors=1062

# 重启mysql服务，一般配置文件写完后，都需要重启该服务才生效
systemctl restart mysql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

2、重启mysql服务，使新配置生效

>  systemctl restart mysqld
1

3、slave进行关联master节点

1.进入到slave节点：

mysql > mysql -uroot -p密码
1

2.开始绑定

mysql> change master to master_host='master服务器ip', master_user='root', master_password='master密码', master_port=3306, master_log_file='master二进制日志文件名',master_log_pos=master二进制日志文件Position;

# 如
mysql> change master to master_host='120.118.55.220', master_user='root', master_password='3456789', master_port=3306, master_log_file='mysql-bin.000003',master_log_pos=2897;
1
2
3
4

4、在slave节点上查看主从同步状态

1.启动主从复制

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
1
2

2.再查看从机的主从同步状态

mysql> show slave status\G;
# \G是换行查看，不换行查看的话，即没有格式化内容，就比较凌乱。
1
2

3.当Slave_IO_Running和Slave_SQL_Running都是 Yes 的时候，说明主从能够正常同步了

其他命令 (slave节点执行)

# 停止复制
mysql> stop slave;
1
2

5、主从复制测试

1、查看主节点（Master）中存在的数据库：

2、查看在从节点（Slave）中存在的数据库：

3、我们在主节点（Master）中创建Student数据库：

mysql > create database Student charset utf8;
mysql> show databases;
1
2

4、我们在从节点（Slave）中查看是否有 Student 数据库的存在：

5、测试完成，这时候，你在主库的Student创建的数据，在从库也会看到！

小总结

1. 在master下创建数据库和表，或者修改和新增，删除记录都会进行同步(master节点执行)
2. 点击查看slave节点信息(slave节点执行)
3. 在主从复制操作的时候，不要基于去创建数据库或者相关操作，然后又马上去删除。这样会造成主从复制的pos改变，而造成复制失败，如果出现此类问题，查看下面的常见问题排查。
4. 注意从机添加数据，则以从机为准，主机不会更新从机插入的数据，当主机添加id为2的数据，从机不会更新该数据，但添加别的新的数据后，从机自动更新该数据。
5. 如主机从机都只有id等于1的数据，从机添加id等于2，name=zhangsan,主机不会发生任何变换，而主机这时候添加id=2,name=lisi，从机也不会发生任何变化。但是主机添加id=3,name=xiaoming，从机跟着进行复制，表里也有id=3,name=xiaoming

2.3、主从复制相关问题排查

1、主从复制Connecting问题

使用start slave开启主从复制过程后，如果SlaveIORunning一直是Connecting，则说明主从复制一直处于连接状态，这种情况一般是下面几种原因造成的，我们可以根据 Last_IO_Error提示予以排除。

1. 网络不通
   检查ip,端口
2. 密码不对
   检查是否创建用于同步的用户和用户密码是否正确
3. pos不对
   检查Master的 Position

2、MYSQL镜像服务器因错误停止的恢复 —Slave_SQL_Running: No

# 在从机先stop slave，然后执行了一下提示的语句
mysql > stop slave;
mysql > set global sql_slave_skip_counter=1;
mysql > start slave; 
mysql > show slave status\G ;
1
2
3
4
5

3、从MYSQL服务器Slave_IO_Running: No的解决

• master节点执行，获取日志文件和post

mysql > show master status;
1

• slave节点进行重新绑定

mysql > stop slave;mysql > CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000008', MASTER_LOG_POS=519086591; mysql > start slave;
1

造成这类问题的原因一般是在主从复制的时候，基于创建表，然后又马上去删除和操作了数据表或者表，导致master节点的pos位置发生改变！