mysql replication

MYSQL支持单向、异步复制，复制过程中一个服务器充当主服务器，而一个或多个其它服务器充当从服务器。主服务器将 更新写入二进制日志文件，并维护日志文件的一个索引以跟踪日志循环。当一个从服务器连接到主服务器时，它通知主服务器从服务器在日志中读取的最后一次成功 更新的位置。从服务器接收从那时起发生的任何更新，然后封锁并等待主服务器通知下一次更新。

在实际项目中，两台分布于异地的主机上安装 有MYSQL数据库，两台服务器互为主备，客户要求当其中一台机器出现故障时，另外一台能够接管服务器上的应用，这就需要两台数据库的数据要实时保持一 致，在这里使用MYSQL的同步功能实现双机的同步复制。

以下是操作实例：

1、数据库同步设置

主机操作系统：RedHat Enterprise Linux 5

数据库版本：mysql Ver 14.12 Distrib 5.0.22

前提：MYSQL数据库正常启动

假设两台主机地址分别为：

ServA：10.240.136.9

ServB：10.240.136.149

1.1 配置同步账号

在ServA上增加一个ServB可以登录的帐 号：

Mysql>GRANT all privileges ON *.* TO tongbu@'10.240.136.149' IDENTIFIED BY '123456';

在ServB上增加一个ServA 可以登录的帐号：

Mysql>GRANT all privileges ON *.* TO tongbu@'10.240.136.9' IDENTIFIED BY '123456';

1.2 配置数据库参数

1、 以root用户登录ServA，修改ServA的my.cnf文件

vi /etc/my.cnf

在 [mysqld]的配置项中增加如下配置：

<

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

> 1  default - character - set = utf8
2 
3  log - bin = mysql - bin
4 
5  relay - log = relay - bin
6 
7  relay - log - index = relay - bin - index
8 
9  server - id = 1
10 
11  master - host = 10.240 . 136.149
12 
13  master - user = tongbu
14 
15  master - password = 123456
16 
17  master - port = 3306
18 
19  master - connect - retry = 30
20 
21  binlog - do - db = umsdb
22 
23  replicate - do - db = umsdb
24 
25  replicate - ignore - table = umsdb.boco_tb_menu
26 
27  replicate - ignore - table = umsdb.boco_tb_connect_log
28 
29  replicate - ignore - table = umsdb.boco_tb_data_stat
30 
31  replicate - ignore - table = umsdb.boco_tb_log_record
32 
33  replicate - ignore - table = umsdb.boco_tb_workorder_record

2、以root用户登录ServB，修改ServB的my.cnf文件

vi /etc/my.cnf

在[mysqld]的配置项中增加如下配置：

<

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

> 1  default - character - set = utf8
2 
3  log - bin = mysql - bin
4 
5  relay - log = relay - bin
6 
7  relay - log - index = relay - bin - index
8 
9  server - id = 2
10 
11  master - host = 10.240 . 136.9
12 
13  master - user = tongbu
14 
15  master - password = 123456
16 
17  master - port = 3306
18 
19  master - connect - retry = 30
20 
21  binlog - do - db = umsdb
22 
23  replicate - do - db = umsdb
24 
25  replicate - ignore - table = umsdb.boco_tb_menu
26 
27  replicate - ignore - table = umsdb.boco_tb_connect_log
28 
29  replicate - ignore - table = umsdb.boco_tb_data_stat
30 
31  replicate - ignore - table = umsdb.boco_tb_log_record
32 
33  replicate - ignore - table = umsdb.boco_tb_workorder_record

1.3 手工执行数据库同步

假设以ServA为主服务器，在ServB上 重启mysql：

service mysqld restart

在ServB上用root用户登录mysql，执 行：

Mysql> stop slave;

Mysql> load data from master;

Mysql> start slave;

在ServA上重启mysql：

service mysqld restart

1.4 查看数据库同步状态

在mysql命令提示符下执行：

Mysql> show slave status\G

将显示同步进程的状态，如下所示，两行蓝色字体为slave进程状态， 如果都为yes表示正常;红色字体表示同步错误指示，如果有问题会有错误提示：

<

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

> 1  ***************************   1 . row  ***************************
2 
3  Slave_IO_State: Waiting  for  master  to  send event
4 
5  Master_Host:  10.21 . 2.90
6 
7  Master_User: tongbu
8 
9  Master_Port:  3306
10 
11  Connect_Retry:  30
12 
13  Master_Log_File: localhost - bin. 000005
14 
15  Read_Master_Log_Pos:  39753882
16 
17  Relay_Log_File: localhost - relay - bin. 000062
18 
19  Relay_Log_Pos:  9826663
20 
21  Relay_Master_Log_File: localhost - bin. 000005
22 
23  Slave_IO_Running: Yes
24 
25  Slave_SQL_Running: Yes
26 
27  Replicate_Do_DB: bak,umsdb
28 
29  Replicate_Ignore_DB:
30 
31  Replicate_Do_Table:
32 
33  Replicate_Ignore_Table: umsdb.boco_tb_connect_log,umsdb.boco_tb_menu,umsdb.boco_tb_workorder_record,umsdb.boco_tb_data_stat,umsdb.boco_tb_log_record
34 
35  Replicate_Wild_Do_Table:
36 
37  Replicate_Wild_Ignore_Table:
38 
39  Last_Errno:  0
40 
41  Last_Error:
42 
43  Skip_Counter:  0
44 
45  Exec_Master_Log_Pos:  39753882
46 
47  Relay_Log_Space:  9826663
48 
49  Until_Condition: None
50 
51  Until_Log_File:
52 
53  Until_Log_Pos:  0
54 
55  Master_SSL_Allowed: No
56 
57  Master_SSL_CA_File:
58 
59  Master_SSL_CA_Path:
60 
61  Master_SSL_Cert:
62 
63  Master_SSL_Cipher:
64 
65  Master_SSL_Key:
66 
67  Seconds_Behind_Master:  0

