<返回更多

如何基于 MySQL 主从模式搭建上万并发的系统架构？

2019-08-20    
加入收藏

作者：惨绿少年

出处：http://clsn.io

一、主从复制基础概念

在了解主从复制之前必须要了解的就是数据库的二进制日志(binlog),主从复制架构大多基于二进制日志进行。

1.1 二进制日志管理说明

二进制日志在哪？如何设置位置和命名？

在my.cnf文件中使用 log-bin = 指定；命名规则为 MySQL-bin.000000 （后为6位数字）

二进制日志位置：

mysql> show variables like '%log_bin%' ;
+---------------------------------+-----------------------------------------+
| Variable_name | Value |
+---------------------------------+-----------------------------------------+
| log_bin | ON |
| log_bin_basename | /Application/mysql/data/mysql-bin |
| log_bin_index | /application/mysql/data/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+-----------------------------------------+
6 rows in set (0.06 sec)

日志命名：

mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 2979 |
| mysql-bin.000002 | 120 |
+------------------+-----------+
2 rows in set (0.00 sec)

二进制日志记录什么？二进制日志中记录的是一个个完成的事件

二进制日志格式是怎样的？推荐使用row格式

查看当前使用的日志格式：

mysql> show variables like '%format%';
+--------------------------+-------------------+
| Variable_name | Value |
+--------------------------+-------------------+
| binlog_format | ROW |
| date_format | %Y-%m-%d |
| datetime_format | %Y-%m-%d %H:%i:%s |
| default_week_format | 0 |
| innodb_file_format | Antelope |
| innodb_file_format_check | ON |
| innodb_file_format_max | Antelope |
| time_format | %H:%i:%s |
+--------------------------+-------------------+
8 rows in set (0.00 sec)

二进制日志如何滚动？每次重启都会刷新日志，也可以通过命令进行刷新 reset master;

二进制日志用来干嘛？备份恢复，起始点的备份恢复

二进制日志的操作命令？查看都有哪些二进制日志

mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 2979 |
| mysql-bin.000002 | 167 |
| mysql-bin.000003 | 120 |
+------------------+-----------+
3 rows in set (0.00 sec)

查看当前使用的二进制日志文件：

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 120 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

binlog相关详情参照：

http://www.cnblogs.com/clsn/p/8087678.html#_label6

1.2 mysql传统备份方式和缺陷

  1. 二进制日志备份
  2. mysqldump
  3. 必须有数据库服务器完成逻辑工作，需要更多地cpu周期
  4. 逻辑备份还原速度慢：需要MySQL加载和解释语句、转化存储格式、重建引擎
  5. xtrabackup
  6. 文件大
  7. 不总是可以跨平台、操作系统和MySQL版本

1.3 MySQL主从复制能为我们做什么

二、MySQL主从复制介绍

2.1 复制技术

作用：

主从复制实现基本原理

2.2 复制架构

mysql复制的应用常见场景：

应用场景1：从服务器作为主服务器的实时数据备份

应用场景2：主从服务器实现读写分离，从服务器实现负载均衡

应用场景3：把多个从服务器根据业务重要性进行拆分访问

传统的 MySQL复制提供了一种简单的主–从复制方法，有一个主，以及一个或多个从。

主节点执行和提交事务，然后将它们（异步地）发送到从节点，以重新执行（在基于语句的复制中）或应用（在基于行的复制中）。

这是一个 shared-nothing 的系统，默认情况下所有 server 成员都有一个完整的数据副本。

 

如何基于 MySQL 主从模式搭建上万并发的系统架构？

（图）MySQL 异步复制

还有一个半同步复制，它在协议中添加了一个同步步骤。

这意味着主节点在提交时需要等待从节点确认它已经接收到事务。只有这样，主节点才能继续提交操作。

 

如何基于 MySQL 主从模式搭建上万并发的系统架构？

（图）MySQL 异步复制

在上面的两个图片中，可以看到传统异步 MySQL 复制协议（以及半同步）的图形展示。

蓝色箭头表示在不同 server 之间或者 server 与 client 应用之间的信息交互。

2.3 MySQL主从复制原理介绍

