mysql 主从选主_mysql主从、主主复制

前提

数据库版本最好一致。(或slave库版本高于master库，不能相差太大)

master库修改

# 1、修改my.cnf

[mysqld]

log-bin=mysql-bin # 打开二进制日志，8.0已自动开启

server-id=1

# 2、重启mysql

# 3、创建replication账号密码

create user 'repl'@'%' identified with mysql_native_password by '123'; # 8.0 加密方式改变，需要如此

grant replication slave on *.* to 'repl'@'%'; # 赋予replication权限

flush privileges; # 刷新

# 4、查看master状态，记录二进制文件名和位置

mysql > SHOW MASTER STATUS;

+------------------+----------+--------------+------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000003 | 73 | test | manual,mysql |

+------------------+----------+--------------+------------------+

## 默认传输所有表，如果只记录指定表

[mysqld]

# 不同步那些表

binlog-ignore-db = mysql

binlog-ignore-db = test

binlog-ignore-db = information_schema

# 只同步那些表，除此之外，其他不同步

binlog-do-db = game

slave库修改

可以提前做连通性测试

mysql -urepl -h9.7.68.140 -p

# 1、修改my.cnf

[mysqld]

server-id=2

# 2、重启mysql

# 3、执行同步SQL语句

mysql> change master to

-> master_host='9.7.68.140',

-> master_user='repl',

-> master_password='123',

-> master_log_file='mysql-bin.000001',

-> master_log_pos=3173;

# 4、启动slave同步进程

start slave;

# 5、查看状态

show slave status\G

<