pi数据库同步解决方案_MySQL数据库主主同步配置实战

最近云服务器大减价，趁机买了几台，博客就放在其中一台上，为了不让剩下的两台服务器闲置，打算都利用起来，对博客网站进行负载均衡。使用两台数据库进行主主同步配置，扩展网站数据库架构，提高数据库的读写性能。

以下是MySQL主主同步配置实战过程。

首先说下什么是MySQL主主同步。

假设有两台服务器，这两台服务器上都安装了MySQL数据库软件，但是网站如果只使用单台服务器，性能会受到限制。为了扩展网站的容量，可以同时使用这两台数据库服务器，再搭配上负载均衡配置，让访客随机访问这两台数据库服务器，这样访问速度更快了，且不容易因为访客过多而导致服务器宕机。

一、软件版本和环境设置

两台数据库服务器：

Server 1:192.168.0.1

Server 2:192.168.0.2

两台数据库服务器MySQL版本一致，均为：

MySQL 5.7.29-log – Source distribution

两台服务器的数据库已经手动同步完毕

二、主主同步数据库配置

1.修改MySQL配置文件，开启同步：

在数据库server 1的my.cnf文件mysqld节点添加以下配置：

server-id = 1

log-bin=mysql-bin

binlog_format=mixed

auto_increment_increment=2

auto_increment_offset=1

replicate-ignore-db=mysql

replicate-ignore-db=information_schema

replicate-ignore-db=performance_schema

character_set_server=utf8

log-bin-trust-function-creators=1

在数据库server 2的my.cnf文件mysqld节点添加以下配置：

server-id = 2

log-bin=mysql-bin

binlog_format=mixed

auto_increment_increment=2

auto_increment_offset=2

replicate-ignore-db=mysql

replicate-ignore-db=information_schema

replicate-ignore-db=performance_schema

character_set_server=utf8

log-bin-trust-function-creators=1

注意：可能部分命令已经配置后了，这里只需要将没有配置的上述部分写如MySQL配置文件。

下面对刚才写的配置文件进行解析：

server-id 是服务器标号，可以为任意数字，但是两台服务器要求不能相同

log_bin 表示启动mysql二进制日志，如果没有配置这个将无法远程链接

binlog_format 为数据库复制模式

replicate-ignore-db 指定不同步的数据库，如果有多个数据库不需要同步可以多个分别声明

character_set_server=utf8 指定utf8为默认字符集

auto_increment_increment=2  为了避免两台数据库同步冲突，将数据库中自动增长键值的步进值auto_imcrement设置为2。一般有n台主MySQL就填n

auto_increment_offset 设定数据库中自动增长的起点，两台mysql的起点必须不同，这样才能避免两台服务器同步时出现主键冲突

log-bin-trust-function-creators=1 在默认情况下，mysql会阻止主从同步的数据库function的创建,这会导致我们在导入sql文件时如果有创建function或者使用function的语句将会报错。

2.重启数据库

修改数据库配置后分别重启：

/etc/inint.d/mysqld restart

3.进入MySQL进行同步授权

分别进入数据库：

mysql -uroot -p

然后分别进行同步授权：

server 1：

grant replication slave, replication client on *.* to 'root'@'192.168.0.2' identified by '数据库密码';

server 2：

grant replication slave, replication client on *.* to 'root'@'192.168.0.1' identified by '数据库密码';

之后分别刷新权限：

flush privileges;

4.查看两台数据库服务器的日志节点

为进行下一步配置，需要获得两台数据库服务器mysql-bin位置和Position值。

分别使用show master status;命令查看。

server 1：

mysql> show master status;

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

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| mysql-bin.000011 | 8428 | | | |

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

1 row in set (0.00 sec)

server 2：

mysql> show master status;

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

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| mysql-bin.000005 | 5346 | | | |

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

1 row in set (0.00 sec)

这样获得了mysql-bin的位置和Position值，下一步配置的时候需要用到。

5.分别设置日志同步节点

server 1：

mysql> change master to

-> master_host='192.168.0.2',

-> master_port=3306,

-> master_user='root',

-> master_password='数据库密码',

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

-> master_log_pos=5346;

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

server 2：

mysql> change master to

-> master_host='192.168.0.1',

-> master_port=3306,

-> master_user='root',

-> master_password='数据库密码',

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

-> master_log_pos=8428;

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

这里根据自己服务器的真实IP地址、数据库用户名、数据库端口、数据库密码进行设置，master_log_file和master_log_pos分别设置成在另外一台数据库服务器上查询得到的数据。

注意：每次“flush privileges”和“stop slave”后，或者数据库重启后，日志节点的内容会改变，所以如果不确定，每次执行此操作前必须要查看日志节点——show master status；。另外，执行此操作时slave必须没有启动，如果已经启动了，那就先停止，指令：stop slave;

6.启动同步

分别在两台数据库服务器上启动同步：

start slave;

这样数据库主主同步就成功了！

三、查看同步状态

启动同步后，可以查看数据库的同步状态。

查看同步状态的命令是：show slave status\G

server 2:

mysql> show slave status\G

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

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.0.1

Master_User: root

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000011

Read_Master_Log_Pos: 27366

Relay_Log_File: iZ8vbamf5ysf8obxd0lheqZ-relay-bin.000002

Relay_Log_Pos: 19258

Relay_Master_Log_File: mysql-bin.000011

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB: mysql,information_schema,performance_schema

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

Relay_Log_Space: 19483

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: 562716ca-8e01-11ea-9589-00163e04c0bd

Master_Info_File: /www/server/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)

可以看到数据库同步已经连接成功了！

这时如果安装了phpmyadmin，可以看到显示数据库已经处于“主复制”和“从复制”状态了。

MySQL主主同步配置成功

在一台数据库中进行修改，另外一台数据库也会执行同样的更改，可以验证MySQL主主同步配置成功！

四、常见错误

MySQL主主同步配置常见的错误信息如下：

Slave_IO_State: Connecting to master

或者显示：

Slave_IO_Running: Connecting

Slave_SQL_Running: Yes

这个错误表明连接另一台数据库服务器不成功，可能的原因有网络不通、数据库账号密码错误、pos错误或者数据库端口未开放，其中，数据库端口未开放最常见。

我使用的是宝塔面板，第一次配置后出现了这个错误，最后发现是宝塔面板没有对数据库的3306端口放行导致的，放开3306端口后错误解决。

放开数据库3306端口