mysql 支持单向,双向,链式级别,实时,异步复制。在复制过程中,一台服务器充当主服务器(Master),另一台服务器充当从服务器(Slave)
多实例配置参考 https://blog.csdn.net/weixin_41754736/article/details/95176007
数据库 3306作为主(Master)
数据库 3307作为从(Slave)
一. 首先配置3306的my.cnf
开启log-bin,添加唯一server-id
vi /data/mysql/3306/my.cnf
#内容如下
server-id = 3306
log-bin = /data/mysql/3306/data/mybinlog
可使用命令查看binlog配置(这两个配置一定要在 [mysqld] 模块下)
[root@bogon local]# egrep "log-bin|server-id" /data/mysql/3306/my.cnf
server-id = 3306
log-bin = /data/mysql/3306/data/mybinlog
二.查看是否生效
[root@bogon data]# ll /data/mysql/3306/data/mybinlog*
-rw-r-----. 1 mysql mysql 177 Jul 9 05:29 /data/mysql/3306/data/mybinlog.000001
-rw-r-----. 1 mysql mysql 982 Jul 9 15:24 /data/mysql/3306/data/mybinlog.000002
-rw-r-----. 1 mysql mysql 194 Jul 9 15:24 /data/mysql/3306/data/mybinlog.000003
-rw-r-----. 1 mysql mysql 114 Jul 9 15:24 /data/mysql/3306/data/mybinlog.index
[root@bogon data]# mysql -uroot -proot -S /data/mysql/3306/data/mysql.sock -e "show variables like 'log_bin';"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
三.创建唯一的主库用户
GRANT replication slave ON *.* TO 'slave'@'%' IDENTIFIED BY '123456';
#查看master信息
show master status;
show master logs;
mysql -uroot -proot -S /data/mysql/3307/data/mysql.sock
stop slave;
CHANGE MASTER TO
MASTER_HOST = '192.168.43.145',
MASTER_PORT =3306,
MASTER_USER = 'slave',
MASTER_PASSWORD = '123456',
MASTER_LOG_FILE = 'mybinlog.000003',
MASTER_LOG_POS = 2486;
start slave;
show slave status\G;