Mysql主从复制

Mysql主从复制

 
本文档主要阐述mysql5.5.10的主从复制的配置操作，MySQL5.5的Replication是一个半同步的复制过程，较之前旧版本的异步同步有了较大的改进。
 
Master：172.16.42.214
OS：RadHat Enterprise AS 4.5
MySQL：mysql-5.5.10-linux2.6-i686
Mysql服务配置文件：/etc/my.cnf
主机ssh远程登录： root/123456
mysql登录：root/12345678 (主机上登录不需要密码，直接用mysql登录)
 
Slave：172.16.42.148
OS：RadHat Enterprise AS 4.5
MySQL：mysql-5.5.10-linux2.6-i686
Mysql服务配置文件：/etc/my.cnf
主机ssh远程登录： root/123456
mysql登录：root/12345678 (主机上登录不需要密码，直接用mysql登录)

Master-Slave架构图

下图示例一个完整的复制流程：
 

Master-Slave流程图

 
在master和slave上均创建三个数据库mydb、addb、mytest，这是slave需要同步的三个库。
 
一、在master端
修改mysql配置文件[mysqld]节点内容：
log-bin = mysql-bin       #复制过程即Slave从Master端获取该日志再执行日志记录的操作
server-id = 1             #1代表master服务
binlog-do-db = mydb      #要同步的库mydb
binlog-do-db = addb       #要同步的库addb
binlog-do-db = mytest      #要同步的库mytest
binlog-ignore-db = mysql   #不同步的库，初始所有库默认为不同步，但为了明确最好加上
 
重启mysql服务，使配置生效。
service mysqld stop
service mysqld start
 
添加要从slave服务器访问master服务器的有权限的帐号
登录mysql后执行（用户名：repluser，密码：123456）：
mysql>grant replication slave on *.* to repluser@’172.16.42.148′ identified by ‘123456’;
 
备份需要同步的数据库
mysql> flush tables with read lock;
保证在执行此次同步备份过程中master上不会有新数据或程序（存储过程）的写入或更新，若在备份过程中有新数据写入则会等待直到解锁后才会执行。
 
查看master状态信息
mysql> show master status;
+———————-+———-+———————–+————————————–+
| File            | Position | Binlog_Do_DB   |  Binlog_Ignore_DB        |
+———————-+———-+———————–+————————————–+
| mysql-bin.000053 |    107 | mydb,addb,mytest |   mysql                  |
+———————-+———-+———————–+————————————–+
记录下file和Position信息，slave需根据这两个信息来确定复制位置点。
通过mysqldump生成mytest的备份文件：dump.sql，如果不需要复制mydb之前的内容则可以不用备份，但slave上一定得有需要复制库的表结构。
 
待备份完成后进行解锁
mysql> unlock tables;
在锁定过程中等待的表写入和存储过程更新等操作解锁后会立即执行。
 
二、在slave端
修改mysql配置文件[mysqld]节点内容：
server-id = 2    #2代表slave服务
replicate-ignore-table = mytest.tbltestc  #不同步mytest库的tbltestc表
replicate-ignore-table = mytest.tbltestd  #不同步mytest库的tbltestd表
skip-slave-start  #防止复制随着mysql启动而自动启动。即slave端的mysql服务重启后需手动来启动主从复制（slave start），最好加上，slave端数据库服务重启后手动启动slave比较安全。
 
如果slave端数据库服务终止，在此期间master端可能有新创建的表newtable，并有数据写入该表，由于slave端服务终止，此时slave端无法创建newtable和写入数据，待slave数据库服务重新启动后，若master端再次对表newtable进行写操作时，slave端会执行同样的写表语句，但由于无法找到newtable表而产生错误，可通过show slave status\G 来查看Last_SQL_Error的错误信息。所以在slave端mysql服务终止的情况下，最好记录下master端新增的表结构。
 
如果slave服务也同时作为另一个mysql服务的master服务，则配置文件[mysqld]节点内容还需要加上log-slave-updates，该参数可参阅手册说明：
Normally, a slave does not log to its own binary log any updates that are received from a master server. This option tells the slave to log the updates performed by its SQL thread to its own binary log. For this option to have any effect, the slave must also be started with the –log-bin option to enable binary logging. –log-slave-updates is used when you want to chain replication servers. For example, you might want to set up replication servers using this arrangement:
A -> B -> C
Here, A serves as the master for the slave B, and B serves as the master for the slave C. For this to work, B must be both a master and a slave. You must start both A and B with –log-bin to enable binary logging, and B with the –log-slave-updates option so that updates received from A are logged by B to its binary log.
 
重启mysql服务，使配置生效。
service mysqld stop
service mysqld start
 
登录mysql并停止slave服务
mysql>slave stop;
设置与master服务器相关的配置参数（用到之前记录的master信息）：
mysql>change master to
mysql>MASTER_HOST=’172.16.42.214′,
mysql>MASTER_USER=’repluser’,
mysql>MASTER_PASSWORD=’123456′,
mysql>MASTER_LOG_FILE=’mysql-bin.000053′,
mysql>MASTER_LOG_POS=107;
 
开启slave服务
mysql> slave start;
若在slave未停止的状态下启动slave服务，会产生错误：
ERROR 1198 (HY000): This operation cannot be performed with a running slave; run STOP SLAVE first.
 
查看slave状态信息
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.42.214
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000053
Read_Master_Log_Pos: 107
Relay_Log_File: localhost-relay-bin.000009
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000053
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table: mytest.tbltestb,mytest.tbltesta
Replicate_Ignore_Table: mytest.tbltestd,mytest.tbltestc
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 107
Relay_Log_Space: 559
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec);
 
保证Slave_IO_Running: Yes
Slave_SQL_Running: Yes
 
MySQL自5.1.7版本后不必在salve端mysql配置文件[mysqld]节点内容配置关于master的参数，否则slave端的mysql服务无法启动。
 
指定不同步的表必须在slave端配置文件[mysqld]节点内容中声明，在master端配置文件[mysqld]节点内容中声明不会生效，如：
replicate-do-table = mydb.tblname_a
replicate-do-table = mydb.tblname_b
replicate-ignore-table = mydb.tblname_c  #不同步mydb库的tblname_c表
replicate-ignore-table = mydb.tblname_d  #不同步mydb库的tblname_d表
 
当master端在声明mytest为不复制的数据库时(binlog-ignore-db=mytest)，在slave端中针对mytest库表而声明的replicate-do-table都将不会生效。

最后编辑：2013-11-04

作者：dean

这个作者貌似有点懒，什么都没有留下。

站内专栏 站点