Linux系统MySQL主从结构搭建

一、前言

由于最近有个项目随着业务增长存在并发压力隐患，优化迫在眉睫。由于当前系统是单数据库系统原因，能优化的地方也尽力优化了但是数据库瓶颈还是严重限制了项目的并发能力。所以就考虑了添加数据库来增大项目并发能力。考虑到读写分离等，从而需要进行mysql主从复制。在此介绍如何进行mysql主从搭建。

二、提前准备

由于需要提前进行搭建测试，本地使用了VirtualBox创建了两个虚拟机，具体如何创建可以参考 《win10搭建Vagrant+VirtualBox环境》 。同时准备两个mysql数据库，linux下mysql安装方法可以参考 《linux下安装mysql-5.7.23详细步骤》 。从而我们环境准备如下：

IP	主从关系	server-id
192.168.33.11	master	11
192.168.33.12	salve	12

<span data-slate-string="true">注意</span>:确保主机ip和从机ip在同一网段，即在局域网内；在从机上执行链接信息命令，让从机主动连接主机，由于配置文件的信息校验通过，主机允许从机访问主机MySQL服务从而实现主从结构。

三、主从搭建

3.1 步骤一（master）

修改mysql的my.cnf文件，在[mysqld]下增加如下内容：

# 同一局域网要唯一
server-id = 11
# 开启二进制日志，可以随便取
log-bin = mysql-bin
#自动清理 7 天前的log文件,可根据需要修改
expire_logs_days = 7
# 复制过滤，不需要备份的数据库，不输出（mysql库一般不用）,多个库写多行
binlog-ignore-db = mysql
# 为每个session分配内润，在事物过程中用来存储二进制日志的缓存
binlog_cache_size = 2M
# 主从复制的格式（mixded，statement，row）
binlog_format = mixed
log_bin_index = master-bin.index

<span data-slate-string="true">注意</span>：配置内容必须在[mysqld]的下面。

3.2 步骤二（slave）

修改mysql的my.cnf文件，在[mysqld]下增加如下内容：

# 同一局域网要唯一
server-id = 12
# 开启二进制日志，以备slave作为其他slave的master时使用
log-bin = mysql-slave-bin
# relay_log配置中继日志
relay_log=edu-mysql-relay-bin
# 复制过滤，不需要备份的数据库不输出
binlog-ignore-db = mysql
# 为每个session分配内润，在事物过程中用来存储二进制日志的缓存
binlog_cache_size = 2M
# 主从复制的格式（mixded，statement，row）
binlog_format = mixed
# 如果需要同步函数或者存储过程
log_bin_trust_function_creators=true
# 跳过主从复制的所有错误或指定类型的错误，以免slave端复制中断，如：1062，主键重复； 1032 主从复制数据不一致
slave_skip_errors=1062
#加上以下参数可以避免更新不及时，SLAVE 重启后导致的主从复制出错
read_only = 1
master_info_repository = TABLE
relay_log_info_repository = TABLE
relay-log = slave-relay-bin
#主服务器I/O日志读取、记录及存放
relay-log-index = slave-relay-bin.index

3.3 步骤三（master）

在master节点，重启mysql服务后，登陆mysql终端，执行如下命令：

mysql> grant replication slave, replication client on *.* to 'root'@'192.168.33.12' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

<span data-slate-string="true">注意</span>:创建用户必须放开从库地址访问。

查看新增的用户信息：

select user,host from mysql.user;

查看master信息：

show master status;

3.4 步骤四（slave）

在slave节点，重启mysql服务后，登陆mysql终端，设置需要同步的master节点信息，同时需要指定同步位置。

mysql> change master to master_host='192.168.33.11', master_user='root', master_password='123456', master_port=3306, master_log_file='mysql-bin.000017', master_log_pos=194;
Query OK, 0 rows affected, 2 warnings (0.03 sec)

<span data-slate-string="true">注意</span>:具体的配置信息要以 步骤三（master） 中配置和查询的结果为依据，master_log_file：master状态结果中File，master_log_pos：master状态结果中的Position。

开启slave并查看状态：

mysql> start  slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G;

<span data-slate-string="true">注意</span>: 只有看到Slave_IO_Running、Slave_SQL_Running都为Yes时，主从才算搭建完成。

四、主从测试

4.1 测试数据库同步

4.1.1 创建数据库（master）

mysql> show databases;

mysql> create database pure default character set utf8 collate utf8_general_ci;

4.1.2 查看数据库同步（slave）

mysql> show databases;

4.2 测试表数据同步

4.2.1 建表并插入数据（master）

mysql> use testdb;

mysql> show tables;

mysql> create table `student`  (
  `sno` int(11) not null auto_increment comment '学号',
  `sname`  varchar(50) character set utf8 collate utf8_general_ci not null comment '姓名',
  `ssex` varchar(2) character set utf8 collate utf8_general_ci not null comment '性别',
   primary key (`sno`) using btree
) engine = innodb auto_increment = 1 character set = utf8 collate = utf8_general_ci row_format = dynamic;

mysql> insert into `student` values (1, 'KangKang', 'M');

mysql> insert into `student` values (2, 'Mike', 'M');

mysql> insert into `student` values (3, 'Jane', 'F');

mysql> select * from `student`;

4.2.2 查看数据库同步（slave）

mysql> use testdb;

mysql> show tables;

mysql> select * from `student`;