LAMP之MySQL优化

复制

在node2安装数据库
scp -r /usr/local/mysql/ root@172.25.253.2:/usr/local先将node1上源码安装的mysql复制到node2中
scp /etc/my.cnf root@172.25.253.2:/etc/
scp /etc/init.d/mysqld root@172.25.253.2:/etc/init.d/mysqld
并将node2中的mysql写道环境变量中
useradd -u 1000 -M -d /usr/local/mysql/data -s /sbin/nologin mysql
cd /usr/local/mysql/data/
rm -fr *
mysqld --initialize --user=mysql 生成随机数密码，记住随机数密码，重复执行会报错
/etc/init.d/mysqld start 启动mysql,并使用随机数密码修改mysql密码（切记不要重复启动，否则会报错）
mysql_secure_installation 执行安全初始化脚本,全部回车即可
mysql -p 登录mysql(如果无法登录时尝试systemctl start mysqld)

以下配置参考mysql官方文档： https://dev.mysql.com/doc/refman/5.7/en/replication-howto-masterbaseconfig.html

关于复制

载入如今互联网环境中，复制一般分为传统的复制和大趋势下的组复制，而传统的复制又分为主从复制(异步复制)和半同步复制，主从复制有原始slave通过Position复制master的二进制日志文件，也有通过GTID的方式进行全局复制，GTID较前者的好处在于出现某个mysql宕掉后，GTID模式会在全局中自动寻找下一跳，而不用手动配置，主从复制有一个比较致命问题，在生成二进制日志文件后，尤其在一些极端条件下，无法100%确认被slave端IO进程成功复制。而半同步复制中的AFTER_COMMIT恰好解决了这一问题，在slave端IO复制后会返回ACK，但是还有一个问题，在该模式下，插入一个数据时，不论IO线程复制成功与否，在本地都会查询到该数据，这在金融行业时致命的。半同步复制在AFTER_SYNC模式下会在IO线程返回ACK后才会把数据写道本地，当然也可以设置这个等待时间为无穷大，强制数据的一致性。

主从复制(master&slave)

MySQL主从异步复制是最常见的复制场景。数据的完整性依赖于主库BINLOG的不丢失，只要主库的BINLOG不丢失，那么就算主库宕机了，我们还可以通过BINLOG把丢失的部分数据通过手工同步到从库上去。
传统的MySQL复制采用主从的方式进行，可以一主一从也可以一主多从，主库执行一个事务，提交后稍后异步的传送到从库中，如果是基于语句的复制则会重新执行，如果是基于行的负责则会应用日志，同时是shared-nothing的架构，即所有服务器拥有同样的数据复制
传统的数据主从复制均属于异步复制，从库起IO线程连接主库，获取主库二进制日志写到本地中继日志，并更新master-info文件(存放主库相关信息)，从库再利用SQL线程执行中继日志。
补充：为了保证Binlog的安全，MySQL引入sync_binlog参数来控制BINLOG刷新到磁盘的频率。
node1：
vim /etc/my.cnf 设置复制源

server-id=1	#加入id(server_id值为0时，副本拒绝连接到源。)
log-bin=mysql-bin	#启动二进制日志

mysql -p 创建用于复制的用户

mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'redhat';	##%代表除本机外的所有用户，方便测试
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

/etc/init.d/mysqld restart 重启mysqld 重载配置文件
mysql -p 查看二进制日志文件

mysql> SHOW MASTER STATUS;

node2：
vim /etc/my.cnf 增加id

server-id=2	#加入id

/etc/init.d/mysqld restart 重启mysqld 重载配置文件
mysql -p 建立副本

mysql> CHANGE MASTER TO
    -> MASTER_HOST='172.25.253.1',
    -> MASTER_USER='repl',
    -> MASTER_PASSWORD='redhat',
    -> MASTER_LOG_FILE='mysql-bin.000001',	#以node1上二进制日志文件为准
    -> MASTER_LOG_POS=350;	#Position
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G;	#查看到Slave_IO_Running: yes Slave_SQL_Running: Yes

测试：
在主节点node1中插入数据，在从节点node2中查看。

一主两从

此时node1是主数据库，node2为从数据库，在此基础上我们加入node3，有两种方案：1>node2和3都作为node1的从库，但是会加大node1的负载，2>将node3作为node2的从数据库，因此node2既是从库也是主库。以达到宏观上的负载均衡。

在node1和2主从配置好后，在node1写入数据，此时要加入node3，要先备份node1中的库



能在node3上看到主库上的数据。

