1. mysql高可用方案
1.1 低读低写并发、低数据量方案
方案一：
（1）数据库架构图
（2）特点
一台机器A作为读写库，另一台B作为备份库，若A库故障后，读写操作或转移到B库上执行，A库恢复后作为备库。
（3）开发说明
此种情况下，数据源配置中的数据库IP地址，可采用虚拟的IP地址，虚拟IP地址有两台数据库机器上的keepalived配置，互相检测心跳。当一台机器故障后，虚拟IP地址会从故障库漂移到正常库上。
数据库的贮备配置，故障排查和数据不全，需要DBA（数据库管理人员）和运维人员维护。程序代码和配置并不需要修改啦
（4）适应场景
读写不高的场景（单表数据低于500万），双机高可用
（5）优缺点
优点：机器故障后自动切换
缺点：只有一台机器工作，读写未分离，并发有限制
方案二：
（1）数据库架构图
（2）特点
一台机器A作为写库，另一台B作为读库；A库故障后，读的操作转移到B机器上，A库恢复后，A库作为读库，B库作为写库。
（3）开发说明
该方案的实现，要借助数据库中间件Mycat来实现，配置如下
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" writeType="0"
dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!--主，用于写-->
<writeHost host="hostM1" url="192.168.1.135:3306" user="root" password="123" />
<!--主2，用于读,hostM1 down了，自动切换为主，读写都可以-->
<writeHost host="hostM2" url="192.168.1.136:3306" user="root" password="123" />
</dataHost>项目开发中，需要配置Mycat数据源，并实现对Mycat数据源的数据操作。数据库A和B互为主从。
数据库的主主配置，故障排查和数据不全，依然需要DBA和运维人员维护。
（4）适应场景
读写操作要求都不是非常高的场景（单表数据低于1000万），高可用，比方案一高。
（5）优缺点
优点：一个机器故障后可以自动切换；
读写分离，并发有了很大的提升
缺点：引入了一个Mycat节点，若要高可用需要引入两个Mycat。常规的解决方案为：引入haproxy和keepalived对mycat做集群。
1.2 高读低写并发，低数据量
方案三：一主多从+读写分离
（1）数据库架构图
（2）特点
一个主库A多个从库，当A库故障时，提上从库B为主写库，同事修改C，D为B的从库，A故障修复后，会作为B的从库使用。
（3）开发说明
项目开发需要使用Mycat作为中间件，来配置主库和从库，核心配置如下
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" writeType="0"
dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!--主A，用于写-->
<writeHost host="hostM1" url="192.168.1.135:3306" user="root" password="123" />
<!—从B，用于读,hostM1 down了，自动切换为主-->
<writeHost host="hostM2" url="192.168.1.136:3306" user="root" password="123456"/>
<!—从C，用于读-->
<writeHost host="hostM3" url="192.168.1.137:3306" user="root" password="123" />
<!—从D，用于读-->
<writeHost host="hostM4" url="192.168.1.138:3306" user="root" password="123" />
</dataHost>主库A故障后，Mycat会自动把B提升为写库，而C,D从库，可以通过MHA等工具，自动修改其主库B，进而实现自动切换的目的。
MHA Manger可以单独部署在一台独立的机器上管理多个master-slave集群，也可以部署在一台slave节点上，MHA Nod运行在每台Mysql服务器上，MHA Manger会定时探测集群中的master节点，当master出现故障后，它可以自动将最新数据的slave提升为新的master，然后将其他所有的slave重新指向新的master，故障转移对应用程序完全透明。
（4）适应场景
该架构适合写并发不大。但读并发很大的场景
（5）优缺点
优点：读并发能力有了质的提升，理论上来说，读的节点也可以配置多个
缺点：节点多
1.3 高读写并发，低数据量方案
方案四：MariaDB Galera Cluster方案
（1）数据库架构图
（2）特点
多个数据库，在负载均衡作用下，可同事进行写入和读取操作，各个库之间以Galera Replication的方法进行数据同步，即每个库理论上来说，数据是完全一致的
（3）开发说明
数据库读写时，只需要修改读写IP为keepalived的虚拟节点即可；数据库配置方面相比较复杂，需要进入haproxy、keepalived、Galaera等各种插件和配置。
（4）使用场景
读写并发较大，数据量不是非常大的场景
（5）优缺点
优点：
- 可以在任意节点上进行读
- 自动剔除故障节点
- 自动加入新街店
- 真正并行的复制，给予行级
- 客户端连接个和操作单数据库的体验一致
- 同步复制，因此具有较高的性能和可靠性
缺点：
- DELETE操作不支持没有主键的表，没有主见的表在不同的节点顺序将不同
- 处理事务时，会运行一个协调认证程序来保证事务的全局一致性，若该事务长时间运行，就会锁死节点中所有先相关表，导致插入卡主（和单表插入时一样的）
- 整个集群的写入吞吐量室友最弱的节点限制，如果一个节点变得缓慢，哪个整个集群僵尸缓慢地，为了稳定的高性能要求，所有的节点应使用统一的硬件
- 如果DDL语句有问题将会破坏整个集群，建议禁用
- mysql数据库5.7.6及之后的版本才支持此方案
1.4 高读写高并发，高数据量
方案五：
（1）数据库架构图
（2）特点
采用Mycat进行分片存储，可以解决负载均衡和数据量过大的问题；
每个分片配置多个读从库，可以减少单个库的压力
（3）开发说明
此种情况，需要配置Haproxy，keepalived和Mycat集群，每个分片上又需要配置一主多从的进群。每个分片上的完整配置，可以参考方案三，简单把方案三理解为一个分片结构，因此 配置和维护量都比较大
（4）使用场景
读写并发和数据量非常大的场景
（5）优缺点
优点：终极解决高并发高数据量的方法
缺点：配置和维护都比较麻烦，需要软硬件设备资源大
2. mysql复制技术
2.1 主从复制概念
mysql主从复制致数据可以从一个mysql数据库服务器主节点复制到一个或多个从节点。mysql默认采用异步复制方式，这样从节点不用一直访问主服务器来更新自己的数据，数据的更新可以在远程连接上进行，从节点可以复制主数据库中的所有数据或者特定的数据库，或特定的表。
2.2 mysql主从复制的主要用途
（1）读写分离
在开发工作中，常常遇到某个sql语句需要锁表，导致暂时不能使用读的服务，这样就影响现有的业务，使用主从复制，让主库负责写，从库负责读，这样即使主库出现锁表的情况，从裤也可以正常维持业务运行
（2）数据实时备份，当系统中某个节点发生故障时，可以很方便的故障切换
（3）高可用
随着系统中业务访问量的增大，如果都是单机部署数据库，就会导致I/O访问频率过高，有了主从复制，增加多个数据存储节点，将负载分布在多个节点上，降低了单机磁盘I/O访问的频率，提高单个机器的I/O性能
重置数据库操作：
# service mysqld stop
# rm -rf /usr/local/mysql/data/*
# /usr/local/mysql/scripts/mysql_install_db --user=mysql --
basedir=/usr/local/mysql --datadir=/usr/local/mysql/data2.3 mysql主从复制原理
（1）Slave上面的O线程连接上Master，并请求从指定的Binary log文件的指定位置之后的日志内容
（2）Master接收到来自于Slave的I/O线程请求后，通过负责复制的I/O线程根据请求信息读取指定日志指定位置之后的日志信息，返回给Slave端的I/O线程。返回信息包括：日志所含的信息，本次返回的信息在Master端Binary log文件的名称及位置。
（3）Slave的I/O线程收到信息后，将接收到的日志内容依次写入到Slave端的Relay log文件的最末端，并将读取到的Master端的bin-log的文件名和位置记录到master-info文件中，以便在下次读取的时候能够清楚的知道读取位置。
（4）Slave的sql线程检测到Relay log中新增的内容，会解析该log文件中的内容，还原为真实执行时的可执行查询或操作语句，并在自身执行此操作，这样就保证了两端数据完全一致。
三个线程介绍：
Binlog输出线程：当从库连接到主库时，主库会创建一个线程并发送binlog内容到从库，对每个即将发送给从库的sql事件，binlog输出线程会将其锁住。当该事件被线程读取完之后，锁将会被释放（即使该事件完全发送到从库的时候，该锁也会被释放）。在从库中，当开始复制的时候，会创建两个线程进行处理
从库I/O线程：当start slave再从库执行之后，从库创建一个I/O线程，该县城连接到主库并请求主库发送binlog里面的更新记录到从库上。从库I/O线程读取主库的binlog输出线程发送的更新并拷贝这些更新到比本地文件，其中包括relay log文件
从库的sql线程：从库创建一个sql线程，这个线程读取从库I/O线程写到relay log的更新事件并执行。
对于每一个主从复制的链接，都有三个线程。拥有多个从库的主库为每一个连接到主库的从库创建一个binlog输出线程，每个从库都有它自己的I/O线程和sql线程。而从库通过创建两个独立的线程，使得在进行复制的时候，读写分离。因此，即使负责执行的线程运行较慢，负责读取更新的线程并不会因此变得缓慢
简单来说就是：
- 主库把数据更改记录到二进制日志（Binary log）中
- 备库将主库的日志复制到自己的中继日志（relay log）中
- 备库读取中继日志中的事件，将其放到备库数据库上执行。
3. mysql主从复制实现
3.1 基于刚初始化的数据库配置主从复制
数据库尽量初始化，两个数据库要保持一致，若数据库内容不一致，需要将主数据库文件备份，在从数据库上导入
（1）节点划分
主库： mysql01 192.168.10.102
从库： mysql02 192.168.10.103
（2）初始化环境
# 两个主机都进行
cp -p /etc/my.cnf.ba{,b.bak`date +%F`}
# yum -y install ntp ntpdate
# ntpdate cn.pool.ntp.org
# hwclock --systohc
# systcemctl status firewalld
# systemctl stop firewalld
# systemctl disable firewalld
# setenforce 0
# 选做
# mysql_secure_installation # 可以全部清空（3）主库配置文件及数据库操作
# 修改配置文件
# vim /my.cnf
[mysqld]
server_id=11
log-bin=mysql-bin
log-bin-index=master-bin.index
# 重启服务
# systemctl stop mysqld
# systemctl start mysqld
# mysql -uroot -pABC123.com
mysql> create user 'copy'@'%' identified with mysql_native_password by 'ABC123.com';
mysql> grant replication slave on *.* to 'copy'@'%';
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 826 | | | |
+------------------+----------+--------------+------------------+-------------------+
mysql> flush tables with read lock;（4）从库配置及数据库操作
# 修改配置文件
# vim /etc/my.cnf
server_id=22
relay-log=relay-log
relay-log-index=relay-log.index
# 重启服务
# systemctl stop mysqld
# systemctl start mysqld
# mysql -h192.168.10.102 -ucopy -pABC123.com
mysql> create user 'copy'@'%' identified with mysql_native_password by 'ABC123.com';
mysql> CHANGE MASTER TO
-> MASTER_HOST='192.168.10.102',
-> master_user='copy',
-> master_password='ABC123.com',
-> master_log_file='mysql-bin.000001',
-> master_log_pos=826;
Query OK, 0 rows affected, 2 warnings (0.13 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.102
Master_User: copy
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 826
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 324
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 826
Relay_Log_Space: 527
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 11
Master_UUID: d801e99b-cfb2-11ea-8d20-000c29a287bf
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set (0.00 sec)
# 配置完成
# 注：从库这里可能会出现很多问题，show slave status时。注意主库的库和表是否已经导入到从库中，主库锁表 flush tables with read lock，从库配置完成后主库解表 unlock tables；若还是出现问题，从库可以试试一下操作
mysql> stop slave;
mysql> reset slave;
mysql> start slave;
mysql> show slave status;（5）主库解表
# mysql -uroot -pABC123.com
mysql> unlock tables;（6）验证
# 主库：
mysql> create database test1;
Query OK, 1 row affected (0.56 sec)
# 从库查询
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test1 |
+--------------------+
5 rows in set (0.67 sec)3.2 针对已经运行一段时间的数据库进行配置主主互备
（1）节点划分
Master Slave1 Slave2
IP 192.168.10.11 192.168.10.12 192.168.10.13
Server_ID 11 22 33（2）初始化环境
# 同步时间：
# 分别在三个节点上执行
yum -y install ntp ntpdate
ntpdate cn.pool.ntp.org
hwclock --systohc
# 关闭防火墙
systemctl stop firewalld
systemctl disable firewalld
sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config
setenforce 0（3）配置从库
修改主库配置：开启log-bin功能，并指定server_id为1，必须指定，否则会根据主机名来命名，主机名改变后主从复制会失效。
vim /etc/my.cnf
[mysqld]
server_id=11
log-bin=mysql-bin
log-bin-index=master-bin.index重启服务
[root@localhost ~]# systemctl stop mysql
[root@localhost ~]# systemctl start mysql创建一个让从数据库链接的用户
mysql> create user 'copy'@'%' identified with mysql_native_password by
'Cloudbu@123';
mysql> grant replication slave on *.* to 'copy'@'%';刷新授权表信息
mysql> flush privileges;（4）初始化备库（使其和主库数据一致）：逻辑备份，物理备份
备份主库数据，并拷贝到从库上（备份前锁表，保持一直性）
mysql> flush tables with read lock;
[root@master ~]# mysqldump -uroot -pCloudbu@123 --routines --events --triggers -
-master-data=2 --flush-logs --all-databases >/root/all_`date +%F`.sql
[root@master ~]# scp all_2020-07-21.sql.tar.gz 192.168.10.11:/root
[root@master ~]# scp all_2020-07-21.sql.tar.gz 192.168.10.12:/root
[root@master ~]# mysql -e 'show master status'
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| master-bin.000001 | 699 | | |
+------------------+----------+--------------+------------------+在从库上解压并导入数据
[root@localhost ~]# mysql -uroot -pCloudbu@123 < all_2020-07-21.sql.tar（5）配置从库
修改配置文件，必须指定中继日志的名称
[mysqld]
server_id=22
relay-log=relay-log
relay-log-index=relay-log.index[root@localhost ~]# systemctl stop mysql
[root@localhost ~]# systemctl start mysql在从（Slave）节点上设置主节点参数
CHANGE MASTER TO
MASTER_HOST='192.168.10.11',
MASTER_USER='copy',
MASTER_PASSWORD='Cloudbu@123',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=699;开启主从同步
start slave;查看主从同步状态
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.11
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 699
Slave_IO_Running: Yes
Slave_SQL_Running: Yes其中的 IO线程和SQL线程均为YES，则配置成功
（6）在主库上解表测试
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
mysql> create database mmp;
Query OK, 1 row affected (0.01 sec)在从库上查看创建信息
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mmp |
| mysql |
| performance_schema |
| sys |
+--------------------+4. mysql主主互备
4.1 mysql双主架构方案说明
（1）两台mysql都可读写，互为主备，默认只是用一台master负责数据的写入，另一台master备用
（2）master是masterB的主库，masterB又是MasterA的主库。互为主从
（3）两台主库之间做高可用，可以采用keepalived等方案
（4）所有提供服务的从服务器与masterB进行主从同步（双主多从）
（5）建议采用高可用策略的时候，masterA或masterB均不因宕机恢复后而枪战VIP（非抢占模式）
这样做可以在一定程度上保证主库的高可用，在一台主库down掉之后，可以在极短的时间内切换到另一台主库上，减少了主从同步给线上主库带来的压力，减少主库宕机对业务造成的影响。
master可能会一直处于空闲状态（可以充当从库，负责部分查询）
主库后面提供的从库要等masterB先同步完了数据后才能masterB去同步数据，这样可能会造成一定程度的同步延时；
4.2 mysql双主架构的实现
基于主从复制的基础上，反向主从
(1) mysql01和mysql02 进行如下配置
# mysql01 配置文件
# vim /etc/my.cnf
[mysqld]
server_id=11
log-bin=mysql-bin
log-bin-index=master-bin.index
relay-log=relay-log
relay-log-index=relay-log.index
# mysql02 配置文件
# vim /etc/my.cnf
[mysqld]
server_id=22
log-bin=mysql-bin
log-bin-index=master-bin.index
relay-log=relay-log
relay-log-index=relay-log.index
# 都重启服务
# systemctl stop mysqld
# systemctl start mysqld注：上面主从复制时候mysql02也创建了copy用户，此时直接用mysql01测能否远程登录
# mysql -h192.168.10.103 -ucopy -pABC123.com
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
# 退出（2）配置mysql02
# 给copy用户授权
# mysql -uroot -pABC123.com
mysql> grant replication slave on *.* to 'copy'@'%';
Query OK, 0 rows affected (0.10 sec)
# 刷新授权表信息
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
# 获取主节点binary log文件名和位置
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 543 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
# 锁表
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)（3）配置mysql01
mysql> CHANGE MASTER TO
-> master_host='192.168.10.103',
-> master_user='copy',
-> master_password='ABC123.com',
-> master_log_file='mysql-bin.000001',
-> master_log_pos=543;
Query OK, 0 rows affected, 2 warnings (0.04 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.103
Master_User: copy
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 543
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 324
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 543
Relay_Log_Space: 527
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 22
Master_UUID: fc966ccf-cfb2-11ea-9cf5-000c29c86c0f
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set (0.00 sec)（4）mysql02 解锁
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
# 此时主主配置完成（5）测试
# mysql02 给test1创建一个表
mysql> create table test1(id int not null,name char(20) not null);
Query OK, 0 rows affected (0.62 sec)
# 用mysql01查询
mysql> select * from test1;
Empty set (0.00 sec)
mysql> show tables;
+-----------------+
| Tables_in_test1 |
+-----------------+
| test1 |
+-----------------+
1 row in set (0.00 sec)
# 配置完成------------------------------------------------------------------------------------------------------- 返回目录
155
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
