环境准备
两个机器:一主两从
主库(MySQL Master):IP为192.168.74.140 port为3306
从库(MySQL Slave ):IP为192.168.95.130 port为3307和3308(多实例安装MySQL)
主库配置
1、设置server-id值并开启bin_log参数
[root@localhost ~]# vim /etc/my.cnf
[mysqld] //此标签下添加以下参数
log_bin = mysql-bin
server_id = 120
2、重启数据库
[root@localhost ~]# systemctl restart mysqld
3、建立同步账号
mysql> grant replication slave on *.* to 'rep'@'192.168.74.%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show grants for 'rep'@'192.168.74.%';
+--------------------------------------------------------+
| Grants for rep@192.168.74.% |
+--------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'rep'@'192.168.74.%' |
+--------------------------------------------------------+
1 row in set (0.00 sec)
4、锁表(设置为只读)
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
5、查看主数据库状态
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 447 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
6、备份