MySQL(十一)--MySQL主从复制

MySQL(十一)–MySQL主从复制

一、什么是主从复制：

主从复制，是用来建立一个和主数据库完全一样的数据库环境，称为从数据库；主数据库一般是实时的业务数据库

将主数据库中的DDL和DML操作通过二进制日志传输到从数据库上，然后将这些日志重新执行（重做）；从而使得从数据库的数据与主数据库保持一致。

DDL：操作数据库、表、列等（这些对象进行操作），使用的关键字：CREATE、 ALTER、 DROP

DML：DML是对表中的数据进行增、删、改的操作。不要与DDL混淆了

二 、为什么要做主从复制？

1. 数据同步备份 : master发生故障后，可以马上切换到slave，降低服务风险

2. 读写分离 : 在业务复杂的系统中，有这么一个情景，有一句sql语句需要锁表，导致暂时不能使用读的服务，那么就很影响运行中的业务，使用主从复制，让主库负责写，从库负责读，这样，即使主库出现了锁表的情景，通过读从库也可以保证业务的正常运作可以把写操作放在master，读取操作放在slave，减轻单一数据库的操作压力

3. 架构的扩展。业务量越来越大，I/O访问频率过高，单机无法满足，此时做多库的存储，降低磁盘I/O访问的频率，提高单个机器的I/O性能。

三、主从复制的作用：

1、主数据库出现问题，可以切换到从数据库。
2、可以进行数据库层面的读写分离。
3、可以在从数据库上进行日常备份。

四、复制过程：

Binary log：主数据库的二进制日志。
Relay log：从服务器的中继日志。

【第一步】：master在每个事务更新数据完成之前，将该操作记录串行地写入到binlog（二进制日志）文件中。

【第二步】：salve开启一个I/O Thread，该线程在master打开一个普通连接，主要工作是binlog dump process。如果读取的进度已经跟上了master，就进入睡眠状态并等待master产生新的事件。I/O线程最终的目的是将这些事件写入到中继日志中。（也就是第二步中，备库将主库上的日志复制到自己的中继日志中）

【第三步】：SQL Thread会读取中继日志，并顺序执行该日志中的SQL事件，从而与主数据库中的数据保持一致。

为什么主服务器上已经有了Binary log，为什么还要记录Relay log？（搬过来直接记录不就可以了吗？）

因为从计算机设计合理的角度来说，我们是需要队列去操作的，由于网络的原因Binary log是不能一口气传到从服务器上来的，为不影响从服务器去执行其它的任务，new 了一个I/O Thread线程来接受Binary log中的数据存储到 Relay log当中。Relay log就类似于生产者（I/O Thread）与消费者(SQL Thread)里面的管道，缓存Binary log中传来的数据，Relay log是存储在从服器的缓存当中的，因此它的开销是很小的。SQL Thread相当于消费者从换从中读取数据，将主服务器传来的指令做到从服务器的数据库中。同过这种生产者消费者模式 ，不影响消费者的性能。

五、主从复制的具体操作：

这里的具体操作是转载自：https://www.javazhiyin.com/20158.html

1.实现MySQL主从复制需要进行的配置：

主服务器：

• 开启二进制日志
• 配置唯一的server-id
• 获得master二进制日志文件名及位置
• 创建一个用于slave和master通信的用户账号

从服务器：

• 配置唯一的server-id
• 使用master分配的用户账号读取master二进制日志
• 启用slave服务

2、准备工作：

• 1.主从数据库版本最好一致
• 2.主从数据库内数据保持一致
• 主数据库：182.92.172.80 /linux
• 从数据库：123.57.44.85 /linux

3、主数据库master修改：

（1）.修改mysql配置：

找到主数据库的配置文件my.cnf(或者my.ini)，我的在/etc/mysql/my.cnf,在[mysqld]部分插入如下两行：

[mysqld]
log-bin=mysql-bin #开启二进制日志
server-id=1 #设置server-id

（2）.重启mysql，创建用于同步的用户账号：

打开mysql会话shell>
mysql -hlocalhost -uname -ppassword

创建用户并授权：用户：rel1密码：slavepass

mysql> CREATE USER 'repl'@'123.57.44.85' IDENTIFIED BY 'slavepass';#创建用户
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'123.57.44.85';#分配权限
mysql>flush privileges;   #刷新权限

（3）.查看master状态，记录二进制文件名(mysql-bin.000003)和位置(73)：

mysql > SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 73       | test         | manual,mysql     |
+------------------+----------+--------------+------------------+

4、从服务器slave修改：

（1）.修改mysql配置：

同样找到my.cnf配置文件，添加server-id

[mysqld]
server-id=2 #设置server-id，必须唯一