3、 数据库同步测试

配置完数据库后进行测试，首先在网络正常情况下测试，在ServA上进行数据库操作，和在ServB上进行数据库操作，数据都能够同步过去。

拔掉ServB主机上的网线，然后在ServA上做一些数据库操作，之后再恢复ServB的网络环境，但是在ServB上却看不到同步的数据，通过命令 show slave status\G查看发现Slave_IO_Running的状态是No，这种状态持续很长一段时间，数据才能同步到ServB上去。这是什么问题呢?同 步延迟不会这么大吧。后来通过网上查找相关资料，找到一个同步延迟相关的参数：

--slave-net-timeout=seconds

参数含义：当slave从主数据库读取log数据失败后，等待多久重新建立连 接并获取数据。

于是在配置文件中增加该参数，设置为60秒

slave-net-timeout=60

重启MYSQL数据库后测试，该问题解决。

4、 数据库同步失效的解决

当数据同步进程失效后，首先手工检查slave主机当前备份的数据库日志文件在 master主机上是否存在，在slave主机上运行：

mysql> show slave status\G

一般获得如下的信息：

<

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

> 1  ***************************   1 . row  ***************************
2 
3  Slave_IO_State: Waiting  for  master  to  send event
4 
5  Master_Host:  10.21 . 3.240
6 
7  Master_User: tongbu
8 
9  Master_Port:  3306
10 
11  Connect_Retry:  30
12 
13  Master_Log_File: mysql - bin. 000001
14 
15  Read_Master_Log_Pos:  360
16 
17  Relay_Log_File: localhost - relay - bin. 000003
18 
19  Relay_Log_Pos:  497
20 
21  Relay_Master_Log_File: mysql - bin. 000001
22 
23  Slave_IO_Running: Yes
24 
25  Slave_SQL_Running: Yes
26 
27  Replicate_Do_DB: bak
28 
29  Replicate_Ignore_DB:
30 
31  Replicate_Do_Table:
32 
33  Replicate_Ignore_Table:
34 
35  Replicate_Wild_Do_Table:
36 
37  Replicate_Wild_Ignore_Table:
38 
39  Last_Errno:  0
40 
41  Last_Error:
42 
43  Skip_Counter:  0
44 
45  Exec_Master_Log_Pos:  360
46 
47  Relay_Log_Space:  497
48 
49  Until_Condition: None
50 
51  Until_Log_File:
52 
53  Until_Log_Pos:  0
54 
55  Master_SSL_Allowed: No
56 
57  Master_SSL_CA_File:
58 
59  Master_SSL_CA_Path:
60 
61  Master_SSL_Cert:
62 
63  Master_SSL_Cipher:
64 
65  Master_SSL_Key:
66 
67  Seconds_Behind_Master:  0

其中Master_Log_File描述的是master主机上的日志文件。

在master上检查当前的数据库列表：

mysql> show master logs;

得到的日志列表如下：

+----------------------+-----------+

| Log_name | File_size |

+----------------------+-----------+

| localhost-bin.000001 | 495 |

| localhost-bin.000002 | 3394 |

+----------------------+-----------+

如果slave主机上使用的的 Master_Log_File对应的文件在master的日志列表中存在，在slave主机上开启从属服务器线程后可以自动同步：

mysql> start slave;

如果master主机上的日志文件已经不存在，则需要首先从master主机上恢复全部数据，再开启同步机制。

在slave主机上运 行：

mysql> stop slave;

在master主机上运行：

mysql> stop slave;

在slave主机上运行：

mysql> load data from master;

mysql> reset master;

mysql> start slave;

在master主机上运行：

mysql> reset slave;

mysql>start slave;

注意：LOAD DATA FROM MASTER目前只在所有表使用MyISAM存储引擎的数据库上有效。

原文地址：http://tech.it168.com/a2009/0413/271/000000271999.shtml

