当单台MYSQL服务器无法满足当前网站流量时的优化方案。需要搭建mysql集群技术。
1、复制功能:
数据分布
负载均衡(读)
备份
高可用和故障切换
MySQL升级测试
2、复制方式:
主–从复制
主–主复制
半同步复制
3、复制原理:
Mysql中有一种日志叫做bin日志(二进制日志)。这个日志会记录下所有修改了数据库的SQL语句(insert,update,delete,ALTER TABLE,grant等等)。
主从复制的原理其实就是把主服务器上的BIN日志复制到从服务器上执行一遍,这样从服务器上的数据就和主服务器上的数据相同了。
4、复制流程图:
5、复制过程:
主节点必须启用二进制日志,记录任何修改数据库数据的事件。
从节点开启一个线程(I/O Thread)把自己扮演成mysql的客户端,通过mysql协议,请求主节点的二进制日志文件中的事件
主节点启动一个线程(dump Thread),检查自己二进制日志中的事件,跟对方请求的位置对比,如果不带请求位置参数,则主节点就会从第一个日志文件中的第一个事件一个一个发送给从节点。
从节点接收到主节点发送过来的数据把它放置到中继日志(Relay log)文件中。并记录该次请求到主节点的具哪个二进制日志文件的哪个位置。
从节点启动另外一个线程(sql Thread ),把replaylog中的事件读取出来,并在本地再执行一次。
6、复制中线程的作用:
从节点:
I/O Thread:从Master请求二进制日志事件,并保存于中继日志中。
Sql Thread:从中继日志中读取日志事件,在本地完成重放。
主节点:
Dump Thread:为每个Slave的I/O Thread启动一个dump线程,用于向从节点发送二进制事件。
思考
:从节点需要建立二进制日志文件吗?
看情况,如果从节点需要作为其他节点的主节点时,是需要开启二进制日志文件的。这种情况叫做级联复制。如果只是作为从节点,则不需要创建二进制文件。
Mysql复制特点:
异步复制:主节点中一个用户请求一个写操作时,主接点不需要把写的数据在本地操作完成同时发送给从服务器并等待从服务器反馈写入完成,在响应用户,主机点只需要把写入操作在本地完成,就响应用户。但是,从节点中的数据有可能会落后主服务,可以使用(很多软件来检查是否落后)
主从数据不一致。
7、主从复制配置过程:
主节点:
启用二进制日志。
为当前节点设置一个全局唯一的server_id。
创建有复制权限的用户账号 REPLIACTION SLAVE ,REPLIATION CLIENT。
从节点:
启动中继日志。
为当前节点设置一个全局唯一的server_id。
使用有复制权限的用户账号连接至主节点,并启动复制线程。
环境:
server1:主服务器
server2:从服务器
操作演示:
一、主从复制
1、安装 mysql
mysql-community-client-5.7.17-1.el6.x86_64.rpm
mysql-community-common-5.7.17-1.el6.x86_64.rpm
mysql-community-libs-5.7.17-1.el6.x86_64.rpm
mysql-community-libs-compat-5.7.17-1.el6.x86_64.rpm
mysql-community-server-5.7.17-1.el6.x86_64.rpm
2、修改mysql的配置文件
server1:
[root@server1 ~]# /etc/init.d/mysqld start
Starting mysqld: [ OK ]
[root@server1 ~]# vim /etc/my.cnf
[root@server1 ~]# grep password /var/log/mysqld.log
Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Remove test database and access to it? (Press y|Y for Yes, any other key for No) :
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
server2:
[root@server2 ~]# vim /etc/my.cnf
[root@server2 ~]# /etc/init.d/mysqld start
Starting mysqld: [ OK ]
[root@server2 ~]# grep password /var/log/mysqld.log
Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Remove test database and access to it? (Press y|Y for Yes, any other key for No) :
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
server2测试,是否可以登陆server1 的mysql
3、配置主从复制
server1:
[root@server1 ~]# mysql -p
mysql> grant replication slave on *.* to repl@'172.25.50.%' identified by 'Jane.123';
Query OK, 0 rows affected, 1 warning (0.06 sec)
server2:
[root@server2 ~]# mysql -p
mysql> change master to master_host='172.25.50.1', master_user='repl',master_password='Jane.123', master_log_file='mysql-bin.000002', master_log_pos=843;
Query OK, 0 rows affected, 2 warnings (0.81 sec)
4、测试,在server1 建立用户,在server2查看。如果可以看到,则同步成功。
server1:
server2:
二、gtid 主从复制
1、修改配置文件
server1:
[root@server1 ~]# vim /etc/my.cnf
[root@server1 ~]# /etc/init.d/mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
server2:
[root@server2 ~]# vim /etc/my.cnf
[root@server2 ~]# /etc/init.d/mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
2、
server2:
mysql> change master to master_host='172.25.50.1', master_user='repl', master_password='Jane.123', MASTER_AUTO_POSITION = 1;
Query OK, 0 rows affected, 2 warnings (0.79 sec)
3、测试:在 server1 删除一个信息,然后去server2查看,是否同步。
server1:
server2:
三、并行复制
将server2连接成server3的主
1、修改server2的配置文件
[root@server2 ~]# vim /etc/my.cnf
[root@server2 ~]# /etc/init.d/mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
2、查看server2 mysql的数据 并 手动传给 server3
[root@server2 ~]# mysqldump -p test > test.sql
Enter password:
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.
[root@server2 ~]# scp test.sql root@172.25.50.3:
root@172.25.50.3's password:
test.sql 100% 2123 2.1KB/s 00:00
3、server3 修改文件 并 查看是否同步成功
[root@server3 ~]# vim test.sql ##写入
[root@server3 ~]# mysql -p < test.sql
Enter password:
成功
4、在server2 masetr 授权给 slave server3 ,并 在server3 申请 授权 server2
[root@server2 ~]# mysql -p
Enter password:
mysql> grant replication slave on *.* to repl@'172.25.50.%' identified by 'Jane.123';
Query OK, 0 rows affected, 1 warning (0.29 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.07 sec)
[root@server3 ~]# mysql -p
Enter password:
mysql> change master to master_host='172.25.50.2', master_user='repl', master_password='Jane.123', master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (1.07 sec)
mysql> start slave;
Query OK, 0 rows affected (0.04 sec)
5、在server2配置文件里添加并行复制的参数
[root@server2 ~]# vim /etc/my.cnf
[root@server2 ~]# /etc/init.d/mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
DATABASE:默认值,基于库的并行复制方式
LOGICAL_CLOCK:基于组提交的并行复制方式
max_committed_transaction:记录上次组提交时的logical_clock,代表上述mysqlbinlog中的last_committed
transaction_counter:记录当前组提交中各事务的logcial_clock,代表上述mysqlbinlog中的sequence_number
涉及到组提交的一个延时控制参数:binlog_group_commit_sync_delay
总结:
MySQL 5.7推出的Enhanced Multi-Threaded Slave解决了困扰MySQL长达数十年的复制延迟问题,再次提醒一些无知的PostgreSQL用户,不要再停留在之前对于MySQL的印象,物理复制也不一定肯定比逻辑复制有优势,而MySQL 5.7的MTS已经完全可以解决延迟问题。
四、半同步复制
半同步复制的原理图:
1、半同步主服务器设置
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
mysql> show global variables like '%semi%';
+------------------------------------+-------+
| Variable_name | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled | OFF | (是否启动半同步功能)
| rpl_semi_sync_master_timeout | 10000 | (连接从节点的超时时间)
| rpl_semi_sync_master_trace_level | 32 |(是否检测半同步从节点上线)
| rpl_semi_sync_master_wait_no_slave | ON |
+------------------------------------+-------+
mysql> set global rpl_semi_sync_master_enabled=1;
mysql> set global rpl_semi_sync_master_timeout=2000;
2、半同步从服务器配置
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
mysql> set global rpl_semi_sync_slave_enabled=1;
mysql> show global variables like '%semi%';
mysql> STOP SLAVE IO_THREAD;
mysql> START SLAVE IO_THREAD;
3、在主服务器验证半同步
mysql> show global status like '%semi%';
重启从上的IO线程
mysql> STOP SLAVE IO_THREAD;
mysql> START SLAVE IO_THREAD;
如果没有重启,则默认还是异步复制,重启后,slave会在master上注册为半同步复制的slave角色。
当我们在server1删除user2.在server2查看:
半同步从服务器配置
半同步从服务器配置
卸载插件:
uninstall
一旦某次等待超时,会自动降级为异步
事实上,半同步复制并不是严格意义上的半同步复制
当半同步复制发生超时时(由rpl_semi_sync_master_timeout参数控制,单位是毫秒,默认为10000,即10s),会暂时关闭半同步复制,转而使用异步复制。当master dump线程发送完一个事务的所有事件之后,如果在rpl_semi_sync_master_timeout内,收到了从库的响应,则主从又重新恢复为半同步复制。
测试一:
在server1 主服务中建立用户:
server2查看:
server3从服务查看同步:
测试二:延迟
在server2 关闭IO
在server1建立用户
在server3查看:
没有同步用户,所以在server2打开IO
回到server3去查看用户同步
rpl_semi_sync_master_wait_no_slave
ON
默认值,当状态变量Rpl_semi_sync_master_clients中的值小于rpl_semi_sync_master_wait_for_slave_count时,Rpl_semi_sync_master_status依旧显示为ON。
OFF
当状态变量Rpl_semi_sync_master_clients中的值于rpl_semi_sync_master_wait_for_slave_count时,Rpl_semi_sync_master_status立即显示为OFF,即异步复制。
说得直白一点,如果我的架构是1主2从,2个从都采用了半同步复制,且设置的是rpl_semi_sync_master_wait_for_slave_count=2,如果其中一个挂掉了,对于rpl_semi_sync_master_wait_no_slave设置为ON的情况,此时显示的仍然是半同步复制,如果rpl_semi_sync_master_wait_no_slave设置为OFF,则会立刻变成异步复制。
状态变量中,比较重要的有以下几个
Rpl_semi_sync_master_clients
当前半同步复制从的个数,如果是一主多从的架构,并不包含异步复制从的个数。
Rpl_semi_sync_master_no_tx
The number of commits that were not acknowledged successfully by a slave.
具体到上面的测试中,指的是insert into test.test values(2)这个事务。
Rpl_semi_sync_master_yes_tx
The number of commits that were acknowledged successfully by a slave.
具体到上面的测试中,指的是以下四个事务
create database test;
create table test.test(id int);
insert into test.test values(1);
insert into test.test values(3);
总结
在一主多从的架构中,如果要开启半同步复制,并不要求所有的从都是半同步复制。
MySQL 5.7极大的提升了半同步复制的性能。
5.6版本的半同步复制,dump thread 承担了两份不同且又十分频繁的任务:传送binlog 给slave ,还需要等待slave反馈信息,而且这两个任务是串行的,dump thread 必须等待 slave 返回之后才会传送下一个 events 事务。dump thread 已然成为整个半同步提高性能的瓶颈。在高并发业务场景下,这样的机制会影响数据库整体的TPS 。
5.7版本的半同步复制中,独立出一个 ack collector thread ,专门用于接收slave 的反馈信息。这样master 上有两个线程独立工作,可以同时发送binlog 到slave ,和接收slave的反馈。
五、全同步复制
1、首先关掉所有的节点的mysql
[root@server1 ~]# /etc/init.d/mysqld stop
Stopping mysqld: [ OK ]
[root@server1 ~]# cd /var/lib/mysql
[root@server1 mysql]# ls
auto.cnf ib_logfile1 mysql-bin.000003 mysql-bin.index
ib_buffer_pool mysql mysql-bin.000004 performance_schema
ibdata1 mysql-bin.000001 mysql-bin.000005 sys
ib_logfile0 mysql-bin.000002 mysql-bin.000006 test
[root@server1 mysql]# rm -fr *
[root@server1 mysql]# ls
[root@server1 mysql]# pwd
/var/lib/mysql
[root@server1 mysql]# vim /etc/my.cnf
删掉之前写的所有
2、配置文件
[root@server1 mysql]# vim /etc/init.d/mysqld
[root@server1 mysql]# /etc/init.d/mysqld start
Initializing MySQL database: [ OK ]
Starting mysqld: [ OK ]
[root@server1 mysql]# vim /etc/my.cnf
写入:
29 server_id=1
30 gtid_mode=ON
31 enforce_gtid_consistency=ON
32 master_info_repository=TABLE
33 relay_log_info_repository=TABLE
34 binlog_checksum=NONE
35 log_slave_updates=ON
36 log_bin=binlog
37 binlog_format=ROW
38
39 transaction_write_set_extraction=XXHASH64
40 loose-group_replication_group_name="9cbb62a8-9c7b-11e8-b5b8-525400382249"
41 loose-group_replication_start_on_boot=off
42 loose-group_replication_local_address= "172.25.50.1:24901"
43 loose-group_replication_group_seeds="172.25.50.1:24901,172.25.50.2:24901,172 .25.50.3:24901"
44 loose-group_replication_bootstrap_group= off
45 loose-group_replication_single_primary_mode=off
46 loose-group_replication_enforce_update_everywhere_checks=on
47 loose-group_replication_ip_whitelist="172.25.0.0/24,127.0.0.1/8"
[root@server1 mysql]# /etc/init.d/mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
3、配置 主服务mysql
[root@server1 mysql]# mysql -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.17-log
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> ALTER USER root@localhost identified by 'Jane.123';
Query OK, 0 rows affected (0.07 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%' IDENTIFIED BY 'Jane.123';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> reset master;
Query OK, 0 rows affected (0.14 sec)
mysql> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)
mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='Jane.123' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.37 sec)
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (0.20 sec)
mysql> SHOW PLUGINS;
mysql> SET GLOBAL group_replication_bootstrap_group=ON;
Query OK, 0 rows affected (0.00 sec)
mysql> STOP GROUP_REPLICATION;
Query OK, 0 rows affected (0.00 sec)
mysql> SET GLOBAL group_replication_ip_whitelist="172.25.50.0/24,127.0.0.1/8";
Query OK, 0 rows affected (0.00 sec)
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (8.88 sec)
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 497143aa-9c7a-11e8-a665-5254004a7658 | server1 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
1 row in set (0.00 sec)
mysql>
mysql> CREATE DATABASE test;
Query OK, 1 row affected (0.08 sec)
mysql> USE test;
Database changed
mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL);
Query OK, 0 rows affected (0.52 sec)
mysql> INSERT INTO t1 VALUES (1, 'Luis');
Query OK, 1 row affected (0.32 sec)
mysql> SELECT * FROM t1;
+----+------+
| c1 | c2 |
+----+------+
| 1 | Luis |
+----+------+
1 row in set (0.00 sec)
4、修改server’2的配置文件
[root@server2 ~]# /etc/init.d/mysqld stop
Stopping mysqld: [ OK ]
[root@server2 ~]# cd /var/lib/mysql
[root@server2 mysql]# ls
auto.cnf ib_logfile1 mysql-bin.index server2-relay-bin.index
ib_buffer_pool mysql performance_schema sys
ibdata1 mysql-bin.000001 server2-relay-bin.000016 test
ib_logfile0 mysql-bin.000002 server2-relay-bin.000017
[root@server2 mysql]# rm -fr *
[root@server2 mysql]# ls
[root@server2 mysql]# pwd
/var/lib/mysql
[root@server2 mysql]# vim /etc/my.cnf ##删掉所有之前添加的内容
[root@server2 mysql]# vim /etc/init.d/mysqld ##注释掉113-116
[root@server2 mysql]# /etc/init.d/mysqld start
Initializing MySQL database: [ OK ]
Starting mysqld: [ OK ]
[root@server2 mysql]# vim /etc/my.cnf
[root@server2 mysql]# /etc/init.d/mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
[root@server2 mysql]#
5、配置server2 的mysql
[root@server2 mysql]# grep password /var/log/mysqld.log
[root@server2 mysql]# mysql -p
Enter password:
mysql> ALTER USER root@localhost identified by 'Jane.123';
Query OK, 0 rows affected (0.08 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%' IDENTIFIED BY 'Jane.123';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> reset master;
Query OK, 0 rows affected (0.13 sec)
mysql> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)
mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='Jane.123' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.33 sec)
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (0.40 sec)
mysql> SHOW PLUGINS;
mysql> STOP GROUP_REPLICATION;
Query OK, 0 rows affected (0.00 sec)
mysql> SET GLOBAL group_replication_ip_whitelist="172.25.50.0/24,127.0.0.1/8";
Query OK, 0 rows affected (0.00 sec)
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (6.37 sec)
mysql> select * from performance_schema.replication_group_members;
查看成员信息,全部都是ONLINE的即可
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 57e44e94-9ca4-11e8-8ea9-525400158c10 | server1 | 3306 | ONLINE |
| group_replication_applier | 8bc31fd8-9ca6-11e8-a2f1-5254001d1106 | server2 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
2 rows in set (0.00 sec)
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1 |
+----------------+
1 row in set (0.00 sec)
mysql> select * from t1;
+----+------+
| c1 | c2 |
+----+------+
| 1 | Luis |
+----+------+
1 row in set (0.00 sec)
6、server3 和server2 的操作一模一样,我们直接 copy 即可。切记要更改配置文件里的id和ip。
但是server3 查看成员信息的时候 ,得同时出现server1 server2 server3 都是ONLINE 才成功。
7、测试一,我们再server3 建立用户,去server2 server1查看。
Server3:
Server2;
8、测试二,我们在server2建立用户,去server1 server3 查看。
Server2:
Server1:
Server3:
总结:
全同步复制(Fully synchronous replication)
1、逻辑上
指当主库执行完一个事务,所有的从库都执行了该事务才返回给客户端。因为需要等待所有从库执行完该事务才能返回,所以全同步复制的性能必然会收到严重的影响。
2、技术上
当主库提交事务之后,所有的从库节点必须收到、APPLY并且提交这些事务,然后主库线程才能继续做后续操作。但缺点是,主库完成一个事务的时间会被拉长,性能降低。