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

# 6.1、是否成功，以下都为yes即成功

slave_io_running: yes

slave_sql_running: yes

# 6.2 主库插入数据，从库查看

注：如果出现问题

stop slave;

reset slave;

重新配置

start slave;

mysql> show slave status \G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 9.7.68.140

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 4105

Relay_Log_File: localhost-relay-bin.000002

Relay_Log_Pos: 1256

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 4105

Relay_Log_Space: 1469

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1

Master_UUID: be46c74d-53ea-11eb-b094-005056bf6ee0

Master_Info_File: mysql.slave_master_info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:

Master_public_key_path:

Get_master_public_key: 0

Network_Namespace:

以上步骤完成 主从复制

下面为 主主复制

master库

# 1、修改my.cnf

[mysqld]

[mysqld1]

port=3306

basedir=/app/mysql

datadir=/app/mysql/data

socket=/app/mysql/data/mysqld.sock

log-error=/app/mysql/data/mysqld.error

# 主-从形式新加

log-bin=mysql-bin # 开启二进制日志

server-id=1 # 设置server-id

# binlog-do-db = test

# binlog-ignore-db = mysql

# 主主形式另加

log-slave-updates

sync_binlog = 1

auto_increment_offset = 1

auto_increment_increment = 2

# replicate-do-db = test

# replicate-ignore-db = mysql,information_schema

# 2、重启

# 3、创建用户，授权

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 | Executed_Gtid_Set |

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

| mysql-bin.000002 | 1218 | | | |

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

slave库

# 1、修改my.cnf

[mysqld]

port=3307

basedir=/app/mysql

datadir=/app/mysql/data1

socket=/app/mysql/data1/mysqld.sock

log-error=/app/mysql/data1/mysqld.err

# 主-从

server-id=2

log-bin=mysql-bin

# 主-主 新加

log-slave-updates

sync_binlog = 1

auto_increment_offset = 2

auto_increment_increment = 2

# binlog-do-db = test

# binlog-ignore-db = mysql

# 2、 重启

# 3、创建用户，授权

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 | Executed_Gtid_Set |

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

| mysql-bin.000001 | 1209 | | | |

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

同步

# A端

1、 stop slave;

2、

mysql> change master to

-> master_host='9.7.68.147',

-> master_port=3307,

-> master_user='repl_slave',

-> master_password='123',

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

-> master_log_pos=156;

3、start slave;

4、show slave status\G

# 2、 B端

1、stop slave;

2、

mysql> change master to

-> master_host='9.7.68.140',

-> master_user='repl',

-> master_password='123',

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

-> master_log_pos=156;

3、stop slave;

4、show slave status\G

# 是否成功，以下都为yes即成功

slave_io_running: yes

slave_sql_running: yes

# 主库插入数据，从库查看

配置参数说明

server-id

ID值唯一标识了复制群集中的主从服务器

log-bin

表示打开binlog，打开才可以通过IO写到slave的relay-log，也是可以进行replication的前提

binlog-do-db

表示记录二进制日志的数据库。多个用 逗号 隔开

binlog-ignore-db

表示不记录二进制日志的数据库。多个用 逗号 隔开

replication-do-db

表示需要同步的数据库。多个用 逗号 隔开

replication-ignore-db

表示不需要同步数据库。多个用 逗号 隔开

master-connect-retry

master-connect-retry=n 表示从服务器与主服务器的连接没有成功，则等待n秒(默认60s)。如果从服务器存在master.info文件，忽略此选项

log-slave-updates

配置从库上的更新操作是否写入二进制文件。如果这台从库，还要做其他从库的主库，需要打开

slave-skip-errors

复制过程中，binlog中的sql出错。默认从库停止复制。使用该参数定义错误号，如果遇见错误是定义的错误号，便跳过。可能会导致数据库不一致

sync_binlog=1 or N

默认是0，这种模式，mysql不会同步到磁盘中。mysql依赖操作系统刷新二进制binlog。

N，表示每N次binlog写入后与硬盘同步

1，最安全的，最多丢失一个语句

auto_increment_offset 和 auto_increment_increment

用于主 - 主 复制，并用来控制auto_increment列的操作。

auto_increment_increment：控制列中的值是增量值

auto_increment_offset：确定auto_increment列值的起点

如果auto_increment_offset大于auto_increment_increment值，则auto_increment_offset值被忽略。