Ubuntu中Mysql结合防火墙的使用实现Bind多IP、MySQL数据库读写分离中间件及ProxySQL的安装以及Mysql日志刷到磁盘的规则以及检查点checkpoint

一、Ubuntu中Mysql结合防火墙的使用实现Bind多IP

    Mysql中要在配置中bind指定多个IP是实现不了的，之前也有一篇相关的文章，可在博客中搜索，其配置项要么是指定一个IP地址，要么是0.0.0.0。默认是配置的127.0.0.1，但这会导致外面的服务器以及win上的client无法连接mysql查看数据。而如果改成0.0.0.0，又对外暴露了端口，比较危险。我以前的做法是绑定局域网IP地址，然后使用haproxy作负载提供对外的连接服务，可在haproxy中的七层四层上做对应的IP限制。实际上另外一个结合防火墙的方法也比较好用。

    在Ubuntu中先要将mysql的bind-address配置注释或者设置成0.0.0.0，重启mysql；这时mysql支持了外部IP的访问，然后通过防火墙阻止需要的IP外对3306端口的访问，从而间接实现绑定了多个IP，包括127.0.0.1，内网IP和外网IP地址。如下：

#防火墙的配置
root@us12:~# /sbin/iptables -A INPUT -p tcp -s 112.95.214.8 --dport 3306 -j ACCEPT 
root@us12:~# /sbin/iptables -A INPUT -p tcp -s 192.168.162.8  --dport 3306 -j ACCEPT 
root@us12:~# /sbin/iptables -A INPUT -p tcp --dport 3306 -j DROP
#查看防火墙的设置
root@us12:~# /sbin/iptables --list
Chain INPUT (policy ACCEPT)
target     prot opt source               destination         
ACCEPT     tcp  --  112.95.214.8         anywhere             tcp dpt:mysql
ACCEPT     tcp  --  192.168.162.8        anywhere             tcp dpt:mysql
DROP       tcp  --  anywhere             anywhere             tcp dpt:mysql

Chain FORWARD (policy DROP)
target     prot opt source               destination         
DOCKER-USER  all  --  anywhere             anywhere            
DOCKER-ISOLATION-STAGE-1  all  --  anywhere             anywhere            
ACCEPT     all  --  anywhere             anywhere             ctstate RELATED,ESTABLISHED
DOCKER     all  --  anywhere             anywhere            
ACCEPT     all  --  anywhere             anywhere            
ACCEPT     all  --  anywhere             anywhere            

Chain OUTPUT (policy ACCEPT)
target     prot opt source               destination
#可以使用-D选项删除指定条，如果执行下面这条命令就是删除Chain INPUT中112.95.214.8的许可这条    
root@us12:~# iptables -D INPUT 1
#也可以通过!排除法进行添加多个IP
root@us12:/etc/# /sbin/iptables -A INPUT -p tcp --dport 3306 ! -s 112.95.214.8 -j DROP
root@us12:/etc/# /sbin/iptables -A INPUT -p tcp --dport 3306 ! -s 192.168.162.8 -j DROP

    在centos中防火墙我是通过/etc/init.d/iptables命令进行重启，但是在Ubuntu16中未找到对应的命令文件，不过可以通过修改/etc/sysconfig/iptables配置让它自动加载生效并保存。

#使用iptables-save保存iptables规则
root@us12:/etc/# iptables-save > /etc/iptables.rules
#iptables规则自动保存与自动加载,添加以下两行
root@us12:/etc/# vim /etc/network/interfaces
pre-up iptables-restore < /etc/iptables.rules
post-down iptables-save > /etc/iptables.rules

二、MySQL数据库读写分离中间件及ProxySQL的安装

    现在的框架或者很多开源CMS基本都已经在代码层面支持了读写分离，但现在手上有一套代码层面已经不好改动的系统，要想实现读写分离，那可得伤筋动骨，毕竟系统比较庞大，且其中有大量的事务处理，而事务处理中不能简单将一个事务中的SQL机械地根据是insert还是update来执行分离，所以只有考虑使用读写分离的中间件了，

    ProxySQL是一个高性能、高可用的MYSQL读写分离开源中间件，当然市面上也有不少的相关的产品，有mysql官方的MySQL-Proxy、有阿里巴巴的Cobar、Amoeba、TDDL，还有其它比如Atlas，mycat，one proxy，proxySQL等等中间件。MySQL-Proxy更新缓慢，长期停留在Alpha版阶段， Amoeba不支持事务，TDDL未完整开源。360开发的Atlas开始感觉不错，但是版本好久没有更新了，上次更新还是2016-07-28，

地址：https://sourceforge.net/projects/math-atlas/files/Stable/ 

        不过从网上来看这个产品认可度也还好，这里找到一个安装的shell脚本 https://gist.github.com/kparrish/6395318 最后还是选择一个稳定、使用比较普遍的ProxySQL。因为它对事务有很明确的支持。

    官网地址：https://proxysql.com/ 下载地址：Index of /ProxySQL/
    目前最新版本是2019年刚发布的2.0版，地址：Index of /ProxySQL/proxysql-2.0.x/stretch/

