MySQL的主从复制

介绍

为什么要主从复制

• 做数据的热备

• 如果主数据库宕机，可以快速将业务系统切换到从数据库上，可避免数据丢失。

• 业务量越来越大，I/O访问频率过高，单机无法满足，此时做多库的存储，降低磁盘I/O访问的频率，提高单个机器的I/O性能。如果对数据库的读和写都在同一个数据库服务器中操作，业务系统性能会降低。

• 在业务复杂的系统中，有这么一个情景，有一句sql语句需要锁表，导致暂时不能使用读的服务，那么就很影响运行中的业务，使用主从复制，让主库负责写，从库负责读，这样，即使主库出现了锁表的情景，通过读从库也可以保证业务的正常运作。通过做主从复制（读写分离）来减轻主数据库的负载。

原理

• 步骤一：主库db的更新事件(update、insert、delete)被写到binlog
• 步骤二：从库发起连接，连接到主库
• 步骤三：此时主库创建一个binlog dump thread，把binlog的内容发送到从库
• 步骤四：从库启动之后，创建一个I/O线程，读取主库传过来的binlog内容并写入到relay log
• 步骤五：还会创建一个SQL线程，从relay log里面读取内容，从Exec_Master_Log_Pos位置开始执行读取到的更新事件，将更新内容写入到slave的db

注：上面的解释是解释每一步做了什么，整个mysql主从复制是异步的，不是按照上面的步骤执行的。

同步操作通过 3 个线程实现，其基本步骤如下：

主服务器将数据的更新记录到二进制日志中（记录被称作二进制日志事件）-- 主库线程；
从库将主库的二进制日志复制到本地的中继日志（relay log）-- 从库 I/O 线程；
从库读取中继日志中的事件，将其重放到数据中 -- 从库 SQL 线程。

主从复制

准备工作

• 主从数据库版本最好一致
• 主从数据库内数据保持一致
• 主数据库：10.168.1.248 从数据库：10.168.1.249

配置主库Master

修改MySQL配置

找到主数据库的配置文件my.cnf，我的在/etc/my.cnf

vi /etc/my.cnf

在[mysqld]部分插入

[mysqld]
log-bin=mysql-bin #开启二进制日志
server-id=1 #设置server-id，必须唯一

**配置说明 **

1. log-bin：设置二进制日志文件的基本名；
2. log-bin-index：设置二进制日志索引文件名；
3. binlog_format：控制二进制日志格式，进而控制了复制类型，三个可选值
4. ​ -STATEMENT：语句复制
5. ​ -ROW：行复制
6. ​ -MIXED：混和复制，默认选项
7. server-id：服务器设置唯一ID，默认为1，推荐取IP最后部分；
8. sync-binlog：默认为0，为保证不会丢失数据，需设置为1，用于强制每次提交事务时，同步二进制日志到磁盘上。

创建用户

重启服务，创建用于同步的用户

打开mysql会话shell

mysql -uroot -p

创建用户并授权：用户：repl密码：repl’

# 创建用户
create user 'repl'@'10.168.1.249' identified by 'repl';

# 授权，只授予复制和客户端访问权限
grant replication slave on *.* to 'repl'@'10.168.1.249';#分配权限

查看master状态

记录二进制文件名(mysql-bin.000001)和位置(154):

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |     1416 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

配置从库Slave

修改MySQL配置

找到主数据库的配置文件my.cnf，我的在/etc/my.cnf

vi /etc/my.cnf

在[mysqld]部分插入

[mysqld]
server-id=2 #设置server-id，必须唯一

执行同步SQL语句

重启mysql，打开mysql会话，执行同步SQL语句(需要主服务器主机名，登陆凭据，二进制文件的名称和位置)：

mysql> CHANGE MASTER TO
    -> MASTER_HOST='10.168.1.248',
    -> MASTER_USER='repl',
    -> MASTER_PASSWORD='repl',
    -> MASTER_LOG_FILE='mysql-bin.000001',
    -> MASTER_LOG_POS=1416;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

启动slave同步进程

mysql>start slave;

查看slave状态

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.168.1.248
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 1917
               Relay_Log_File: sl249-relay-bin.000003
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
          				......

当Slave_IO_Running和Slave_SQL_Running都为YES的时候就表示主从同步设置成功了。

验证

• 主服务器

  mysql> show databases;
  +--------------------+
  | Database           |
  +--------------------+
  | information_schema |
  | mysql              |
  | performance_schema |
  | sys                |
  +--------------------+
  4 rows in set (0.00 sec)
  
  mysql> create database mysql_test;
  Query OK, 1 row affected (0.00 sec)
  
  mysql> show databases;
  +--------------------+
  | Database           |
  +--------------------+
  | information_schema |
  | mysql              |
  | mysql_test         |
  | performance_schema |
  | sys                |
  +--------------------+
  5 rows in set (0.00 sec)
  

• 从服务器

  mysql> show databases;
  +--------------------+
  | Database           |
  +--------------------+
  | information_schema |
  | mysql              |
  | performance_schema |
  | sys                |
  +--------------------+
  4 rows in set (0.00 sec)
  
  mysql> show databases;
  +--------------------+
  | Database           |
  +--------------------+
  | information_schema |
  | mysql              |
  | mysql_test         |
  | performance_schema |
  | sys                |
  +--------------------+
  5 rows in set (0.00 sec)