一文实战 MySQL 主从复制

Dear 丶 2021-09-07 10:38 368阅读 0赞

主从复制的好处？为什么要用主从复制？

当系统访问量大时，单数据库可能无法保证系统的性能，需要配置多台数据库来提高系统性能进行读写分离，把访问量分担，同时主从复制也起到了一个数据备份的功能。

mysql主从复制的原理是什么？

1.  master将改变记录到二进制日志（binary log）这个过程叫做二进制日志时间 binary log events，
2.  slave 将binary log events 拷贝到它的中继日志（relay\_log）。
3.  slave重做中继日志中的事件，将改变应用到自己的数据库中，mysql复制时异步的且串行化的

简单通俗说，主机把改变操作记录保存到一个日志文件中，从机获取到这个日志文件，在自己的机器上运行

mysql主从复制,一主一从的配置过程环境准备

准备两台装有mysql数据库的机器，在此笔者使用了两台虚拟机系统是ubuntu18.04，在一台机器安装好mysql数据库之后克隆就行了（通过这种方法会有一个坑后面再说），保证这两台虚拟机可上网可以相互ping通

mysql主从复制,一主一从主机配置

修改数据库配置文件 /etc/mysql/mysql.conf.d/mysqld.cnf， 注意：这个文件根据安装mysql方式不同路径不同，需要找到自己mysql的配置文件 在\[mysqld\]节点中配置如下：

![f54de1361aaeea48c3658860cc463706.png][]

##### 配置说明： #####

1.  server-id:指定master服务id,必须
2.  log-bin=mysql-bin：需要打开日志文件，必须，这里有些人会指定一个目录，但是笔者指定一个目录之后发现mysql就启动不起来了，这里直接设置为 mysql-bin 就可以，生成的文件过后是可以使用命令查看的
3.  错误日志文件归集路径，可选
4.  read-only=0 数据库读写模式 因为主机一般都是可读可写的，也可以使用默认值这项可选， 0为可读可写，1为只读
5.  binlog\_do\_db ：这项可选所以没有配置，意思 要复制哪些数据库
6.  binlog\_ignore\_db  这项可选所以没有配置，意思 忽略复制哪些数据库

这里就完成了主机数据库配置文件的修改，保存之后重启mysql服务。

mysql主从复制,一主一从从机配置

从机数据库配置相对简单点，也是修改数据库配置文件（如上路径），只需要在【mysqld】节点中配置server-id即可：

![f8227d6adc859af84af456c509f5d17a.png][]

server-id不能和主机重复了，read-only这里理论上从机只做读操作，所以设置为1，但是这里有坑下文再说

从机的配置配置一个server-id就可以了保存退出，重启服务,这里我们就配置好了一台主机和从机 主机：192.168.192.133 从机：192.168.192.132

查看log-bin文件的生成位置

1.  首先登录到数据库中 mysql -u root -p 根据提示输入密码
2.  输入命令：show variables like '%log\_bin%';

![568d40e38f9f62ec5964f75e96150b20.png][]

在图中我们可以看到log\_bin 的value是ON说明是打开了二进制文件配置，log\_bin\_basenaem 的value 就是文件的保存地址，去到目录查看

![92f4c8973e0003cd4907ae30e73d0382.png][]

这里的mysql-bin.000001就是生成的二进制文件了

主机给从机链接授权

1.  mysql -u root -p 根据提示输入密码
2.  使用命令：

grant REPLICATION SLAVE ON *.* to 'ayou'@'192.168.192.132' identified by '123456';

3. 命令解析

*  grant: 是数据库的授权命令
 *  ayou：被授权的账号
 *  REPLICATION SLAVE：复制权限
 *  '192.168.192.132'：被授权登录的从机ip
 *  '123456':被授权账号的密码

执行命令后，再执行刷新权限表命令：

FLUSH PRIVILEGES;

查看某个账号被分配权限的命令：如

show grants for  'ayou'@'192.168.192.132'

![89f144639ab901ac1f166eb581b53449.png][]

##### 使用命令查看 master 状态 #####

show master status;

![1feeb42f91ea6ada43fce2f5e4b071a3.png][]

*  file:二进制文件，从机要复制主机的哪一个二进制文件
 *  position:磁道，就是从机要从哪一个节点开始复制主机的内容
 *  binlog\_Do\_DB：要复制的哪一个数据库（这一项可在上文配置文件配置）
 *  Binlog\_Igore\_DB:要排斥复制的哪个数据库，（这一项可在上文配置文件配置）

##### 从机连接主机配置 #####

在上文中在主机中为从机账号授权了，现在要通过这个账号来连接主机，步骤如下：

1.  登录数据库mysql -u root -p
2.  登录成功后输入命令：

change master to master_host='192.168.192.133',master_user='ayou',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=10756;

命令详解：

