mysql8 主从复制和主主复制

 

• 主从复制：

主服务器

create user 'masteruser'@'192.168.12.1' identified WITH 'mysql_native_password' by  'xxx';
    grant all privileges on *.* to 'masteruser'@'192.168.12.1' with grant option;
    FLUSH PRIVILEGES;
    show master status;

    从服务器
    

CHANGE MASTER TO MASTER_HOST='192.168.12.2', MASTER_USER='masteruser', MASTER_PASSWORD='xxx', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=155;
start slave;
show slave status\G;

 

• 主主复制

主服务器配置
    auto_increment_increment=2
    auto_increment_offset=1
从服务器配置
    auto_increment_increment=2
    auto_increment_offset=2
重启mysqld
从服务器
    

create user 'masteruser13'@'192.168.12.2'  identified WITH 'mysql_native_password' by  'xxx';
    grant all privileges on *.* to 'masteruser13'@'192.168.12.2' with grant option;
    FLUSH PRIVILEGES;
    show master status;

主服务器    
    

CHANGE MASTER TO MASTER_HOST='192.168.12.1', MASTER_USER='masteruser13', MASTER_PASSWORD='xxx', MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=155;
start slave
show slave status

 

• 命令参考

stop slave 停止
reset slave all 删除
flush tables with read lock 锁表
unlock tables 接触锁表
show processlist 查看连接