MySql主从复制

1.复制的基本原理

slave会从master读取binlog来进行数据同步
三步骤+原理图

MySQL复制三步骤：

1. master将写操作记录到二进制日志（binary log）。这些记录过程叫做二进制日志事件，binary log events；
2. slave将master的binary log events拷贝到它的中继日志（relay log）； 从服务器I/O线程将主服务器的二进制日志读取过来记录到从服务器本地文件，然后SQL线程会读取relay-log日志的内容并应用到从服务器，从而使从服务器和主服务器的数据保持一致！
3. slave重做中继日志中的事件，将改变应用到自己的数据库中。 MySQL复制是异步的且串行化的，而且重启后从接入点开始复制。

复制的最大问题（延时）

2.复制的基本原则

1. 每个slave只有一个master
2. 每个slave只能有一个唯一的服务器ID
3. 每个master可以有多个salve

3.一主一从常见配置

3.1.主机配置文件

建议mysql版本一致且后台以服务运行

logbin格式：

binlog_format=STATEMENT（默认）：数据操作的时间，同步时不一致
binlog_format=ROW：批量数据操作时，效率低
binlog_format=MIXED：是以上两种level的混合使用，有函数用ROW，没函数用STATEMENT，但是无法识别系统变量
1
2
3

server-id=1
log-bin=mysql-bin
binlog-ignore-db=mysql
binlog-ignore-db=infomation_schema
binlog-do-db=mytestdb
binlog_format=STATEMENT
1
2
3
4
5
6

先不要创建mytestdb数据库。主从搭建好了再创建

3.2.从机配置文件

主从所有配置项都配置在[mysqld]节点下，且都是小写字母
[必须]从服务器唯一ID：server-id=2
[可选]启用中继日志：relay-log=mysql-relay

server-id=2
relay-log=mysql-relay
1
2

3.3.主从机重启配置生效

systemctl restart mysqld
1

3.4.主从机都关闭防火墙

systemctl stop firewalld
1

3.5.在主机上建立帐户并授权slave

GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY '123456';
flush privileges;
grant all privileges on *.* to root@'%'  identified by 'root'; 
flush privileges;
给root 用户授予远程访问权限
查询master的状态: show master status;
1
2
3
4
5
6

记下File和Position的值
执行完此步骤后不要再操作主服务器MYSQL，防止主服务器状态值变化

3.6.在Linux从机上配置需要复制的主机

CHANGE MASTER TO MASTER_HOST='主机ip地址',
MASTER_USER='X',MASTER_PASSWORD='X',
MASTER_LOG_FILE='mysql-bin.具体数字',MASTER_LOG_POS=具体值; 
1
2
3

启动从服务器复制功能

start slave;
1

show slave status\G;
1

下面两个参数都是Yes，则说明主从配置成功！

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
1
2

如果出现：Slave_IO_Running：No
则可能的解决办法是：

1）停止stop slave; 再启动start slave;看是否能正常运行 2）两个服务器的防火墙是否关闭，是否互相能ping通
3）配置文件是否正确、是否重启了服务器
4）连接主机的语句是否正确
5) 可能是uuid 一致(master,slave uuid) vim
/var/lib/mysql/auto.cnf 下更改uuid，重启服务

3.7.主机新建库、新建表、insert记录，从机复制

CREATE DATABASE mytestdb;
CREATE TABLE mytbl(id INT,NAME VARCHAR(16));
INSERT INTO mytbl VALUES(1, 'zhang3');
INSERT INTO mytbl VALUES(2, 'li4');
1
2
3
4

3.8.如果停止从服务复制功能，重新配置主从

在从机上执行。功能说明：停止I/O 线程和SQL线程的操作。

mysql> stop slave; 
1

在从机上执行。功能说明：用于删除SLAVE数据库的relaylog日志文件，并重新启用新的relaylog文件。

mysql> reset slave;
1

在主机上执行。功能说明：删除所有的binglog日志文件，并将日志索引文件清空，重新开始所有新的日志文件。
用于第一次进行搭建主从库时，进行主库binlog初始化工作；

mysql> reset master;
1