02-基于binlog的主从复制

环境

主机       IP                 系统版本  
mysql01    192.168.17.131    CentOS  Linux  release 7.3 
mysql02    192.168.17.132    CentOS  Linux  release 7.3

安装mysql软件

略，参考上篇文章01

配置过程

查看并修改默认字符集

mysql> show variables like '%character%';

[root@mysql01 ~]# vi /etc/my.cnf # 主库
character_set_server=utf8

[root@mysql02 ~]# vi /etc/my.cnf # 从库
character_set_server=utf8

查看主从log_bin状态

mysql> show variables like '%log_bin%';

注意: 主库需要开启 binlog,从库不是必须的。

开启binlog，并修改server-id，主从server-id不能相同

[root@mysql01 ~]# vi /etc/my.cnf # 主库
server-id=101
log-bin=mysql-bin

[root@mysql02 ~]# vi /etc/my.cnf # 主库
server-id=102
log-bin=mysql-bin # 一主一从模式从库可以不用开启

主库添加repl用户并添加replication权限

mysql> grant replication slave on *.* to 'repl'@'%' identified by '000000';
mysql> flush privileges;

在主库里面运行show master status

记下file和position字段对应的参数

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      589 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

在从库上配置master,并启动slave

mysql> help CHANGE MASTER TO # 查看语法

配置master

CHANGE MASTER TO
 MASTER_HOST='192.168.17.131',
 MASTER_USER='repl',
 MASTER_PASSWORD='000000',
 MASTER_PORT=3306,
 MASTER_LOG_FILE='mysql-bin.000001',
 MASTER_LOG_POS=589;

启动并查看状态

启动前先查看主从的server_uuid是否相同，主从必须不能相同。

[root@mysql01 ~]# cat /var/lib/mysql/auto.cnf
[auto]
server-uuid=f1ec3e3f-51f3-11ea-9628-005056285bae

启动从库

mysql> start slave;
mysql> show slave status\G;
Slave_IO_Running: Yes    # 这两个进行必须为yes
Slave_SQL_Running: Yes

查看主从开启的进程

主库：

+----+------+----------------------+------+-------------+------+---------------------------------------------------------------+------------------+
| Id | User | Host                 | db   | Command     | Time | State                                                         | Info             |
+----+------+----------------------+------+-------------+------+---------------------------------------------------------------+------------------+
|  6 | repl | 192.168.17.132:60156 | NULL | Binlog Dump |  324 | Master has sent all binlog to slave; waiting for more updates | NULL             |
|  8 | root | localhost            | NULL | Query       |    0 | starting                                                      | show processlist |
+----+------+----------------------+------+-------------+------+---------------------------------------------------------------+------------------+
2 rows in set (0.00 sec)

从库：

mysql> show processlist;
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| Id | User        | Host      | db   | Command | Time | State                                                  | Info             |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
|  1 | system user |           | NULL | Connect |  362 | Waiting for master to send event                       | NULL             |
|  2 | system user |           | NULL | Connect |  891 | Slave has read all relay log; waiting for more updates | NULL             |
|  5 | root        | localhost | NULL | Query   |    0 | starting                                               | show processlist |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
3 rows in set (0.00 sec)