MySQL主从复制基础实践

23uyut

MySQL主从复制应用场景:

在实际应用场景中，MySQL复制90%以上都是一个Master复制到一个或者多个Slave的架构模式，主要用于读压力比较大的应用的数据库端廉价扩展解决方案。因为只要Master和Slave的压力不是太大（尤其是Slave端压力）的话，异步复制的延时一般都很少很少。尤其是自从Slave端的复制方式改成两个线程处理之后，更是减小了Slave端的延时问题。而带来的效益是，对于数据实时性要求不是特别Critical的应用，只需要通过廉价的pcserver来扩展Slave的数量，将读压力分散到多台Slave的机器上面，即可通过分散单台数据库服务器的读压力来解决数据库端的读性能瓶颈，毕竟在大多数数据库应用系统中的读压力还是要比写压力大很多。这在很大程度上解决了目前很多中小型网站的数据库压力瓶颈问题，甚至有些大型网站也在使用类似方案解决数据库瓶颈。
MySQL复制过程

    SLAVE:
        IO thread：向主服务器请求二进制日志中的事件
        SQL thread：从中继日志中读取事件并在本地执行         
    MASTER:
        binlog dump: 将IO thread请求的事件发送给对方

    下图描述了复制过程

    该过程的第一部分就是master记录二进制日志。在每个事务更新数据完成之前，master在二日志记录这些改变。MySQL将事务串行的写入二进制日志，即使事务中的语句都是交叉执行的。在事件写入二进制日志完成后，master通知存储引擎提交事务。
    下一步就是slave将master的binary log拷贝到它自己的中继日志。首先，slave开始一个工作线程——I/O线程。I/O线程在master上打开一个普通的连接，然后开始binlog dump process。Binlog dump process从master的二进制日志中读取事件，如果已经跟上master，它会睡眠并等待master产生新的事件。I/O线程将这些事件写入中继日志。
    SQL slave thread（SQL从线程）处理该过程的最后一步。SQL线程从中继日志读取事件，并重放其中的事件而更新slave的数据，使其与master中的数据一致。只要该线程与I/O线程保持一致，中继日志通常会位于OS的缓存中，所以中继日志的开销很小。
    此外，在master中也有一个工作线程：和其它MySQL的连接一样，slave在master中打开一个连接也会使得master开始一个线程。复制过程有一个很重要的限制——复制在slave上是串行化的，也就是说master上的并行更新操作不能在slave上并行操作。
    示例：主从复制的配置

    版本
        1、双方的mysql版本要一致：
        2、如果不一致，主的要低于从的；

    从哪开始复制：
        1、都从0开始；
        2、主服务器已经运行一段时间，并且存在不小的数据集；
            把主服务器备份，然后在从服务器恢复，从主服务器上备份时所处的位置开始复制；

    配置过程：

    主服务器：
        1、改server-id
        2、启用二进制日志
        3、创建有复制权限的账号
    从服务器：
        1、改server-id
        2、启用中继日志
        3、连接主服务器
        4、启动复制线程
    连接主服务器的命令：
        CHANGE MASTER TO
            MASTER_HOST = '',   #指定主服务器ip地址
            MASTER_PORT='',     #指定主服务器mysql端口
            MASTER_USER= '',    #指定主服务器上授权的用户名
            MASTER_PASSWORD='', #授权用户的密码
            MASTER_LOG_FILE='', #指定从主服务器的哪一个二进制开始复制，不指定从默认为位置
            MASTER_LOG_POS=;    #指定从主服务器二进制日志的那个点开始复制

主机地址规划:

MASTER:192.168.2.197
SLAVE：192.168.2.111
两台服务器部署MySQL（本例采用MariaDB-10）

    两台服务器安装方式相同这里只演示master端安装

1.创建mysql用户及mysql组

[iyunv@node1-master ~]# groupadd -g 301 -r mysql
[iyunv@node1-master ~]# useradd -r -g mysql -u 301 -s /sbin/nologin mysql

2.安装MariaDB

