mysql主从

自打接触运维以来，影响比较深的，一个云服务，另一个是容器，在很多东西使用起来越来越方便快捷的同时，有些最基本的东西总是不变的，于是有了这篇在云服务器上通过创建容器搭建MySQL主从。

且怒且悲且狂哉，是人是鬼是妖怪。    ——《悟空》

一、前言

MySQL 主从又叫做 replication、AB 复制。

意思是 A、B 两台服务器做主从配置之后，在 A 上写数据，另外一台 B 也会跟着写数据，两者数据是实时同步的。

MySQL 主从是基于 binlog 的，主库上须开启binlog才能进行主从同步。大致步骤如下：

1、主库将更改操作记录到 binlog 里面；

2、从库将主库的 binlog 事件（sql语句）同步到本机上并记录在relaylog（中继日志）中；

3、从库根据 relaylog 里面的 sql 语句按顺序执行。

主服务器上有一个 log dump 的线程，用来和从的I/O 线程传递 binlog。

从上有两个线程，其中 I/O 线程用来同步主上的 binlog 并生成 relaylog，另外一个 sql 线程则用来把 relaylog 里面的 sql 语句落地。

MySQL主从一类用作备份，一类为了将从库用作只读库，减轻主库压力。

 

二、基础环境

2.1 下载二进制文件

http://mirrors.sohu.com/mysql/

这里下载的是 5.6 版本的当做实验。

2.2 创建一主一从

在 docker 环境的支持下，基于 centos 镜像创建两个独立环境，一台作为主（mysql_master），一台作为从（mysql_slave）。

docker run --name=mysql_master -itd -p 3307:3306 -p 27:22 -v /usr/local/src/:/opt  /centos

docker run --name=mysql_slave -itd -p 3308:3306 -p 28:22 -v /usr/local/src/:/opt  /centos

通信地址

mysql_master：172.17.0.4

mysql_slave   ：172.17.0.3

 

2.3 分别在主从上安装 mysql

1、登录主服务器

docker exec -it mysql_master /bin/bash

2、解压二进制文件

[root@0560232277b8 opt]# tar zxf mysql-5.6.41-linux-glibc2.12-x86_64.tar.gz -C /usr/local/

3、将文件进行重命名

 

[root@0560232277b8 local]# mv mysql-5.6.41-linux-glibc2.12-x86_64 mysql

4、定义一下alias

 

[root@0560232277b8 local]# alias 'mysql=/usr/local/mysql/bin/mysql'

5、添加一个mysql用户

 

useradd mysql

6、接着对mysql进行初始化，进入到mysql二进制目录下

 

[root@0560232277b8 mysql]# ./scripts/mysql_install_db --user=mysql --datadir=/data/mysql
bash: ./scripts/mysql_install_db: /usr/bin/perl: bad interpreter: No such file or directory

这里我们可能会发现没有perl，安装一下即可

yum install perl  perl-devel autoconf  perl-Data-Dumper  libaio numactl-libs -y

最后初始化完成

 

[root@0560232277b8 mysql]# ./scripts/mysql_install_db --user=mysql --datadir=/usr/local/mysql/data/

7、修改配置

接着我们需要修改一下配置文件my.cnf，将数据文件目录 datadir 修改成我们配置的目录

[mysqld]
datadir=/usr/local/mysql/data/
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]
log-error=/var/log/mysql/mariadb.log
pid-file=/var/run/mysql/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

8、拷贝启动文件

然后我们可以拷贝一下启动文件，修改启动文件中相应的配置

[root@0560232277b8 mysql]# cp support-files/mysql.server /etc/init.d/mysqld

定义好 basedir 和 datadir 地址

basedir=/usr/local/mysql
datadir=/usr/local/mysql/data

在启动过程中可能需要创建一些不存在的文件，并给相应的目录赋予mysql所有者及写的权限

mkdir -p /var/lib/mysql
chown -R mysql. /var/lib/mysql
mkdir -p  /var/log/mysql  
chown -R mysql. /var/log/mysql
chmod -R 755 /usr/local/mysql/data

9、主库启动成功

 

[root@0560232277b8 mysql]# /etc/init.d/mysqld start
Starting MySQL. SUCCESS!

10、从库启动成功

 

