mysql dump不同服务器_mysql5.5分别把这两个数据库同步到不同的从服务器

一个主库上有2个数据库，分别把这两个数据库同步到不同的从服务器

需要注意的是：若使用基于语句的或混合类型的复制，则此处不能使用--replicate-do-db选项，因为此选项的影响会因当前所选择的数据库而变化。然而，若使用的是基于行的复制，可以使用--replicate-do-db选项，因为此时当前锁选择的数据库对选项操作无影响。

mysql5.5复制配置

1.规划网络和主从机器

master:10.10.54.64

slave:10.10.54.67

slave:10.10.54.63

2.master config

log-bin=master-bin

server-id=1

binlog_formate=mixed

[root@gyf  ~]# /etc/init.d/mysqld restart

3. slave config10.10.54.67

[root@gyf  ~]# vim /etc/my.cnf

log-bin=slave-bin

binlog_formate=mixed

server-id=10必须大于主

replicate-wild-do-table=employees.%

innodb_file_per_table=1

或者

log-bin=slave-bin

binlog_formate=mixed

server-id=10必须大于主binlog_formate=row

replicate-do-table=employees

innodb_file_per_table=1

[root@gyf  ~]# /etc/init.d/mysqld restart

slave config：10.10.54.63

log-bin=slave-bin

binlog_formate=mixed

server-id=11必须大于主

replicate-wild-do-table=master.%

innodb_file_per_table=1

或者

binlog_formate=row

replicate-do-table=master

[root@gyf  ~]# /etc/init.d/mysqld restart

4.在master上面创建一个复制用户并授予权限

mysql> grant replication slave on *.* to 'gyf'@'10.10.54.67' identified by 'aaa12345';

mysql> grant replication slave on *.* to 'gyf'@'10.10.54.63' identified by 'aaa12345';

mysql> flush privileges;

//在从上测试是否能用复制用户登录

[root@gyf  ~]# mysql -ugyf -paaa12345 -h10.10.54.64

5.查看master上二进制日志和position位置

mysql> show master status;

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

| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| master-bin.000001 |      751 |              |                  |

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

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=751;

拓展：重置master  reset master

6.备份master上的数据,把备份master数据库还原到从库上

[root@gyf  ~]# mysqldump -uroot -paaa12345 --databases employees |mysql -uroot -paaa12345 -h10.10.54.67

或者

[root@nan86 tmp]# mysqldump -uroot -paaa12345 --master-data=2 --single-transaction --flush-logs --database employees >employees.sql

[root@gyf  ~]# mysqldump -uroot -paaa12345 --databases employees |mysql -uroot -paaa12345 -h10.10.54.67

[root@gyf  tmp]# mysql -uroot -paaa12345 -h10.10.54.67

ERROR 1130 (HY000): Host '10.10.54.64' is not allowed to connect to this MySQL server

mysql> grant all privileges on *.*  to root@10.10.54.64 identified by 'aaa12345';

mysql> flush privileges;

[root@nan86 tmp]# mysql -uroot -paaa12345 -h10.10.54.67

7.在slave上面change master操作

mysql> change master to master_host='10.10.54.64',master_user='gyf',

master_password='aaa12345', master_log_file='master-bin.000001',master_log_pos=751;

8.在slave上启动slave

mysql> start slave;

9.查看slave状态

确定slave上的I/O线程和SQL线程状态为YES

mysql> show slave status\G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 10.10.54.64

Master_User: gyf

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: master-bin.000001

Read_Master_Log_Pos: 751

Relay_Log_File: gyf-relay-bin.000002

Relay_Log_Pos: 254

Relay_Master_Log_File: master-bin.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

//可以单独停掉某一个线程

mysql> STOP SLAVE IO_THREAD;

mysql> STOP SLAVE SQL_THREAD;

###测试

在master上

mysql> drop database aa;

mysql> show databases;

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

| Database           |

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

| information_schema |

| employees          |

| master             |

| mysql              |

| performance_schema |

| test               |

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

在slave上显示

mysql> show databases;

mysql> show databases;

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

| Database           |

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

| information_schema |

| aa                 |

| employees          |

| master             |

| mysql              |

| performance_schema |

| test               |

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

在master上

mysql> use master;

Database changed

mysql> create table a(a int);

Query OK, 0 rows affected (0.00 sec)

mysql> create table a(a int);

ERROR 1050 (42S01): Table 'a' already exists

mysql> show tables;

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

| Tables_in_master |

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

| a                |

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

在10.10.54.63

mysql> use master;

Database changed

mysql> show tables;

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

| Tables_in_master |

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

| a                |

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