2019独角兽企业重金招聘Python工程师标准>>>
(1)规划主从主机
主机:10.10.54.86
从机1:10.10.54.85
从机2:10.10.54.87
(2)更改主机配置文件
[root@nan86 ~]# vim /etc/my.cnf
log-bin=master-bin
binlog_format=mixed
server-id=1
(3)更改从机配置文件
从1
[root@nan85 ~]# vim /etc/my.cnf
log-bin=slave-bin
binlog_format=mixed
server-id=10
从2
[root@nan87 ~]# vim /etc/my.cnf
log-bin=slave-bin
binlog_format=mixed
server-id=11
(4)在master上创建复制用户,并授予权限
mysql> grant replication slave on *.* to 'emp1'@'10.10.54.85' identified by 'emp1';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> grant replication slave on *.* to 'emp2'@'10.10.54.87' identified by 'emp2';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
(5)查看master上二进制日志和position位置
mysql> show master status;
+-------------------+-----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+-----------+--------------+------------------+
| master-bin.000014 | 189672455 | | |
+-------------------+-----------+--------------+------------------+
1 row in set (0.00 sec)
(6)备份master上的数据,把备份master数据库还原到从库上
[root@nan86 tmp]# mysqldump -uroot -p123456 --master-data=2 --single-transaction --flush-logs --database employees >emp.sql
[root@nan86 tmp]# mysql -uroot -p123456 -h10.10.54.85<employees.sql
[root@nan86 tmp]# mysql -uroot -p123 -h10.10.54.87<employees.sql
(7)在slave上面change master操作
从1:
mysql> change master to master_host='10.10.54.86',master_user='emp1', master_password='emp1', master_log_file='master-bin.000014',master_log_pos=189672455;
Query OK, 0 rows affected (0.01 sec)
从2:
mysql> change master to master_host='10.10.54.86',master_user='emp2',master_password='emp2',master_log_file='master-bin.000014',master_log_pos=189672455;
Query OK, 0 rows affected (0.02 sec)
(8)在slave上启动slave
从1:
mysql> slave start;
Query OK, 0 rows affected (0.00 sec)
从2:
mysql> slave start;
Query OK, 0 rows affected (0.00 sec)
(9)查看slave状态
确定slave上的I/O线程和SQL线程状态为YES
从1:
mysql> show slave status\G;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
从2:
mysql> show slave status\G;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
(10)测试
主上:
创建一个数据库
mysql> create database a;
Query OK, 1 row affected (0.00 sec)
从1上:
同步创建一个数据库a
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| a |
| employees |
| mysql |
| performance_schema |
| test |
+--------------------+
6 rows in set (0.00 sec)
从2上:
同步创建一个数据库a
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| a |
| employees |
| mysql |
| performance_schema |
| test |
+--------------------+
6 rows in set (0.00 sec)
经测试在master上创建删除增加数据,两个slave上都能同步。