mysql主从(master-slave)复制安装配置

参考：

关于清除master和slave的bin log:

http://blog.chinaunix.net/space.php?uid=1838361&do=blog&id=66638

一. 环境准备

准备两台服务器，IP分别为
192.168.1.119 (主数据库服务器）
192.168.1.120 (从数据库服务器）

分别装好mysql数据库服务器

二. 安装配置

1. 在主数据库服务器上设置一个复制使用的账户，并授予replication slave权限，这里创建一个复制用户rep，可以从IP为192.168.1.120的主机进行连接.

mysql > grant replication slave on *.* to 'rep'@'192.168.1.120' identified by '123456';

2. 修改主数据库服务器的配置文件my.cnf，开启binlog，并设置server-id的值。

关于清除master和slave的bin log:

http://blog.chinaunix.net/space.php?uid=1838361&do=blog&id=66638

注意：log-bin必须打开。如果对从库有非SELECT的操作，将会记录日志。

[mysqld] 

server-id=1 

log-bin=/var/lib/mysql/mysql-bin.log

数据库重启后生效

3. 在主服务器上，设置读锁有效，这个操作是为了确保没有数据库操作，以便获得一个一致性的快照。

mysql > flush tables with read lock;

注：只能用在MyISAM存储类型.

可以同时用于MyISAM和InnoDB表：

在master上做SQL转储而无需如上所述备份二进制日志。运行mysqldump --master-data=2命令，然后把结果文件转储到slave上。

4. 然后得到主服务器上当前的二进制日志名的偏移量值。

对于mysqldump，也可以通过查看dump出来的sql文件来获取这两个值，head前50行即可看到。

这个操作的目的是为了在从数据库启动以后，从这个点开始进行数据的恢复。

mysql> show master status;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000058 |     1326 |              |                  |

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

1 row in set (0.00 sec)

5. 建立测试数据库test1

mysql> create database test1; Query OK, 1 row affected (0.01 sec)

6. 备份一份数据库文件，将其恢复到从服务器上，可以通过mysqldump或者直接拷贝文件的方式。这里采用直接copy文件的方式。（或者先mysqldump导出主机A的数据test为 test.sql 然后在，从机B上建立数据库test，mysql导入 test.sql到test库中）

[~@master]# cd /var/lib/mysql 

[~@master]# tar cvf test1.tar test1 test1/ test1/db.opt 

[~@master]# scp test1.tar root@192.168.1.120:/var/lib/mysql/ ... 

[~@slave]# tar xvf test1.tar    

如果dump，最好使用mysqldump --master-data=2 参数记录日志位置

对于同步特定表，就需要使用dump。指定要dump的表，dump，然后倒入到从库。

使用mysqldump，
--master-data=1时，会将change mster写到文件中，
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=2553618;

--master-data=2时，会将change mster写到文件中，但是会注释掉
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=2553618;
稍后start slave时此时你会发现你还需要输入change master相关的参数

  7. 备份完毕后，主数据库恢复被写操作

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

8. 修改从数据库的配置文件my.cnf,添加server-id参数，注意这里的server-id参数必须唯一，不能和主数据库的配置相同.复制使用的用户、主数据库服务器的IP、端口以及连接丢失时，重试的时间

注意：

从服务器上my.cnf中的master-*的设置仅在第一次生效，后保存在master.info文件里。 所以，以下命令写在配置文件里可能不生效，对于mysql5.5，master-host等参数已经不能设置了，设置后会无法启动mysql，需要在mysql命令行中通过change master to来执行以生效。

change master to 命令的使用方法在后面

[mysqld] 

server-id=2 

master-host=192.168.1.119 

master-user=rep 

master-password=123456

master-connect-retry=60

#replicate-do-db=test1(只复制某个库，多个库写多行，尽量不要使用，有弊端)

#replicate-ignore-db=mysql #不复制某个库
注：replicate-do-db= replicate-ignore-db 这些命令不能通过change master to命令来设置，所以如果要同步多个库，请在配置文件里写，然后重启数据库。

不用使用replicate-do-db，要使用replicate-wild-do-table
http://www.mysqlperformanceblog.com/2009/05/14/why-mysqls-binlog-do-db-option-is-dangerous/

不要使用replicate-do-db，要使用replicate-wild-do-table
 最主要的问题就是relicate-do-db不支持跨数据库更新
replicate-wild-do-table This works for both cross-database updates and default database updates

--replicate-do-db=db_name
Tell the slave SQL thread to restrict replication to statements where the default database (that is, the one selected by USE) is db_name.

http://dev.mysql.com/doc/refman/5.0/en/replication-options-slave.html#option_mysqld_replicate-wild-do-table

http://www.mysqlperformanceblog.com/2009/05/14/why-mysqls-binlog-do-db-option-is-dangerous/

注意：对于mysql5.5.，上面的写法会报错，mysql无法启动：

/usr/local/mysql/bin/mysqld: unknown variable 'master-host=1.1.1.1'

不要在mysql.cnf中指定 master-host、master-user、master-password、master-connect-retry，只能使用change master命令来设置主从，详细信息见后面。

在master上设置binlog_do_弊端：
1、过滤操作带来的负载都在master上
2、无法做基于时间点的复制（利用binlog）。

主：

server-id = 1(主数据库一般都是id为1)
log-bin=mysql-bin (必须的)
binlog_format=mixed （必须的，推荐类型为mixd）
expire_logs_days=5 （为避免日志文件过大，设置过期时间为5天）
binlog-ignore-db = mysql （忽略同步的文件，也不记入二进制日志，可列多行)
binlog-ignore-db = information_schema

从：

server-id = 2
log-bin=mysql-bin
binlog_format=mixed
expire_logs_days=5
replicate-do-db = test

下面的例子是同步指定表

忽略数据库db1里面的ox_data_  ox_log_  ox_ext_开头的表

# slave cfg
slave-skip-errors=1062,1146,1050
slave_net_timeout=30
#read_only=true
replicate-do-db=openx

replicate-ignore-table=db1.ox\_data\_%
replicate-ignore-table=db1.ox\_log\_%
replicate-ignore-table=db1.ox\_ext\_%

replicate-wild-do-table=db1.ox\_%

replicate-do-table=db1.table1
replicate-do-table=db1.table2

replicate-do-table和 replicate-wild-do-table的区别

表名使用了通配符时使用replicate-wild-do-table

关于级联复制：

架构如下 master a --------> slave b -------> slave c

需要注意：对于slave b：

这两个配置项必须添加上去。
log_bin=mysql-bin打开从数据库的日志开关，如果对从库有非SELECT的操作，将会记录日志。
log_slave_updates=1 从主库复制过来的SQL语句，将会记录日志。这个的作用一般都A-B-C级联复制的时候使用。

否则，在slave b上show master status时，你会发现，虽然实际数据库的数据已经更改，但是show master status并没有改变，也就造成slave c不更新。

9. 在从服务器上，使用–skip-slave-start 选项启动从数据库，这样就不会立即启动从数据库上的复制进程，方便对数据库的服务进程进行进一步的配置（与正常启动，然后stop slave效果一样）

[~@slave]# /usr/local/mysql/bin/mysqld_safe --skip-slave-start & 

[~@slave]# Starting mysqld daemon with databases from /var/lib/mysql

10. 指定开始执行复制的日志文件和位置（上面的注意项在这里执行）

mysql> change master to master_host='172.18.6.134',master_port=3306,master_user='rsync1',master_password='passowrd',master_log_file='mysql-bin.000058',master_log_pos=1167;

这里的log_file和log_pos是通过主服务器中执行

mysql > show master stauts;+------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 2207 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)