[iyunv@node1-master ~]# tar xf mariadb-10.0.24-linux-x86_64.tar.gz -C /usr/local/    #解压至/usr/local/目录下
[iyunv@node1-master ~]# cd /usr/local/
[iyunv@node1-master local]# mv mariadb-10.0.24-linux-x86_64 mysql     #重命名为mysql
[iyunv@node1-master local]# cd mysql/
[iyunv@node1-master mysql]# ll
total 208
-rw-r--r--  1 wangenzhi wangenzhi 17987 Feb 18 04:59 COPYING
-rw-r--r--  1 wangenzhi wangenzhi 26545 Feb 18 04:59 COPYING.LESSER
-rw-r--r--  1 wangenzhi wangenzhi  1980 Feb 18 04:59 CREDITS
-rw-r--r--  1 wangenzhi wangenzhi  8245 Feb 18 04:59 EXCEPTIONS-CLIENT
-rw-r--r--  1 wangenzhi wangenzhi  8694 Feb 18 04:59 INSTALL-BINARY
-rw-r--r--  1 wangenzhi wangenzhi 90917 Feb 18 04:59 README
drwxr-xr-x  2 root  root   4096 May 17 10:31 bin
drwxr-xr-x  3 root  root   4096 May 17 10:31 data
drwxr-xr-x  3 root  root   4096 May 17 10:30 include
drwxr-xr-x  3 root  root   4096 May 17 10:30 lib
drwxr-xr-x  4 root  root   4096 May 17 10:31 man
drwxr-xr-x 11 root  root   4096 May 17 10:30 mysql-test
drwxr-xr-x  2 root  root   4096 May 17 10:31 scripts
drwxr-xr-x 28 root  root   4096 May 17 10:30 share
drwxr-xr-x  4 root  root   4096 May 17 10:31 sql-bench
drwxr-xr-x  3 root  root   4096 May 17 10:31 support-files
[iyunv@node1-master mysql]# chown -R root.mysql ./*  #将mysql下所有文件权限修改为属主root，属组mysql
[iyunv@node1-master mysql]# ll
total 208
-rw-r--r--  1 root mysql 17987 Feb 18 04:59 COPYING
-rw-r--r--  1 root mysql 26545 Feb 18 04:59 COPYING.LESSER
-rw-r--r--  1 root mysql  1980 Feb 18 04:59 CREDITS
-rw-r--r--  1 root mysql  8245 Feb 18 04:59 EXCEPTIONS-CLIENT
-rw-r--r--  1 root mysql  8694 Feb 18 04:59 INSTALL-BINARY
-rw-r--r--  1 root mysql 90917 Feb 18 04:59 README
drwxr-xr-x  2 root mysql  4096 May 17 10:31 bin
drwxr-xr-x  3 root mysql  4096 May 17 10:31 data
drwxr-xr-x  3 root mysql  4096 May 17 10:30 include
drwxr-xr-x  3 root mysql  4096 May 17 10:30 lib
drwxr-xr-x  4 root mysql  4096 May 17 10:31 man
drwxr-xr-x 11 root mysql  4096 May 17 10:30 mysql-test
drwxr-xr-x  2 root mysql  4096 May 17 10:31 scripts
drwxr-xr-x 28 root mysql  4096 May 17 10:30 share
drwxr-xr-x  4 root mysql  4096 May 17 10:31 sql-bench
drwxr-xr-x  3 root mysql  4096 May 17 10:31 support-files
[iyunv@node1-master mysql]# cp support-files/my-large.cnf /etc/my.cnf #复制配置文件模板
cp: overwrite `/etc/my.cnf'? y
[iyunv@node1-master mysql]# cp support-files/mysql.server /etc/init.d/mysqld #复制mysql启动脚本
[iyunv@node1-master mysql]# chmod +x /etc/init.d/mysqld
[iyunv@node1-master mysql]# chkconfig --add mysqld  #添加为系统服务
[iyunv@node1-master mysql]# chkconfig mysqld on
[iyunv@node1-master mysql]# mkdir -pv  /mydata/{data,binlogs} #创建用于存放数据文件的目录和存放二进制日志文件的目录，生产环境中应当放置于不同的磁盘中避免磁盘故障导致数据全部丢失
mkdir: created directory `/mydata'
mkdir: created directory `/mydata/data'
mkdir: created directory `/mydata/binlogs'
[iyunv@node1-master mysql]# chown -R mysql.mysql /mydata/data/
[iyunv@node1-master mysql]# chown -R mysql.mysql /mydata/binlogs/

3.修改mysql配置文件

[iyunv@node1-master mysql]# vi /etc/my.cnf
[mysqld]
datadir = /mydata/data  #添加数据目录的位置
log-bin=/mydata/binlogs/master-bin #修改二进制日志的存放位置及名称
保存退出

4.初始化mysql

[iyunv@node1-master mysql]# ./scripts/mysql_install_db --user=mysql --datadir=/mydata/data/

--user= 指定mysql用户
--datadir= 指定存放mysql数据的目录

[iyunv@node1-master mysql]# ls /mydata/data/ #初始化成功会在/mydata/data目录下生成下面文件
aria_log.00000001  aria_log_control  ib_logfile0  ib_logfile1  ibdata1  mysql  performance_schema  test

5.启动mysql服务

[iyunv@node1-master mysql]# /etc/init.d/mysqld start
Starting MySQL. SUCCESS!
[iyunv@node1-master mysql]# ss -tnl|grep 3306
LISTEN 0  150  :::3306:::*

6.为了方便使用命令配置一下环境变量

[iyunv@node1-master mysql]# vi /etc/profile.d/mysql.sh
export PATH=/usr/local/mysql/bin:$PATH
[iyunv@node1-master mysql]# . /etc/profile.d/mysql.sh
[iyunv@node1-master mysql]# ln -sv /usr/local/mysql/include/ /usr/include/mysql
`/usr/include/mysql' -> `/usr/local/mysql/include/'
[iyunv@node1-master mysql]# vi /etc/ld.so.conf.d/mysql.conf
/usr/local/mysql/lib
[iyunv@node1-master mysql]# ldconfig