（2）.重启mysql，打开mysql会话，执行同步SQL语句(需要主服务器主机名，登陆凭据，二进制文件的名称和位置)：

mysql> CHANGE MASTER TO
    ->     MASTER_HOST='182.92.172.80',
    ->     MASTER_USER='rep1',
    ->     MASTER_PASSWORD='slavepass',
    ->     MASTER_LOG_FILE='mysql-bin.000003',
    ->     MASTER_LOG_POS=73;

（3）.启动slave同步进程：

mysql>start slave;

（4）.查看slave状态：

mysql> show slave statusG;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 182.92.172.80
                  Master_User: rep1
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000013
          Read_Master_Log_Pos: 11662
               Relay_Log_File: mysqld-relay-bin.000022
                Relay_Log_Pos: 11765
        Relay_Master_Log_File: mysql-bin.000013
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
        ...

当Slave_IO_Running和Slave_SQL_Running都为YES的时候就表示主从同步设置成功了。接下来就可以进行一些验证了，比如在主master数据库的test数据库的一张表中插入一条数据，在slave的test库的相同数据表中查看是否有新增的数据即可验证主从复制功能是否有效，还可以关闭slave（mysql>stop slave;）,然后再修改master，看slave是否也相应修改（停止slave后，master的修改不会同步到slave），就可以完成主从复制功能的验证了。

还可以用到的其他相关参数：

master开启二进制日志后默认记录所有库所有表的操作，可以通过配置来指定只记录指定的数据库甚至指定的表的操作，具体在mysql配置文件的[mysqld]可添加修改如下选项：

# 不同步哪些数据库  
binlog-ignore-db = mysql  
binlog-ignore-db = test  
binlog-ignore-db = information_schema  

# 只同步哪些数据库，除此之外，其他不同步  
binlog-do-db = game 

小结：

主库：

• 开启binlog功能
• 确保所有实例server_id不同
• 授权复制的用户rep(grant replication slave on. to rep ‘172.16.1.%’ identified by ‘123456’)
• 锁表（flush table with resd lock;）,查看binlog文件及位置点（show master status;/-master-data=2）
• 新开窗口导出全备（mysqldump -uroot -p123456 -A -B -events -s/data/3306/mysql.sock | gzip >/server/backup/req_$ (data+%F).sql.gz）
• 解锁，开放用户写入功能（unlock table）

从库：

• 确保所有实例server_id不同
• 把主库的全备导入到主库
• 找位置点，配置master.info

change master to
master_host='172.16.1.52'
master_port = 3306
master_password = '123456'
master_log_file='master_bin.000010'
master_log_pos=2892;

• start slave; show slave status \G（若出现slave中的两个线程喜状态为yes，基本就成功了）

六、 MySQL主从复制存在的问题及解决方法：

关于一些复制的问题及解决方案在MySQL书的477-478页有相应的说明

mysql主从复制存在的问题：

• 主库宕机后，数据可能丢失
• 从库只有一个sql Thread，主库写压力大，复制很可能延时

解决方法：

• 半同步复制—解决数据丢失的问题 （详情见https://blog.csdn.net/qfc8930858/article/details/90321720）
• 并行复制----解决从库复制延迟的问题 参考：https://blog.csdn.net/jh993627471/article/details/79009313

这里说一下：影响主从延迟的因素：

• 主库写入二进制日志的时间------>修改为：控制主库的事务大小，分割大事务
• 二进制日志的传输时间------>改为：使用mixed日志格式
• 默认情况下，从只有一个SQL线程，主上并发的修改在从上变成了串行------>改为：使用多线程复制

再一个问题：

假设发生了突发事件，master宕机，现在的需求是要将192.168.1.102提升为主库，另外一个为从库：

步骤：

• 1.确保所有的relay log全部更新完毕，在每个从库上执行stop slave io_thread; show processlist;直到看到Has read all relay log,则表示从库更新都执行完毕了
• 2.登陆所有从库，查看master.info文件，对比选择pos最大的作为新的主库，这里我们选择192.168.1.102为新的主库
• 3.登陆192.168.1.102，执行stop slave; 并进入数据库目录，删除master.info和relay-log.info文件,配置my.cnf文件，开启log-bin,如果有log-slaves-updates和read-only则要注释掉，执行reset master
• 4.创建用于同步的用户并授权slave，同第五大步骤
• 5.登录另外一台从库，执行stop slave停止同步
• 6.根据第七大步骤连接到新的主库
• 7.执行start slave;
• 8.修改新的master数据，测试slave是否同步更新

感谢并参考：

https://www.javazhiyin.com/20158.html

https://blog.csdn.net/zfl589778/article/details/51441719

https://blog.csdn.net/qfc8930858/article/details/90319519

本文地址：https://blog.csdn.net/chen772209/article/details/107465316