![mysql]( "mysql")
环境操作系统：CentOS-6.6-x86_64-bin-DVD1.isoMySQL版本：mysql-5.6.22.tar.gz主节点IP：192.168.1.205 主机名：edu-mysql-01从节点IP：192.168.1.206 主机名：edu-mysql-02MySQL主从复制官方文档http://dev.mysql.com/doc/refman/5.6/en/replication.htmlMySQL主从复制的方式MySQL5.6开始主从复制有两种方式：基于日志（binlog）、基于GTID（全局事务标示符）。本文主要讲基于日志（binlog）的复制。MySQL主从复制（也称A/B复制）的原理(1)Master将数据改变记录到二进制日志(binarylog)中，也就是配置文件log-bin指定的文件，这些记录叫做二进制日志事件(binarylogevents)；(2)Slave通过I/O线程读取Master中的binarylogevents并写入到它的中继日志(relaylog)；(3)Slave重做中继日志中的事件，把中继日志中的事件信息一条一条的在本地执行一次，完成数据在本地的存储，从而实现将改变反映到它自己的数据(数据重放)。主从配置需要注意的点(1)主从服务器操作系统版本和位数一致；(2)Master和Slave数据库的版本要一致；(3)Master和Slave数据库中的数据要一致；(4)Master开启二进制日志，Master和Slave的server_id在局域网内必须唯一；主从配置的简要步骤1、Master上的配置(1)安装数据库；(2)修改数据库配置文件，指明server_id，开启二进制日志(log-bin)；(3)启动数据库，查看当前是哪个日志，position号是多少；(4)登录数据库，授权数据复制用户（IP地址为从机IP地址，如果是双向主从，这里的还需要授权本机的IP地址，此时自己的IP地址就是从IP地址)；(5)备份数据库（记得加锁和解锁）；(6)传送备份数据到Slave上；(7)启动数据库；以下步骤，为单向主从搭建成功，想搭建双向主从需要的步骤：(1)登录数据库，指定Master的地址、用户、密码等信息（此步仅双向主从时需要）；(2)开启同步，查看状态；2、Slave上的配置(1)安装数据库；(2)修改数据库配置文件，指明server_id（如果是搭建双向主从的话，也要开启二进制日志log-bin）；(3)启动数据库，还原备份；(4)查看当前是哪个日志，position号是多少（单向主从此步不需要，双向主从需要）；(5)指定Master的地址、用户、密码等信息；(6)开启同步，查看状态。单向主从环境（也称MySQLA/B复制）的搭建1、Master（192.168.1.205）和Slave（192.168.1.206）上都安装了相同版本的数据库（mysql-5.6.22.tar.gz），参考《高可用架构篇--第13节--MySQL源码编译安装（CentOS6.6+MySQL5.6）》。注意：两台数据库服务器的的selinux都要disable（永久关闭selinux，请修改/etc/selinux/config，将SELINUX改为disabled）2、修改Master的配置文件/etc/my.cnf[root@edu-mysql-01~]#vi/etc/my.cnf#在[mysqld]中增加以下配置项#设置server_id，一般设置为IPserver_id=205#复制过滤：需要备份的数据库，输出binlog#binlog-do-db=yixiaoqun#复制过滤：不需要备份的数据库，不输出（mysql库一般不同步）binlog-ignore-db=mysql#开启二进制日志功能，可以随便取，最好有含义log-bin=edu-mysql-bin##为每个session分配的内存，在事务过程中用来存储二进制日志的缓存binlog_cache_size=1M##主从复制的格式（mixed,statement,row，默认格式是statement）binlog_format=mixed #二进制日志自动删除/过期的天数。默认值为0，表示不自动删除。expire_logs_days=7#跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。#如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致slave_skip_errors=1062（如想了解以上参数的更多详细解析，大家可以直接百度参数名）2.1复制过滤可以让你只复制服务器中的一部分数据，有两种复制过滤：(1)在Master上过滤二进制日志中的事件；(2)在Slave上过滤中继日志中的事件。如下： 2.2MySQL对于二进制日志(binlog)的复制类型(1)基于语句的复制：在Master上执行的SQL语句，在Slave上执行同样的语句。MySQL默认采用基于语句的复制，效率比较高。一旦发现没法精确复制时，会自动选着基于行的复制。(2)基于行的复制：把改变的内容复制到Slave，而不是把命令在Slave上执行一遍。从MySQL5.0开始支持。(3)混合类型的复制：默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制。3、启动/重启Master数据库服务，登录数据库，创建数据同步用户，并授予相应的权限[root@edu-mysql-01~]#servicemysqlrestartShuttingdownMySQL..[OK]StartingMySQL..[OK][root@edu-mysql-01~]#mysql-uroot-pEnter password:Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 2Server version: 5.6.22-log Source distributionCopyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.##创建数据同步用户，并授予相应的权限mysql>grantreplicationslave,replicationclienton*.*to'repl'@'192.168.1.206'identifiedby'123456';QueryOK,0rowsaffected(0.00sec)##刷新授权表信息mysql>flushprivileges;QueryOK,0rowsaffected(0.00sec)##查看position号，记下position号（从机上需要用到这个position号和现在的日志文件)mysql>showmasterstatus;+----------------------+----------+--------------+------------------+-------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+----------------------+----------+--------------+------------------+-------------------+| edu-mysql-bin.000001 | 1312 | | mysql | |+----------------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec) 4、创建 edu 库、表，并写入一定量的数据，用于模拟现有的业务系统数据库 createdatabaseifnotexistsedudefaultcharsetutf8collateutf8_general_ci;useedu;DROPTABLEIFEXISTS`edu_user`;CREATETABLE`edu_user`(`Id`int(11)NOTNULLAUTO_INCREMENT,`userName`varchar(255)NOTNULLDEFAULT''COMMENT'用户名',`pwd`varchar(255)NOTNULLDEFAULT''COMMENT'密码',PRIMARYKEY(`Id`))ENGINE=InnoDBAUTO_INCREMENT=2DEFAULTCHARSET=utf8COMMENT='用户信息表';INSERTINTO`edu_user`VALUES(1,'yixiaoqun','123456');5、为保证Master和Slave的数据一致，我们采用主备份，从还原来实现初始数据一致 ##先临时锁表mysql>flushtableswithreadlock;QueryOK,0rowsaffected(0.00sec)##这里我们实行全库备份，在实际中，我们可能只同步某一个库，那也可以只备份一个库[root@edu-mysql-01 mysql]# mysqldump -u root -p edu > /tmp/edu.sqlEnter password:[root@edu-mysql-01 mysql]# cd /tmp[root@edu-mysql-01 tmp]# ll | grep edu.sql-rw-r--r-- 1 root root 2031 Apr 25 01:18 edu.sql #注意：实际生产环境中大数据量（超2G数据）的备份，建议不要使用mysqldump进行比分，因为会非常慢。此时推荐使用XtraBackup进行备份。#解锁表mysql>unlocktables;QueryOK,0rowsaffected(0.00sec)将Master上备份的数据远程传送到Slave上，以用于Slave配置时恢复数据[root@edu-mysql-01 tmp]# scp /tmp/edu.sql root@192.168.1.206:/tmp/edu.sqlThe authenticity of host '192.168.1.206 (192.168.1.206)' can't be established.RSA key fingerprint is da:70:7b:d5:0c:16:b3:1a:53:b7:3d:9f:20:01:26:3e.Are you sure you want to continue connecting (yes/no)? yesWarning: Permanently added '192.168.1.206' (RSA) to the list of known hosts.root@192.168.1.206's password:edu.sql 100% 2031 2.0KB/s 00:00 6、接下来处理Slave（192.168.1.206），配置文件只需修改一项，其余配置用命令来操作 [root@edu-mysql-02 ]#vi/etc/my.cnf#在[mysqld]中增加以下配置项#设置server_id，一般设置为IPserver_id=206#复制过滤：需要备份的数据库，输出binlog#binlog-do-db=edu#复制过滤：不需要备份的数据库，不输出（mysql库一般不同步）binlog-ignore-db=mysql#开启二进制日志，以备Slave作为其它Slave的Master时使用log-bin=edu-mysql-slave1-bin##为每个session分配的内存，在事务过程中用来存储二进制日志的缓存binlog_cache_size=1M#主从复制的格式（mixed,statement,row，默认格式是statement）binlog_format=mixed#二进制日志自动删除/过期的天数。默认值为0，表示不自动删除。expire_logs_days=7#跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。#如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致slave_skip_errors=1062##relay_log配置中继日志relay_log=edu-mysql-relay-bin##log_slave_updates表示slave将复制事件写进自己的二进制日志log_slave_updates=1##防止改变数据(除了特殊的线程)read_only=1如果Slave为其它的Slave的Master时，必须设置bin_log,在这里，我开启了二进制日志，而且显式的命名(默认名称为hostname),但是如果hostname改变则会出现问题。)relay_log配置中继日志，log_slave_updates表示slave将复制事件 写进自己的二进制日志.当设置log_slave_updates时，你可以让slave扮演其它slave的master.此时，slave把sql线程执行的事件写进自己的二进制日志(binary log)然后，它的slave可以获取这些事件并执行它。如下图所示(发送复制事件到其它的Slave): 7.保存后重启MySQL服务，还原备份数据[root@edu-mysql-02 ~]# service mysql restartShutting down MySQL. SUCCESS!Starting MySQL.. SUCCESS!Slave上创建相同库 [root@edu-mysql-02 ~]# mysql -uroot -pEnter password:Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 1Server version: 5.6.22-log Source distributionCopyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> create database if not exists edu default charset utf8 collate utf8_general_ci;Query OK, 1 row affected (0.02 sec)mysql> use edu;Database changed6.导入数据[root@edu-mysql-02 ~]# mysql -uroot -p edu Enter password:[root@edu-mysql-02 ~]# mysql -uroot -pEnter password:Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 3Server version: 5.6.22-log Source distributionCopyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> use edu;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> select * from edu_user;+----+-----------+--------+| Id | userName | pwd |+----+-----------+--------+| 1 | yixiaoqun | 123456 |+----+-----------+--------+1 row in set (0.00 sec)8.登录Slave数据库,添加相关参数(Master的IP、端口、同步用户、密码、position号、读取哪个日志文件)change master to master_host='192.168.1.205',master_user='repl',master_password='123456',master_port=3306,master_log_file='edu-mysql-bin.000001',master_log_pos=1312,master_connect_retry=30;上面执行的命令的解释:master_host='192.168.1.205' ##Master的IP地址master_user='repl' ##用于同步数据的用户(在Master中授权的用户)master_password='123456' ##同步数据用户的密码master_port=3306 ##master数据库服务的端口master_log_file='edu-mysql-bin.000001' ##指定Slave从哪个日志文件开始读取复制文件(可在Master上使用show master status查看到日志文件名)master_log_pos=429 ##从哪个POSITION号开始读master_connect_retry=30 #当重新建立主从连接时，如果连接建立失败，间隔多久后重试,单位为秒，默认设置为60秒，同步延迟调优参数。 ##查看主从同步状态 show slave status\G;可看到Slave_IO_State为空，Slave_IO_Runngin和Slave_SQL_Running是No,表时Slave还是没有开始复制过程。#开启主从同步mysql> start slave;再次查看同步状态#show slave status\G;主要看以下两个参数,这两个参数如果是Yes，就表示主人同步正常Slave_IO_Running:YesSlave_SQL_Running:Yes由截图的主从同步状态信息可以看出，我们配置的主从同步是正常的。可查看master和slave上线程的状态,在master上，可以看到slave的I/O线程创建的连接Master:mysql>show processlist\G;1.row为处理slave的I/O线程的连接。2.row为处理MySQL客户连接线程。3.row为处理本地命令行的线程Slave:mysql>show processlist\G; 1.row为处理slave的I/O线程的连接。2.row为处理MySQL客户连接线程。3.row为处理本地命令行的线程9.主从数据复制同步测试(1)在Master中的edu库上变更数据的同步测试:Master:mysql> insert into edu_user values(2,'test1','123456');Slave:mysql> start slave;同步数据库 08-31 11:10