MySQL 主从配置优化

主从复制

为什么要做主从

• 做数据的热备
• 如果主数据库宕机，可以选择将业务切到从数据库上，可避免数据丢失。
• 业务量越来越大，I/O 访问频率过高，单机无法满足，此时做多库的存储，降低磁盘 I/O 访问的频率，提高单个机器的 I/O 性能。如果对数据库的读和写都在同一个数据库服务器中操作，业务系统性能会降低。
• 在业务复杂的系统中，有这么一个场景，有一句SQL语句需要锁表，导致暂时不能使用读的服务，那么就很影响运行中的业务，使用主从复制，让主库负责写，从库负责读，这样，即使主库出现锁表场景，通过读从库也可以保证业务的正常运作。通过做主从复制（读写分离） 来减轻主数据库的负载

原理

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

准备工作

• 主从数据库版本一致
• 主从数据库内数据一致

配置主库

修改 MYSQL 配置

找到主数据库配置文件 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 -proot

创建用户并授权 ： 用户： yun 密码 : yun123456

# 创建用户
create user 'yun'@'SlaveIp' identified by 'yun123456';

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

查看master状态

记录二进制文件名（mysql-bin.000001）和 位置（154） ：

mysql> SHOW MASTER STATUS;

配置从库 Slave

修改 MySQL 配置

找到主数据库的配置文件 my.cnf

在 [mysqld]部分插入

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

执行同步 SQL

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

mysql> CHANGE MASTER TO
	-> MASTER_HOST = 'MasterIp',
	-> NASTER_USER = 'yun',
	-> MASTER_PASSWORD = 'yun123456',
	-> MASTER_LOG_FILE = 'mysql-bin.000001',
	-> MASTER_LOG_POS = 1416;

启动 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)