mysql 多主多从配置，自增id解决方案

时间：2022-10-22 03:17:07

MySQL两主（多主）多从架构配置

一、角色划分

1、MySQL数据库规划

我现在的环境是：zhdy04和zhdy05已经做好了主主架构配置，现在需要的是把两台或者多台从服务器与主一一同步。

主机名

IP 地址

角色

Mysql_server_id

zhdy04

192.168.230.145

masterA

145

zhdy05

192.168.230.146

masterB

146

zhdy06

192.168.230.147

slaveA

147

zhdy07

192.168.230.148

slaveB

148
mysql 多主多从配置，自增id解决方案

二、MySQL数据同步设定

1、因为咱们测试的机器数据库全部都是初始值，所以不需要导入任何数据库。（写的原因是假如，配置的环境和现在不一致。我们仍然需要做数据备份和导入）

2、数据同步帐号添加

按理来说，之前我们已经配置了主主，也就是zhdy04和zhdy05已经互相授权了。接下来我们需要设置的是：

  1. 在zhdy04上面授权zhdy06可以同步。
  2. 在zhdy05上面授权zhdy07可以同步。

zhdy04 masterA服务器：

mysql> grant replication slave on *.* to 'repl'@'192.168.230.147' identified by 'zhangduanya';

Query OK, 0 rows affected (0.03 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

zhdy05 masterB服务器：

mysql> grant replication slave on *.* to 'repl'@'192.168.230.148' identified by 'zhangduanya';

Query OK, 0 rows affected (0.03 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

3、数据库配置文件修改

  1. zhdy04 masterA服务器：
server-id=145

log-bin=mysql-bin

auto-increment-increment=2

auto-increment-offset=1

log-slave-updates
  1. zhdy05 masterB服务器：
server-id=146

log-bin=mysql-bin

auto-increment-increment=2

auto-increment-offset=2

log-slave-updates
  1. zhdy06 slaveA服务器：
server-id=147
  1. zhdy07 slaveB服务器：
server-id=148

在此我为了让大家更加容易理解我所做的配置，所以没有添加任何附加的服务，例如如下：

log-bin = mysql-bin                                                     #打开二进制功能,MASTER主服务器必须打开此项

binlog-format=ROW

binlog-row-p_w_picpath=minimal

log-slave-updates=true

gtid-mode=on

enforce-gtid-consistency=true

master-info-repository=TABLE

relay-log-info-repository=TABLE

sync-master-info=1

slave-parallel-workers=0

sync_binlog=0

binlog-checksum=CRC32

master-verify-checksum=1

slave-sql-verify-checksum=1

binlog-rows-query-log_events=1

#expire_logs_days=5

max_binlog_size=1024M                                                   #binlog单文件最大值

replicate-ignore-db = mysql                                             #忽略不同步主从的数据库

replicate-ignore-db = information_schema

replicate-ignore-db = performance_schema

replicate-ignore-db = test

replicate-ignore-db = zabbix

max_connections = 3000

max_connect_errors = 30

skip-character-set-client-handshake                                     #忽略应用程序想要设置的其他字符集

init-connect='SET NAMES utf8'                                           #连接时执行的SQL

character-set-server=utf8                                               #服务端默认字符集

wait_timeout=1800                                                       #请求的最大连接时间

interactive_timeout=1800                                                #和上一参数同时修改才会生效

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES                     #sql模式

skip-name-resolve

slow_query_log=1

long_query_time = 6

slow_query_log_file=slow-query.log

innodb_flush_log_at_trx_commit = 2

innodb_log_buffer_size = 16M

binlog-do-db=openfire

binlog-ignore-db=mysql,information_schema

replicate-do-db=openfire

replicate-ignore-db=mysql,information_schema

read_only = 1       #开始只读模式

read_only=0;    #关闭只读,可以读写

4、重启4台DB:

/etc/init.d/mysqld restart

5、同步设置：

  1. zhdy04 masterA服务器：
mysql> show master status;

记录下各自的 master_log_file、master_log_pos值

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| mysql-bin.000004 |      120 |              |                  |                   |

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

1 row in set (0.01 sec)
  1. zhdy05 masterB服务器：
mysql> show master status;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| mysql-bin.000004 |      120 |              |                  |                   |

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

1 row in set (0.00 sec)
  1. zhdy06 slaveA服务器：
mysql> change master to master_host='192.168.230.145', master_user='repl', master_password='zhangduanya', master_log_file='mysql-bin.000004', master_log_pos=120;

Query OK, 0 rows affected, 2 warnings (0.04 sec)
  1. zhdy07 slaveB服务器：
mysql> change master to master_host='192.168.230.146', master_user='repl', master_password='zhangduanya', master_log_file='mysql-bin.000004', master_log_pos=120;

Query OK, 0 rows affected, 2 warnings (0.08 sec)

6、检查配置

首先一定要把四台的firewalld全部关闭！

# systemctl stop firewalld

# systemctl disable firewalld

分别检查四台服务器的

Slave_IO_Running: Yes Slave_SQL_Running: Yes

开启情况！

  1. zhdy06 slaveA服务器：
mysql> start slave;

Query OK, 0 rows affected (0.02 sec)

mysql> show slave status\G

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

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.230.145

                  Master_User: repl

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000004

          Read_Master_Log_Pos: 120

               Relay_Log_File: zhdy06-relay-bin.000002

                Relay_Log_Pos: 283

        Relay_Master_Log_File: mysql-bin.000004

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes
  1. zhdy04 masterA服务器：
mysql> start slave;

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show slave status\G

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

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.230.146

                  Master_User: repl

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000004

          Read_Master_Log_Pos: 120

               Relay_Log_File: zhdy04-relay-bin.000012

                Relay_Log_Pos: 283

        Relay_Master_Log_File: mysql-bin.000004

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes
  1. zhdy07 slaveB服务器：
mysql> start slave;

Query OK, 0 rows affected (0.02 sec)

mysql> show slave status\G

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

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.230.146

                  Master_User: repl

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000004

          Read_Master_Log_Pos: 120

               Relay_Log_File: zhdy07-relay-bin.000002

                Relay_Log_Pos: 283

        Relay_Master_Log_File: mysql-bin.000004

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes
  1. zhdy05 masterB服务器：
mysql> start slave;

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show slave status\G

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

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.230.145

                  Master_User: repl

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000004

          Read_Master_Log_Pos: 120

               Relay_Log_File: zhdy05-relay-bin.000011

                Relay_Log_Pos: 283

        Relay_Master_Log_File: mysql-bin.000004

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

由此看来全部同步成功！

7、最后一步，在主服务器zhdy04 masterA创建一个数据库，也就是说这四台服务器都会有这个数据库！

mysql> show databases;

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

| Database           |

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

| information_schema |

| mysql              |

| performance_schema |

| qqq                |

| test               |

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

5 rows in set (0.06 sec)

我就不一一截屏了，然后所有的数据库均有了qqq这个数据库！

8、总结

也就是说，未来我们搭建多主多从看完这个配置是不是也有思路了？就像配置路由器一样，互相连接的互相授权下是不是就可以了？

给大家一个设置的方法：

auto-increment-increment = 10

auto-increment-offset   = 1
auto-increment-increment = 10

auto-increment-offset   = 2
auto-increment-increment = 10

auto-increment-offset   = 3

这样是不是就可以设置10台主mysql了呢？

但是一旦超过20台呢？或者是100台？

20台

auto-increment-increment = 20

auto-increment-offset   = 1
auto-increment-increment = 20

auto-increment-offset   = 2

…..

auto-increment-increment = 20

auto-increment-offset   = 20

100台

auto-increment-increment = 100

auto-increment-offset   = 1
auto-increment-increment = 100

auto-increment-offset   = 2

…..

auto-increment-increment = 100

auto-increment-offset   = 100

是不是就不会重复了？ 不难看出，自增长值可设置为主机的数量值即可，但是一定要想到可扩充性，也就是尽量设置这个值大些。

相关文章