复制过程：

复制前提：

主从怎么实现的？

如何基于 MySQL 主从模式搭建上万并发的系统架构？

 

2.4 执行原理--第一次开启主从过程

到此为止，一次主从复制就完成

一旦主从运行起来：就不需要手工执行change master to，因为信息都会被存放到master.info（user、password、port、ip，上次获取过的binlog信息file和position）中。

详细的mysql replication 过程

 

如何基于 MySQL 主从模式搭建上万并发的系统架构？

 

三、 主从搭建配置

本次主从搭建使用mysql多实例进行实验。多实例配置参考文档进行配置：

http://www.cnblogs.com/clsn/p/8038964.html#_label8

3.1 多实例数据库slave配置

系统环境说明：

[root@db02 ~]# cat /etc/redhat-release
centos release 6.9 (Final)
[root@db02 ~]# uname -r
2.6.32-696.el6.x86_64
[root@db02 ~]# /etc/init.d/iptables status
iptables: Firewall is not running. # 注意：务必关闭防火墙（iptables selinux）
[root@db02 ~]# getenforce
Disabled
[root@db02 ~]# mysql --version
mysql Ver 14.14 Distrib 5.6.36, for Linux (x86_64) using EditLine wrapper

1、启动多实例数据库

[root@db02 ~]# /data/3306/mysql start
Starting MySQL...
[root@db02 ~]# /data/3307/mysql start
Starting MySQL...

2、配置文件说明：

master 配置文件说明：

[root@db02 ~]# cat /data/3306/my.cnf
[client]
port = 3306
socket = /data/3306/mysql.sock
[mysqld]
user = mysql
port = 3306
socket = /data/3306/mysql.sock
basedir = /application/mysql
datadir = /data/3306/data
log-bin = /data/3306/mysql-bin
server-id = 6 # server id 不能相同
skip_name_resolve = 0 # 跳过域名解析参数
[mysqld_safe]
log-error=/data/3306/mysql_3306.err
pid-file=/data/3306/mysqld.pid

slave 配置文件说明：

[root@db02 ~]# cat /data/3307/my.cnf
[client]
port = 3307
socket = /data/3307/mysql.sock
[mysqld]
user = mysql
port = 3307
socket = /data/3307/mysql.sock
basedir = /application/mysql
datadir = /data/3307/data
log-bin = /data/3307/mysql-bin
server-id = 7 # server id 不能相同
skip_name_resolve = 0 # 跳过域名解析参数
read_only = 1 # 从库只读 （非root用户 ）
[mysqld_safe]
log-error=/data/3307/mysql_3307.err
pid-file=/data/3307/mysqld.pid

3、在主库创建复制用户

登陆到主数据库中：

mysql -uroot -p123 -S /data/3306/mysql.sock

创建授权用户，注意是slave用户。

grant replication slave on *.* to repl@'10.0.0.%' identified by '123';

4、初始化从库数据

备份主库当前数据

mysqldump -uroot -p123 -A -B -F --master-data=2 -S /data/3306/mysql.sock >/tmp/full.sql

部分参数说明：

到从库进行恢复

mysql -uroot -p123 -S /data/3307/mysql.sock

恢复备份的数据

set sql_log_bin=0;
source /tmp/full.sql

5、开启从库复制

查看备份的当前使用的文件及POS号

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000012 | 120 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

登入数据库，进行slave配置。

mysql -uroot -p123 -S /data/3307/mysql.sock
CHANGE MASTER TO
MASTER_HOST='10.0.0.52',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000012',
MASTER_LOG_POS=120;
start slave; # 启动从库复制

该配置想关说明可以通过 help 获得。

mysql> help CHANGE MASTER TO
CHANGE MASTER TO
MASTER_HOST='master2.mycompany.com',
MASTER_USER='replication',
MASTER_PASSWORD='bigs3cret',
MASTER_PORT=3306,
MASTER_LOG_FILE='master2-bin.001',
MASTER_LOG_POS=4,
MASTER_CONNECT_RETRY=10;

3.2 测试主从同步

查看slave库的状态，主要查看：

Slave_IO_Running与Slave_SQL_Running是否都为Yes

