配置MySQL Replication

MySQL支持单向、异步复制，复制过程中一个服务器充当主服务器(Master)，而一个或多个其它服务器充当从服务器(Slave)，利用该特性实现读写分离，是很多大型网站常用的数据库架构。MySQL的replication的配置相对于Oracle来说，要简单的多。本文演示了在同一台 windows机器中配置单向异步复制的过程。这里的Replication是异步复制。MySQL的同步复制是MySQL Cluster中的一个特性。

要启用复制特性，MySQL必须使用二进制日志。关于二进制日志的特性，请参考官方手册（5.0 ,5.1 ,6.0 ）。

本例中MySQL的版本：

 

Master 配置

mysql> select version();
+————————-+
| version() |
+————————-+
| 5.0.37-community-nt-log |
+————————-+
1 row in set (0.00 sec)

 

主库配置文件my.ini

port=3306
datadir=”D:/Program Files/MySQL/MySQL Server 5.0/Data/”  # 可选

server-id=1
log-bin=mysql-bin.log

binlog-ignore-db=mysql #第三行表示不记录数据库mysql的更新日志，这就避免了Master上的权限设置等被同步到Slave上

port=3306
datadir=”D:/Program Files/MySQL/MySQL Server 5.0/Data/”  # 可选

server-id=1
log-bin=mysql-bin.log

binlog-ignore-db=mysql #第三行表示不记录数据库mysql的更新日志，这就避免了Master上的权限设置等被同步到Slave上

 

进入master, 运行如下命令：

创建复制用户

mysql>GRANT REPLICATION SLAVE ON *.* TO rep@localhost IDENTIFIED BY 'rep';

如果想要在Slave上有权限执行 "LOAD TABLE FROM MASTER" 或 "LOAD DATA FROM MASTER" 语句的话，必须授予全局的

FILE 和 SELECT 权限：

mysql>GRANT FILE,SELECT,REPLICATION SLAVE ON *.* TO rep@localhost IDENTIFIED BY 'rep';

 

接下来备份Master上的数据，首先执行如下SQL语句：

mysql>FLUSH TABLES WITH READ LOCK; 

不要退出这个终端，否则这个锁就不生效了；接着导出数据，可以直接打包压缩数据文件，也可以使用mysqldump工具来做，推荐前者的方法，这样更为快捷简便。

 

 

Slave 配置

从库配置文件my2.ini

port=3307

server-id=2

log-slave-updates #启用从库日志，这样可以进行链式复制

read-only=1 #从库是否只读，0表示可读写，1表示只读

replicate-do-table=tablename #只复制某个表

replicate-wild-do-table=tablename% #只复制某些表（可用匹配符）

replicate-do-db=dbname #只复制某个库

replicte-wild-do-db=dbname% #只复制某些库

replicate-ignore-table=tablename #不复制某个表

replicate-wild-ignore-table=tablename% #不复制某些表

replicate-ignore-db=dbname #不复制某个库

relay-log-purge=1 #复制完的sql语句是否立即从中继日志中清除，1表示立即清除
report-host=hostname #从服务器主机，用于show slave hosts生成从库清单

master-host = localhost #主服务器名
master-user = rep  #同步账户名，默认是test
master-password = rep #同步帐户密码，默认是空
master-port = 3306 #主服务器的 TCP/IP 端口号，默认是3306
replicate-ignore-db=mysql #略过同步的数据库名，如果有多个，请设置多次
replicate-do-db=test #想要同步的数据库名，如果有多个，请设置多次
read-only=1 #从库是否只读，0表示可读写，1表示只读

 

 

复制master库到slave库

初始化使用mysqldump来完成，将需要同步的表格结构复制到slave中。

 

启动从库
mysqld-nt –defaults-file=my2.ini

连接到从库进行配置

D:\>mysql -uroot -ppassword -P3307

 

 

mysql> start slave;

测试Replication

在主库 解开table的锁定
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

创建测试table，插入数据

mysql> use test
Database changed

mysql> create table testrep(i int);
Query OK, 0 rows affected (0.05 sec)

mysql> insert into testrep values(1);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

 

然后再到从库 查询
mysql> select * from testrep;
+——+
| i |
+——+
| 1 |
+——+
1 row in set (0.00 sec)

可见数据已经从主库复制到从库。

 

在从库的数据目录下，有几个和复制相关的文件需要说明一下：
*-reloay-bin.* 从主库同步过来的Bin log文件，也叫中继日志
master.info 主库帐号信息和同步信息，这里记录了复制用户名和密码，需要保护好权限。
relay-log.info 跟踪执行同步过来的Bin log的执行情况

通过show processlist可以查看主从库用于复制的相关进程(在windows上实际实现为线程)的信息
主库：

mysql> show processlist\G
*************************** 1. row ***************************
Id: 1
User: root
Host: localhost:3736
db: test
Command: Query
Time: 0
State: NULL
Info: show processlist
*************************** 2. row ***************************
Id: 2
User: rep
Host: localhost:3745
db: NULL
Command: Binlog Dump
Time: 68
State: Has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
2 rows in set (0.00 sec)

可以看到Id为2的进程是用于复制的进程，state可用于监控复制的状态，具体含义参考官方文档。

从库：

mysql> show processlist\G
*************************** 1. row ***************************
Id: 1
User: root
Host: localhost:3741
db: test
Command: Query
Time: 0
State: NULL
Info: show processlist
*************************** 2. row ***************************
Id: 2
User: system user
Host:
db: NULL
Command: Connect
Time: 88
State: Waiting for master to send event
Info: NULL
*************************** 3. row ***************************
Id: 3
User: system user
Host:
db: NULL
Command: Connect
Time: 51
State: Has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
3 rows in set (0.00 sec)

可以看到从库启动了两个复制进程，一个用于和主库交互，取得日志，另外一个则用于应用日志到从库。

MySQL的复制主要是通过解析主库的二进制日志，然后再在从库应用来实现的。这种方式和Oracle Streams的本质思想是一致的。通过MySQL自带的工具mysqlbinlog，可以dump出二进制日志中的具体内容，实际上就是一条条的sql 语句：