注意以上安装两台服务器完全相同操作，下面配置复制过程需单独操作
7.在MASTER服务器授权复制用户

MariaDB [(none)]> grant replication slave,replication client on *.* to 'repluser'@'192.168.2.111' identified by 'replpass';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> \q
Bye

8.在SLAVE服务器上修改配置文件

[iyunv@node2-slave mysql]# vi /etc/my.cnf
datadir = /mydata/data  #在[mysqld]标签下添加datadir指定目录位置
server-id   = 11     #修改server-id 不能与MASTER相同

# Replication Slave (comment out master section to use this)
#
# To configure this host as a replication slave, you can choose between
# Replication Master Server (default)
# binary logging is required for replication
#log-bin=mysql-bin  #SLAVE服务器不需要记录二进制日志将其注释

# binary logging format - mixed recommended
#binlog_format=mixed    #注释掉

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id   = 11     #修改为11
relay-log = /mydata/binlogs/relay-bin #添加中继日志存放位置

9.启动SLAVE服务器上的mysql

[iyunv@node2-slave mysql]# /etc/init.d/mysqld start
Starting MySQL. SUCCESS!
[iyunv@node2-slave mysql]# ss -tnl|grep 3306
LISTEN 0  150  :::3306:::*

10.配置SLAVE连接MASTER

MariaDB [(none)]> change master to
-> master_host='192.168.2.197', #指定master服务器地址
-> master_user='repluser',      #master创建的授权用户
-> master_password='replpass',  #授权用户的密码
-> master_log_file='master-bin.000003', #指定从哪个二进制日志开始复制，在MASTER端执行show master status;查看
-> master_log_pos=657;  #指定从master二进制日志中的哪个pos位置开始，在MASTER端执行show master status;查看
Query OK, 0 rows affected (0.09 sec)

MariaDB [(none)]> show slave status\G  #查看SLAVE端的状态
*************************** 1. row ***************************
   Slave_IO_State:
  Master_Host: 192.168.2.197
  Master_User: repluser
  Master_Port: 3306
Connect_Retry: 60
  Master_Log_File: master-bin.000003 #master端的二进制日志名称
  Read_Master_Log_Pos: 657  #读到哪个位置
   Relay_Log_File: relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: master-bin.000003
Slave_IO_Running: No  #从服务器上的IO复制线程在没启动时没有运行
Slave_SQL_Running: No  #从服务器上的SQL线程也是没有运行

11.启动SLAVE复制线程

MariaDB [(none)]> start slave; #启动SLAVE复制线程命令
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> show slave status\G  #查看当前状态
*************************** 1. row ***************************
   Slave_IO_State: Waiting for master to send event
  Master_Host: 192.168.2.197
  Master_User: repluser
  Master_Port: 3306
Connect_Retry: 60
  Master_Log_File: master-bin.000003
  Read_Master_Log_Pos: 657
   Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 536
Relay_Master_Log_File: master-bin.000003
Slave_IO_Running: Yes  #线程为启动模式
Slave_SQL_Running: Yes  #线程为启动模式

12.验证主从复制

    在MASTER端创建一个数据库

MariaDB [(none)]> create database mydb; #创建mydb数据库
Query OK, 1 row affected (0.03 sec)

MariaDB [(none)]> show master status;  #POS值为778
+-------------------+----------+--------------+------------------+
| File  | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000003 |  778 |  |  |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

    在SLAVE端查看同步状态

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
   Slave_IO_State: Waiting for master to send event
  Master_Host: 192.168.2.197
  Master_User: repluser
  Master_Port: 3306
Connect_Retry: 60
  Master_Log_File: master-bin.000003
  Read_Master_Log_Pos: 778  #从服务器读到的值也为778，说明复制没有问题
   Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 657
Relay_Master_Log_File: master-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
MariaDB [(none)]> show databases; #通过show命令查看mydb数据库也存在
+--------------------+
| Database   |
+--------------------+
| information_schema |
| mydb   |
| mysql  |
| performance_schema |
| test   |
+--------------------+
5 rows in set (0.03 sec)

小结:

配置主从复制的步骤：
        主服务器：
            1、改server-id
            2、启用二进制日志
            3、创建有复制权限的账号
    从服务器：
        1、改server-id
        2、启用中继日志
        3、连接主服务器
        4、启动复制线程
    连接主服务器的命令：
        CHANGE MASTER TO
            MASTER_HOST = '',   #指定主服务器ip地址
            MASTER_PORT='',     #指定主服务器mysql端口
            MASTER_USER= '',    #指定主服务器上授权的用户名
            MASTER_PASSWORD='', #授权用户的密码
            MASTER_LOG_FILE='', #指定从主服务器的哪一个二进制开始复制，不指定从默认为位置
            MASTER_LOG_POS=;    #指定从主服务器二进制日志的那个点开始复制

本人作为刚入行的新手觉得配置一个简单的主从复制比较简单，但往往除了问题能够快速定位并解决问题再是我们学习的关键。写得有错误的地方希望大家帮忙纠正欢迎大家加我QQ一起讨论学习，QQ:510749025