mysql5.5主从同步复制配置

蔷薇525

　　实际测试下mysql5.5的主从同步复制功能。
　　注意mysql5.1.7以前版本与其以后的版本在主从同步部分参数不同。mysql5.1.7以后的版本中不支持master-connect-retry之类的参数。如果在my.cnf文件中加入该类似的参数，MySQL会在下次重启时报错。
　　说明：主库master与从库slave都是centos6.5 64bit，如下：
　　cat /etc/system-release

http://images.cnitblog.com/blog/62984/201411/251005412773679.png

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

http://images.cnitblog.com/blog/62984/201411/251005441215975.png

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

http://images.cnitblog.com/blog/62984/201411/251005464186074.png

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

http://images.cnitblog.com/blog/62984/201411/251005479346045.png

　　根据《烂泥：学习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

http://images.cnitblog.com/blog/62984/201411/251005499656457.png

　　其中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'@'%'>　　select user,repl_slave_priv from user where user='ilanni';

http://images.cnitblog.com/blog/62984/201411/251005512319971.png

　　通过上图，我们可以看到目前数据库用户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

http://images.cnitblog.com/blog/62984/201411/251005531377655.png

　　通过上图我们可以看到，目前在从服务器slave已经可以正常连接master服务器。
　　三、 主库master锁表
　　先锁住主库master的表，防止数据再写入，导致主从数据库不一致。使用如下命令锁表：
　　flush tables with read lock;

http://images.cnitblog.com/blog/62984/201411/251005549657596.png

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

http://images.cnitblog.com/blog/62984/201411/251005560439166.png

　　通过上图可以看到，目前主库已经不能再写入数据。
　　注意：目前这个锁表的终端不要退出，否则这个锁就失效了。
　　四、记录主库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

http://images.cnitblog.com/blog/62984/201411/251005575124152.png

　　通过以上两张截图，我们可以很明显的看到目前主库的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

http://images.cnitblog.com/blog/62984/201411/251005584187008.png

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

http://images.cnitblog.com/blog/62984/201411/251006003562151.png

　　六、配置从库slave同步复制时所需要的选项
　　从库slave上我们只需要在my.cnf文件中，修改server-id值为唯一即可。如下：
　　cat /etc/my.cnf|grep -v ^#|grep -v ^$

http://images.cnitblog.com/blog/62984/201411/251006016219962.png

　　七、在从库slave上创建数据库ilanni并导入备份
　　我们在第五步已经把ilanni的备份文件通过scp命令复制到从库，现在我们需要在从库slave上，然后把备份的数据导入进去。如下：
　　create database ilanni;
　　mysql –uroot –p123456 ilanni
　　select>

http://images.cnitblog.com/blog/62984/201411/251006140124311.png

　　此时主库master的ilanni数据库中只有1、3、4、5、6，这个5条数据。
　　现在我们登录从库slave，查看下ilanni数据库的情况。如下：

http://images.cnitblog.com/blog/62984/201411/251006160909709.png

　　通过上图，我们可以看到从库slave中的ilanni数据库和主库master中的ilanni数据库，数据是一致。说明主从已经同步复制已经成功。
　　现在我们再来查看master.info文件的信息，如下：
　　mysqlbinlog /usr/local/mysql/data/mysql-bin.000003
　　cat master.info |more

http://images.cnitblog.com/blog/62984/201411/251006185742752.png

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