Mysql集群及读写分离

一、集群搭建之主从复制

（一）主从复制原理

　　这里需要了解两个文件，bin log和relay log（中继日志）文件。

　　bin log：bin log记录所有的数据更改操作，可用于本机数据恢复和主从数据同步，其刷入磁盘方式有三种：每秒一次将数据刷新入磁盘；每次事务提交将数据刷新入磁盘；每一秒一次 + 每次事务提交都会将数据刷入磁盘。

　　relay log：中继日志会进行回访，从而使从数据库达到与主数据库主从同步的目的。

　　主从复制的原理就是：

　　　　主服务器会将binlog写入本地，从数据库定时请求增量binlog，然后主节点将binlog同步到从节点；

　　　　从节点单独的线程将binlog 复制到从节点的 relaylog中

　　　　从节点定时重放relaylog

　　上面说到，主服务器会将数据变更刷入磁盘，那么刷新类型有三种模式。

　　binlog的三种模式：

模式	说明	优点	缺点
statement level	每一条修改数据的sql都会被记录到master节点的bin-log中，slave在复制的时候，会将该sql重新执行一遍	其只需要关心在master节点上执行的sql及语句执行的上下文信息，不需要记录每一行数据的变化，减少了bin-log的数据量，节约io，提高性能，	由于该种模式记录的是执行sql，为了能让sql在slave上也能正常的执行，其需要记录sql执行的上下文信息，但是由于mysql发展很快，在执行一些特定函数的时候，已经发现不少会造成主从复制问题的情况，例如sleep()函数就不能正常复制
row level	日志中会记录成每一条数据被修改的形式，slave直接将数据进行修改	不需要记录修改数据的上下文信息，仅仅记录了被修改成什么样子，因此不存在无法被正确的主从复制的情况	由于需要记录每一条数据变更的数据，因此bin-log的日志量会很大，会增加io
mixed	就是上面两种情况的结合体，在该种模式下，会根据sql语句的具体内容来选择使用statement level还是选择row level

　　查看binlog日志模式

mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.00 sec)

　　调整binlog日志模式

mysql> set binlog_format=statement;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'binlog_format';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.00 sec)

　　查看bin log和relay log日志

　　首先查看binlog是否开启

mysql> show variables like 'log%';
+----------------------------------------+---------------------+
| Variable_name                          | Value               |
+----------------------------------------+---------------------+
| log_bin                                | OFF                 |
| log_bin_basename                       |                     |
| log_bin_index                          |                     |
| log_bin_trust_function_creators        | OFF                 |
| log_bin_use_v1_row_events              | OFF                 |
| log_builtin_as_identified_by_password  | OFF                 |
| log_error                              | /var/log/mysqld.log |
| log_error_verbosity                    | 3                   |
| log_output                             | FILE                |
| log_queries_not_using_indexes          | OFF                 |
| log_slave_updates                      | OFF                 |
| log_slow_admin_statements              | OFF                 |
| log_slow_slave_statements              | OFF                 |
| log_statements_unsafe_for_binlog       | ON                  |
| log_syslog                             | OFF                 |
| log_syslog_facility                    | daemon              |
| log_syslog_include_pid                 | ON                  |
| log_syslog_tag                         |                     |
| log_throttle_queries_not_using_indexes | 0                   |
| log_timestamps                         | UTC                 |
| log_warnings                           | 2                   |
+----------------------------------------+---------------------+

　　可以看到binlog是关闭的（log_bin的值为OFF），需要开启，只需要在/etc/my.cnf文件中增加以下内容，然后重启mysql服务即可。

log-bin=mysql-bin
server-id=1
binlog_format=ROW

　　如果在/etc/my.cnf中没有设置binlog的位置，则默认在/var/lib/mysql文件夹中，会生成mysql-bin.0000*的文件，由于该文件是个二进制文件，没办法直接查看，这里可以使用mysql自带的mysqlbinlog工具进行解码，将该二进制文件转为可阅读的sql语句。

