MySql Replication

raymanan

1.   准备服务器

由于MySQL不同版本之间的(二进制日志)binlog格式可能会不一样，因此最好的搭配组合是Master的MySQL版本和Slave的版本相同或者更低，Master的版本肯定不能高于Slave版本。

 

假设同步主机1名为：192.168.0.150，主机2名为：192.168.0.142，2个MySQL的basedir目录都是/usr/share/mysql，datadir都是：/var/lib/mysql。

 

1. 主机1：
2. 
   
3. 操作系统：fc13.x86_64
4. 
   
5. IP：192.168.0.150
6. 
   
7. MySQL版本：5.1.52

  

1. 主机2：
   
2. 
   
3. 操作系统：fc13.x86_64
   
4. 
   
5. IP：192.168.0.142
   
6. 
   
7. MySQL版本：5.1.52

2.   安装Mysql

1. Yum install mysql

3.   创建数据库

分别登录主机1和主机2的mysql：

1. mysql –u root –p

创建数据库：

1. create database repdatabase;

4.   设置同步服务器 4.1   设置主机1：192.168.0.150 4.1.1     配置my.cnf文件

每个同步服务器都必须设定一个唯一的编号，否则同步就不能正常运行。接下来开始修改 /etc目录下的my.cnf，在[mysqld]配置段增加以下几行：

1. server-id = 1
2. 
   
3. log-bin
4. binlog_format='MIXED'
5. 
   
6. binlog-do-db= repdatabase #需要同步的数据库，如果没有本行，即表示同步所有的数据库
7. 
   
8. binlog-ignore-db=mysql #被忽略的数据库
9. 
   
10. 
    
11. 
    
12. master-host = 192.168.0.142 #主服务器名
13. 
    
14. master-user = rep #同步账户名，默认是test
15. 
    
16. master-password = rep #同步帐户密码，默认是空
17. 
    
18. master-port = 3306 #主服务器的 TCP/IP 端口号，默认是3306
19. 
    
20. master-connect-retry=60
21. 
    
22. 
    
23. 
    
24. #为了使用事务的InnoDB在复制中最大的持久性和一致性，应该指定
25. 
    
26. innodb_flush_log_at_trx_commit=1,sync_binlog=1 选项：
27. 
    
28. replicate-ignore-db=mysql
29. 
    
30. replicate-do-db=repdatabase
31. 
    
32. 
    
33. 
    
34. innodb_flush_log_at_trx_commit=1
35. 
    
36. sync_binlog=1

4.1.2     创建一个有复制权限的用户

在主机1上增加一个账号专门用于同步，如下：

mysql>

1. GRANT REPLICATION SLAVE ON *.* TO rep@192.168.0.142 IDENTIFIED BY 'rep';

 

如果想要在主机2上有权限执行 "LOAD TABLE FROM MASTER" 或 "LOAD DATA FROM MASTER" 语句的话，必须授予全局的 FILE 和 SELECT 权限：

mysql>

1. GRANT FILE,SELECT,REPLICATION SLAVE ON *.* TO rep@192.168.0.142 IDENTIFIED BY 'rep';

4.1.3     重启mysql服务

1. service mysqld restart

4.1.4     锁住主机，注意二进制日志的位置并备份主机数据库

a)   用show master status 命令看日志情况

mysql>

1. show master status;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysqld-bin.000001 |      106 | repdatabase  | mysql            |

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

1 row in set (0.00 sec)

b)         备份主机1上的数据，首先执行如下SQL语句：

mysql>

1. FLUSH TABLES WITH READ LOCK;

 

不要退出这个终端，否则这个锁就不生效了；接着导出数据:

1. [billy@black ~]$ mysqldump -u root repdatabase > /home/billy/mysql/master/repdatabase.sql

用以下命令在主机上重新打开写操作功能：

mysql>

1. UNLOCK TABLES;

