mysql5.5主从同步复制配置

分手后的思念是犯贱 2022-08-14 02:58 185阅读 0赞

在上篇文章《[烂泥：学习mysql数据库主从同步复制原理][mysql]》中，我们介绍了有关mysql主从复制的基本原理。在这篇文章中，我们来实际测试下mysql5.5的主从同步复制功能。

注意mysql5.1.7以前版本与其以后的版本在主从同步部分参数不同。mysql5.1.7以后的版本中不支持master-connect-retry之类的参数。如果在my.cnf文件中加入该类似的参数，mysql会在下次重启时报错。

说明：主库master与从库slave都是centos6.5 64bit，如下：

cat /etc/system-release

[![clip\_image001][clip_image001]][clip_image001_clip_image001]

主库master与从库slave都是mysql数据库版本都为5.5.39。

mysql –V

[![clip\_image002][clip_image002]][clip_image002_clip_image002]

主库master与从库slave的IP分别如下：

master IP：192.168.1.213 slave IP：192.168.1.214

[![clip\_image003][clip_image003]][clip_image003_clip_image003]

我们以主库master的ilanni数据库为实验对象，如下：

[![clip\_image004][clip_image004]][clip_image004_clip_image004]

根据《[烂泥：学习mysql数据库主从同步复制原理][mysql]》文章可知，mysql的主从同步复制过程如下：

1、主库master在执行SQL语句之后，会把相关的SQL语句记录到binlog文件中。

2、从库slave连接主库master，并从主库master获取该binlog文件，存于本地relay-log文件中，然后从master.info文件读取上次同步时的pos位置节点起执行SQL语句。

由以上信息可知，我们的mysql主从同步复制实验，需要以下几个步骤：

1、 配置主库master同步复制时的选项

2、 在主库master上创建同步复制时的用户并授权

3、 主库master锁表

4、 记录主库master的binlog以及pos位置节点

5、 导出ilanni数据库

6、 配置slave端同步复制时所需要的选项

7、 在从库slave上创建数据库ilanni并导入备份

8、 解锁主库表

9、 设置从库slave与主库master同步

10、 在从库slave上开启同步

11、 查看从库slave的relay-log以及master.info

12、 测试主从同步

一、配置主库master同步复制时的选项

mysql的主从配置，我们只需要修改my.cnf文件即可。如下：

cat /etc/my.cnf |grep -v ^\#|grep -v ^$

log-bin=mysql-bin

max\_binlog\_size = 500M

server-id=1

binlog-do-db=ilanni

binlog-ignore-db=mysql

[![clip\_image005][clip_image005]][clip_image005_clip_image005]

其中log-bin=mysql-bin表示启用mysql二进制日志，该项必须要启用，否则mysql主从不会生效。

max\_binlog\_size=500M表示每个binlog文件最大大小，当此文件大小等于500M时，会自动生成一个新的日志文件。注意：一条记录不会写在2个日志文件中，所以有时日志文件会超过此大小。

server-id=1表示mysql服务器ID，该ID必须在该主从中是唯一的，默认是1，该ID可以自行自定义，但必须为数字。

binlog-do-db=ilanni表示需要同步的数据库名字，如果是多个数据库，就以此格式再写一行即可。

binlog-ignore-db=mysql表示不需要同步的数据库名字，如果是多个数据库，就以此格式再写一行即可。

注意：如果binlog-do-db和binlog-ignore-db不加的话，那么默认是同步复制整个mysql数据库。

二、在主库master上创建同步复制时的用户并授权

登录master端，创建数据库用户ilanni，并授权为replication slave权限。如下：

grant replication slave on \*.\* to 'ilanni'@'%' identified by '123456';

select user,repl\_slave\_priv from user where user='ilanni';

[![clip\_image006][clip_image006]][clip_image006_clip_image006]

通过上图，我们可以看到目前数据库用户ilanni被授予replication slave权限，在user表中的表现为repl\_slave\_priv字段为Y。

注意：replication slave权限：只有拥有此权限的用户才可以查看从服务器slave以及从主服务器master读取二进制日志的权限。

授权完毕后，我们需要在slave测试ilanni用户是否可以连接master。如下：

ifconfig eth0|grep "inet addr"|awk '\{print $2\}'|cut -d: -f2

mysql -h 192.168.1.213 -uilanni -p123456

[![clip\_image007][clip_image007]][clip_image007_clip_image007]

通过上图我们可以看到，目前在从服务器slave已经可以正常连接master服务器。

三、 主库master锁表

先锁住主库master的表，防止数据再写入，导致主从数据库不一致。使用如下命令锁表：

flush tables with read lock;

[![clip\_image008][clip_image008]][clip_image008_clip_image008]

这样主库master只能被读取，而不能被写入数据。如下：

[![clip\_image009][clip_image009]][clip_image009_clip_image009]

