mysql换服务器后数据同步_Mysql数据同步（双向）

Mysql数据同步(双向)

一．准备环境 Master：

Ip：192.168.211.128

Mysql版本：5.0.77(可以通过命令mysqladmin -uroot -p123 version查看)

Slave：

Ip：192.168.211.131

Mysql版本：5.0.77

二．设置同步 设置master同步

1. 修改my.cnf

vi /etc/my.cnf   添加内容如下：

server-id=1 (mysql标示)

log-bin=mysql-bin(开启log-bin二进制日志文件，默认存在/var/lib/mysql下日志文    件以mysql-bin为开头)

binlog-do-db =backup(backup为需要同步的数据库名，如需同步多个数据库，可以另起行如binlog-do-db =backup1)

# binlog-ignore-db=db_name (不进行同步日志的数据库，不需要的话注释掉)

max-binlog-size=104857600(日志的大小，超出会自动生成一个新的)

master-host=192.168.211.131(同步主机)

master-user=backup(同步用户)

master-password=123(同步用户密码)

master-port=3306 replicate-do-db=backup(同步数据库)

修改完后保存退出，并重启mysql 如：service mysql restart(重启正常，表示配置没错误)

2. 在作为master的mysql上建立一个账户专门用于slave来进行数据同步

mysql>grant file on *.* toidentified by '123'；

mysql>flush privileges；

Mysql>create database backup；

在slave上测试账户backup是否可以访问mster上的mysql：

#mysql -u backup -p -h 192.168.211.128(输入密码123，可以访问说明设置正确)

设置slave同步

1. 修改my.cnf

vi /etc/my.cnf        添加内容如下：

server-id= 2(mysql标示，不能出现重复)

log-bin=mysql-bin(开启log-bin二进制日志文件，默认存在/var/lib/mysql下日志文    件以mysql-bin为开头)

binlog-do-db =backup(backup为需要同步的数据库名，如需同步多个数据库，可以另起行如binlog-do-db =backup1)

max-binlog-size=104857600(日志的大小，超出会自动生成一个新的

master-host=192.168.211.128(同步Master的ip地址)

master-user=backup(同步所需的账号)

master-password=123(同步账号的密码)

master-port=3306(mstart 中mysql的访问端口)

replicate-do-db=backup(所需同步的数据库名)

#replicate-ignore-db=mysql(不同步mysql库)

#replicate-do-table=backup.stu (如果只需同步backup数据库中的stu表)

#replicate-do-table=backup.teacher(如果只需同步backup数据库中的stu表)

master-connect-retry=60(主服务器宕机或连接丢失的情况下，从服务器线程重新尝试连接主服务器之前睡眠的秒数

log-bin=mysql-bin(开启log-bin二进制日志文件)

修改完后保存退出，并重启mysql 如：service mysql restart(重启正常，表示配置没错误)

2. 在作为slave的mysql上建立用来同步的账户和库

mysql>grant file on *.* tobackup@'192.168.211.128'identified by '123'；

mysql>flush privileges；

Mysql>create database backup；

三．查看状态  在Master上：

mysql> show master status; +------------------+----------+--------------+------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000005 |      189 | backup       |                  |

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

1 row in set (0.00 sec)

mysql> show slave status\G *************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event                 Master_Host: 192.168.211.131

Master_User: backup

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000005

Read_Master_Log_Pos: 207

Relay_Log_File: mysqld-relay-bin.000002

Relay_Log_Pos: 344

Relay_Master_Log_File: mysql-bin.000005

Slave_IO_Running: Yes

Slave_SQL_Running: Yes             Replicate_Do_DB: backup

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: 207

Relay_Log_Space: 344

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)

在Slave上：

mysql>show master status; +------------------+----------+--------------+------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000005 |      207 | backup       |                  |

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

1 row in set (0.00 sec)

mysql> show slave status\G *************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event                 Master_Host: 192.168.211.128

Master_User: backup

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000005

Read_Master_Log_Pos: 189

Relay_Log_File: mysqld-relay-bin.000002

Relay_Log_Pos: 326

Relay_Master_Log_File: mysql-bin.000005

Slave_IO_Running: Yes

Slave_SQL_Running: Yes             Replicate_Do_DB: backup

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: 189

Relay_Log_Space: 326

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)

验证是否配置正确

在slave服务器上执行show slave status\G;

Waiting for master to send event

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

如以上二行同时为Yes 说明配置成功

四.测试

1、在master服务器backup数据库中创建user表

mysql>use backup;

mysql>create table user(id int);

2在slave服务器中查看user表

mysql>use backup;mysql> show tables;+------------------+

| Tables_in_backup |

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

|user        |

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

1 row in set (0.00 sec)

3.在slave服务器backup数据库中创建pass表

mysql>use backup;mysql>create table pass(id int);

4.在master服务器中查看pass表

mysql>show tables;+------------------+

| Tables_in_backup |

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

| pass             |

| user        |

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

2 rows in set (0.00 sec)

说明主从数据同步成功。