得到

11. 在从服务器上,启动slave进程

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

12. 这时在slave上执行show processlist 命令将显示类似如下进程

mysql> show processlist \G 

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

这表明slave已经连接上master,并开始接受并执行日志.

13.测试复制服务器的正确性,在主数据库上执行一个更新操作,观察是否在从数据库上同步.

mysql> use test1; Database changed

 mysql> create table rep(id int); Query OK, 0 rows affected (0.00 sec)  

 mysql> insert into rep values(1),(2),(3),(4),(5); 

Query OK, 5 rows affected (0.01 sec) Records: 5 Duplicates: 0 Warnings: 0

15. 在从数据库上检查新表是否被创建,数据是否被同步.

mysql> use test1; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A   Database changed mysql> show tables; +-----------------+ | Tables_in_test1 | +-----------------+ | rep | +-----------------+ 1 row in set (0.00 sec)   mysql> select * from rep; +------+ | id | +------+ | 1 | | 2 | | 3 | | 4 | | 5 | +------+ 5 rows in set (0.00 sec)

可以看到数据可以正确同步到从数据库上,复制服务配置成功完成.

三 复制启动选项

这些选项可以在启动时加入,也可以直接写在my.cnf里

1. log-slave-updates
这个参数用来配置从服务器上的更新操作是否写进进制日志,默认不打开,但是,如果这个从服务器同时也要作为其它服务器的主服务器时,就需要启动.
这个参数需要和log-bin一起使用.

2. master-connect-retry
这个参数用来设置在和主服务器连接丢失的时候,重度的时间间隔.

3. read-only
这个参数用来设置从服务器只能接受超级用户的更新操作,从而限制应用程序错误的对从服务器的更新操作.

4. 指定复制的数据库或者表
可以使用replicate-do-db、replicate-do-table、replicate-ignore-db、replicate-ignore-table或replicate-wild-do-table来指定从主数据库复制到从从数据库的数据库或者表。