---

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

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
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#071201 13:17:31 server id 1 end_log_pos 98 Start: binlog v 4, server v 5.0.37-community-nt-log created 071201 13:17
:31 at startup
# Warning: this binlog was not closed properly. Most probably mysqld crashed writing it.
ROLLBACK/*!*/;
# at 98
#071201 13:18:43 server id 1 end_log_pos 228 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1196486323/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/;
SET @@session.sql_mode=1344274432/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
grant replication slave on *.* to ‘rep’@'localhost’ identified by ‘rep’/*!*/;
# at 228
#071201 13:24:43 server id 1 end_log_pos 89 Query thread_id=1 exec_time=0 error_code=0
use test/*!*/;
SET TIMESTAMP=1196486683/*!*/;
insert into test values(6)/*!*/;
# at 317
#071201 13:24:43 server id 1 end_log_pos 344 Xid = 14
COMMIT/*!*/;
# at 344
#071201 13:28:00 server id 1 end_log_pos 434 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1196486880/*!*/;
create table testrep(i int)/*!*/;
# at 434
#071201 13:28:11 server id 1 end_log_pos 92 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1196486891/*!*/;
insert into testrep values(1)/*!*/;
# at 526
#071201 13:28:11 server id 1 end_log_pos 553 Xid = 19
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

---

MySQL复制可被用在许多不同的环境,和用于不同的目的.

1.用于备份

    1.1 使用mysqldump备份Slave

          为了保证数据的完整性,在备份之前需要暂时停止slave的复制使用:

          shell>mysqladmin stop-slave

          或只是暂时停止处理中继日志(relay log),通过:

          shell>mysql -e 'STOP SLAVE SQL_THREAD;'

          这样的话,slave会继续接收数据从master,并将这些数据存储在中继日志中,但阻止了slave执行和修改这些数据,在比较繁忙的时候允许I/O线程继续工作,会加快重新启动slave时的速度.

          接下来通过:

          shell>mysqldump --all-databases >fulldb.dump 来备份所有数据库

          最后重启slave:

          shell>mysqladmin start-slave

     1.2 直接备份源数据(Raw Data)

     1.3 通过将Master或Slave变成只读来进行备份

2. 通过复制来修改存储引擎 -- 比较简单,不必多说

3. 通过复制来实现扩展(Scale-Out)

     因为复制工作是分布式的,所以当需要大量的读操作,少量写或更新操作的时候,使用复制实现扩展会工作的很好.如下图显示了通过复制来提高性能的方案:

4.将不同的数据库复制到不同的Slaves

为了实现这种分离,只需要在每一个slave上,通过--replicate-wild-do-table来过滤掉不需要的语句就可以了.

5.提高复制性能

当多个slaves全部连接到一个Master上时,无形之间就增加了Master的负荷,并且每个slave都要接到一份对Master二进制日志的全拷贝,因此随着网络负荷的增加,新的瓶颈也就随着出现了. 如何解决呢? 一种提高性能的办法就是提供深一层的复制架构,让master只服务于一个slave,然后其它的slaves都连接到上个基本的slave来满足它们各自的复制需求.如图:

为了实现上面的架构,需要如此配置MySQL:

• Master1作为最主要的master,所有的变更全部由它来写入数据库,二进制日志功能必须开启
• Master2是Master1的一个slave,Master2也必须开启二进制日志功能,和--log-slave-updates,这样来自于的Master1的复制指令在Master2中同样被写入了二进制日志,从而可以通过Master2复制给其它Slaves
• Slave1,Slave2,Slave3,都是Master2的Slave

6. 故障期间切换Masters

上图中,MySQL Master处理的是Master数据库,MySQL Slaves都是复制用的Slave,每个Web Client处理数据库的读写问题.每个Slave都开启:--log-bin,而不开启:--log-slave-updates,这样从master收到的事件就不会记录在slave的二进制日志中了.而每个slave只是被初始化为空. 如果由于某些原因,MySQL Master变得不可用了,我们就可以拿出其中一个Slave变成新的Master.例如:用Slave1来替换Master,然后将所有的Web Clients重定向到Slave1,Slave1将会记录所有的更新到二进制日志,Slave2,Slave3从Slave1开始复制. 为了确保每个Slave已经完全处理完了它们的中继日志,在每个Slave上,可以先运行:

STOP SLAVE IO_THREAD,然后查看

SHOW PROCESSLIST,直到显示:Has read all relay log

因为Slave1已经被提升为Master,所以需要:STOP SLAVE 和 RESET MASTER.

其它Slaves需要,STOP SLAVE,然后通过CHANGE MASTER TO,来指向现在的新Master. 参考下图:

7. 使用SSL创建复制

为了使用SSL加密传输的数据,首先必须设置Master支持SSL网络连接,同是创建合适的证书,加到Master的配置中的mysqld部分:

ssl-ca=cacert.pem 证书颁发机构(CA)证书

ssl-cert=server-cert.pem 服务器公有钥匙,用于发送到客户端,并验证其所持有的CA证书

ssl-key=server-key.pem 服务器的私有钥匙

在Slave上有两种方法可以设置SSL,一是在slave的配置文件中的[client]部分加上前面的如Master一样的ssl选项,二是使用CHANGE MASTER TO 进行配置.

如果需要强制要求每个Slave必须使用SSL进行连接,那么可以在创建复制用户时加上:REQUEST SSL选项,如:

mysql>GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.mydomain.com' IDENTIFIED BY 'pass' REQUEST SSL;

8. 半同步复制(MySQL>=5.5)----暂略