通过上图可以看到，目前主库已经不能再写入数据。

注意：目前这个锁表的终端不要退出，否则这个锁就失效了。

四、记录主库master的binlog文件名以及pos位置节点

为什么要记录此时主库master的binlog文件名以及pos位置节点？

因为当我们把主库的数据库迁移或导入到从库slave后，我们就会让从库slave从这个binlog文件的该pos位置节点与主库master同步。

查看主库master的binlog文件名及pos位置节点，如下：

show master status;

show master status\\G;

/usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/data/mysql-bin.000003

[![clip\_image010][clip_image010]][clip_image010_clip_image010]

通过以上两张截图，我们可以很明显的看到目前主库的binlog为mysql-bin.000003，pos位置节点为1616。

五、导出ilanni数据库

在从库slave与主库master第一次同步数据时，有三种方法。

第一种方法就是在主库锁表后，使用tar把master库直接打包，然后使用scp或者rsync把该打包文件弄到从库slave上。这种情况一般适用于网站或者业务在初始化，抑或在数据库大于100G时建议使用。

第二种方法就是在主库锁表后，我们直接使用mysqldump命令导出数据库，然后在从库上进行恢复。这个方法比较常见，所以我们一般是使用这个方法。

注意以上两种方法，我们都需要进行在主库master锁表后进行操作。

第三种方法，其实我们不需要做其他工作。只需开启同步复制即可。但是这个有一个前提就是，mysql的binlog必须齐全，这个齐全就是要包括该数据创建时的binlog也要存在。并且同步时，还必须要从最初始的binlog开始。所以，这种方法，我们一般不使用。

下面我们是通过第二种方法来导出ilanni数据库，如下：

mysqldump -uroot -p123456 ilanni>ilanni.sql

ll -h |grep ilanni.sql

[![clip\_image011][clip_image011]][clip_image011_clip_image011]

现在我们再把ilanni数据库的备份文件ilanni.sql，通过scp命令复制到从库slave上，如下：

scp ilanni.sql root@192.168.1.214:/root

[![clip\_image012][clip_image012]][clip_image012_clip_image012]

六、配置从库slave同步复制时所需要的选项

从库slave上我们只需要在my.cnf文件中，修改server-id值为唯一即可。如下：

cat /etc/my.cnf|grep -v ^\#|grep -v ^$

[![clip\_image013][clip_image013]][clip_image013_clip_image013]

七、在从库slave上创建数据库ilanni并导入备份

我们在第五步已经把ilanni的备份文件通过scp命令复制到从库，现在我们需要在从库slave上，然后把备份的数据导入进去。如下：

create database ilanni;

mysql –uroot –p123456 ilanni</root/ilanni.sql

[![clip\_image014][clip_image014]][clip_image014_clip_image014]

[![clip\_image015][clip_image015]][clip_image015_clip_image015]

通过上图，我们可以看到目前从库已经完全恢复ilanni数据库。

八、解锁主库master表

从库slave的ilanni数据库创建并导入备份后，我们现在来解锁主库master表，使用如下命令：

unlock tables;

[![clip\_image016][clip_image016]][clip_image016_clip_image016]

九、设置从库slave与主库master同步

在第七步中我们已经恢复ilanni数据库的数据，我们来开始设置从库slave与主库master同步，使用如下命令：

change master to master\_host='192.168.1.213',master\_user='ilanni',master\_password='123456',master\_log\_file='mysql-bin.000003',master\_log\_pos=1616, master\_connect\_retry=100;

其中：master\_host表示是主库的IP

master\_user表示主库master上允许同步的用户

maser\_password表示同步用户的密码

master\_log\_file表示从哪个binlog文件开始同步

master\_log\_pos表示从该binlog文件的那个pos节点位置开始同步

master\_connect\_retry表示从库slave与主库master同步周期，默认是60s。

注意：master\_log\_file和master\_log\_pos，就是我们在第四步记录的binlog文件名和pos位置节点。

[![clip\_image017][clip_image017]][clip_image017_clip_image017]

十、在从库slave上开启同步

以上配置完毕后，我们现在来开启主从同步。开启主从同步，我们需要在从库上开启。开启主从同步非常简单，只需一条命令即可，如下：

start slave;

[![clip\_image018][clip_image018]][clip_image018_clip_image018]

以上就开启了mysql主从同步复制的开关。我们使用如下命令检查，同步是否正常。如下：

show slave status\\G;

[![clip\_image019][clip_image019]][clip_image019_clip_image019]

我们查看同步是否主要是查看Slave\_IO\_Running与Slave\_SQL\_Running选项。如果正常同步，这两选必须同时为YES。

如果Slave\_IO\_Running为NO，说明可能是从库与主库的网络不通。

如果Slave\_SQL\_Running为NO，说明很可能是从库与主库的数据不一致。