现在开始将将node3配置成node2的从库。

再将node3设置为从库。
node2中：
mysql -p 创建用于复制的用户

mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'redhat';	##%代表除本机外的所有用户，方便测试
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

/etc/init.d/mysqld restart 重启mysqld 重载配置文件
mysql -p 查看二进制日志文件

mysql> SHOW MASTER STATUS;

node3中：
vim /etc/my.cnf 增加id

server-id=2	#加入id

/etc/init.d/mysqld restart 重启mysqld 重载配置文件
mysql -p 建立副本

mysql> CHANGE MASTER TO
    -> MASTER_HOST='172.25.253.1',
    -> MASTER_USER='repl',
    -> MASTER_PASSWORD='redhat',
    -> MASTER_LOG_FILE='mysql-bin.000002',	#以node1上二进制日志文件为准
    -> MASTER_LOG_POS=154;	#Position
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G;	#查看到Slave_IO_Running: yes Slave_SQL_Running: Yes

IO线程与SQL线程

在图中我们观察到SLAVE的IO线程和SQL线程都是开启的，IO线程负责从master读取二进制日志文件，SQL线程负责回放读取的二进制日志，如图中红色方框内容，read和exec，一般情况下读了之后回放是有延迟的，视配置而定。
此时我们在刚刚的基础上将主从复制改为GTID模式的主从复制
测试

基于GTID(全局事务标识)的主从复制

由于以上主从复制，在其中一个宕机后，要保证数据的一致性难度较大，因此一般使用GTID模式。像这样一主多从的架构下，出现宕机时，不用再手动设置，GTID模式在全局中自动寻找下一跳。

在node1（主节点中）
vim /etc/my.cnf 增加以下内容，开启gtid服务

log-bin=mysql-bin 	##基于二进制日志位置的主从复制
server-id=1
gtid_mode=ON 	##打开gtid
enforce-gtid-consistency=ON	#ON或1都表示开启

/etc/init.d/mysqld restart

在node2（从节点中）
vim /etc/my.cnf 配置从站slave开启GTID的服务

server-id=2   ##基于二进制日志位置的主从复制
gtid_mode=ON  ##打开gtid
enforce-gtid-consistency=ON

/etc/init.d/mysqld restart
mysql -p 进入mysql，配置从站slave使用基于GTID的自动定位。

mysql> stop slave; ##停止基于二进制位置的复制

mysql> CHANGE MASTER TO
    -> MASTER_HOST = '172.25.253.1',
    -> MASTER_USER = 'repl',
    -> MASTER_PASSWORD = 'redhat',
    -> MASTER_AUTO_POSITION = 1;

mysql> START SLAVE;			 ##开启gtid的主从复制
mysql> SHOW SLAVE STATUS\G 	 ##查看复制状态

测试：
在主节点node1中插入数据，在从节点node2中查看。在数据更新后使用SHOW SLAVE STATUS\G(在master中用SHOW MASTER STATUS\G查看)可以查看GTID号。

mysql的半同步复制

MySQL也提供了一个半同步复制，其要求主库在commit时等待从库接受完事务并返回确认信息后才能提交。半同步复制是建立在基本的主从复制基础上，利用插件完成半同步复制，传统的主从复制，不管从库是否正确获取到二进制日志，主库不断更新，半同步复制则当确认了从库把二进制日志写入中继日志才会允许提交，如果从库迟迟不返回ack,主库会自动将半同步复制状态取消，进入最基本的主从复制模式。
半同步复制保证了事务成功提交后，至少有两份日志记录，一份在主库的BINLOG日志上，另一份在至少一个从库的中继日志Relay Log上，从而更进一步保证了数据的完整性。
半同步复制模式下，假如在传送BINLOG日志到从库时，从库宕机或者网络延迟，导致BINLOG并没有即使地传送到从库上，此时主库上的事务会等待一段时间(时间长短由参数rpl_semi_sync_master_timeout设置的毫秒数决定)，如果BINLOG在这段时间内都无法成功发送到从库上，则MySQL自动调整复制为异步模式，事务正常返回提交结果给客户端。
半同步复制很大程度上取决于主从库之间的网络情况，往返时延RTT越小决定了从库的实时性越好。通俗地说，主从库之间的网络越快，从库约实时。

node1：
mysql -p 进入数据库，主库安装服务插件，并且开启半同步复制：

mysql> install plugin rpl_semi_sync_master SONAME 'semisync_master.so';
mysql> set global rpl_semi_sync_master_enabled