MySQL Master/Slave 主从数据同步

转自：http://www.linuxidc.com/Linux/2012-08/67412.htm

http://blog.sina.com.cn/s/blog_9f02ddef0100yjcz.html

MySQL的同步原理是用一个服务器作为Master(主服务器)，另一个或多个服务器作为Salve(从服务器)。Master服务器把更新的内容写到 二进制日志(Binary log或bin log中)，并且用一个索引文件来记录日志循环的情况。这些日志中的更新部分会被发送到Slave服务器。当一个Slave连接到Master之后，它会通知Master最后一次成功增量更新的日志位置。Slave会找出所有从那个时刻开始的更新操作，然后阻塞并等待Master发送新的更新操作。

MySQL数据库同步的主要优点如下：
1. 使系统更加稳健，当Master上发生问题时，可以把Slave作为备用切换过去。
2. 提高系统响应速度，可以在Slave和Master之间分担一些查询，这就能加速响应时间。SELECT查询就可以在Slave上执行以减少Master的负载。更新数据的语句则要放在Master上执行以保持Master和Slave的同步。
3. 容易管理，可以在Slave上备份数据，无需干扰Master。而备份数据时Master照样继续运作。

------------------------------------------------------------------------------------------------

MySQL的Master/Slave数据同步原理是在Master开启二进制日志功能(log- bin="log-file-name")，该功能会记录数据的任何操作(create, insert, update等)，然后由Slave取回Master的二进制日志再自己执行一遍从而实现同步的功能。

因此它可实现数据或表结构的同步，在Master中新建/修改/删除表一样被同步到Slave中。

项目申请了两台数据库服务器，为了保持数据同步，就搞了个数据同步，以此文记录以备后续使用：

首先声明：

服务器操作系统都是CentOS6.1、mysql版本号一致，为了减少出现其他问题的可能性，故做此环境搭配。

数据库服务器A，ip地址：192.168.6.108

数据库服务器B，ip地址：192.168.6.200

当然 首先要备份一台数据最新的数据库，然后等设置同步完毕之后都还原成相同的数据！

先做B同步A数据库的数据功能：

暂时称A为主机Master、B为从机Slave

A主机Master

1、修改mysql数据库conf文件，修改/etc/my.conf

在

# Replication Master Server (default)

# binary logging is required for replication

之后增加

server-id=1#自带就有的话就不用添加看一下my.conf上下文
binlog-do-db=mydb_name#需要同步的数据库名称
binlog-ignore-db=mysql#需要忽略的数据库名

2、重启mysql

（CentOS是service mysqld restart 其他系统的自行重启 若不知请谷歌、度娘）

3、创建一个MySQL帐号为同步专用

用root用户登陆mysql

mysql> grant replication slave,reload,super on *.* to backup@192.168.5.200 identified by 'backup';

mysql> flush privileges;

ps：我这里是创建了一个backup用户密码也是backup的mysql数据库用户，用于同步。@后面指定该账户允许远程登录的ip，也就是B从机的ip地址

B从机Slave

1、同样的修改mysql数据库conf文件，修改/etc/my.conf

在

# Replication Master Server (default)

# binary logging is required for replication

之后增加

server-id=2#自带就有的话就不用添加看一下my.conf上下文
master-host=192.168.5.108#此处是需要备份的主机ip这里填的是A主机的ip
master-user=backup#此处是备份的账号
master-password=backup#此处是备份账号的密码
master-port=3306#数据库端口号没改就填默认的3306
replicate-ignore-db=mysql#需要忽略的数据库名
replicate-do-db=mydb_name#需要同步的数据库名称

启动同步

在主服务器A上，mysql命令下（root用户登陆mysq）

mysql> show master status;

eg：

+一一一一一一一一一一+一一一一一一+一一一一一一一一+一一一一一一一一一一+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+一一一一一一一一一一+一一一一一一+一一一一一一一一+一一一一一一一一一一+

|mysql-bin.000012 | 106 | mydb_name | mysql |

+一一一一一一一一一一+一一一一一一+一一一一一一一一+一一一一一一一一一一+

从服务器B上，mysql命令下（同样用root用户登陆mysq）

mysql> slave stop;

mysql> change master to master_log_file='mysql-bin.000012',master_log_pos=106;

mysql> slave start;

ps：

master_log_file就是在主服务器mysql下show master status;显示的File列的值

master_log_pos就是在主服务器mysql下show master status;显示的Position列的值

还是在从服务器B的mysql命令下

用show slave status \G;看一下从服务器的同步情况

mysql>show slave status \G;

有如下两项的值都是YES就表示已经在同步

Slave_IO_Running:　Yes

Slave_SQL_Running:　Yes

否则就是同步失败，当然了，失败的原因有N种，自己慢慢谷歌、度娘，这里只把我自己的操作步骤记录。

设置MySQL双向同步

其实设置双向同步就是把上面的步骤重复一下，只是A和B服务器的操作反过来，A变成从服务器、B变成主服务器！

1、修改B服务器的my.conf，添加

binlog-do-db=mydb_name#需要同步的数据库名称
binlog-ignore-db=mysql#需要忽略的数据库名

2、重启B服务器的mysql (service mysqld restart)

3、登陆mysql，

mysql> grant replication slave,reload,super on *.* to backup@192.168.5.108 identified by 'backup';

mysql> flush privileges;

ps：创建一个backup用户，密码也是backup的mysql数据库用户，用于同步。@后面指定该账户允许远程登录的ip，也就是A从机的ip地址。

4、修改A服务器的my.cnf,添加

master-host=192.168.5.200#此处是需要备份的主机ip这里填的是A主机的ip
master-user=backup#此处是备份的账号
master-password=backup#此处是备份账号的密码
master-port=3306#数据库端口号没改就填默认的3306
replicate-ignore-db=mysql#需要忽略的数据库名
replicate-do-db=mydb_name#需要同步的数据库名称

5、重启A服务器的mysql（service mysqld restart）

6、在主服务器B MySQL命令符下：

mysql> show master status;

eg：

+一一一一一一一一一一+一一一一一一+一一一一一一一一+一一一一一一一一一一+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+一一一一一一一一一一+一一一一一一+一一一一一一一一+一一一一一一一一一一+

|mysql-bin.000014| 188 | mydb_name | mysql |

+一一一一一一一一一一+一一一一一一+一一一一一一一一+一一一一一一一一一一+

7、在服务器A MySQL命令符下：

mysql> slave stop;

mysql> change master to master_log_file='mysql-bin.000014',master_log_pos=188;

mysql> slave start;

8、还是在从服务器A的mysql命令下

用show slave status \G;看一下从服务器的同步情况

mysql>show slave status \G;

有如下两项的值都是YES就表示已经在同步

Slave_IO_Running:　Yes

Slave_SQL_Running:　Yes

然后还原数据至最新数据库！

至此 双向同步配置完毕！

ps：因为是基于操作日志备份，所以设置同步前请把数据库先备份，然后用用一份备份还原数据库，之后数据就可以保持一致！