MySQL主从搭建

2019-04-09 2023-10-25 约 2460 字预计阅读 5 分钟次阅读条评论

警告

本文最后更新于 2023-10-25，文中内容可能已过时。

1 简介

MySQL作为世界上使用最为广泛的数据库之一，免费是其原因之一。但不可忽略的是它本身的功能的确很强大。随着技术的发展，在实际的生产环境中，由于单台MySQL数据库服务器不能满足实际的需求。此时数据库集群就很好的解决了这个问题了。采用MySQL分布式集群，能够搭建一个高并发、负载均衡的集群服务器。在此之前我们必须要保证每台MySQL服务器里的数据同步。数据同步我们可以通过MySQL内部配置就可以轻松完成，主要有主从复制和主主复制。

2 环境

主机名	IP	MySQL版本
mysql-primary	192.168.1.71	5.7
mysql-standby	192.168.1.72	5.7

3 主从复制

3.1 MySQL

已经安装好，并且没有任何数据

3.1.0.1 配置文件

一般Linux中的MySQL配置文件都在/etc/my.cnf(windows中的配置文件为mysql.ini)

1
2
3
log-bin=mysql-bin #开启二进制日志
max_binlog_size = 100M	#binlog每个日志文件大小
server-id=1       #设置server-id

注意：二进制日志必须开启，因为数据的同步实质上就是其他的MySQL数据库服务器将这个数据变更的二进制日志在本机上再执行一遍。

3.2 开始构建主从复制

第一步：在192.168.1.71中创建一个192.168.1.72主机中可以登录的MySQL用户用户：mysql1 密码：mysql1 MySQL 5.7及以前版本

1
2
mysql> grant replication slave on *.* to ‘mysql1’@’192.168.1.72’ identified by ‘mysql1’;
mysql> flush privileges;

MySQL 8创建

1
2
3
mysql> create user 'mysql1'@'192.168.1.72' identified by 'mysql1';  #创建用户  
mysql> grant replication slave on *.* to 'mysql1'@'192.168.1.72';   #授权  
mysql> flush privileges;

第二步：查看192.168.1.71 MySQL服务器二进制文件名与位置

1
mysql> show master status;

https://img.bwcxtech.com/img/20200928164522.jpeg

第三步：告知二进制文件名与位置在192.168.1.72中执行

1
2
3
4
5
6
mysql> CHANGE MASTER TO
MASTER_HOST='192.168.1.71',
MASTER_USER='mysql1',
MASTER_PASSWORD='mysql1',
MASTER_LOG_FILE='mysql-bin.000048',
MASTER_LOG_POS=432;

https://img.bwcxtech.com/img/20200928164526.png

完成主从复制配置

3.3 测试主从复制

在192.168.1.72中

1
2
mysql> start slave;         #开启复制
mysql> show slave status\G  #查看主从复制是否配置成功

https://img.bwcxtech.com/img/20200928164534.png

当看到Slave_IO_Running: YES、Slave_SQL_Running: YES才表明状态正常实际测试：登陆192.168.1.71主MySQL

1
mysql> show databases;

https://img.bwcxtech.com/img/20200928164539.jpeg

登陆192.168.1.72从MySQL

1
mysql> show databases;

https://img.bwcxtech.com/img/20200928164544.jpeg

192.168.1.71主MySQL操作

1
2
3
4
5
mysql> create database aa;
mysql> use aa;
mysql> create table tab1(id int auto_increment,name varchar(10),primary key(id));
mysql> show databases;
mysql> show tables;

https://img.bwcxtech.com/img/20200928164549.jpeg

192.168.1.72从MySQL操作

1
2
mysql> show databases;
mysql> show tables;

https://img.bwcxtech.com/img/20200928164557.jpeg

由上面两个结果图可得知，两主机达到了数据同步。主从复制的配置就是如此的简单。

3.4 还可以用到的其他相关参数

master开启二进制日志后默认记录所有库所有表的操作，可以通过配置来指定只记录指定的数据库甚至指定的表的操作，具体在mysql配置文件的[mysqld]可添加修改如下选项：不同步哪些数据库

1
2
3
binlog-ignore-db = mysql
binlog-ignore-db = test
binlog-ignore-db = information_schema

只同步哪些数据库，除此之外，其他不同步

1
binlog-do-db = game  

4 MySql主主复制

4.1 实现原理

主主复制即在两台MySQL主机内都可以变更数据，而且另外一台主机也会做出相应的变更。聪明的你也许已经想到该怎么实现了。对，就是将两个主从复制有机合并起来就好了。只不过在配置的时候我们需要注意一些问题，例如，主键重复，server-id不能重复等等。

