mysql配置主主复制(目前单机)
修改my.cnf:
数据库1上:
vim /etc/my.cnf
[mysqld]
server-id=1
log-bin=/opt/mysql/data/mysql1-bin //开启二进制日志
relay_log=/opt/mysql/data/relay-log //开始中继日志
auto-increment-increment = 2 //每次增长的数值
auto-increment-offset = 1 //开始的数值
数据库2上:
vim /etc/my.cnf
[mysqld]
server-id=2
log-bin=/opt/mysql/data/mysql2-bin
relay_log=/opt/mysql/data/relay-log
auto-increment-increment = 2
auto-increment-offset = 2
3.创建具有复制权限的用户:
数据库1上:
service mysqld start //启动数据库
mysql -u root -p //进入数据库
GRANT REPLICATION SLAVE,REPLICATION CLIENT ON . TO mmm@192.168.19.48 IDENTIFIED BY ‘123456’;
FLUSH PRIVILEGES;
数据库2上:
service mysqld start //启动数据库
mysql -u root -p //进入数据库
GRANT REPLICATION SLAVE,REPLICATION CLIENT ON . TO mmm@192.168.19.43 IDENTIFIED BY ‘123456’;
FLUSH PRIVILEGES;
4.查看二进制位置:
数据库1上:
SHOW MASTER LOGS;
数据库2上使用同样的命令:
5.配置CHANGE MASTER:
数据库1上:
CHANGE MASTER TO MASTER_HOST=‘192.168.19.48’,MASTER_USER=‘mmm’,MASTER_PASSWORD=‘123456’,MASTER_LOG_FILE=‘mysql2-bin.000001’,MASTER_LOG_POS=642;
START SLAVE;
数据库2上:
START SLAVE;
6.查看状态:
SHOW SLAVE STATUS\G