mysql主主复制架构_搭建MySQL的主从、半同步、主主复制架构

快来打我* 2022-10-27 02:50 252阅读 0赞

复制其最终目的是让一台服务器的数据和另外的服务器的数据保持同步，已达到数据冗余或者服务的负载均衡。一台主服务器可以连接多台从服务器，并且从服务器也可以反过来作为主服务器。主从服务器可以位于不同的网络拓扑中，由于mysql的强大复制功能，其复制目标可以是所有的数据库，也可以是某些数据库，甚至是某个数据库中的某些表进行复制。

MySQL支持的两种复制方案：基于语句复制，基于行复制

基于语句复制基于行复制，这两种复制方式都是通过记录主服务器的二进制日志中任何有可能导致数据库内数据发生改变的SQL语句到中继日志，并且在从服务器上执行以下中继日志内的SQL语句，而达到与主服务器的数据同步。不同的是，当主服务器上执行了一个基于变量的数据并将其更新到数据库中，如now()函数，而此时基于语句复制时记录的就是该SQL语句的整个语法，而基于行复制就是将now()更新到数据库的数值记录下来。

例如：在主服务器上执行以下语句：

mysql>update user set createtime=now() where sid=16;

假如此时now()返回的值是：2012-04-16 20:46:35

基于语句的复制就会将其记录为：update user set createtime=now() where sid=16;

基于行复制的就会将其记录为：update user set createtime='2012-04-16 20:46:35' where sid=16;

进行主从复制启动的三个线程

Binlog dump线程：将二进制日志的内容发送给从服务器

I/O从线程：将接受的的数据写入到中继日志

SQL线程：一次从中继日志中读出一句SQL语句在从服务器上执行

一、主从复制：

准备工作：

1.修改配置文件(server\_id一定要修改)

2.建立复制用户

3.启动从服务器的从服务进程

规划：

Master：IP地址：172.16.4.11    版本：mysql-5.5.20

Slave：IP地址：172.16.4.12    版本：mysql-5.5.20

这里需注意，mysql复制大部分都是后向兼容，所以，从服务器的版本一定要高于或等于主服务器的版本。

1、Master

修改配置文件，将其设为mysql主服务器

\#vim /etc/my.cnf

server\_id=11                \#修改server\_id=11

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

sync\_binlog=1               \#任何一个事务提交之后就立即写入到磁盘中的二进制文件

innodb\_flush\_logs\_at\_trx\_commit=1       \#任何一个事物提交之后就立即写入到磁盘中的日志文件

保存退出

\#service mysql reload             \#重新载入mysql的配置文件

2、Master上创建用户，授予复制权限

mysql>grant replication client,replication slave on \*.\* to repl@172.16.4.12 identified by '135246';

mysql>flush privileges;

3、Slave

修改配置文件，将其设置为一个mysql从服务器

\#vim /etc/my.cnf

server\_id=12                \#修改server\_id=12

\#log-bin                \#注释掉log-bin，从服务器不需要二进制日志，因此将其关闭

relay-log=mysql-relay                \#定义中继日志名，开启从服务器中继日志

relay-log-index=mysql-relay.index     \#定义中继日志索引名，开启从服务器中继索引

read\_only=1                    \#设定从服务器只能进行读操作，不能进行写操作

保存退出

\#service mysql reload             \#重新载入mysql的配置文件

4、验证Slave上中继日志以及server\_id是否均生效

mysql>show variables like 'relay%';

\+-----------------------+-----------------+

| Variable\_name         | Value           |

\+-----------------------+-----------------+

| relay\_log             | relay-bin       |

| relay\_log\_index       | relay-bin.index |

| relay\_log\_info\_file   | relay-log.info  |

| relay\_log\_purge       | ON              |

| relay\_log\_recovery    | OFF             |

| relay\_log\_space\_limit | 0               |

\+-----------------------+-----------------+

mysql>show variables like 'server\_id';

\+---------------+-------+

| Variable\_name | Value |

\+---------------+-------+

| server\_id     | 12    |

\+---------------+-------+

5、启动从服务器的从服务进程

场景一、如果主服务器和从服务器都是新建立的，并没有新增其他数据，则执行以下命令：

mysql>change master to \\

master\_host='172.16.4.11',

master\_user='repl',

master\_password='135246';

mysql>show slave status\\G

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

Slave\_IO\_State:

Master\_Host: 172.16.4.11

Master\_User: repl

Master\_Port: 3306

Connect\_Retry: 60

Master\_Log\_File: mysql-bin.000001

Read\_Master\_Log\_Pos: 107

Relay\_Log\_File: relay-bin.000001

Relay\_Log\_Pos: 4

Relay\_Master\_Log\_File: mysql-bin.000001

Slave\_IO\_Running: No

Slave\_SQL\_Running: No

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

Relay\_Log\_Space: 2565465

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

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

mysql>start slave;

mysql>show slave status\\G

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

Slave\_IO\_State: Queueing master event to the relay log

Master\_Host: 172.16.4.11

Master\_User: repl

Master\_Port: 3306

