【MySQL】1托2 ab复制 一个主机两个slave操作手册
所有实验环境全部是新建的，如果不是新建的mysql一定要备份！！！
环境：CentOS release 6.8 x64
master：192.168.25.100
slave1: 192.168.25.101
slave2: 192.168.25.102
1.查看是否开启二进制日志
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| binarylog.000001 | 154 |
+------------------+-----------+
1 row in set (0.00 sec)如果有回显，证明已经开启二进制日志
如果显示为：
mysql> show binary logs;
ERROR 1381 (HY000): You are not using binary logging
则证明需要手动将其开启
开启过程如下：
修改/etc/my.cnf文件
添加如下内容：
log-bin=/usr/local/mysql/binarylog //开启二进制日志
server-id=1 //需要执行server-id才能启动成功，5.7以下版本不需要指定
sync_binlog=1
innodb_flush_log_at_trx_commit=1 //这两条是优化参数修改完成后重启mysql服务，查看是否开启
这里还有一个细节，如果数据库里以前就应用了二进制日志，那么建议做一次重置
2.创建一个数据库在master上，并将该库进行备份
mysql> create database zhang;
Query OK, 1 row affected (0.00 sec)
mysql> use zhang;
Database changed
mysql> create table zhang(id int,name varchar(10));
Query OK, 0 rows affected (0.26 sec)
mysql> insert into zhang values(1,"zhang");
Query OK, 1 row affected (0.11 sec)
mysql> insert into zhang values(2,"da");
Query OK, 1 row affected (0.11 sec)
mysql> select * from zhang;
+------+-------+
| id | name |
+------+-------+
| 1 | zhang |
| 2 | da |
+------+-------+
2 rows in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| zhang |
+--------------------+
5 rows in set (0.00 sec)备份该数据库
mysqldump -uroot -p -B mysql zhang> zhang.sql //-B这个选项是备份多个数据库 这里备份了mysql数据库和zhang数据库
3.在slave1上查看是否有和我这个库重名的库，如果有就备份后删除掉，如果没有的话，直接将zhang.sql导入到slave1中
将master备份的zhang.sql传输到slave1服务器上。
导入到slave1中
scp ~/zhang.sql 192.168.25.101:~/
mysql -uroot -p < zhang.sql登陆到slave1上的mysql查看是否导入成功；
[root@test6_101 ~]# mysql -uroot -p
Enter password:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| zhang |
+--------------------+
5 rows in set (0.00 sec)4.在master服务器端创建用户 test来传输二进制日志
mysql> GRANT REPLICATION SLAVE ON *.* TO 'test'@'192.168.25.%' IDENTIFIED BY 'test123';
查看二进制日志号
mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
File: binarylog.000001
Position: 1325
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)5.在slave端配置：
5.1 配置my.cnf文件
[mysqld]
log_bin = /usr/local/mysql/binarylog //启动SQL日志，并指定文件名前缀
server_id = 2 //指定服务器ID号，不要与Master的相同
log_slave_updates=1 //记录从库更新，便于实现“主-从-从”链式复制
sync_binlog=1
innodb_flush_log_at_trx_commit=1 //这两条是优化参数完成过后创建路径mkdir /usr/local/mysql/binarylog -p
更改完成后，重启mysql数据库
5.2 登陆mysql数据库中发起同步操作
mysql> change master to master_host='192.168.25.100', //master的主机ip
-> master_user='test', //拥有replication slave权限的用户
-> master_password='test123', //用户密码
-> master_log_file='binarylog.000001', //日志文件的名称，可以根据上述的mysql> SHOW MASTER STATUS\G查看
-> master_log_pos=1325; //这个日志pos可以从show master status查看
Query OK, 0 rows affected, 2 warnings (0.28 sec)5.3 启动复制功能
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)5.4查看状态
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.25.100
Master_User: test
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binarylog.000002
Read_Master_Log_Pos: 694
Relay_Log_File: test6_101-relay-bin.000006
Relay_Log_Pos: 860
Relay_Master_Log_File: binarylog.000002
Slave_IO_Running: Yes //这里是YES
Slave_SQL_Running: Yes //这里是YES 就证明同步成功了，
Replicate_Do_DB:
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: 694
Relay_Log_Space: 1071
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
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 55681c8d-a1be-11e8-8cdb-000c2936b376
Master_Info_File: /usr/local/mysql/mydata/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)5.5测试
在master上执行一条更新或者插入一条数据，在slave上查看是否同步
主：
mysql> use zhang;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> insert into zhang values(3,"zhangda");
Query OK, 1 row affected (0.11 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status
-> ;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| binarylog.000002 | 423 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> show binary logs
-> ;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| binarylog.000001 | 1348 |
| binarylog.000002 | 423 |
+------------------+-----------+
2 rows in set (0.00 sec)
mysql> insert into zhang values(4,"zhangdada");
Query OK, 1 row affected (0.01 sec)从：
mysql> use zhang;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from zhang;
+------+-----------+
| id | name |
+------+-----------+
| 1 | zhang |
| 2 | da |
| 3 | zhangda |
| 4 | zhangdada |
+------+-----------+
4 rows in set (0.00 sec)
证明已经可以同步数据了~~
6.下面来操作slave2
6.1 在master重新备份一个sql文件
传到slave2上
scp ~/zhang.sql 192.168.25.102:~/
应用该sql
[root@test6_102 ~]# mysql -uroot -p < zhang.sql
Enter password:
[root@test6_102 ~]# mysql -uroot -p
Enter password:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| zhang |
+--------------------+
5 rows in set (0.00 sec)
6.2 配置my.cnf文件
[mysqld]
log_bin = /usr/local/mysql/binarylog //启动SQL日志，并指定文件名前缀
server_id = 3 //指定服务器ID号，不要与Master的相同
log_slave_updates=1 //记录从库更新，便于实现“主-从-从”链式复制
sync_binlog=1
innodb_flush_log_at_trx_commit=1 //这两条是优化参数完成过后创建路径
mkdir /usr/local/mysql/binarylog -p
更改完成后，重启mysql数据库
6.3 登陆mysql数据库中发起同步操作
mysql> change master to master_host='192.168.25.100', //master的主机ip
-> master_user='test', //拥有replication slave权限的用户
-> master_password='test123', //用户密码
-> master_log_file='binarylog.000001', //日志文件的名称，可以根据上述的mysql> SHOW MASTER STATUS\G查看
-> master_log_pos=1325; //这个日志pos可以从show master status查看
Query OK, 0 rows affected, 2 warnings (0.14 sec)6.4 启动复制功能
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)6.5 查看状态
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.25.100
Master_User: test
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binarylog.000002
Read_Master_Log_Pos: 694
Relay_Log_File: test6_101-relay-bin.000006
Relay_Log_Pos: 860
Relay_Master_Log_File: binarylog.000002
Slave_IO_Running: Yes //这里是YES
Slave_SQL_Running: Yes //这里是YES 就证明同步成功了，
Replicate_Do_DB:
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: 694
Relay_Log_Space: 1071
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
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 55681c8d-a1be-11e8-8cdb-000c2936b376
Master_Info_File: /usr/local/mysql/mydata/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)检验一下看数据是否同步
完成
7 后续操作
slave端/etc/my.cnf
因为是备份库，所以建议将备库设置为read only read_only=1
还有一个参数是report_host=
在master输入：SHOW SLAVE HOSTS 这个参数设置后，可以在master端查看有哪些slave已经开始工作
参考：http://blog.51cto.com/xujpxm/1386300
只有学习才能跟上时代的脚步
浙公网安备 33010602011771号