一、集群搭建之主从复制
（一）主从复制原理
这里需要了解两个文件，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冲突的话，也可以不这么做。