mysqlbinlog --base64-output=decode-rows -v -v mysql-bin.000001 > binlog

　　可以发现已经多了一个binlog文件，然后，可以使用vim直接查看

（二）基于binlog主从复制

　　1、主从服务器都先关闭防火墙：

#关闭防火墙
systemctl stop firewalld
#设置开机不启动
systemctl disable firewalld.service

　　2、主服务器配置

　　（1）然后需要保证主服务器binlog是开启状态，如果未开启，需要修改my.cnf配置文件进行开启，同时，主从的service-id不能一致

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid


# MySQL设置大小写不敏感：默认：区分表名的大小写，不区分列名的大小写 # 0：大小写敏感 1>：大小写不敏感
lower_case_table_names=1
# 默认字符集
character-set-server=utf8

log-bin=mysql-bin
server-id=104

　　（2）修改my.cnf配置文件需要重启服务。

　　（3）给从服务器授权备份权限

　　进入mysql后，可以使用如下命令进行授权操作 grant replication slave on *.* to '从服务器用户名'@'从服务器IP' identified by '从服务器密码'; 其中 *.* 表示授权所有库的所有表。

grant replication slave on *.* to 'root'@'192.168.1.106' identified by 'root';

　　如果在执行语句过程中，提示密码策略不符合要求，可以先对密码策略做修改，修改密码策略详见 https://www.cnblogs.com/liconglong/p/14437439.html 中第二点（安装）中第5点（修改初始密码）

　　上述命令样例中，可以将从服务器的ip替换为%，表示不限制ip，只要用户名密码正确，就可以备份。

grant replication slave on *.* to 'root'@'%' identified by 'root';

　　（4）刷新权限

flush privileges;

　　（5）查看主服务器状态

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

　　3、从服务器配置

　　（1）修改从服务器配置

　　修改从服务的配置文件my.cnf，设置service-id，要与主服务器区别开

　　（2）删除错误的UUID

rm -f /var/lib/mysql/auto.cnf

　　（3）重启服务器

　　（4）登录到mysql中，对从服务器进行配置

　　需要配置从服务器的host、user、password等内容；

　　其中主服务器的binlog文件名称，是在查看主服务器状态时的File的值；

　　master_log_pos则是对应主服务器的positions；

　　MASTER_AUTO_POSITION大小写敏感，必须为大写。

change master to
master_host='192.168.1.104',
master_port=3306,
master_user='root',
master_password='root',
master_log_file='mysql-bin.000001',
master_log_pos=884,
MASTER_AUTO_POSITION=0;

　　（5）启动从服务器

mysql> start slave;

　　（6）查看从服务状态

mysql> show slave status \G;
*************************** 1. row ***************************.............
                  Master_Host: 192.168.1.104
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 884
               Relay_Log_File: bogon-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
..............

　　其中Slave_IO_Running和Slave_SQL_Running必须为YES，否则的话就是没有配置成功。

　　（7）测试

　　最后就是测试了，在主服务器上创建库、表即增删改数据，都会同步到从数据库上。

（三）主从同步延迟的原因及解决办法

　　1、原因

　　主从同步时，主服务器会把更新语句写入binlog，从服务器的IO线程（5.6.3之前的IO只有一个线程，5.6.3之后开始使用多线程，因此在5.6.3之后速度变快）会去读主服务器的binlog并且写入从服务器的relaylog，然后从服务器的sql线程会一个一个的执行relaylog中的sql，进行数据恢复。

　　但是，主从复制的集群，只有主服务器对外提供服务，从服务器只作为备份使用，同时主服务器可能有多个客户端同时进行并发操作，但是从服务器读取binlog的线程只有一个，当某个sql执行时间较长，或者由于某个sql要进行锁表，就会导致主服务器的sql大量积压，从而未被同步到从服务器中，这也就是所谓的主从同步延迟。

　　2、解决方案

　　对于该种延迟的解决方法，并没有很确切的解决方案，但是我们可以提供一些环节措施。

