mysql实现主从复制

Mysql实现主从复制

​ 本次示例采用docker部署，获得两台一主一从数据库。

​ mysql实现主从复制的关键是借用了binlog日志。在mysql中binlog日志会记录数据库中的所有ddl和dml操作，主库开启binlog日志，从库连接主库，获取主库中的binlog日志中的信息。通过这些信息在从库中同步主库的数据和信息。

​ 下面我就通过一个示例来展示，具体如何实现mysql的主从复制。

一、使用docker，运行两个mysql容器

主：

​ 端口：3307 密码：123456

从：

​ 端口：3308 密码：123456

（ps：本次mysql的版本选用的是8)

1.主库运行容器的命令

docker run --name mysql-master -p 3307:3306 -e MYSQL_ROOT_PASSWORD=123456 -d mysql:8

2.从库运行容器的命令

docker run --name mysql-slave -p 3308:3306 -e MYSQL_ROOT_PASSWORD=123456 -d mysql:8

​ 通过以上两个命令，我们得到了两个运行的容器，里面分别运行着主库和从库。

（ps：本地没有镜像也没关系，使用run命令如果本地没有对应镜像，会先从远程下载对应镜像，然后再运行容器，所以docker起来后可以直接运行以上两行命令。）

二、配置主库开启binlog

由于主库容器没有将配置文件的目录挂载出去，因此需要直接进入容器内，修改配置文件。（ps：文章后面会提到如何挂载目录）

1.进入容器

docker exec -it mysql-master /bin/bash

2.切换目录到/etc/mysql*下*

cd /etc/mysql

查看该目录下的文件发现会有一个my.cnf文件，我们需要修改此文件

root@3278b1c8e6c1:/etc/mysql# ls
conf.d  my.cnf  my.cnf.fallback

3.使用vim修改my.cnf中的内容

由于我的mysql是使用docker启起来的，因此里面只保留了最基本的命令，vim是没有的，所以可以执行以下命令安装vim。

apt-get update
apt-get install -y vim 

使用以上命令可能会报错，出现以下报错

The following signatures couldn't be verified because the public key is not available: NO_PUBKEY A7813D884I98HR43
Reading package lists... Done
W: GPG error: https://download.docker.com/linux/ubuntu bionic InRelease: The following signatures couldn't be verified because the public key is not available: NO_PUBKEY A7813D884I98HR43
E: The repository 'https://download.docker.com/linux/ubuntu bionic InRelease' is not signed.
N: Updating from such a repository can't be done securely, and is therefore disabled by default.
N: See apt-secure(8) manpage for repository creation and user configuration details.

在这个时候先执行以下命令，然后再重新安装vim即可。

apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv A7813D884I98HR43

（ps：此处的A7813D884I98HR43要和上面的报错对应起来）

安装完vim后，即可修改my.cnf文件。

vim  my.cnf

my.cnf中的初始内容

# Copyright (c) 2017, Oracle and/or its affiliates. All rights reserved.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301 USA

#
# The MySQL  Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
secure-file-priv= NULL

# Custom config should go here
!includedir /etc/mysql/conf.d/

我们只需要在其[mysqld]中添加如下配置即可。

server-id=1
log-bin=mysql-bin
binlog-format=ROW

my.cnf文件最终的样子

# Copyright (c) 2017, Oracle and/or its affiliates. All rights reserved.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301 USA

#
# The MySQL  Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
secure-file-priv= NULL
server-id=1
log-bin=mysql-bin
binlog-format=ROW
# Custom config should go here
!includedir /etc/mysql/conf.d/

保存my.cnf。

4.重启docker容器

由于我是在windows中运行的docker，所以直接可以再可视化的界面中点击一下重启即可。

如果你是在linux系统中，则可执行以下命令。

docker restart 容器id/容器名

5.创建给从库使用的账号

#1.进入容器
docker exec -it mysql-master /bin/bash

#2.登入mysql
mysql -uroot -p123456

#3.创建账号
CREATE USER 'slave'@'%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';

6.查看master状态

show master status;

+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      156 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.04 sec)

此处的mysql-bin.000002和156比较重要，需要记录。

三、配置从库

1.进入容器

docker exec -it mysql-slave /bin/bash

2.修改my.cnf配置文件（vim和上面一样安装）**

vim /etc/mysql/my.cnf

my.cnf最终结果

# Copyright (c) 2017, Oracle and/or its affiliates. All rights reserved.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301 USA

#
# The MySQL  Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
secure-file-priv= NULL
server-id=2

# Custom config should go here
!includedir /etc/mysql/conf.d/

3.开启slave

#1.登入mysql
mysql -uroot -p123456

#2.改变从库的master
change master to master_host='172.17.0.2',master_port=3306,master_user='slave',master_password='123456',
master_log_file='mysql-bin.000002',master_log_pos=156;
#其中master_host填写的服务器ip地址，由于我使用的是docker，可用
docker inspect --format='{{.NetworkSettings.IPAddress}}' mysql-master 查看
#其中master_port是mysql暴露的端口号，由于此处是docker启动，因此直接填3306（不要填宿主机配置的3307）
#其中master_user填写的是连接数据库的用户名，如果之前配置的slave连不了，可直接使用root账号进行填充。
#其中master_password填写的是账号对应的密码。
#其中master_log_file填写的就是主库中记录的mysql-bin.000002
#其中master_log_pos填写的就是主库中记录的156

#3.启动slave
start slave;

4.查看是否配置成功

show slave status \G

其中Slave_IO_Running和Slave_SQL_Running都是Yes表示配置成功。

四、测试主从复制

此时你可以试试修改主库，查看从库是否会同步主库。

主库添加testdb2库后，从库自动添加testdb2

五、配置文件挂载出去的方式

1.首先和之前一样的方式将容器启动起来

docker run --name mysql-master -p 3307:3306 -e MYSQL_ROOT_PASSWORD=123456 -d mysql:8

2.将配置文件拷贝到宿主机上

docker cp mysql-master:/etc/mysql/my.cnf  D:\docker_container_config\mysql\master\conf

3.销毁之前的容器

docker rm -f 容器id/容器名

4.重新启动容器并挂载目录

docker run --name mysql-master -p 3307:3306  -v D:\docker_container_config\mysql\master\conf:/etc/mysql  
-e MYSQL_ROOT_PASSWORD=123456 -d mysql:8

然后就和之前一样直接修改宿主机中的配置文件即可，接下来的步骤和上述流程一样。