一个主库上有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 |
+------------------+