Mysql分布式部署 - 主从复制

作者: 红薯爱帅 | 来源:发表于2018-02-11 21:40 被阅读15次

1，概述

MySQL数据库自身提供的主从复制功能，可以方便地实现数据的多处自动备份，完成Mysql的分布式部署。多个数据备份，解决了以下几个问题：

数据的安全性，不易丢失。
读写分离，提高数据库的读写性能。
数据库的负载均衡，提高数据库的高可用和容错性。

2，Mysql主从复制的基本原理和步骤

2.1，基本介绍

MySQL内建的复制功能是构建大型、高性能应用程序的基础。将MySQL的数亿分布到到多个系统上去，这种分步的机制，是通过将MySQL的某一台主机（Master）的数据复制到其它主机（Slave）上，并重新执行一遍来实现的。复制过程中，一台服务器充当Master，而一个或多个其它服务器充当Slave。

2.2，实现步骤

MySQL之间数据复制的基础是二进制日志文件（binary log file）。一台MySQL数据库一旦启用二进制日志后，其作为master，它的数据库中所有操作都会以“事件”的方式记录在二进制日志中，其他数据库作为slave通过一个【I/O线程】与主服务器保持通信，并监控master的二进制日志文件的变化，如果发现master二进制日志文件发生变化，则会把变化复制到自己的中继日志中，然后slave的一个【SQL线程】会把相关的“事件”执行到自己的数据库中，以此实现从数据库和主数据库的一致性，也就实现了主从复制。

主从复制过程，可以简化为三个步骤：

Master将改变记录到二进制日志中。
Slave将Master的二进制日志拷贝到它的中继日志(Relay_log)。
Slave重做中继日志中的事件，将改变反映它自己的数据。

Mysql主从复制-实现原理.jpg

注意：在主从复制架构中，所有对参与主从复制的数据表的更新操作，必须在主服务器上进行。

2.3，典型的应用场景

在一主多从的数据库体系中，多个从服务器采用异步的方式更新主数据库的变化，业务服务器在执行写或者相关修改数据库的操作是在主服务器上进行的，读操作则是在各从服务器上进行。

Mysql主从复制-一主多从.jpg

2.4. MySQL支持的复制类型

基于语句的复制
在主服务器上执行的SQL语句，在从服务器上执行同样的语句。否则，你必须要小心，以避免用户对主服务器上的表进行的更新与对服务器上的表所进行的更新之间的冲突。
配置：binlog_format = 'STATEMENT'
基于行的复制
把改变的内容复制过去，而不是把命令在从服务器上执行一遍，从 MySQL 5.0开始支持。
配置：binlog_format = 'ROW'
混合类型的复制
默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制。
配置：binlog_format = 'MIXED'

3，实现过程（采用Docker方式部署）

3.1，准备工作

查找mysql的docker镜像

