mysql之replication配置

在MySQL 3.23.15版本之后,MySQL提供了数据库复制的功能,可以实现两个数据库实时同步,增强了MySQL数据库的稳定性，而且可以在企业级应用的数据库层实现Cluster…

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

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

本例中MySQL的版本：

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
从库配置文件my2.ini

在master上，其实不需要做什么设置，只需要打开log-bin,写上server-id=1,写上要备份的数据库，则自动是master模式，于是问题主要集中在slave上.那么slave上是如何工作的呢?
Slave上Mysql的Replication工作有两个线程,I/O thread和SQL thread,I/O 的作用是从master 3306端口上把它的binlog取过来(master在被修改了任何内容之后,就会把修改了什么写到自己的binlog等待slave更新),然后写到本地的relay-log,而SQL thread则是去读本地的relay-log,再把它转换成本Mysql所能理解的东西，于是同步就这样一步一步的完成.决定I/O thread的是/var/lib/mysql/master.info,而决定SQL thread的是/var/lib/mysql/relay-log.info.
请注意，因为上边提到了binlog里的内容是改了什么东东,而不是改了以后是什么东东,所以在进行同步之前必须保证两个数据库是完全相同的,不然可能出错.打个比方来说.A机上有一个表里的元组为2,而操作是减一,则binlog只会记录减一这个操作，如果B机上没有，那么则无法得到同步,因为B机没有这个字段，就不知道减一是什么操作.
对于故障诊断,我的方法是都在slave(master/slave是相对的)的mysql(指客户端)里完成：
方法一:show slave status;
方法二:show processlist;
方法三:show master status;
方法四   查看错误日志
   在D:/Program Files/MySQL/MySQL Server 5.0/（win）或（linux）/var/lib/mysql（mysql安装目录）下有个hostname.err文件，所有的错误都在其中被记录,如下所示:

port=3307
datadir=”D:/Program Files/MySQL/MySQL Server 5.0/Data2/”

server-id=2

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

#从库是否只读，0表示可读写，1表示只读
read-only=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

#复制完的sql语句是否立即从中继日志中清除，1表示立即清除
relay-log-purge=1

#从服务器主机，用于show slave hosts生成从库清单
report-host=hostname
启动主库

mysqld-nt –defaults-file=my.ini

连接到主库中，创建复制用户

D:\>mysql -uroot -ppassword -P3306
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.37-community-nt-log MySQL Community Edition (GPL)

mysql> grant replication slave on *.* to ‘rep’@'localhost’ identified by ‘rep’;
Query OK, 0 rows affected (0.00 sec)
锁住主库的table，以便备份数据文件到从库进行初始化

mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

显示主库状态，注意记下当前二进制日志文件名和position

mysql> show master status;
+——————+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+——————+
| mysql-bin.000002 | 228 | | |
+——————+———-+————–+——————+
1 row in set (0.00 sec)

将D:/Program Files/MySQL/MySQL Server 5.0/Data/下的内容打包复制到D:/Program Files/MySQL/MySQL Server 5.0/Data2/下，执行从库的初始化。当然，初始化也可以使用mysqldump来完成。

另外开启一个cmd，启动从库

mysqld-nt –defaults-file=my2.ini

连接到从库进行配置

D:\>mysql -uroot -ppassword -P3307
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.37-community-nt-log MySQL Community Edition (GPL)

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql> CHANGE MASTER TO
-> MASTER_HOST=’localhost’,
-> MASTER_USER=’rep’,
-> MASTER_PASSWORD=’rep’,
-> MASTER_LOG_FILE=’mysql-bin.000002′,
-> MASTER_LOG_POS=228;
Query OK, 0 rows affected (0.01 sec)
注意到这里master_log_file和master_log_pos就是前面show master status的结果。

启动复制进程

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

至此配置基本完成，在主库解开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语句：

D:\Program Files\MySQL\MySQL Server 5.0\data>mysqlbinlog mysql-bin.000002