MySQL 的主从 Replication同步

MySQL 的主从 Replication同步,又叫主从复制是一个很成熟的架构，很多线上环境都用这种方案。它的优点如下：

1.在从服务器上执行查询，在主服务器上写，降低主服务器压力，实现读写分离

2.在从服务器上进行备份，避免备份期间影响主服务器服务

3.主服务器出现故障时，可切换到从服务器

我配置的过程如下，找了两台局域网机器，

192.168.3.119(master) 和192.168.3.24(slave)

master:

vi /etc/my.cnf

添加:

log-bin = master.bin

log-bin-index =master-bin.index

server-id = 1

#希望同步的数据库.有多个就写多行

binlog-do-db=payment

binlog-do-db=datacenter

#不希望同步的数据库.有多个就写多行

binlog-ignore-db=test

binlog-ignore-db=mysql

重启

server mysql restart

mysql -h127.0.0.1 -uroot -p

mysql> create userrepl_user;

Query OK, 0 rows affected (0.00sec)

mysql> grantreplication slave on *.* to repl_user identified by 'root';

Query OK, 0 rows affected (0.00sec)

slaver:

vi /etc/my.cnf

添加:

server-id = 2

relay-log-index =slave-relay-bin.index

relay-log = slave-relay-bin

#不希望同步的数据库

replicate-ignore-db=mysql

#希望同步的数据库

replicate-do-db=payment

附：可以通过该参数进行分片，即：将不同的功能放到不同的 slave组中.

重启

service mysql restart

连接 master 和 slave

登录 slave:

mysql> change masterto master_host = '192.168.3.119',master_port = 3306,master_user ='repl_user',master_password='root';

Query OK, 0 rows affected (0.01sec)

mysql> startslave;

Query OK, 0 rows affected (0.00sec)

在主库上建库，表，插入数据。看从库变化。

如果没有， 在从库上用：

show slavestatus; 

查看从库状态，里面可能有报错信息。

要注意的是，MySQL 安装后，可能自身的 my.cnf 就有binlog 相关的配置，如 server-id在进行上面的配置时要注意看一下，自己的配置会不会被默认配置给覆盖，也就是我们的配置写在上面，但后面有系统自己的配置。

在 slave status的状态中，有两项: Slave_IO_Running和  Slave_SQL_Running,必须都为 Yes 才说明正常运行了。

如果是slave_io_runningno了，那么就我个人看有三种情况，一个是网络有问题，连接不上，第二个是有可能my.cnf有问题.

一旦io为no了先看err日志，看看有什么错，很可能是网络，也有可能是包太大收不了，这个时候从机上改max_allowed_packet这个参数。

Slave_SQL_Running 为 No 时:

解决办法一

1.程序可能在slave上进行了写操作

2.也可能是slave机器重起后，事务回滚造成的.

一般是事务回滚造成的：

解决办法：

mysql> slavestop;

mysql> set GLOBALSQL_SLAVE_SKIP_COUNTER=1;

mysql> slavestart;

推荐使用该办法.

set GLOBAL SQL_SLAVE_SKIP_COUNTER=N,用来 跳过备机的一条或N条出错的复制语句。

在show slave status 中会有出错的语句。

set GLOBAL SQL_SLAVE_SKIP_COUNTER=1 一次后会发现跳过了一条语句。

如果设置完后发现还有错误语句。要多执行几次。

解决办法二

首先停掉Slave服务：slave stop

到主服务器上查看主机状态：

记录File和Position对应的值

进入master

mysql> show masterstatus;

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

| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB|

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

| master-bin.000005    | 194244   |  payment     |  test,mysql       | 

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

1 row in set (0.00 sec)

然后到slave服务器上执行手动同步：

change master to master_host ='192.168.3.119',master_port = 3306,master_user ='repl_user',master_password='root',master_log_file='master-bin.000005',master_log_pos=194244;

注:这种办法可能会导致从服务器上的数据不完整,如从服务器一直出错,但主