mysql主从复制的原理
Master,记录数据更改操作
启用binlog记录模式
允许Slave读取binlog日志
Slave运行两个同步线程
Slave_IO:负责连接Master,复制其binlog日志文件到本机的relay-log文件
Slave_SQL:负责执行本机relay-log文件里的SQL语句,重现Master的数据操作
基本构建思路
初始化现有库
将主库倒入重库,确保数据一致性
配置Master,主服务器
调整运行参数,授权一个同步用户
配置Slave,从服务器
调整运行参数,发起同步操作
一：初始化现有库
1)备份MySQL Master上现有的库,如果服务器已经启用binlog，建议对日志做一次重置，否则可忽略：
[root@master ~]# mysql -u root -p Enter password: //以数据库用户root登入 .. .. mysql> RESET MASTER; //重置binlog日志 Query OK, 0 rows affected (0.07 sec) mysql> quit //退出mysql> 环境 Bye
以备份mysql库、test库为例，导出操作如下：
[root@master ~]# mysqldump -u root -p -B mysql test > /root/mytest.sql Enter password: //验证口令 [root@dbsvr1 ~]# ls -lh /root/mytest.sql //确认备份结果 -rw-r--r--. 1 root root 457K 1月 18 11:50 /root/mytest.sql
2）在MySQL Slave上导入备份的库
先清理目标库，避免导入时冲突。主要是采用InnoDB引擎的库，授权库mysql多采用MyISAM引擎，可不做清理。
[root@slave ~]# mysql -u root -p Enter password: //以数据库用户root登入 .. .. mysql> DROP DATABASE test; //删除test库 Query OK, 0 rows affected (0.03 sec) mysql> quit //退出mysql> 环境 Bye
使用scp工具下载备份文件：
[root@slave ~]# scp dbsvr1:/root/mytest.sql ./ root@dbsvr1's password: //验证对方系统用户root的口令 mytest.sql 100% 577KB 576.6KB/s 00:01 [root@dbsvr2 ~]# ls -lh mytest.sql //确认下载结果 -rw-r--r--. 1 root root 457K 1月 16 12:00 mytest.sql
执行导入操作：
[root@slave ~]# mysql -u root -p < mytest.sql
Enter password:
二：配置MySQL Master
1)修改/etc/my.cnf配置，重新启动MySQL服务程序,指定服务器ID号、允许日志同步：
[root@master mysql]# vim /etc/my.cnf [mysqld] log_bin=dbsvr1-bin //启用binlog日志，并指定文件名前缀 server_id = 10 //指定服务器ID号 innodb_flush_log_at_trx_commit=1 //优化设置 sync-binlog=1 //允许日志同步 #binlog-do-db=YYY //需要同步的数据库，不在内的不同步。（不添加这行表示同步所有） binlog-do-db=test1,test2 //这里主库只同步test1，test2库。 #binlog-ignore-db = test1,test2 //这里向从库同步时忽略test1，test2库 .. .. sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES explicit_defaults_for_timestamp=1 [root@master ~]# service mysqld restart
2）新建一个备份用户，授予复制权限
需要的权限为REPLICATION SLAVE，允许其从Slave服务器访问：
REPLICATION SLAVE 常用于建立复制时所需要用到的用户权限，也就是slave server必须被master server授权具有该权限的用户，才能通过该用户复制。
并且"SHOW SLAVE HOSTS"这条命令和REPLICATION SLAVE权限有关，否则执行时会报错：
REPLICATION CLIENT 不可用于建立复制，有该权限时，只是多了可以使用如"SHOW SLAVE STATUS"、"SHOW MASTER STATUS"等命令。
在5.6.6版本以后，也可以使用"SHOW BINARY LOGS"。
mysql> GRANT REPLICATION SLAVE ON *.* TO 'replicater'@'192.168.10.%' IDENTIFIED BY 'pwd123'; Query OK, 0 rows affected (0.03 sec)
3）检查Master服务器的同步状态
在已经初始化现有库的情况下，查看MASTER状态，记录下当前的日志文件名、偏移的位置（下面SLAVE发起复制时需要用到）：
mysql> SHOW MASTER STATUSG *************************** 1. row *************************** File: dbsvr1-bin.000002 //记住当前的日志文件名 Position: 334 //记住当前的位置 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.03 sec)
三：配置MySQL Slave（从服务器）
1）修改/etc/my.cnf配置，重新启动MySQL服务程序
指定服务器ID号、允许日志同步：
[root@slave ~]# vim /etc/my.cnf [mysqld] log_bin=dbsvr2-bin //启动SQL日志，并指定文件名前缀 server_id = 20 //指定服务器ID号，不要与Master的相同 innodb_flush_log_at_trx_commit=1 //优化设置 sync-binlog=1 //允许日志同步 log_slave_updates=1 //记录从库更新，便于实现“主-从-从”链式复制 #replicate-do-db //设定需要复制的数据库（多数据库使用逗号，隔开） #replicate-ignore-db //设定需要忽略的复制数据库 （多数据库使用逗号，隔开） #replicate-do-table=XXX.XX //设定需要复制的表 #replicate-ignore-table //设定需要忽略的复制表 #replicate-wild-do-table //同replication-do-table功能一样，但是可以通配符 #replicate-wild-ignore-table //同replication-ignore-table功能一样，但是可以加通配符 .. .. sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES explicit_defaults_for_timestamp=1
增加通配符的两个配置
replicate-wild-do-table=db_name.% //只复制哪个库的哪个表 replicate-wild-ignore-table=mysql.% //忽略哪个库的哪个表
在生产环境中，还可以根据需要设置更详细的同步选项。比如，指定当主、从网络中断时的重试超时时间（slave-net-timeout=60 ）等，具体可参考MySQL手册。
配置完成后，重启mysql服务：
2）登入 mysql> 环境，发起同步操作
通过CHANGE MASTER语句指定MASTER服务器的IP地址、同步用户名/密码、起始日志文件、偏移位置（参考MASTER上的状态输出）：
mysql> CHANGE MASTER TO MASTER_HOST='192.168.10.10', -> MASTER_USER='replicater', -> MASTER_PASSWORD='pwd123', -> MASTER_LOG_FILE='dbsvr1-bin.000002', //对应Master的日志文件 -> MASTER_LOG_POS=334, //对应Master的日志偏移位置 -> MASTER_PORT=3306;
Query OK, 0 rows affected, 2 warnings (0.12 sec)
然后执行START SLAVE（较早版本中为SLAVE START）启动复制：
mysql> START SLAVE; //启动复制 Query OK, 0 rows affected (0.04 sec
注意：一旦启用SLAVE复制，当需要修改MASTER信息时，应先执行STOP SLAVE停止复制，然后重新修改、启动复制。
通过上述连接操作，MASTER服务器的设置信息自动存为master.info文件，以后每次MySQL服务程序时会自动调用并更新，无需重复设置。查看master.info文件的开头部分内容，可验证相关设置：
3）检查Slave服务器的同步状态
通过SHOW SLAVE STATUS语句可查看从服务器状态，确认其中的IO线程、SQL线程正常运行，才能成功同步：
mysql> SHOW SLAVE STATUSG *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.4.10 Master_User: replicater Master_Port: 3306 Connect_Retry: 60 Master_Log_File: dbsvr1-bin.000002 Read_Master_Log_Pos: 334 Relay_Log_File: dbsvr2-relay-bin.000002 Relay_Log_Pos: 284 Relay_Master_Log_File: dbsvr1-bin.000002 Slave_IO_Running: Yes //IO线程应该已运行 Slave_SQL_Running: Yes //SQL线程应该已运行 .......... .........
若START SLAVE直接报错失败，请检查CHANGE MASTER相关设置是否有误，纠正后再重试；若IO线程或SQL线程有一个为“No”，则应检查服务器的错误日志，分析并排除故障后重启主从复制。
四:测试主从同步效果
1）在Master上操作数据库、表、表记录
新建newdb库、newtable表，随意插入几条表记录,然后在slave上查看是否同步到相应的数据
........
.........
五:将Slave服务器设为只读
一般来说，为了避免写入冲突，采用主、从复制结构时，不应该允许用户从Slave执行数据库写入操作，这样会导致双方数据的不一致性。
正因为如此，我们可以把Slave数据库限制为只读模式，这种情况下有SUPER权限的用户和SLAVE同步线程才能写入。相关验证操作及效果可参考以下过程。
1）修改/etc/my.cnf文件，重载配置
[root@slave ~]# vim /etc/my.cnf [mysqld] .. .. read_only=1 //启动只读模式 [root@slave ~]# service mysqld restart //重启服务 Shutting down MySQL.. [确定] Starting MySQL... [确定]