　　（1）调整binlog参数

　　由于主服务器对安全性要求比较高，所以会设置一些参数，例如设置同步存储binlog、每次提交时更新binlog等（sync_binlog=1、innodb_flush_log_at_trx_commit=1），而从服务器不需要那么高的数据安全，完全可以异步更新binlog（sync_binlog=0）或者关闭binlog。innodb_flushlog、innodb_flush_log_at_trx_commit也可以设置为0来提高sql的执行效率（这个能很大程度的提高效率）；另外一个就是可以使用比主库更好的硬件设备来作为从库。

　　（2）从库只作为数据备份

　　由于从库还有可能提供数据查询功能（读写分离），这样从库除了要获取主库的binlog之外，还要对外提供查询，可以将从库只作为数据备份库，不提供查询，从而降低从库的负载，从而提高效率。

　　（3）增加从服务器。

　　3、判断主从同步延迟

　　查看从服务器的状态（show slave status \G;），在输出结果中查看Seconds_Behind_Master的值，如果为0，表明主从复制状态正常，如果为NULL，说明主从同步发生了延迟或故障。

二、集群搭建之读写分离

　　主从复制只会保证主服务器对外提供服务，而从服务器不对外提供服务，只作为数据备份使用。

　　读写分离，主服务器提供读写服务，从数据库提供读服务。

　　其实也可以两个数据库互为主从，那么两个服务器就会同时对外提供读写服务。

　　数据库读写分离可以使用Mysql-proxy或Mysql-Mouter实现

　　上面已经使用192.168.1.104和192.168.1.106做了主从复制，在该基础上继续做读写分离

（一）Mysql-Proxy

　　1、下载&解压

　　在一台新的服务器上下载Mysql-Proxy并解压

 wget https://cdn.mysql.com/archives/mysql-proxy/mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
tar -zxvf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
mv mysql-proxy-0.8.5-linux-el6-x86-64bit mysql-proxy

　　2、配置Mysql-Proxy配置文件

　　创建mysql-proxy.cnf

vi mysql-proxy.cnf

　　内容

[mysql-proxy]
user=root
admin-username=root
admin-password=root
proxy-address=0.0.0.0:3306
proxy-backend-addresses=192.168.1.104:3306
proxy-read-only-backend-addresses=192.168.1.106:3306
proxy-lua-script=./share/doc/mysql-proxy/rw-splitting.lua
log-file=./mysql-proxy.log
log-level=debug
keepalive=true
daemon=true

　　其中proxy-address为可以设置监控的ip和端口，proxy-backend-addresses为主服务器地址（提供读写服务），proxy-read-only-backend-addresses为从服务地址（提供写服务）

　　修改配置文件权限

chmod 660 mysql-proxy.cnf

　　修改rw-splitting.lua脚本，位置在mysql-proxy/share/doc/mysql-proxy下，将最小连接和最大连接都改为1，方便模拟测试。

if not proxy.global.config.rwsplit then
        proxy.global.config.rwsplit = {
                min_idle_connections = 1,#默认超过4个连接数时，才开始读写分离，改为1 测试需要
                max_idle_connections = 1,#默认8，改为1测试需要
                is_debug = false
        }
end

　　3、启动

./bin/mysql-proxy --defaults-file=mysql-proxy.cnf > mysql-proxy.out 2>&1 &

　　说明：Mysql-proxy虽然可以实现读写分离，但是Mysql官方并没有推出稳定版本，因此不推荐在生产中使用，在生产中使用推荐Mysql-Mouter。

（二）Mysql-Mouter

　　Mysql Router2.0是初始版本，目前已经废弃，2.1版本为支持Mysql InnoDB Cluster而引入，从2.1.5版本之后，就废弃了2.1.x的版本代号，转而使用8.0.x版本号，与mysql版本一致。

　　1、下载&解压