#安装ProxySQL
yes@u07:/# apt-get install -y lsb-release
yes@u07:/# wget -O - 'http://repo.proxysql.com/ProxySQL/repo_pub_key' | apt-key add -
yes@u07:/# echo deb http://repo.proxysql.com/ProxySQL/proxysql-1.4.x/$(lsb_release -sc)/ ./ \
| tee /etc/apt/sources.list.d/proxysql.list
yes@u07:~# lsb_release --help
Usage: lsb_release [options]
Options:
  -h, --help         show this help message and exit
  -v, --version      show LSB modules this system supports
  -i, --id           show distributor ID
  -d, --description  show description of this distribution
  -r, --release      show release number of this distribution
  -c, --codename     show code name of this distribution
  -a, --all          show all of the above information
  -s, --short        show requested information in short format
yes@u07:~# lsb_release -a
No LSB modules are available.
Distributor ID: Debian
Description:    Debian GNU/Linux 9.2(stretch)
Release:        9.2
Codename:       stretch
yes@u07:/# echo "deb http://repo.proxysql.com/ProxySQL/proxysql-1.4.x/stretch/ ./" >> /etc/apt/sources.list.d/proxysql.list
yes@u07:/# apt-get update
yes@u07:/# apt-get install proxysql

    lsb_release命令，其中LSB是Linux Standard Base的缩写，显示发行版本信息。如上示例中使用-ac选项得取short版的codename值。proxysql开启/关闭/重启命令

开启：service proxysql start
关闭：service proxysql stop
重启：service proxysql restart

三、Mysql日志刷到磁盘的规则以及检查点checkpoint

        此第三篇文章内容可能来自于转载，如作者有需要，我联系我删除。

     Mysql中，在log buffer中但未刷到磁盘的日志称为脏日志(dirty log)。默认情况下进行事务处理的时候每次提交事务都会刷事务日志到磁盘中，这是因为变量 innodb_flush_log_at_trx_commit 的值为1。innodb在有commit动作后刷日志到磁盘只是innodb存储引擎刷日志的规则之一。Mysql日志什么时候还会刷到磁盘呢？有以下规则：

1.发出commit动作。commit发出后是否刷日志由变量 innodb_flush_log_at_trx_commit 控制。
2.几秒刷一次。这个刷日志的频率由变量 innodb_flush_log_at_timeout 值决定，默认是1秒。要注意这个和commit动作无关。
3.当log buffer中已经使用的内存超过一半时。
4.当有checkpoint时，checkpoint在一定程度上代表了刷到磁盘时日志所处的LSN位置。

    什么是checkpoint？checkpoint是检查点的意思，我理解checkpoint检查点就是标记重做日志中已经完成刷到磁盘的位置点，如果缓冲池中有很多重做日志，完全恢复需要1分钟，checkpoint可能标记到了第58秒的位置，这时数据库恢复只需要重做最后2秒里的数据日志，Checkpoint缩短了数据库的恢复时间。

    由于数据和日志都以页的形式存在，所以脏页表示脏数据和脏日志。不仅仅是日志需要刷盘，脏数据页也一样需要刷盘。在innodb中，数据刷盘的规则只有一个：checkpoint。但是触发checkpoint的情况却有几种。不管怎样，checkpoint触发后，会将buffer中脏数据页和脏日志页都刷到磁盘。

    innodb存储引擎中checkpoint分为两种：

sharp checkpoint：在重用redo log文件(例如切换日志文件)的时候，将所有已记录到redo log中对应的脏数据刷到磁盘。
fuzzy checkpoint：一次只刷一小部分的日志到磁盘，而非将所有脏日志刷盘。有以下几种情况会触发该检查点：

        master thread checkpoint：由master线程控制，每秒或每10秒刷入一定比例的脏页到磁盘。
flush_lru_list checkpoint：从MySQL5.6开始可通过 innodb_page_cleaners 变量指定专门负责脏页刷盘的page cleaner线程的个数，该线程的目的是为了保证lru列表有可用的空闲页。

        async/sync flush checkpoint：同步刷盘还是异步刷盘。例如还有非常多的脏页没刷到磁盘(非常多是多少，有比例控制)，这时候会选择同步刷到磁盘，但这很少出现；如果脏页不是很多，可以选择异步刷到磁盘，如果脏页很少，可以暂时不刷脏页到磁盘。

        dirty page too much checkpoint：脏页太多时强制触发检查点，目的是为了保证缓存有足够的空闲空间。too much的比例由变量 innodb_max_dirty_pages_pct 控制，MySQL 5.6默认的值为75，即当脏页占缓冲池的百分之75后，就强制刷一部分脏页到磁盘。

    由于刷脏页需要一定的时间来完成，所以记录检查点的位置是在每次刷盘结束之后才在redo log中标记的。MySQL停止时是否将脏数据和脏日志刷入磁盘，由变量innodb_fast_shutdown={ 0|1|2 }控制，默认值为1，即停止时只做一部分purge，忽略大多数flush操作(但至少会刷日志)，在下次启动的时候再flush剩余的内容，实现fast shutdown。