mysql 主主同步配置_Mysql主主复制配置

1.安装mysql

参考 centos7非root下安装mysql5.7.29

主机A：192.168.62.37

主机B：192.168.62.15

2.主机A 配置my.cnf，内容如下：

# For advice on how to change settings please see

# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]

#

# Remove leading # and set to the amount of RAM for the most important data

# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.

# innodb_buffer_pool_size = 128M

#

# Remove leading # to turn on a very important data integrity option: logging

# changes to the binary log between backups.

# log_bin

#

# Remove leading # to set options mainly useful for reporting servers.

# The server defaults are faster for transactions and fast SELECTs.

# Adjust sizes as needed, experiment to find the optimal values.

# join_buffer_size = 128M

# sort_buffer_size = 2M

# read_rnd_buffer_size = 2M

character-set-server = utf8

port = 3306

basedir=/usrdata/mysql/mysql-5.7.29

datadir=/usrdata/mysql/mysql-5.7.29/data

socket=/usrdata/mysql/mysql-5.7.29/mysql.sock

log_error=/usrdata/mysql/mysql-5.7.29/error.log

pid-file=/usrdata/mysql/mysql-5.7.29/mysql.pid

# Disabling symbolic-links is recommended to prevent assorted security risks

#symbolic-links = 0

#标识唯一id(必须)

server-id=1

#开启二进制日志

log-bin=mysql-bin

#不同步的数据库，可设置多个

binlog-ignore-db=information_schema

binlog-ignore-db=performance_schema

binlog-ignore-db=mysql

binlog-ignore-db=sys

#指定需要同步的数据库(和slave是相互匹配的)，可以设置多个

#binlog-do-db=test

#主－主形式需要多添加的部分

log-slave-updates

#sync_binlog = 1

auto_increment_offset = 1

auto_increment_increment = 2

replicate-ignore-db = mysql,information_schema,performance_schema,sys

#设置存储模式不设置默认

binlog_format=MIXED

#日志清理时间

expire_logs_days=7

#日志大小

max_binlog_size=100m

#缓存大小

binlog_cache_size=4m

#最大缓存大小

max_binlog_cache_size=521m

[client]

port=3306

default-character-set=utf8

socket=/usrdata/mysql/mysql-5.7.29/mysql.sock

3.主机B 配置my.cnf，内容如下：

# For advice on how to change settings please see

# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]

#

# Remove leading # and set to the amount of RAM for the most important data

# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.

# innodb_buffer_pool_size = 128M

#

# Remove leading # to turn on a very important data integrity option: logging

# changes to the binary log between backups.

# log_bin

#

# Remove leading # to set options mainly useful for reporting servers.

# The server defaults are faster for transactions and fast SELECTs.

# Adjust sizes as needed, experiment to find the optimal values.

# join_buffer_size = 128M

# sort_buffer_size = 2M

# read_rnd_buffer_size = 2M

character-set-server = utf8

port = 3306

basedir=/usrdata/mysql/mysql-5.7.29

datadir=/usrdata/mysql/mysql-5.7.29/data

socket=/usrdata/mysql/mysql-5.7.29/mysql.sock

log_error=/usrdata/mysql/mysql-5.7.29/error.log

pid-file=/usrdata/mysql/mysql-5.7.29/mysql.pid

# Disabling symbolic-links is recommended to prevent assorted security risks

#symbolic-links = 0

#标识唯一id(必须)

server-id=2

#开启二进制日志

log-bin=mysql-bin

#不同步的数据库，可设置多个

binlog-ignore-db=information_schema

binlog-ignore-db=performance_schema

binlog-ignore-db=mysql

binlog-ignore-db=sys

#指定需要同步的数据库(和slave是相互匹配的)，可以设置多个

#binlog-do-db=test

#主－主形式需要多添加的部分

log-slave-updates

#sync_binlog = 1

auto_increment_offset = 2

auto_increment_increment = 2

replicate-ignore-db = mysql,information_schema,performance_schema,sys

#设置存储模式不设置默认

binlog_format=MIXED

#日志清理时间

expire_logs_days=7

#日志大小

max_binlog_size=100m

#缓存大小

binlog_cache_size=4m

#最大缓存大小

max_binlog_cache_size=521m

[client]

port=3306

default-character-set=utf8

socket=/usrdata/mysql/mysql-5.7.29/mysql.sock

4.重启mysql，分别创建用于同步的用户账号

1).配置MasterA主=》MasterB从

# MasterA 主机配置

mysql> grant replication slave on *.* to 'repl'@'192.168.62.15' identified by '123456';

mysql> flush privileges; #刷新权限

mysql> SHOW MASTER STATUS;

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

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| mysql-bin.000007 | 613 | | information_schema,performance_schema,mysql,sys | |

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

#File、Position 在配置从机时需要用到

#MasterB 从机配置

mysql> change master to master_host='192.168.62.37',

master_user='repl',

master_password='123456',

master_log_file='mysql-bin.000007',

master_log_pos=613;

#启动slave同步进程：

mysql> start slave;

#查看slave状态：

mysql> show slave status\G;

2).配置MasterB主=》MasterA从

# MasterB主机配置

mysql> grant replication slave on *.* to 'repl'@'192.168.62.37' identified by '123456';

mysql> flush privileges; #刷新权限

mysql> SHOW MASTER STATUS;

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

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| mysql-bin.000004 | 613 | | information_schema,performance_schema,mysql,sys | |

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

#File、Position 在配置从机时需要用到

#MasterA从机配置

mysql> change master to master_host='192.168.62.15',

master_user='repl',

master_password='123456',

master_log_file='mysql-bin.000004',

master_log_pos=613;

#启动slave同步进程：

mysql> start slave;

#查看slave状态：

mysql> show slave status\G;

3).配置成功后,分别查看slave状态，

#当Slave_IO_Running和Slave_SQL_Running都为YES的时候就表示主从同步设置成功了

mysql> show slave status\G;

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

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.62.15

Master_User: ntrepl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000004

Read_Master_Log_Pos: 613

Relay_Log_File: nt-metra-1-relay-bin.000002

Relay_Log_Pos: 320

Relay_Master_Log_File: mysql-bin.000004

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB: mysql,information_schema,performance_schema,sys

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

Relay_Log_Space: 532

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: a0ce91d7-3053-11eb-95b3-fa163e443845

Master_Info_File: /usrdata/mysql/mysql-5.7.29/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)

ERROR:

No query specified

5.正确关闭slave及停止mysql服务步骤

关闭MySQL从库

1. 先查看当前的主从同步状态show slave status\G;看是否双yes

2. 分别执行stop slave，停止从库线程

3. 停止从库服务mysqladmin shutdown -u用户名 -p密码

4. 查看是否还有mysql的进程ps -ef | grep mysql

5. 如果部署了多个实例，那每个实例都要按照以上步骤来操作

关闭MySQL主库

1. 停止主库服务mysqladmin shutdown -u用户名 -p密码

2. 查看是否还有mysql的进程ps -ef | grep mysql

6.正确启动mysql主从服务步骤

启动MySQL主库

1. 启动主库服务mysqladmin start -u用户名 -p密码

2. 查看mysql的进程ps -ef | grep mysql

启动MySQL从库

1. 启动从库服务mysqladmin start -u用户名 -p密码

2. 分别启动复制start slave;

3. 检查同步状态show slave status\G;是否双yes

4. 查看mysql的进程ps -ef | grep mysql