Connect\_Retry: 60

Master\_Log\_File: mysql-bin.000001

Read\_Master\_Log\_Pos: 107

Relay\_Log\_File: relay-bin.000001

Relay\_Log\_Pos: 4

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

Relay\_Log\_Space: 300

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

场景二、如果主服务器已经运行过一段了，从服务器是新添加的，则需要将主服务器之前的数据导入到从服务器中：

Master：

\#mysqldump -uroot -hlocalhost -p123456 --all-databases --lock-all-tables --flush-logs --master-data=2 > /backup/alldatabase.sql

mysql>flush tables with read lock;

mysql>show master status;

\+------------------+----------+--------------+------------------+

\+------------------+----------+--------------+------------------+

| mysql-bin.000004 |      360 |              |                  |

\+------------------+----------+--------------+------------------+

mysql>unlock tables;

\#scp /backup/alldatabase.sql 172.16.4.12:/tmp

Slave：

\#mysql -uroot -p123456 < /tmp/alldatabase.sql

mysql>change master to \\

master\_host='172.16.4.11',

master\_user='repl',

master\_password='135246',

master\_log\_file='mysql-bin.000004',

master\_log\_pos=360;

mysql>show slave status\\G

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

Slave\_IO\_State:

Master\_Host: 172.16.4.11

Master\_User: repl

Master\_Port: 3306

Connect\_Retry: 60

Master\_Log\_File: mysql-bin.000004

Read\_Master\_Log\_Pos: 360

Relay\_Log\_File: relay-bin.000001

Relay\_Log\_Pos: 4

Relay\_Master\_Log\_File: mysql-bin.000004

Slave\_IO\_Running: No

Slave\_SQL\_Running: No

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

Relay\_Log\_Space: 107

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

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

mysql>start slave;

mysql>show slave status\\G

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

Slave\_IO\_State: Queueing master event to the relay log

Master\_Host: 172.16.4.11

Master\_User: repl

Master\_Port: 3306

Connect\_Retry: 60

Master\_Log\_File: mysql-bin.000004

Read\_Master\_Log\_Pos: 360

Relay\_Log\_File: relay-bin.000001

Relay\_Log\_Pos: 4

Relay\_Master\_Log\_File: mysql-bin.000004

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

Relay\_Log\_Space: 300

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

说明MySQL的主从复制架构成功

注1：MySQL的复制可以基于某个数据库或库中的默写表进行复制，要想实现该功能，只需在其配置文件中添加以下配置：

Master：

binlog-do-db=db\_name        只复制db\_name数据库

binlog-ignore-db=db\_name    不复制db\_name数据库

注2：在Master上定义过滤规则，意味着，任何不涉及到该数据库相关的写操作都不会被记录到二进制日志中，因此不建议在Master上定义过滤规则，并且不建议binlog-do-db与binlog-ignore-db同时定义。

Slave：

replicate\_do\_db=db\_name            只复制db\_name数据库

replicate\_ignore\_db=db\_name        不复制db\_name数据库

replicate\_do\_table=tb\_name        只复制tb\_name表

replicate\_ignore\_table=tb\_name        只复制tb\_name表

replicate\_wild\_do\_table=test%        只复制以test为开头并且后面跟上任意字符的名字的表

replicate\_wild\_ignore\_table=test\_    只复制以test为开头并且后面跟上任意单个字符的名字的表

注3：如果需要指定多个db或table时，则只需将命令多次写入

=============================================================================================

二、半同步复制

由于Mysql的复制都是基于异步进行的，在特殊情况下不能保证数据的成功复制，因此在mysql 5.5之后使用了来自google补丁，可以将Mysql的复制实现半同步模式。所以需要为主服务器加载对应的插件。在Mysql的安装目录下的lib/plugin/目录中具有对应的插件semisync\_master.so，semisync\_slave.so

在Master和Slave的mysql命令行运行如下命令：

Master：

mysql> install plugin rpl\_semi\_sync\_master soname 'semisync\_master.so';

mysql> set global rpl\_semi\_sync\_master\_enabled = 1;

mysql> set global rpl\_semi\_sync\_master\_timeout = 1000;

mysql> show variables like '%semi%';

\+------------------------------------+-------+

| Variable\_name                      | Value |

\+------------------------------------+-------+

| rpl\_semi\_sync\_master\_enabled       | ON    |

| rpl\_semi\_sync\_master\_timeout       | 1000  |

| rpl\_semi\_sync\_master\_trace\_level   | 32    |

| rpl\_semi\_sync\_master\_wait\_no\_slave | ON    |

\+------------------------------------+-------+

Slave：

mysql> install plugin rpl\_semi\_sync\_slave soname 'semisync\_slave.so';

mysql> set global rpl\_semi\_sync\_slave\_enabled = 1;

mysql> stop slave;

mysql> start slave;

mysql> show variables like '%semi%';

\+---------------------------------+-------+

| Variable\_name                   | Value |

\+---------------------------------+-------+

| rpl\_semi\_sync\_slave\_enabled     | ON    |