4.2   设置主机2：192.168.0.142 4.2.1     配置my.cnf文件

在[mysqld]配置段增加如下几行：

1. server-id = 2
2. 
   
3. log-bin
4. binlog_format='MIXED'
5. 
   
6. binlog-do-db= repdatabase #需要同步的数据库，如果没有本行，即表示同步所有的数据库
7. 
   
8. binlog-ignore-db=mysql #被忽略的数据库
9. 
   
10. 
    
11. 
    
12. master-host = 192.168.0.150 #主服务器名
13. 
    
14. master-user = rep #同步账户名，默认是test
15. 
    
16. master-password = rep #同步帐户密码，默认是空
17. 
    
18. master-port = 3306 #主服务器的 TCP/IP 端口号，默认是3306
19. 
    
20. master-connect-retry=60
21. 
    
22. replicate-ignore-db=mysql
23. 
    
24. replicate-do-db=repdatabase
25. 
    
26. 
    
27. 
    
28. #为了使用事务的InnoDB在复制中最大的持久性和一致性，应该指定
29. 
    
30. innodb_flush_log_at_trx_commit=1,sync_binlog=1 选项：
31. 
    
32. innodb_flush_log_at_trx_commit=1
33. 
    
34. sync_binlog=1

4.2.2     创建一个有复制权限的用户

在主机2上增加一个账号专门用于同步，如下：

mysql>

1. GRANT REPLICATION SLAVE ON *.* TO rep@192.168.0.150 IDENTIFIED BY 'rep';

 

如果想要在主机2上有权限执行 "LOAD TABLE FROM MASTER" 或 "LOAD DATA FROM MASTER" 语句的话，必须授予全局的 FILE 和 SELECT 权限：

mysql>

1. GRANT FILE,SELECT,REPLICATION SLAVE ON *.* TO rep@192.168.0.150 IDENTIFIED BY 'rep';

4.2.3     在主机2上加载Dump（转存）文件

1. [billy@blue ~]$ mysql -u root repdatabase </home/billy/mysql/slave/ repdatabase.sql

4.2.4     重启mysql服务

1. service mysqld restart

4.2.5     初始化复制

a)         在主机2中进入mysql，启动Slave：

mysql>

1. start slave;

 

若提示：

ERROR 1200 (HY000): The server is not configured as slave; fix in config file or with CHANGE MASTER TO

则执行以下操作：

先在master主机执行show master status：

1. mysql>show master status;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysqld-bin.000001 |      106 | repdatabase  | mysql            |

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

1 row in set (0.00 sec)

然后在主机2执行：

1. mysql> stop slave;
   
2. 
   
3. Query OK, 0 rows affected (0.00 sec)
   
4. 
   
5. mysql> change master to master_host='192.168.0.150', master_user='rep', master_password='rep',master_log_file='mysqld-bin.000001', master_log_pos=106;
   
6. 
   
7. Query OK, 0 rows affected (0.09 sec)
   
8. 
   
9. mysql> start slave;
   
10. 
    
11. Query OK, 0 rows affected (0.00 sec)

这些参数代表：

l MASTER_HOST: 主机的IP或者名称

l MASTER_USER: 这是我们在前面步骤中授予REPLICATION SLAVE权限的用户，在这

个例子中就是“rep”

l MASTER_PASSWORD: 这是我们在前面步骤中分配给“rep”的密码

l MASTER_LOG_FILE: 这是我们在前面步骤中确定的文件名（File）

l MASTER_LOG_POS: 这是我们在前面步骤中确定的位置（Position）

b)   启动主机1的slave：

1. mysql>start slave;

 （细节参考上一条）

c)         启动成功后，分别登录两台主机，查看同步状态：

1. mysql -hlocalhost -uroot
   
2. 
   
3. mysql>SHOW SLAVE STATUS\G
   
4. 
   
5. *************************** 1. row ***************************
   
6. 
   
7.              Slave_IO_State: Waiting for master to send event
   
