CentOS8.2 MySQL8主从复制的实现
作者：温新
时间：2021-10-25
之前基于MySQL5.7.26实现过主从复制，详记windows下MySQL主从复制的实现。无论是MySQL5.x还是MySQL8.x，主从复制实现的原理都是一样的。但是，MySQL8有点不同了。安装完成后，就会发现MySQL的配置文件没有了，这一个，另外配置文件从my.ini变成了my.cnf。
MySQL8.x中，它的配置文件在哪？my.cnf配置文件在/etc目录下。下面简单记录Linux下实现MySQL主从复制。
所有记录，都是亲自实测并实现后记录的。MySQL8实现主从复制，关于my.cny的配置就出现的很多问题，最后给出一个最简单的记录实现。
1、MySQL主从复制原理
- 1）master主库开启bin-log二进制日志文件，当有对MySQL操作的事件时，会开启一个线程，把内容写入到二进制日志，然后再执行相关操作；
- 2）slave从库连接到主库，启动复制后，从库创建一个I/O线程，监听主库bin-log的变化，当有变化时将内容写入到slave的
relay log中继日志；还会创建一个SQL线程用于监听relay log的变化，从中读取内容到数据库。
2、MySQL主从配置注意点
- 操作系统一致（不一致也可以实现）
- 数据库版本最好一致
3、我使用的环境说明
虚拟机中准备两个数据库。我使用源码安装。
- 系统：CentOS8.2
- 数据库：MySQL-8.0.22
- IP地址：主库（master) 192.168.157.129； 从库（slave)192.168.157.132
4、实现主从复制
4.1、master主库的配置
第一步：新建配置文件
vim /etc/my.cnf
第二步：添加配置内容到my.cnf保存并退出
<span style="box-sizing: border-box;padding-right: 0.1px">[mysqld]</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">log-bin=mysql-bin</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">server-id=1</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"># 指定从库同步的数据库</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">binlog-do-db=master_slave_test</span>
第三步：重启MySQL
systemctl restart mysql.service
第四步：创建用于从库复制的用户
<span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(84, 110, 122)"># 创建从库复制的用户</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">mysql<span style="box-sizing: border-box">></span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">create</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">user</span> <span style="box-sizing: border-box;color: rgb(195, 232, 141)">'myslave'</span><span style="box-sizing: border-box;color: rgb(128, 203, 196)">@'182.168.157.132' identified with mysql_native_password by '123456'</span><span style="box-sizing: border-box">;</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(84, 110, 122)"># 分配壄</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">mysql<span style="box-sizing: border-box">></span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">grant</span> replication <span style="box-sizing: border-box;color: rgb(199, 146, 234)">slave</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">on</span> <span style="box-sizing: border-box">*</span>.<span style="box-sizing: border-box">*</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">to</span> <span style="box-sizing: border-box;color: rgb(195, 232, 141)">'myslave'</span><span style="box-sizing: border-box;color: rgb(128, 203, 196)">@'182.168.157.132'</span><span style="box-sizing: border-box">;</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(84, 110, 122)"># 刷新</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">mysql<span style="box-sizing: border-box">></span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">flush</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">privileges</span><span style="box-sizing: border-box">;</span></span>
第五步：查看master主库 binary log文件名和position位置
需要登录到MySQL中。
<span style="box-sizing: border-box;padding-right: 0.1px">mysql<span style="box-sizing: border-box">></span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">show</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">master</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">status</span><span style="box-sizing: border-box">;</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box">+------------------+----------+-------------------+------------------+-------------------+</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box">|</span> File <span style="box-sizing: border-box">|</span> Position <span style="box-sizing: border-box">|</span> Binlog_Do_DB <span style="box-sizing: border-box">|</span> Binlog_Ignore_DB <span style="box-sizing: border-box">|</span> Executed_Gtid_Set <span style="box-sizing: border-box">|</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box">+------------------+----------+-------------------+------------------+-------------------+</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box">|</span> mysql<span style="box-sizing: border-box">-</span>bin<span style="box-sizing: border-box;color: rgb(247, 118, 105)">.000003</span> <span style="box-sizing: border-box">|</span> <span style="box-sizing: border-box;color: rgb(247, 118, 105)">379</span> <span style="box-sizing: border-box">|</span> master_slave_test <span style="box-sizing: border-box">|</span> <span style="box-sizing: border-box">|</span> <span style="box-sizing: border-box">|</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box">+------------------+----------+-------------------+------------------+-------------------+</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(247, 118, 105)">1</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">row</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">in</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">set</span> <span style="box-sizing: border-box;color: rgb(153, 153, 119)">(</span><span style="box-sizing: border-box;color: rgb(247, 118, 105)">0.00</span> sec<span style="box-sizing: border-box;color: rgb(153, 153, 119)">)</span></span>
4.2、slave从库配置
第一步：新建配置文件
vim /etc/my.cnf
第二步：添加配置内容到my.cnf保存并退出
<span style="box-sizing: border-box;padding-right: 0.1px">[mysqld]</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">server-id=2</span>
第三步：重启MySQL
systemctl restart mysql.service
第四步：在slave从库上设置连接主库参数。需要登录从库的mysql
<span style="box-sizing: border-box;padding-right: 0.1px">mysql<span style="box-sizing: border-box">></span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">CHANGE</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">MASTER</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">TO</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">MASTER_HOST<span style="box-sizing: border-box">=</span><span style="box-sizing: border-box;color: rgb(195, 232, 141)">'192.168.157.129'</span><span style="box-sizing: border-box">,</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">MASTER_USER<span style="box-sizing: border-box">=</span><span style="box-sizing: border-box;color: rgb(195, 232, 141)">'myslave'</span><span style="box-sizing: border-box">,</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">MASTER_PASSWORD<span style="box-sizing: border-box">=</span><span style="box-sizing: border-box;color: rgb(195, 232, 141)">'123456'</span><span style="box-sizing: border-box">,</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">MASTER_LOG_FILE<span style="box-sizing: border-box">=</span><span style="box-sizing: border-box;color: rgb(195, 232, 141)">'mysql-bin.000003'</span><span style="box-sizing: border-box">,</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">MASTER_LOG_POS<span style="box-sizing: border-box">=</span><span style="box-sizing: border-box;color: rgb(247, 118, 105)">379</span><span style="box-sizing: border-box">;</span></span>
MASTER_HOST 为主库地址
MASTER_USER 是在主库创建的，用于复制使用的用户
MASTER_LOG_FILE 为二进制节点
MASTER_LOG_POS 为从这个位置开始复制
第五步：启动slave复制
mysql> start slave;
其他命令
- reset slave 重置从库
- stop slave 停止从库
第六步：查看是否开启复制
mysql> show master status\G;
注意，这一步需要关注两个非常重要的信息，它说明了是否成功开启了主从复制，信息如下：
<span style="box-sizing: border-box;padding-right: 0.1px"> Slave_IO_State<span style="box-sizing: border-box">:</span> Waiting <span style="box-sizing: border-box;color: rgb(199, 146, 234)">for</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">master</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">to</span> send <span style="box-sizing: border-box;color: rgb(199, 146, 234)">event</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"> Master_Host<span style="box-sizing: border-box">:</span> <span style="box-sizing: border-box;color: rgb(247, 118, 105)">192.168.157.129</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"> Master_User<span style="box-sizing: border-box">:</span> myslave</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"> Master_Port<span style="box-sizing: border-box">:</span> <span style="box-sizing: border-box;color: rgb(247, 118, 105)">3306</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"> Connect_Retry<span style="box-sizing: border-box">:</span> <span style="box-sizing: border-box;color: rgb(247, 118, 105)">60</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"> Master_Log_File<span style="box-sizing: border-box">:</span> mysql<span style="box-sizing: border-box">-</span>bin<span style="box-sizing: border-box;color: rgb(247, 118, 105)">.000003</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"> Read_Master_Log_Pos<span style="box-sizing: border-box">:</span> <span style="box-sizing: border-box;color: rgb(247, 118, 105)">379</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"> Relay_Log_File<span style="box-sizing: border-box">:</span> localhost<span style="box-sizing: border-box">-</span>relay<span style="box-sizing: border-box">-</span>bin<span style="box-sizing: border-box;color: rgb(247, 118, 105)">.000002</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"> Relay_Log_Pos<span style="box-sizing: border-box">:</span> <span style="box-sizing: border-box;color: rgb(247, 118, 105)">324</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"> Relay_Master_Log_File<span style="box-sizing: border-box">:</span> mysql<span style="box-sizing: border-box">-</span>bin<span style="box-sizing: border-box;color: rgb(247, 118, 105)">.000003</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"> Slave_IO_Running<span style="box-sizing: border-box">:</span> Yes</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"> Slave_SQL_Running<span style="box-sizing: border-box">:</span> Yes</span>
只有当Slave_IO_Running和Slave_SQL_Running全部为Yes时，才说明主从复制已经成功。
5、主从复制测试
第一步：master主库中创建一个表
<span style="box-sizing: border-box;padding-right: 0.1px">mysql<span style="box-sizing: border-box">></span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">use</span> master_slave_test<span style="box-sizing: border-box">;</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">mysql<span style="box-sizing: border-box">></span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">create</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">table</span> t1<span style="box-sizing: border-box;color: rgb(153, 153, 119)">(</span>id <span style="box-sizing: border-box;color: rgb(222, 203, 107)">int</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">not</span> <span style="box-sizing: border-box;color: rgb(247, 118, 105)">null</span><span style="box-sizing: border-box;color: rgb(153, 153, 119)">)</span><span style="box-sizing: border-box">;</span></span>
第二步：slave从库查看是否同步成功
<span style="box-sizing: border-box;padding-right: 0.1px">mysql<span style="box-sizing: border-box">></span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">use</span> master_slave_test<span style="box-sizing: border-box">;</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">mysql<span style="box-sizing: border-box">></span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">show</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">tables</span><span style="box-sizing: border-box">;</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box">+-----------------------------+</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box">|</span> Tables_in_master_slave_test <span style="box-sizing: border-box">|</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box">+-----------------------------+</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box">|</span> t2 <span style="box-sizing: border-box">|</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box">+-----------------------------+</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(247, 118, 105)">1</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">row</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">in</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">set</span> <span style="box-sizing: border-box;color: rgb(153, 153, 119)">(</span><span style="box-sizing: border-box;color: rgb(247, 118, 105)">0.00</span> sec<span style="box-sizing: border-box;color: rgb(153, 153, 119)">)</span></span>
可以看到主从复制已经完成。
总结：我在配置MySQL8的主从时，因为my.cnf配置文件，遇到了不少的问题。由于版本的不同，出现的问题也可能不一样，但是配置一次后，还是会发现，其实挺简单的，问题就是记不住。因此，笔记就来了。
我是温新
每天进步一点，就一点点
请登录后再评论