主库进行操作，在从库验证

[root@db02 ~]# mysql -uroot -p123 -S /data/3306/mysql.sock
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
mysql> create database clsn;
Query OK, 1 row affected (0.00 sec)

在从库上可以看到该数据库已创建

[root@db02 ~]# mysql -uroot -p123 -S /data/3307/mysql.sock
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| clsn |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)

至此mysql主从复制就搭建完成

3.3 忘记数据库密码？

shell> /application/mysql/bin/mysqld_safe --defaults-file=/data/3306/my.cnf --skip-grant-tables --skip-networking &
mysql> update user set password=password('123') where user='root' and host='localhost';
mysql> flush privileges;

3.4 主从复制状态失败的原因？

Last_IO_Error: error reconnecting to master 'repl@10.0.0.52:3306' - retry-time: 60 retries: 1

原因：

四、MySQL主从复制常见问题

4.1 从库binlog落后主库binlog？

从库记录的已经主库已经给我传送的binlog事件的坐标，一般在繁忙的生产环境下会落后于主库

show master statusG --- 主
show slave status G --- 从
Master_Log_File: mysql-bin.000007
Read_Master_Log_Pos: 729

落后太远的原因：

4.2 主库update，从库迟迟的没有更新

特殊情况：日志已经传过来了，数据并没有同步

一般情况：

4.3 主从复制延时配置(从库配置)

停止从库复制

mysql>stop slave;
Query OK, 0 rows affected (0.01 sec)

修改延时参数，MASTER_DELAY，单位位S （秒）。

mysql>CHANGE MASTER TO MASTER_DELAY = 30;
Query OK, 0 rows affected (0.07 sec)

启动从库复制

mysql>start slave;
Query OK, 0 rows affected (0.07 sec)

查看配置是否生效

mysql> show slave status G
……
 SQL_Delay: 30

4.4 从库安全配置（其他用户只读）

修改my.cnf配置文件，添加只读参数

read_only = 1 ====> 控制普通用户
innodb_read_only = 1 ====> 控制root用户，正常情况不要加

添加完成后重启数据库

mysql> show variables like '%read_only%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| innodb_read_only | OFF |
| read_only | ON |
| tx_read_only | OFF |
+------------------+-------+
3 rows in set (0.00 sec)

延时从库: delay节点、延时节点

4.5 主从复制故障及解决（跳过错误）

命令行设置

stop slave; #<==临时停止同步开关。
set global sql_slave_skip_counter = 1 ; #<==将同步指针向下移动一个，如果多次不同步，可以重复操作。
start slave;

在配置文件修改，设置要跳过的pos

/etc/my.cnf
slave-skip-errors = 1032,1062,1007

在mysql中可以跳过某些错误,但是最好的解决办法，重新搭建主从复制。

4.6 延时节点概念 --> SQL线程延时？

Last_SQL_Errno: 0
Last_SQL_Error:

原因:

……

4.7 Slave_*_Running：？

4.8 中继日志坐标

Relay_Log_File 和 Relay_Log_Pos 列标识从属服务器中继日志中 SQL 线程已经执行的最近事件的坐标。

这些坐标对应于 Relay_Master_Log_File 和 Exec_Master_Log_Pos 列标识的主服务器二进制日志中的坐标。

如果 Relay_Master_Log_File 和 Exec_Master_Log_Pos 列的输出远远落后于 Master_Log_File 和Read_Master_Log_Pos 列（表示 I/O 线程的坐标）

这表示 SQL 线程（而不是 I/O 线程）中存在延迟。即，它表示复制日志事件快于执行这些事件。

4.9 单一主从需要改变的地方

从库的作用

1、相当于实时备份

2、使用从库备份

3、一主多从应对读多的业务需求

如果，从库只做备份服务器用，那么主库的压力会不减反增。因为，所有的业务都在主库实现，读和写，dump线程读取并投递binlog

解决方案：

声明：本站部分内容来自互联网,如有版权侵犯或其他问题请与我们联系，我们将立即删除或处理。
▍相关推荐
更多资讯 >>>
Copyright @ 简易百科 V 2.0 京ICP备14042104号