kevin@apple:~$ docker search mysql
NAME                                                   DESCRIPTION                                     STARS     OFFICIAL   AUTOMATED
mysql                                                  MySQL is a widely used, open-source relati...   5693      [OK]       
mariadb                                                MariaDB is a community-developed fork of M...   1767      [OK]       
mysql/mysql-server                                     Optimized MySQL Server Docker images. Crea...   391                  [OK]
percona                                                Percona Server is a fork of the MySQL rela...   317       [OK]       
zabbix/zabbix-server-mysql                             Zabbix Server with MySQL database support       80                   [OK]
hypriot/rpi-mysql                                      RPi-compatible Docker Image with Mysql          79                   
centurylink/mysql                                      Image containing mysql. Optimized to be li...   58                   [OK]
zabbix/zabbix-web-nginx-mysql                          Zabbix frontend based on Nginx web-server ...   42                   [OK]
tutum/mysql                                            Base docker image to run a MySQL database ...   31                   
1and1internet/ubuntu-16-nginx-php-phpmyadmin-mysql-5   ubuntu-16-nginx-php-phpmyadmin-mysql-5          25                   [OK]
mysql/mysql-cluster                                    Experimental MySQL Cluster Docker images. ...   19                   
centos/mysql-57-centos7                                MySQL 5.7 SQL database server                   18                   
schickling/mysql-backup-s3                             Backup MySQL to S3 (supports periodic back...   16                   [OK]
linuxserver/mysql                                      A Mysql container, brought to you by Linux...   14                   
zabbix/zabbix-proxy-mysql                              Zabbix proxy with MySQL database support        10                   [OK]
centos/mysql-56-centos7                                MySQL 5.6 SQL database server                   8                    
openshift/mysql-55-centos7                             DEPRECATED: A Centos7 based MySQL v5.5 ima...   6                    
circleci/mysql                                         MySQL is a widely used, open-source relati...   3                    
frodenas/mysql                                         A Docker Image for MySQL                        3                    [OK]
dsteinkopf/backup-all-mysql                            backup all DBs in a mysql server                3                    [OK]
inferlink/landmark-mysql                               landmark-mysql                                  0                    [OK]
ansibleplaybookbundle/mysql-apb                        An APB which deploys RHSCL MySQL                0                    [OK]
cloudposse/mysql                                       Improved `mysql` service with support for ...   0                    [OK]
cloudfoundry/cf-mysql-ci                               Image used in CI of cf-mysql-release            0                    
openzipkin/zipkin-mysql                                Mirror of https://quay.io/repository/openz...   0

创建网络，docker有四种网络类型（bridge、container、host和none），我们使用的是none，可以设置静态IP

kevin@apple:~$ docker network create --subnet=172.18.0.0/24 my_network
0b85ab8c7809886bb7fada8c4a8970b5692f11dbd950790f1e9b1e098691f114

启动新容器master，命名为mysql_master，设置静态IP为172.18.0.10

kevin@apple:~$ docker run -it --name mysql_master -h mysql_master \
-p 13306:3306 --net my_network --ip 172.18.0.10 \
--add-host mysql_master:172.18.0.10 \
mysql:latest /bin/bash

启动新容器slave，命名为mysql_slave，设置静态IP为172.18.0.11

kevin@apple:~$ docker run -it --name mysql_slave -h mysql_slave \
-p 13307:3306 --net my_network --ip 172.18.0.11 \
--add-host mysql_master:172.18.0.10 \
mysql:latest /bin/bash

在docker容器中，部署相关软件

root@mysql_master:~$ apt-get update
root@mysql_master:~$ apt-get install vim
# 安装ifconfig、netstat
root@mysql_master:~$ apt-get install net-tools

3.2，主服务器

开启二进制日志，并配置唯一的server-id

# 在[mysqld]下增加配置
root@mysql_master:/etc/mysql/mysql.conf.d# vi /etc/mysql/mysql.conf.d/mysqld.cnf 
...
...
# 开启二进制日志，可以写绝对路径，例如/var/log/mysql/master-bin
log-bin   = mysql-bin
# Mysql服务ID，必须保证唯一
server-id = 1

# 不进行主从复制的db
binlog-ignore-db = mysql
binlog-ignore-db = information_schema
# 进行主从复制的db
binlog-do-db = test

# 只保存最近7天的bin-log文件，避免文件过多
expire_logs_days=7

重启Mysql服务

root@mysql_master:/etc/mysql/mysql.conf.d# service mysql restart

创建一个用于slave和master通信的用户账号slave1

mysql> CREATE USER 'slave1'@'%' IDENTIFIED BY '123456';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave1'@'%';

创建测试账号和数据库

mysql> Grant all privileges on *.* to 'test'@'%' identified by '123456' with grant option;
mysql> Create database test;

查看master状态，获得master二进制日志文件名及位置

mysql> show master status;
+------------------+----------+--------------+--------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB         | Executed_Gtid_Set |
+------------------+----------+--------------+--------------------------+-------------------+
| mysql-bin.000005 |      955 | test         | mysql,information_schema |                   |
+------------------+----------+--------------+--------------------------+-------------------+
1 row in set (0.00 sec)

3.3，从服务器

配置唯一的server-id

# 在[mysqld]下增加配置
root@mysql_slave:/etc/mysql/mysql.conf.d# vi /etc/mysql/mysql.conf.d/mysqld.cnf 
...
...
# Mysql服务ID，必须保证唯一
server-id = 2

使用master分配的用户账号读取master二进制日志

mysql> CHANGE MASTER TO
MASTER_HOST='mysql_master',
MASTER_USER='slave1',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000005',
MASTER_LOG_POS=955;

启用slave服务，并查看状态

mysql> start slave;
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: mysql_master
                  Master_User: slave1
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 1424
               Relay_Log_File: mysql_slave-relay-bin.000004
                Relay_Log_Pos: 1637
        Relay_Master_Log_File: mysql-bin.000005
             Slave_IO_Running: Yes                                     # IO线程是启动状态，如果是No，可以查看下面日志，分析处理
            Slave_SQL_Running: Yes                                     # SQL线程是启动状态，如果是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: 1424
              Relay_Log_Space: 2016
              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: 1
                  Master_UUID: 0f5421ba-0ede-11e8-b442-0242ac12000a
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

4，测试

4.1，测试用例1

在master中，创建数据表，增加2行数据，观察slave数据变化是否一致（结果一致）

mysql> CREATE TABLE test.`student` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

mysql> Insert into test.student(name) values('aaa');
mysql> Insert into test.student(name) values('bbb');

mysql> Select * from test.student;

4.2，测试用例2

在master中，删除1行数据，更新1行数据，观察slave的数据变化（结果一致）

mysql> delete from test.student where id = 1;
mysql> update test.student set name = 'ccc' where id = 2;

4.3，测试用例3

在master中，删除1个数据表，观察slave的数据变化（结果一致）

mysql> drop table student;

4.4，测试用例4

在master中，删除数据库test，或创建数据库test，观察slave的数据变化（结果一致）

mysql> drop database test;
mysql> create database test;

5，高级应用

5.1，数据库复制

如果是新部署的MySQL主从服务器，这一步不需要。
如果Master之前有数据，需要把Master的数据导出并还原到Slave上，要保证Master和Slave服务器中的数据是一样的。
建议导出Master库的时候进行锁表，导完后再解锁。

锁表，并查看master的bin-log日志文件名称和位置

mysql> flush tables with read lock;
mysql> show master status;

全量导出Master数据库

root@mysql_master:/home# mysqldump -utest -p123456 test > test.sql

手动导入到Slave

root@mysql_slave:/home# mysql -utest -p123456 -e 'create database test;'
root@mysql_slave:/home# mysql -utest -p123456 -e 'show databases;'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
root@mysql_slave:/home# mysql -utest -p123456 test < test.sql

解锁master数据库

mysql> unlock tables;

5.2，MySQL主从一致性检查

写一个监控脚本，用来监控Slave中的两个"yes"，如果只有一个"yes"或者零个，就表明主从有问题。
通过Percona-Toolkit这个工具来实现的，Percona Toolkit是一组高级的命令行工具，用来管理MySQL和系统任务。

6，参考

官方文档
https://dev.mysql.com/doc/refman/5.7/en/sql-syntax-replication.html
MySQL主从复制(Master-Slave)实践
https://www.cnblogs.com/gl-developer/p/6170423.html
MySQL 主从复制详解（详细）
http://blog.csdn.net/u010098331/article/details/50828820
Mysql常用命令行大全
http://www.cnblogs.com/bluecobra/archive/2012/01/11/2318922.html