通过上图，我们可以看到目前Slave\_IO\_Running和Slave\_SQL\_Running都为YES。说明现在主从同步是正常的。

并且通过上图，我们也可以看到从库slave与主库master刚开始同步时的binlog文件名以及开始同步时的pos位置节点。

十一、查看从库slave的relay-log以及master.info

现在我们来查看从库relay-log以及master.info信息，我们首先看relay-log信息，如下：

[![clip\_image020][clip_image020]][clip_image020_clip_image020]

mysqlbinlog ilanni-relay-bin.000002|more

[![clip\_image021][clip_image021]][clip_image021_clip_image021]

通过这张图，我们可以在relay-log日志中看到，从库slave开始同步主库的binlog文件名以及同步复制时的pos位置节点。

现在我们来查看master.info，如下：

cat master.info |more

[![clip\_image022][clip_image022]][clip_image022_clip_image022]

可以看到这个文件保存了从库slave同步主库master时的相关信息：IP、用户、密码、binlog文件名、pos位置节点、同步周期。

十二、测试主从同步

现在我们来测试下，mysql的主从同步。先在主库master上给ilanni数据库插入和删除一条数据。如下：

insert into ilannitable values(6);

delete from ilannitable where id=2;

select id from ilannitable;

[![clip\_image023][clip_image023]][clip_image023_clip_image023]

此时主库master的ilanni数据库中只有1、3、4、5、6，这个5条数据。

现在我们登录从库slave，查看下ilanni数据库的情况。如下：

[![clip\_image024][clip_image024]][clip_image024_clip_image024]

通过上图，我们可以看到从库slave中的ilanni数据库和主库master中的ilanni数据库，数据是一致。说明主从已经同步复制已经成功。

现在我们再来查看master.info文件的信息，如下：

mysqlbinlog /usr/local/mysql/data/mysql-bin.000003

cat master.info |more

[![clip\_image025][clip_image025]][clip_image025_clip_image025]

通过上图中，主库的binlog文件的pos位置节点与master.info对比。我们可以发现master.info文件中确实记录了mysql在同步复制时的binlog文件名以及pos位置节点。

至此，有关mysql主从同步复制的实验暂时告一段落。

[![041405283617635.gif][]][041405283617635.gif 1]

欢迎大家访问我的个人网站 [萌萌的IT人][IT]

