MYSQL数据库主从复制

一：实验环境

三台Linux虚拟机，一台作为master，另外二台作为slave
前提：三台虚拟机互通且时间同步
主数据库（master）地址：192.168.209.145
从数据库（slave01）地址：192.168.209.146
从数据库（slave02）地址：192.168.209.147

二：实验步骤

2.1：主数据库配置

[root@master ~]# vim /etc/my.cnf
[mysqld]
...
server-id=1
log-bin=master-bin   ##设置主服务器的二进制日志文件名
log-slave-updates=true   ##允许从服务器的IO线程通过主服务器的二进制日志文件来生成中继日志文件
[root@master ~]# systemctl restart mysqld
[root@master ~]# mysql -uroot -p  #回车输入登录密码

mysql> grant replication slave on *.* to 'myslave'@'192.168.209.%' identified by '123123'; ##添加一个授权用户，允许从服务器找我复制数据
mysql> flush privileges;  ##刷新权限，不然不会生效
mysql> show master status;  ## 查看同步的二进制日志文件和同步位置position
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 |      604 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

2.2：从数据库配置

• slave01配置

[root@slave01 ~]# vim /etc/my.cnf
[mysqld]
...
server-id=2
relay-log=relay-log-bin  ##设置中继日记名称
relay-log-index=slave-relay-bin.index  ##设置中继日志索引
[root@slave01 ~]# systemctl restart mysqld
[root@slave01 ~]# mysql -uroot -p

mysql> change master to master_host='192.168.209.145',master_user='myslave',master_password='123123',master_log_file='master-bin.000001',master_log_pos=604;
mysql> start slave; ##开启同步功能
mysql> show slave status\G; ##查看状态
##IO线程与SQL线程需都为Yes
...
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes
 .....

• slave02配置
  配置文件中server-id的值改为3，其余配置与slave01配置相同

三：验证主从同步

1、在主数据库中新建一个数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

mysql> create database school;
Query OK, 1 row affected (0.02 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

2、从服务器查看是否有school库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

可以看到同步成功！