| rpl\_semi\_sync\_slave\_trace\_level | 32    |

\+---------------------------------+-------+

检查半同步是否生效：

Master：

mysql> show global status like 'rpl\_semi%';

\+--------------------------------------------+-------+

| Variable\_name                              | Value |

\+--------------------------------------------+-------+

| Rpl\_semi\_sync\_master\_clients               | 1     |

| Rpl\_semi\_sync\_master\_net\_avg\_wait\_time     | 0     |

| Rpl\_semi\_sync\_master\_net\_wait\_time         | 0     |

| Rpl\_semi\_sync\_master\_net\_waits             | 0     |

| Rpl\_semi\_sync\_master\_no\_times              | 0     |

| Rpl\_semi\_sync\_master\_no\_tx                 | 0     |

| Rpl\_semi\_sync\_master\_status                | ON    |

| Rpl\_semi\_sync\_master\_timefunc\_failures     | 0     |

| Rpl\_semi\_sync\_master\_tx\_avg\_wait\_time      | 0     |

| Rpl\_semi\_sync\_master\_tx\_wait\_time          | 0     |

| Rpl\_semi\_sync\_master\_tx\_waits              | 0     |

| Rpl\_semi\_sync\_master\_wait\_pos\_backtraverse | 0     |

| Rpl\_semi\_sync\_master\_wait\_sessions         | 0     |

| Rpl\_semi\_sync\_master\_yes\_tx                | 0     |

\+--------------------------------------------+-------+

说明半同步成功。

让半同步功能在MySQL每次启动都自动生效，在Master和Slave的my.cnf中编辑：

Master：

\[mysqld\]

rpl\_semi\_sync\_master\_enabled=1

rpl\_semi\_sync\_master\_timeout=1000     \#1秒

Slave：

\[mysqld\]

rpl\_semi\_sync\_slave\_enabled=1

也可通过设置全局变量的方式来设置是否启动半同步插件：

Master：

mysql> set global rpl\_semi\_sync\_master\_enabled=1

取消加载插件

mysql> uninstall plugin rpl\_semi\_sync\_master;

Slave：

mysql> set global rpl\_semi\_sync\_slave\_enabled = 1;

mysql> uninstall plugin rpl\_semi\_sync\_slave;

=============================================================================================

三、主主复制架构

1、在两台服务器上各自建立一个具有复制权限的用户；

Master：

mysql>grant replication client,replication slave on \*.\* to repl@172.16.4.12 identified by '135246';

mysql>flush privileges;

Slave：

mysql>grant replication client,replication slave on \*.\* to repl@172.16.4.11 identified by '135246';

mysql>flush privileges;

2、修改配置文件：

Master：

\[mysqld\]

server-id = 11

log-bin = mysql-bin

auto-increment-increment = 2

auto-increment-offset = 1

relay-log=mysql-relay

relay-log-index=mysql-relay.index

Slave：

\[mysqld\]

server-id = 12

log-bin = mysql-bin

auto-increment-increment = 2

auto-increment-offset = 2

relay-log=mysql-relay

relay-log-index=mysql-relay.index

3、如果此时两台服务器均为新建立，且无其它写入操作，各服务器只需记录当前自己二进制日志文件及事件位置，以之作为另外的服务器复制起始位置即可

Master：

mysql> show master status;

\+------------------+----------+--------------+------------------+

\+------------------+----------+--------------+------------------+

| mysql-bin.000004 |      360 |              |                  |

\+------------------+----------+--------------+------------------+

Slave：

mysql> show master status;

\+------------------+----------+--------------+------------------+

\+------------------+----------+--------------+------------------+

| mysql-bin.000005 |      107 |              |                  |

\+------------------+----------+--------------+------------------+

4、各服务器接下来指定对另一台服务器为自己的主服务器即可：

Master：

mysql>change master to \\

master\_host='172.16.4.12',

master\_user='repl',

master\_password='135246',

master\_log\_file='mysql-bin.000005',

master\_log\_pos=107;

Slave：

mysql>change master to \\

master\_host='172.16.4.11',

master\_user='repl',

master\_password='135246',

master\_log\_file='mysql-bin.000004',

master\_log\_pos=360;

5、启动从服务器线程：

Master：

mysql>start slave;

Slave：

mysql>start slave;

到此主主架构已经成功！

mysql主主复制架构_搭建MySQL的主从、半同步、主主复制架构

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 Linux 搭建数据库 Mariadb 主主主从半同步复制

相关 MySql的主从复制、主主复制【MySql 5.7，Docker】

相关 mysql主主复制架构_搭建MySQL的主从、半同步、主主复制架构

相关 MySQL主从(MS)、主主(MM)复制

相关配置mysql5.5主从复制、半同步复制、主主复制

相关 MySQL主从Replication同步&&半同步&&主主复制架构

相关（三）mysql主主复制搭建

相关 MySQL数据的主从复制、半同步复制和主主复制详解

相关 mariadb的主从复制、主主复制、半同步复制配置详解

相关 Mysql的主从复制和主主复制操作