4.2 配置文件

192.168.1.71

1
2
3
4
5
6
7
server-id=11                      #任意自然数n，只要保证两台MySQL主机不重复就可以了。
log-bin=mysql-bin                 #开启二进制日志
auto_increment_increment=2        #步进值auto_imcrement。一般有n台主MySQL就填n
auto_increment_offset=1           #起始值。一般填第n台主MySQL。此时为第一台主MySQL
binlog-ignore=mysql               #忽略mysql库
binlog-ignore=information_schema  #忽略information_schema库
replicate-do-db=aa                #要同步的数据库，默认所有库

192.168.1.72

1
2
3
4
5
server-id=12
log-bin=mysql-bin
auto_increment_increment=2
auto_increment_offset=2
replicate-do-db=aa

配置好后重启MySQL

4.3 开始构建主主复制

因为主主复制是两个主从复制组合一起，所以我就接着上面主从复制接着配置。第一步：在192.168.1.12中创建一个192.168.1.71主机中可以登录的MySQL用户用户：mysql11 密码：mysql11

1
2
mysql> grant replication slave on *.* to ‘mysql11’@’192.168.1.71’ identified by ‘mysql11’;
mysql> flush privileges;

第二步：在192.168.1.72查看二进制日志名和位置

1
mysql> show master status;

https://img.bwcxtech.com/img/20200928164602.jpeg

第三步：告知二进制文件名与位置在192.168.1.71中执行：

1
2
3
4
5
6
mysql> CHANGE MASTER TO
MASTER_HOST=’192.168.1.72’,
MASTER_USER=’mysql11’,
MASTER_PASSWORD=’mysql11’,
MASTER_LOG_FILE=’mysql-bin.000084’,
MASTER_LOG_POS=107;

https://img.bwcxtech.com/img/20200928164608.jpeg

完成主主复制配置

4.4 测试主主复制

分别开启start slave；

1
mysql> show slave status\g  #查看主从复制是否配置成功

192.168.1.71

https://img.bwcxtech.com/img/20200928164614.jpeg

192.168.1.72

https://img.bwcxtech.com/img/20200928164656.jpeg

当看到Slave_IO_Running: YES、Slave_SQL_Running: YES才表明状态正常测试： 192.168.1.71

1
2
mysql> use aa;
mysql> select * from tab1;

tab1无数据 192.168.1.72

1
2
mysql> use aa;
mysql> select * from tab1;

tab1无数据 192.168.1.71插入数据

1
mysql> insert into tab1 (name) value(‘11’),(‘11’),(‘11’);

192.168.1.72插入数据

1
mysql> insert into tab1 (name) value(‘22’),(‘22’),(‘22’);

查看数据：两个主机数据结果一样！

https://img.bwcxtech.com/img/20200928164704.jpeg

主主复制配置成功！

5 注意事项

1、主主复制配置文件中auto_increment_increment和auto_increment_offset只能保证主键不重复，却不能保证主键有序。

2、当配置完成Slave_IO_Running、Slave_SQL_Running不全为YES时，show slave status\G信息中有错误提示，可根据错误提示进行更正。

3、Slave_IO_Running、Slave_SQL_Running不全为YES时，大多数问题都是数据不统一导致。

常见出错点：

1、两台数据库都存在db数据库，而第一台MySQL db中有tab1，第二台MySQL db中没有tab1，那肯定不能成功。

2、已经获取了数据的二进制日志名和位置，又进行了数据操作，导致POS发生变更。在配置CHANGE MASTER时还是用到之前的POS。

3、stop slave后，数据变更，再start slave。出错。终极更正法：重新执行一遍CHANGE MASTER就好了。

目录

MySQL主从搭建

1 简介

2 环境

3 主从复制

3.1 MySQL

3.1.0.1 配置文件

3.2 开始构建主从复制

3.3 测试主从复制

3.4 还可以用到的其他相关参数

4 MySql主主复制

4.1 实现原理

4.2 配置文件

4.3 开始构建主主复制

4.4 测试主主复制

5 注意事项

相关内容

目录

MySQL主从搭建

1 简介

2 环境

3 主从复制

3.1 MySQL

3.1.0.1 配置文件

3.2 开始构建主从复制

3.3 测试主从复制

3.4 还可以用到的其他相关参数

4 MySql主主复制

4.1 实现原理

4.2 配置文件

4.3 开始构建主主复制

4.4 测试主主复制

5 注意事项

相关内容

MySQL常用命令

MySQL安装