四　日常管理维护

1. 查看从服务器状态

mysql> show slave status \G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event 

 Master_Host: 192.168.1.119 

 Master_User: rep1 

 Master_Port: 3306 

 Connect_Retry: 60 

Master_Log_File: mysql-bin.000001 

Read_Master_Log_Pos: 2400 

 Relay_Log_File: mysqld-relay-bin.000002 #slave 本机自己的bin 

 Relay_Log_Pos: 428 

Relay_Master_Log_File: mysql-bin.000001 

Slave_IO_Running: Yes 

 Slave_SQL_Running: Yes 

 Replicate_Do_DB: 

Replicate_Ignore_DB: 

Replicate_Do_Table: 

 Replicate_Ignore_Table: 

 Replicate_Wild_Do_Table: 

 Replicate_Wild_Ignore_Table: 

 Last_Errno: 0 

 Last_Error: 

 Skip_Counter: 0 

Exec_Master_Log_Pos: 2400 

 Relay_Log_Space: 428 

 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 

1 row in set (0.00 sec) 

  ERROR: No query specified

主要关心”Slave_IO_Running”和“Slave_SQL_Running”这两个进程的状态是否为”yes”。只要其中一个进程状态为no，则表示复制进程停止，错误原因可以从“Last_Errno”字段中的值看到。

另注意相同颜色文字的状态。

一个主从数据不同步的例子：
经查，132数据库里，相关的表有数据，但是142的没有数据，确定为同步出现问题。
132:show master status\G;

File: mysql-bin.000466
Position: 898072497

142：show slave status\G;几个点

Master_Log_File: mysql-bin.000466
Read_Master_Log_Pos: 898043521
Exec_Master_Log_Pos: 837488162

可以看出，142已经和132基本上读取到了相同位置的数据，但是，142执行的比较慢。
142：show processlist；
可以看出，某个Query操作执行的时间很长，因为在Query时，表是被锁住的，因为是表级锁，所以当slave在写入到该锁住的表时需要等待，因此造成了数据不同步。
解决：在mysql中kill掉占用时间很长的那个Query操作，注意，不要kill掉那个Locked的进程，那个是slave写入的进程，kill掉该进程会造成数据不同步。
另外：看到那个长时间的Query是由139发出，139为某平台的后台，可能是有人在算大的报表。

2. 主从服务器手工同步
从服务器由于各种原因导致更新速度较慢，从而是主从服务器之间的数据差距越来越大，最终对某些应用产生影响，这种情况下，就需要定期地进行主从服务器的数据同步，使得主从服务器差距能够减到最小。常用方法是：在负载较低的时候暂时阻塞主数据库的更新，强制主从数据库更新同步。

阻塞更新

mysql> flush tables with read lock; 

Query OK, 0 rows affected (0.00 sec)   

mysql> show master status; 

+------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 2400 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)

在从服务器上，执行下面语句，其中master_pos_wait()函数的参数是前面步骤中得到的复制坐标值。

mysql> select master_pos_wait('mysql-bin.000001','2400'); 

+--------------------------------------------+ | master_pos_wait('mysql-bin.000001','2400') | +--------------------------------------------+ | 0 | +--------------------------------------------+ 1 row in set (0.00 sec)

这个语句会阻塞直到从服务器达到指定的日志文件和偏移量后，返回０，如果返回-1。

在主服务器上，执行下面语句允许主服务器重新开始处理更新

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

3. log event entry exceeded max_allowed_packet的处理

mysql> show variables like 'max_allowed_packet'; +--------------------+---------+ | Variable_name | Value | +--------------------+---------+ | max_allowed_packet | 1048576 | +--------------------+---------+ 1 row in set (0.01 sec) mysql> set @@global.max_allowed_packet=16777216

同时在my.cnf里设置max_allowed_packet=16M，保证下次数据库重新启动后参数继续有效。

好了，暂时总结这么多.

关于错误：

Query partially completed on the master (error on master: 1317) and was aborted. There is a chance that your master is inconsistent at this point. If you are sure that your master is ok, run this query manually on the slave and then restart the slave with SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;

解决：

mysql> stop slave;

mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;

mysql> start slave;

此时，有可能Exec_Master_Log_Pos会大于Read_Master_Log_Pos，出现这种情况是因为 Read_Master_Log_Pos指的是Master_Log_File里的position，而Exec_Master_Log_Pos指的是Relay_Master_Log_File的bin文件里的position。因为可能不是同一个file，所以比较大小没有意义。

read_master_log_pos 始终会大于exec_master_log_pos的值(也有可能相等):因为一个值是代表io线程,一个值代表sql线程;sql线程肯定在io线程之后.

可以再.err日志里看到：

Slave SQL thread initialized, starting replication in log 'mysql-bin.000610' at position 327131485, relay log './mysql-relay-bin.133311' position: 3607317