[mysql]: http://www.ilanni.com/?p=7933
[clip_image001]: http://s3.51cto.com/wyfs02/M00/53/E8/wKioL1Rz5eDR1gmuAADeTcPr6h0522.jpg
[clip_image001_clip_image001]: /images/20220810/883c9505ebc741dd89a049c469be5de7.png
[clip_image002]: http://s3.51cto.com/wyfs02/M01/53/E8/wKioL1Rz5eDzJVHKAAC8oaL6BXk051.jpg
[clip_image002_clip_image002]: /images/20220810/36eb4f242edd40d39bcf0e0321bec5a5.png
[clip_image003]: http://s3.51cto.com/wyfs02/M01/53/E8/wKioL1Rz5eGS_mNiAAG5I8e2Qms349.jpg
[clip_image003_clip_image003]: /images/20220810/194e4faadcb04b15bf1503e884fbb402.png
[clip_image004]: http://s3.51cto.com/wyfs02/M02/53/EA/wKiom1Rz5WLDSlV5AADO7po7gxk320.jpg
[clip_image004_clip_image004]: /images/20220810/12aaa4a2e1344f348b2717ec774dea4b.png
[clip_image005]: http://s3.51cto.com/wyfs02/M00/53/EA/wKiom1Rz5WKwxUv1AAHPrerS3ws212.jpg
[clip_image005_clip_image005]: /images/20220810/5a5fc4adbccd43809e2caa3246d1df8b.png
[clip_image006]: http://s3.51cto.com/wyfs02/M01/53/EA/wKiom1Rz5WOTce4UAAKpMakVxDk876.jpg
[clip_image006_clip_image006]: /images/20220810/a11c17fc64ba4d15b8f2d37ebb90782b.png
[clip_image007]: http://s3.51cto.com/wyfs02/M01/53/EA/wKiom1Rz5WOy08QFAAG9WohwQ1Q993.jpg
[clip_image007_clip_image007]: /images/20220810/4992a7ba23484ef0a1d9cff35893dacb.png
[clip_image008]: http://s3.51cto.com/wyfs02/M00/53/E8/wKioL1Rz5eOTqnBrAAIjSNF9ucE871.jpg
[clip_image008_clip_image008]: /images/20220810/50805ff497ed40db918f9e92575ebe46.png
[clip_image009]: http://s3.51cto.com/wyfs02/M01/53/E8/wKioL1Rz5eTAtSaqAAE6o8Dsbm0046.jpg
[clip_image009_clip_image009]: /images/20220810/e8cc253e27474219a9168d69bf7a17e0.png
[clip_image010]: http://s3.51cto.com/wyfs02/M02/53/E8/wKioL1Rz5eTQU3JzAAORwZw73DM317.jpg
[clip_image010_clip_image010]: /images/20220810/f0f53a02652547328407ffbc7cb74398.png
[clip_image011]: http://s3.51cto.com/wyfs02/M00/53/E8/wKioL1Rz5eWCiBI0AADc89s6YWo362.jpg
[clip_image011_clip_image011]: /images/20220810/fa7c2006f0ee48d88855d9ae16f76a4e.png
[clip_image012]: http://s3.51cto.com/wyfs02/M02/53/E8/wKioL1Rz5eugb7_PAADFtO7JRqQ249.jpg
[clip_image012_clip_image012]: /images/20220810/6b99640a1289407ca1d25175a7699d14.png
[clip_image013]: http://s3.51cto.com/wyfs02/M01/53/EA/wKiom1Rz5W_yLuQYAAGX58TvrDw036.jpg
[clip_image013_clip_image013]: /images/20220810/82f7cea8645e4381af10d12e39304724.png
[clip_image014]: http://s3.51cto.com/wyfs02/M02/53/EA/wKiom1Rz5W_wQy0MAAHA8Db6Sa8605.jpg
[clip_image014_clip_image014]: /images/20220810/698136d2fd5341348728176e93d0dbec.png
[clip_image015]: http://s3.51cto.com/wyfs02/M00/53/EA/wKiom1Rz5XDxy3xjAAIwq6Y7B-c086.jpg
[clip_image015_clip_image015]: /images/20220810/7a8c679735d6421ca67943c87c506a4c.png
[clip_image016]: http://s3.51cto.com/wyfs02/M01/53/EA/wKiom1Rz5XDQlI8ZAACDE88M2jM020.jpg
[clip_image016_clip_image016]: /images/20220810/6c39b89b2203431fb4eb3afd0d6db25d.png
[clip_image017]: http://s3.51cto.com/wyfs02/M02/53/EA/wKiom1Rz5XOhBPHSAAJ2fIb7H6o453.jpg
[clip_image017_clip_image017]: /images/20220810/5b751aa03a4b49e9883c052f815fcc07.png
[clip_image018]: http://s3.51cto.com/wyfs02/M02/53/EA/wKiom1Rz5XfiKNz2AAFLB-kY7Q0316.jpg
[clip_image018_clip_image018]: /images/20220810/29666e183a2c4fd8ad0cf29351d1f83a.png
[clip_image019]: http://s3.51cto.com/wyfs02/M01/53/E9/wKioL1Rz5fqDXgiuAAH9yTMWYXE242.jpg
[clip_image019_clip_image019]: /images/20220810/6c6f3fbbd0f64c37946f979a9a477e3a.png
[clip_image020]: http://s3.51cto.com/wyfs02/M02/53/E9/wKioL1Rz5f2CgnkRAAPPrixplqM552.jpg
[clip_image020_clip_image020]: /images/20220810/fc701e4879fd44ff930f81bd3a4d0db7.png
[clip_image021]: http://s3.51cto.com/wyfs02/M00/53/EB/wKiom1Rz5X6AD7bGAAKVBB4qwtQ679.jpg
[clip_image021_clip_image021]: /images/20220810/b2175ee8800a40cd859bd5165860b515.png
[clip_image022]: http://s3.51cto.com/wyfs02/M00/53/EB/wKiom1Rz5X_ykhbvAADiG7NCdrU331.jpg
[clip_image022_clip_image022]: /images/20220810/9d1bc1cd1a5f418988ff5f3901b5b7e0.png
[clip_image023]: http://s3.51cto.com/wyfs02/M01/53/EB/wKiom1Rz5YLQFDwAAAFEUGqPbM8723.jpg
[clip_image023_clip_image023]: /images/20220810/0ef0b01c8a7f41d38bdc86a1ef0933fb.png
[clip_image024]: http://s3.51cto.com/wyfs02/M02/53/E9/wKioL1Rz5gOCi_sMAAL9bQE4WuY483.jpg
[clip_image024_clip_image024]: /images/20220810/b47cf52fe76541008120421e8258119e.png
[clip_image025]: http://s3.51cto.com/wyfs02/M02/53/EB/wKiom1Rz5YSBwNw4AAVtgh8MKB4297.jpg
[clip_image025_clip_image025]: /images/20220810/076ac4ee3f564e2482f58a8470f21bab.png
[041405283617635.gif]: /images/20220810/f9be815bc4a04251915706b1b774b7c4.png
[041405283617635.gif 1]: http://www.itmmd.com/boke.html
[IT]: http://www.itmmd.com