8. 
   
9.                 Master_Host: rep1
   
10. 
    
11.                 Master_User: rep
    
12. 
    
13.                 Master_Port: 3306
    
14. 
    
15.               Connect_Retry: 60
    
16. 
    
17.             Master_Log_File: binlog.000001
    
18. 
    
19.         Read_Master_Log_Pos: 98
    
20. 
    
21.              Relay_Log_File: relay.000003
    
22. 
    
23.               Relay_Log_Pos: 232
    
24. 
    
25.       Relay_Master_Log_File: binlog.000001
    
26. 
    
27.            Slave_IO_Running: Yes
    
28. 
    
29.           Slave_SQL_Running: Yes
    
30. 
    
31.             Replicate_Do_DB:
    
32. 
    
33.         Replicate_Ignore_DB:
    
34. 
    
35.          Replicate_Do_Table:
    
36. 
    
37.      Replicate_Ignore_Table:
    
38. 
    
39.     Replicate_Wild_Do_Table:
    
40. 
    
41. Replicate_Wild_Ignore_Table:
    
42. 
    
43.                  Last_Errno: 0
    
44. 
    
45.                  Last_Error:
    
46. 
    
47.                Skip_Counter: 0
    
48. 
    
49.         Exec_Master_Log_Pos: 98
    
50. 
    
51.             Relay_Log_Space: 232
    
52. 
    
53.             Until_Condition: None
    
54. 
    
55.              Until_Log_File:
    
56. 
    
57.               Until_Log_Pos: 0
    
58. 
    
59.          Master_SSL_Allowed: No
    
60. 
    
61.          Master_SSL_CA_File:
    
62. 
    
63.          Master_SSL_CA_Path:
    
64. 
    
65.             Master_SSL_Cert:
    
66. 
    
67.           Master_SSL_Cipher:
    
68. 
    
69.              Master_SSL_Key:
    
70. 
    
71.       Seconds_Behind_Master: 0
    
72. 
    
73. 1 row in set (0.00 sec)

 

可以看到，Slave_IO_Running 和 Slave_SQL_Running 两列的值都为 "Yes"，这表明 Slave 的 I/O 和 SQL 线程都在正常运行。

 

至此，同步设定成功。

5.   测试

1. create table class(id int primary key not null,name varchar(12) not null);

6.   基本命令

1. Mysql -u username –p password
   
2. 
   
3. Show master status;
   
4. 
   
5. Show slave status;
   
6. 
   
7. SHOW PROCESSLIST

mysql master 相关命令:

1. show master status
   
2. show slave hosts
   
3. show binlog events
   
4. purge logs to ‘log_name’
   
5. purge logs before ‘date’
   
6. reset master(老版本flush master)
   
7. set sql_log_bin=

mysql slave 相关命令:

1. slave start
   
2. slave stop
   
3. SLAVE STOP IO_THREAD #此线程把master段的日志写到本地
   
4. SLAVE start IO_THREAD
   
5. SLAVE STOP SQL_THREAD #此线程把写到本地的日志应用于数据库
   
6. SLAVE start SQL_THREAD
   
7. reset slave
   
8. SET GLOBAL SQL_SLAVE_SKIP_COUNTER
   
9. load data from master
   
10. show slave status(SUPER,REPLICATION CLIENT)
    
11. CHANGE MASTER TO MASTER_HOST=, MASTER_PORT=,MASTER_USER=, MASTER_PASSWORD= #动态改变master信息
    
12. PURGE MASTER [before ‘date’] #删除master端已同步过的日志

1. show variables like 'binlog_for%';

7.   JDBC 连接

Driver= com.mysql.jdbc.Driver

URL=jdbc:mysql://192.168.0.150:3306,192.168.0.142:3306/repdatabase

Property:

useConfigs=clusterBase

8.   参考文档

wp_mysql-5_5_Replication.cn.pdf