wget https://dev.mysql.com/get/Downloads/MySQL-Router/mysql-router-8.0.23-linux-glibc2.12-x86_64.tar.xz
tar -xvJf mysql-router-8.0.23-linux-glibc2.12-x86_64.tar.xz
mv mysql-router-8.0.23-linux-glibc2.12-x86_64 mysql-router

　　2、配置mysqlrouter.cnf文件

vi mysqlrouter.cnf

　　内容

[logger]
level = INFO

[routing:secondary]
bind_address = localhost
bind_port = 7001
destinations = 192.168.1.104:3306,192.168.1.106:3306
routing_strategy = round-robin

[routing:primary]
bind_address = localhost
bind_port = 7002
destinations = 192.168.1.134:3306,192.168.1.106:3306
routing_strategy = first-available

　　配置文件中配置了两个两个路由策略，一个是开放7001端口，通过循环的使用配置的ip，一个通过7002端口，只使用第一个ip

　　3、启动mysqlrouter

./bin/mysqlrouter -c mysqlrouter.conf &

　　4、测试

　　修改主从服务器的hostname，以便可以区分连的是哪一台数据库，修改后重启服务器。

vi /etc/hostname

　　使用7001端口可以发现，是轮询使用。

[root@bogon mysql-router]# mysql -uroot -proot -P7001 --protocol=tcp -e"select @@hostname"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| mysql-106  |
+------------+
[root@bogon mysql-router]# mysql -uroot -proot -P7001 --protocol=tcp -e"select @@hostname"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| mysql-104  |
+------------+
[root@bogon mysql-router]# mysql -uroot -proot -P7001 --protocol=tcp -e"select @@hostname"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| mysql-106  |
+------------+
[root@bogon mysql-router]# mysql -uroot -proot -P7001 --protocol=tcp -e"select @@hostname"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| mysql-104  |
+------------+

　　使用7002端口，可以发现，只使用了主服务器

[root@bogon mysql-router]# mysql -uroot -proot -P7002 --protocol=tcp -e"select @@hostname"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| mysql-106  |
+------------+
[root@bogon mysql-router]# mysql -uroot -proot -P7002 --protocol=tcp -e"select @@hostname"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| mysql-106  |
+------------+
[root@bogon mysql-router]# mysql -uroot -proot -P7002 --protocol=tcp -e"select @@hostname"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| mysql-106  |
+------------+

　　根据以上可以看到，主要是使用了routing_strategy来配置了轮询方案，对于轮询方案，有以下四种

　　round-robin：轮询，以实现负载均衡

　　roud-robin-with-fallback：用于InnoDB Cluster，每个新的连接都循环连接到下一个可用的SECONDARY服务器，如果SECONDARY服务器不可用，则以循环方式使用PRIMIARY服务器

　　first-available：使用第一个可用的服务器

　　next-valiable：使用第一个可用的服务器。与first-avaliable不同的是，如果一个服务器被标记为不可用，那么该服务器江湖被丢弃，并且永远不会再次用作目标。但是router重启，被丢弃的服务器将可再次使用。

三、基于主从复制的高可用方案

　　这种方案主要是双节点主从 + keepalived/heartbeat方案，一般来说，中小型规模的时候，采用这种架构是最省事的。两个节点可以采用简单的一主一从模式，或者双主模式，并且放置于同一个VLAN中，在master节点发生故障后，利用keepalived/heartbeat的高可用机制实现快速切换到slave节点。

　　这里主要说明一点，把两个节点的auto_increment_increment（自增起始值）和auto_increment_offset（自增步长）设成不同值。其目的是为了避免master节点意外宕机时，可能会有部分binlog未能及时复制到slave上被应用，从而会导致slave新写入数据的自增值和原先master上冲突了，因此一开始就使其错开；当然了，如果有合适的容错机制能解决主从自增ID冲突的话，也可以不这么做。

posted @ 2021-02-28 01:31 李聪龙阅读(817) 评论(0) 编辑收藏举报

刷新页面返回顶部

朦胧的夜~