*  master\_host：主机ip地址
 *  master\_user：master授权账号
 *  master\_password：master授权的密码
 *  master\_log\_file：对应的二进制文件，如上图所示的FIle
 *  master\_log\_pos=:对应的是master上的磁道（通俗将就是你要从二进制文件的哪个地方开始做复制）

1.  输入成功后执行如下命令启动slave,:

start slave;

4. 执行如下命令查看slave状态:

show slave status\G;

![77498b4e1b66b79510178ff6672a878b.png][]

*  slave\_Io\_State:状态必须显示为 Waiting for master to send event
 *  Slave\_IO\_Running:YES 状态必须显示为 YES
 *  Slave\_SQL\_Running:YES 状态必须显示为 YES

以上三项条件必须满足才证明主从之间的通道打开

在搭建过程中出现的错误1

1.  在搭建过程中出现,Slave\_IO\_Running：NO

![fc7b37872f88500d6f96191580666195.png][]

如上图所示当我们执行从机连接主机命令且开启slave后，输入命令 show slave status\\G; 时 Slave\_IO\_State状态为空，Slave\_IO\_State为NO,还有上图第三点错误，这个错误说的是从机数据库和主机数据的的UUID相等，所以直接导致了这个错误

原因：笔者在创建主从数据库时，是通过主机克隆出从机，所以数据库服务的uuid就相等了，

解决办法：

1.  从机先停掉 slave
2.  停掉数据库
3.  删除 /var/lib/mysql/auto.cnf,这个文件就是包含了uuid的配置，启动数据库服务的时候这个文件会自动生成
4.  从机再次数据命令连接主机，和启动slave

##### 更新主机数据库，在从机看是否同步成功 #####

![92eb8493b4d25068fcde2b0d79b2422b.png][]

如上所示我们在主机数据库创建了test数据库，在test数据库中创建了test数据表，在表中插入了一条记录，按理说主从连接通道已经建立好此时去从机查看就会和主机数据一致，我们查看从机数据库。

![19eac6e7e88c610374d5ebc86993350c.png][] 再试一次，在主机上插入数据

![38f9f5d439e35cd91079f25259a23add.png][]

insert into test values(1,'234789');

返回从机查看，

![d8a7c0bf8d69d35a81024bf701af52d5.png][]

发现数据同步是成功的，说明我们的主从复制是生效的.

这里补充一下，如果在生产过程中，你的从库是后面再搭建的而主库已经有一些数据库表和数据了， 从库数据的复制是从主从架构搭建之后的，所以搭建之前的主库的数据需要手动导入到从库后建立主从复制机制。

主从数据库数据不一致的如何解决？

数据库出现不一致时从机的slave状态 出现Slave\_SQL\_Running: No

解决方法1(忽略错误，继续同步)：

1.  从机stop slave;
2.  输入命令 set global sql\_slave\_skip\_counter=1;
3.  从机 start slave

该方法适用于主从库数据相差不大，或者要求数据可以不完全统一的情况，数据要求不严格的情况

解决方法2(重新做主从复制，完全同步)：

1.  从机stop slave
2.  主机给表加上读锁，因为要做备份所以备份期间不允许其他人增，删数据

flush table with read lock;

3. 把主机数据库备份（mysqldump 命令不需要登录数据库，直接在虚拟机目录执行就可以） 备份某个数据库命令：

mysqldump -uroot -p test > /etc/mysql/back/test.sql

例子：备份test 库 test3 和test4 两张表，多表使用空格间隔

mysqldump -uroot -p test test4 test3 > /etc/mysql/back/test34.sql

命令解释：

*  mysqldump:数据库备份命令
 *  \-uroot:指定数据库账户
 *  \-p:数据库密码，执行命令后会请求输入密码
 *  test:数据库名称
 *  /etc/mysql/back/test.sql ：备份出来的sql文件存放路径

4. 把从主机备份的sql脚本上传至从机，并执行命令

注意：如果备份到一个全新的从库中，没有相对应的数据库是需要先执行创建数据库的命令，可登录数据库通过命令

create database db_name

或者通过备份命令：

mysqladmin -uroot -p create db_name

确保数据库存在之后再执行备份命令如下：

mysql -uroot -p test< /home/ayou/test.sql

*  "<" 右边就是存放备份脚本的路径

1.  查看主机二进制文件名和position数值，

![5021dfae2edc511ccdb3a88a681d71f6.png][]

6. 从机重新连接主机，

change master to master_host='192.168.192.133',master_user='ayou',master_password='123456',master_log_file='mysql-bin.000003',master_log_pos=1796;

7. 重新启动slave输入命令：start slave，输入命令：show slave status\\G;  如下图显示说明操作成功

![9344bd0121336ef9dd63605ea2798bb7.png][]

8 对主机数据库解锁：

unlock tables;

这一步必须执行的哦不要忘记了！

要把主数据库和从数据库的责任分担明确