[root@40deaafc5701 mysql]# /etc/init.d/mysqld start
Starting MySQL.Logging to '/usr/local/mysql/data/40deaafc5701.err'.
. SUCCESS!

 

注：在登录mysql时可能会出现sock问题

 

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (111)

做一个软连接或者修改下配置文件 my.cnf 路径即可

 

[root@0560232277b8 mysql]# ln -s /var/lib/mysql/mysql.sock /tmp/mysql.sock

 

三、主库配置

3.1 修改配置

在配置文件 /etc/my.cnf 中添加 server-id 和 log_bin

 

server-id=4
log_bin=mysql_MS           # 只需在主上配置开启 binlog

3.2 重启 mysql

[root@0560232277b8 mysql]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS!

然后我们就会看到在data目录下生产了两个文件，一个是binlog文件，一个是索引文件

 

-rw-rw---- 1 mysql mysql      120 Nov 24 05:43 mysql_MS.000001
-rw-rw---- 1 mysql mysql       18 Nov 24 05:43 mysql_MS.index

3.3 导入测试数据

我们创建一个数据库，导入一些数据到新建的库中

[root@0560232277b8 /]# /usr/local/mysql/bin/mysql  -uroot -p jumpserver </opt/jumpserver.sql

接着可以查看到新库中的数据

mysql> use jumpserver;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------------+
| Tables_in_jumpserver |
+----------------------+
| age                  |
| school               |
| user                 |
+----------------------+
3 rows in set (0.00 sec)

3.4 创建数据同步用户

创建用作同步数据的用户

 

mysql> grant replication slave on *.* to 'repl'@'172.17.0.3' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

暂时锁定表的写入操作

mysql> flush tables with read lock;
Query OK, 0 rows affected (0.01 sec)

查看 master 状态

 

mysql> show master status;
+-----------------+----------+--------------+------------------+-------------------+
| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| mysql_MS.000001 |     2872 |              |                  |                   |
+-----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

 

四、从库配置

4.1 查看状态

登录到从库服务器查看mysql状态

 

[root@40deaafc5701 /]# /etc/init.d/mysqld status
 SUCCESS! MySQL running (491)

4.2 修改配置

修改配置文件，添加server-id（不能和master一致）

 

server-id=3

将主库备份的数据库文件导入到从库中

mysql> create database jumpserver;
Query OK, 1 row affected (0.00 sec)


[root@40deaafc5701 /]# mysql -uroot -p jumpserver </opt/jumpserver.sql

4.3 停止slave

 

mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

4.4 配置连接到主库

 

mysql> change master to master_host='172.17.0.4',master_user='repl',master_password='123456',master_log_file='mysql_MS.000001',master_log_pos=2872;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

4.5 开启slave

 

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

4.6 解锁主库上的表

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

4.7 查看 slave 状态

在从库上查看状态是否成功

 

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.17.0.4
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_MS.000001
          Read_Master_Log_Pos: 3170
               Relay_Log_File: 40deaafc5701-relay-bin.000002
                Relay_Log_Pos: 282
        Relay_Master_Log_File: mysql_MS.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
··· ··· ··· ···
··· ··· ··· ···

主要查看 Slave_IO_Running 和 Slave_SQL_Running 状态是否成功，在这里也可以看到 master 的相关信息。

 

 

注：如果Slave_IO_Running 处于connecting状态，可以从以下三方面检查配置

 

• 网络不通
• 密码不对
• pos不对

 

检查这三个地方根据 master 的状态变化重新配置连接即可。

 

五、测试同步

在主库上删除一张 age 表

mysql> show tables;
+----------------------+
| Tables_in_jumpserver |
+----------------------+
| age                  |
| school               |
| user                 |
+----------------------+
3 rows in set (0.00 sec)

mysql> 
mysql> 
mysql> drop table age;
Query OK, 0 rows affected (0.01 sec)

在从库上也同步显示 age 表已删除

mysql> show tables;
+----------------------+
| Tables_in_jumpserver |
+----------------------+
| age                  |
| school               |
| user                 |
+----------------------+
3 rows in set (0.00 sec)

mysql> show tables;
+----------------------+
| Tables_in_jumpserver |
+----------------------+
| school               |
| user                 |
+----------------------+
2 rows in set (0.00 sec)