mysql5.7 主主同步_mysq5.7 主主同步

db01  172.21.0.10

db02  172.21.0.14

一、安装数据库看上一遍博客

修改配置文件  db01  172.21.0.10

[root@VM_0_10_centos mysql]# cat /etc/my.cnf

[mysqld]

port=3306

character-set-server=utf8

basedir=/usr/local/mysql

datadir=/usr/local/mysql/data

#innodb_buffer_pool_size=8M

max_connections=1000

slow_query_log = 1

slow_query_log_file=/usr/local/mysql/log/mysql_slow_query.log

long_query_time = 5

max_connections=1000

log-bin=/usr/local/mysql/log_bin/mysql-bin.log # 开启二进制日志

binlog_format=mixed

server-id = 1

auto_increment_increment=2 # 步进值auto_imcrement。一般有n台主 MySQL 就填n

auto_increment_offset=1 # 起始值。一般填第n台主MySQL。此时为第一台主MySQL

replicate-do-db=schneider # 要同步的数据库，默认所有库

expire_logs_days=180 #binlog过期清理时间

max_binlog_size=512m #binlog每个日志文件大小

replicate-do-db=schneider # 要同步的数据库，默认所有库

[mysqld_safe]

log-error=/usr/local/mysql/data/error.log

pid-file=/usr/local/mysql/data/mysql.pid

tmpdir=/usr/local/mysql/tmp

[client]

default-character-set=utf8

[mysql]

default-character-set=utf8

修改配置文件  db01  172.21.0.14

[root@VM_0_14_centos log_bin]# cat /etc/my.cnf

[mysqld]

port=3306

character-set-server=utf8

basedir=/usr/local/mysql

datadir=/usr/local/mysql/data

#innodb_buffer_pool_size=8M

max_connections=1000

slow_query_log = 1

slow_query_log_file=/usr/local/mysql/log/mysql_slow_query.log

long_query_time = 5

max_connections=1000

log-bin=/usr/local/mysql/log_bin/mysql-bin.log # 开启二进制日志

binlog_format=mixed

server-id = 2 #id 不能一样

auto_increment_increment=2 # 步进值auto_imcrement。一般有n台主 MySQL 就填n

auto_increment_offset=2 # 起始值。一般填第n台主MySQL。此时为第一台主MySQL

replicate-do-db=schneider # 要同步的数据库，默认所有库

expire_logs_days=180 #binlog过期清理时间

max_binlog_size=512m #binlog每个日志文件大小

replicate-do-db=schneider # 要同步的数据库，默认所有库

[mysqld_safe]

log-error=/usr/local/mysql/data/error.log

pid-file=/usr/local/mysql/data/mysql.pid

tmpdir=/usr/local/mysql/tmp

[client]

default-character-set=utf8

[mysql]

default-character-set=utf8

二、互换主从 注意需要锁表 或者停库

db01  172.21.0.10

# 创建用户 并授权

CREATE USER 'mysq114'@'172.21.0.14' IDENTIFIED BY '123456';

GRANT REPLICATION SLAVE ON *.* TO 'mysq114'@'172.21.0.14' IDENTIFIED BY '123456';

FLUSH PRIVILEGES;

mysql> show master status;

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

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| mysql-bin.000002 | 154 | | | |

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

1 row in set (0.00 sec)

# 告诉bin-log位置  在db02  172.21.0.14 执行

CHANGE MASTER TO

MASTER_HOST='172.21.0.10',

MASTER_USER='mysq114',

MASTER_PASSWORD='123456',

MASTER_LOG_FILE='mysql-bin.000003',

MASTER_LOG_POS= 154;

db02  172.21.0.14

CREATE USER 'mysql10'@'172.21.0.10' IDENTIFIED BY '123456';

GRANT REPLICATION SLAVE ON *.* TO 'mysql10'@'172.21.0.10' IDENTIFIED BY '123456';

FLUSH PRIVILEGES;

mysql> show master status;

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

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| mysql-bin.000002 | 154 | | | |

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

1 row in set (0.00 sec)

# 告诉bin-log位置  在db01  172.21.0.10 执行

CHANGE MASTER TO

MASTER_HOST='172.21.0.14',

MASTER_USER='mysql10',

MASTER_PASSWORD='123456',

MASTER_LOG_FILE='mysql-bin.000003',

MASTER_LOG_POS= 154;

三、查看结果

db1 和db2 都执行

start slave;

db01  172.21.0.10

mysql> show slave status\G

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

Slave_IO_State: Waiting for master to send event

Master_Host: 172.21.0.14

Master_User: mysql10

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000004

Read_Master_Log_Pos: 474

Relay_Log_File: VM_0_10_centos-relay-bin.000006

Relay_Log_Pos: 687

Relay_Master_Log_File: mysql-bin.000004

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB: schneider

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: 474

Relay_Log_Space: 1069

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: 2

Master_UUID: 9e57cecc-d148-11e9-92db-5254007bbb3c

Master_Info_File: /data/mysql/data/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:

1 row in set (0.00 sec)

db02  172.21.0.14

mysql> show slave status\G

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

Slave_IO_State: Waiting for master to send event

Master_Host: 172.21.0.10

Master_User: mysq114

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000004

Read_Master_Log_Pos: 355

Relay_Log_File: VM_0_14_centos-relay-bin.000005

Relay_Log_Pos: 568

Relay_Master_Log_File: mysql-bin.000004

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB: schneider

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: 355

Relay_Log_Space: 950

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: 9a3346c4-d148-11e9-a4f1-525400857720

Master_Info_File: /data/mysql/data/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:

1 row in set (0.00 sec)

四、最后测试是否同步