上文操作中我们已经把主从复制搭建好了，但是“主从复制”这四个字常常伴随着“读写分离”四个字，在开发中我们为了避免主从数据库数据冲突，需要给主数据库可读可写的权限，从数据库拥有读的权限就可。写数据的时候都往主数据库写，而读数据时则可以通过java活其他程序来规定读取哪个数据库。

从数据库创建普通账户和授权

1. 创建用户 luo

2. create user`luo`@`*` identified by'123456';

3. 给用户授权为 只用于 select权限

![3c17fa995e75f8ed897aaaf66add6158.png][]

grant select ON *.* to `luo`@`*` identified by'123456';

使用此账号登录从数据库

1.  mysql -u luo -p
2.  根据提示输入密码
3.  在从库上删除一条数据

![c60eaeea87a014ed77bd488a22fa9b62.png][]

当我们之心DML语句是就会报以下错误：

ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement

报错中有一个单词 “read-only”，这个正是我们在上文数据库配置文件中配置为1的，如果在上文我们忽略这个配置，这里的报错就会变成 Access denied（权限不足）类型的错误。

但是从库有全部权限的用户还是可以对数据进行DML操作的，全部权限是指 拥有 all privileges 的账户

![ff526f9e9d1032f9084e5868eb26c3e2.png][]

**最后有一个疑问**

主从复制中从库的DML难道要靠角色权限来控制吗？能不能在master给从库授权连接账号时指定只读权限？既然都说从数据库只需要读的权限了，那我们在主机给从机授权时是不是只要修改命令为如下命令就可?

grant select ON *.* to 'ayou'@'192.168.192.132' identified by '123456';

但是很不幸的是，当把授权命令改为以上命令后，slave是连接不上master的，就无法配置主从复制了,配置后如下图错误

![113126824536ceabacfe18077e897fc8.png][]

-END-如果看到这里，说明你喜欢这篇文章，请 转发、点赞。微信搜索「web_resource」，关注后回复「进群」或者扫描下方二维码即可进入无广告交流群。↓扫描二维码进群↓推荐阅读 1.  面试官常考的 21 条 Linux 命令2.  拿下 Spring Boot !3.  必须掌握的 MySQL 优化原理4.  说一下从 URL 输入到返回请求的过程5.  Java 日常开发的 21 个坑喜欢文章，点个在看

[f54de1361aaeea48c3658860cc463706.png]: /images/20210730/f8efc2c49d4f46c48ea97d423e9c936e.png
[f8227d6adc859af84af456c509f5d17a.png]: /images/20210730/c89db4b6fa6d48029742fcecacfa6872.png
[568d40e38f9f62ec5964f75e96150b20.png]: /images/20210730/c1ddffe2543e4f29af0a5727bae7e4b0.png
[92f4c8973e0003cd4907ae30e73d0382.png]: /images/20210730/5840911c9c8940499deaded2455ec9d4.png
[89f144639ab901ac1f166eb581b53449.png]: /images/20210730/7e4e09890a944d808175f29daa366b88.png
[1feeb42f91ea6ada43fce2f5e4b071a3.png]: /images/20210730/e6a496be5f7d403c83b41ee988366399.png
[77498b4e1b66b79510178ff6672a878b.png]: /images/20210730/9670393a044a4a739536e929b81381b8.png
[fc7b37872f88500d6f96191580666195.png]: /images/20210730/ef9189e36c6f4239aa1f5fd3d857603b.png
[92eb8493b4d25068fcde2b0d79b2422b.png]: /images/20210730/f7e2bbae74024175a30f383911e9a1eb.png
[19eac6e7e88c610374d5ebc86993350c.png]: /images/20210730/fd0317a973e243c8bd247dd4a27fd6bc.png
[38f9f5d439e35cd91079f25259a23add.png]: /images/20210730/80114ce89f04451d8b1a6c47ce6a728e.png
[d8a7c0bf8d69d35a81024bf701af52d5.png]: /images/20210730/70d85b0c393b4014ab8e8e9b01176db4.png
[5021dfae2edc511ccdb3a88a681d71f6.png]: /images/20210730/1a7a0f7d26ba4db89d987bd9c8b59f76.png
[9344bd0121336ef9dd63605ea2798bb7.png]: /images/20210730/cffd89364cc848329021c8543f907d3d.png
[3c17fa995e75f8ed897aaaf66add6158.png]: /images/20210730/2d9db51217b24300a6b9b3cea2ab89a0.png
[c60eaeea87a014ed77bd488a22fa9b62.png]: /images/20210730/a38fd7178aa843e7aacf648ea1ff4e76.png
[ff526f9e9d1032f9084e5868eb26c3e2.png]: /images/20210730/3721f004db624973bfbc0ac7ae04a681.png
[113126824536ceabacfe18077e897fc8.png]: /images/20210730/cc79be73cf514d88b476f779725052e5.png