一个用于快速构建大规模，高质量，全自动化的 mysql分布式集群环境的工具；包含mysql 安装、备份、监控、高可用、读写分离、优化、巡检、自行化运维
mysqltools 权威指南
官方微信公众平台
文档结构
- 质量 - 效率 - 经济 - 技术介绍 - 安装前的准备 - 下载并解压 - 安装Python - 安装ansible - 配置ansible和mysqltools - mysqltools目录介绍 - 自动化mysql单实例安装 - 进入mysql功能目录 - 指定安装的目标主机 - 执行自动化安装 - 谈谈mysqltools如何实现高效 - 谈谈mysqltools如何实现高质量 - mysql单机 - mysql主从复制 - mysql多源复制 - mysql组复制 - mysql小版本升级 - mycat读写分离 - mha - 环境规划 - 配置mha的一些前置备件 - 配置ansible的hosts文件 - 配置mysqltools中mha相关的配置项 - 配置mha的相关信息 - 准备安装配置mha - 安装配置mha - 验证是否成功完成 - mysqltools备份相关的实现细节 - 实施备份计划的前期准备 - 配置备份 - 检查配置是否成功的几个点 - 注意事项 - [开发中] - 目前已经实现的监控项 - 监控项的人肉使用方法 - zabbix监控环境介绍 - zabbix监控环境安装规划 - 安装用于保存监控数据的mysql数据库 - 改配置文件中zabbixserverip这个配置项 - 安装httpd - 安装zabbix服务端 - 安装zabbix客户端 - 通过mysqltools中给出的模板来监控mysql - mysqltools中定义的MySQL的监控模板 - 安装mysql单机 - 安装python - 安装nginx - 查看效果如果你使用 mysql-8.0 那么 https://github.com/Neeky/dbm-agent 在功能上会更加强大
概要
总的来说mysqltools源自于工作、一个dba的日常大概包括 数据库安装，读写分离、高可用、负载均衡等环境的配置， 数据库备份策略拟定与实施，数据库相关的监控，数据库优化，故障分析，也有可能参与到数据库建模，SQL的编写。 这样我们就面临两个问题 1、质量 质量表现在解决问题的深度(类似问题还会再出现吗？) 2、效率 效率表现在你单位时间内解决问题的数量(安装一百个库的用时是一个库的100倍 吗？)； 通常这两个目标并不是互斥的，也就是说我们可以两个都做到。
---
- ## 质量
KFC根据既定的流程生产每一个汉堡，假设这个流程下公众对汉堡给出的评分是80分，那么不管哪个KFC的店它生产出来的汉堡都稳定在80分；一段时间后它发现这个流程中可以改进的项，把汉堡的质量提升到81分，那么它就能做到所有的店里的汉堡都能打81分。
学校后街的蛋炒饭 好不好吃这个事难说；因为好多事都影响到它，有可能老板今天心情不好，也有可能是今天客人太多他比较急，这些都会影响到炒饭的质量。有一次我要买两盒，由于去的比较晚，老板只有一个鸡蛋了，你没有猜错！ 他就只放了一个蛋，按常理是要一盒一个的。
表面上看KFC 流程化生产的好处在于它的东西质量有保障，最要命的是KFC只做加法，它可以不断提升自己，学校后街的蛋炒饭上周一，做的好吃，我们没办法确认那是不是超水平发挥，蛋炒饭质量的方差太大了。
对于DBA来说可以专门针对自己的日常工作开发一款工具，这样做的好处有 1:)由于工具已经把流程固定下来了所以“产出的质量”有保证 2:)随着自己技术的进步自己工作的输出也可以稳步提高。 这样我们在质量这个目标上就只做加法了。 ---
- ## 效率
流水线相对于手工作坊，那是生产力的巨大提升。我为什么要说这个？因为在MySQL的使用中可能会遇到一些场景，比如说“分库分表”，“高可用+读写分离”；特别是前者通常就是一个MyCAT后面有好几十个分片，上百个MySQL实例(通常它们会为一个分片做一主两从并加上高可用)，装100+个MySQL今晚加班不？ 配100+个主从今晚加班不？ 不要忘记还要给它们加 高可用呢？ 好吧这只是测试环境生产环境和测试环境是1:1的，那接下来几天加班不？ 对于生产通常还要加备份，监控那接下来几天加班不？
DBA的工具不应该只是能输出高质量的交付物，更应该要解放生产力----有批量管理的能力。
---
- ## 经济
---
- ## 技术介绍
2、mysqltools的高效源自于ansible这个批量管理工具
3、1 , 2 基本上解决了原生MySQL的环境(单机、master -->slave、mysql-group-replication 、multi-source-replication)的安装部署
4、mysqltools在为MySQL做高可用时采用的是MHA这个方案，读写分离用的是MyCAT
5、mysqltools在备份时支持到了 xtrabackup,mysql enterprise backup,mysqldump
6、mysqltools在监控MySQL时用的是zabbix
7、其它开源工具无法满足的功能通过自己编写程序实现。由于这个程序的许多功能是通用的，所以我把它单独了出来成为一个新的开源项目，这样它就与mysqltools解耦了。详见mysqltools-python
mysqltools的定位是一个集中化管理平台你只要在一台主机上安装好mysqltools就可以了，其它主机作为被管理都的角色。由于mysqltools是基于Python-3.x开发 出来的所以你的主控机上应该事先安装好python-3.x、还要安装上ansible。 好在mysqltools已经包含有所有Python和ansible所有的包。
---
安装mysqltools
假设我们有如下一套环境、把172.16.192.131这台主机作为主控机. 角色 | ip地址 | 系统版本 | -----------:|:-------------------|--------------| 主控机 | 172.16.192.131 |centos-7.4 | 被控机 | 172.16.192.132 |centos-7.4 | ... | ... |centos-7.x |
---
1. ### 安装前的准备 1): 你的主控机上要配置有yum、因为mysqltools要源码编译安装Python-3.6.2、这就涉及gcc ... 等依赖 2): 有主控机的root账号(安装软件时会用到) 3): 被控机上也要配置好yum
---
2. ### 下载并解压 mysqltools是开源在github上的、下载地址如下：https://github.com/Neeky/mysqltools/archive/master.zip linux可以直接执行如下命令完成下载并解压到/usr/local/
cd /tmp/ wget https://github.com/Neeky/mysqltools/archive/master.zip & ll -h /tmp/ -rwxr-xr-x. 1 root root 194M 3月 23 11:52 master.zip unzip master.zip mv mysqltools-master /usr/local/mysqltools3. ### 安装Python mysqltools包含了Python的自动化安装脚本、前提是yum已经可用
cd /usr/local/mysqltools/deploy/packages/python/ bash install.shCollecting setuptools Collecting pip Installing collected packages: setuptools, pip Successfully installed pip-9.0.1 setuptools-28.8.0source /etc/profile python3 --version Python 3.6.2source /etc/profile cd /usr/local/mysqltools/deploy/packages/ansible bash install.shUsing /usr/local/python-3.6.2/lib/python3.6/site-packages Finished processing dependencies for ansible==2.4.0.05. ### 配置ansible和mysqltools 1): 增加ansible的配置文件
# 增加ansible的配置文件
mkdir -p /etc/ansible
touch /etc/ansible/hostshost131 ansibleuser=root ansible_host=172.16.192.131
host132 ansibleuser=root ansible_host=172.16.192.132---
2): 配置主控机与被控机之间的ssh信任
ssh-keygen
ssh-copy-id root@172.16.192.131
ssh-copy-id root@172.16.192.132ssh-keygen # 一直回车就能生成钥匙对了
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
SHA256:D9kR6/ehu5O99p/LRJlZWNqwZ0tzU4+jvPegq7j/Pq8 root@studio2018
The keys randomart image is:
+---[RSA 2048]----+
| . . o|
| o Oo|
| o *+B|
| + o ..+X|
| S o + .* |
| o . +.. |
| . oo+. |
| . ++=o.|
| ooo+EOo**|
+----[SHA256]-----+
ssh-copy-id root@172.16.192.131 # 回答yes、然后输入目标主机的root密码
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/root/.ssh/id_rsa.pub"
The authenticity of host '172.16.192.131 (172.16.192.131)' can't be established.
ECDSA key fingerprint is SHA256:qdoqi3B2aqO3ssOIphwOiWLywSlAoflX2YH+LCG7T/E.
ECDSA key fingerprint is MD5:8f:78:6e:20:ab:d0:2a:6b:c0:1a:e5:09:ac:82:7d:04.
Are you sure you want to continue connecting (yes/no)?
root@172.16.192.131's password:
Number of key(s) added: 1
Now try logging into the machine, with: "ssh 'root@172.16.192.131'"
and check to make sure that only the key(s) you wanted were added.
.... ....3): 测试ansible是否配置成功
ansible -m ping host_132 host_132 | SUCCESS => { "changed": false, "failed": false, "ping": "pong" }4): 配置mysqltools mysqltools的配置文件是mysqltools/config.yaml 它是一个yaml格式的文件；配置项中最基本的有mtlsbasedir、mysqlpackagesdir、mysql_package 1、mtlsbasedir用于配置mysqltools的安装路径：在下载并解压这个步骤中我们把mysqltools解压到了/usr/local/、所以mtlsbasedir的值就应该等于"/usr/local/mysqltools/"
--- 2、mysqlpackagesdir用于配置MySQL二进制安装包保存的位置：MySQL的安装包有600+MB、出于体量的原因mysqltools并没有直接打包MySQL的二进制安装包、而是留有mysqlpackagesdir这个配置项，mysqltools会从这个目录中去找MySQL的二进制安装包。
--- 3、mysqlpackage用于配置MySQL安装包的名字、有这个变量的因为是为了，可以做到有多个不同的MySQL的版本共存、默认值为 mysql-5.7.22-linux-glibc2.12-x8664.tar.gz ---
config.yaml的关键内容大致如下：
mtlsbasedir: /usr/local/mysqltools/ mysqlpackagesdir: /usr/local/src/mysql/ mysqlpackage: mysql-5.7.21-linux-glibc2.12-x8664.tar.gz--- 如果你正确的完成了mysqltools相关的配置那么config.yaml看起来就应该是这样的
--- #----------------------------------mysqltools全局配置文件--------------------- # section 1 #mysqltools所在的目录 mtlsbasedir: /usr/local/mysqltools/ # #mysqltool自带的各类软件的安装文件所在路径(相对路径) mtls_packages: deploy/packages/ # #mysqltool自带的python脚本、下发到被控主机时所保存的路径 mtlsclientbase_dir: /usr/local/ # section 2 #mysqltools自带的各类软件安装文件 的全名、设置这些变量的作用是方便版本共存、mysql不在这里设置是因为 # #mysql的安装包太大了，mysqltools并没有把它打包进来 mtls_apr: apr-1.6.2.tar.gz mtlsaprutil: apr-util-1.6.0.tar.gz mtls_httpd: httpd-2.4.28.tar.gz mtls_php: php-5.6.31.tar.gz #mtls_zabbix: zabbix-3.4.3.tar.gz mtls_zabbix: zabbix-4.0.0.tar.gz mtls_python: python-3.6.2.tar.xz mtlsmysqlconnector_python: mysql-connector-python-2.1.5.tar.gz mtls_mycat: mycat-server-1.6.5-linux.tar.gz mtlsmhanode: mhanode.tar.gz mtlsmhamanager: mhamanager.tar.gz mtls_git: git-2.9.5.tar.gz mtls_nginx: nginx-1.13.7.tar.gz mtls_sysbench: sysbench-1.1.0.tar.gz mtls_meb: meb-4.1.0-linux-glibc2.5-x86-64bit.tar.gz mtlsxtrb: percona-xtrabackup-2.4.9-Linux-x8664.tar.gz mtls_mysqlclient: mysqlclient-1.3.12.tar.gz mtls_pytz: pytz-2018.4.tar.gz mtls_django: django-2.0.4.tar.gz mtls_uwsgi: uwsgi-2.0.17.tar.gz #mysql与php-5.6.x 是否要同时安装在一台主机上、如果是就要把这个设置成yes、以为php导出mysqclient_r.so文件 mtlswithphp: 1 #通过ansible在被控机上安装python-3.x的时候，是否自动安装好mysql-connector-python mtlswithmysqlconntorpython: 1 #是否给mysql用户加密码 mtlsmakemysql_secure: 1 # mtlswithmysqlgroupreplication: 0 #----------------------------------mysqltools全局配置文件--------------------- #### #### mysql 相关的配置 #### #mysql 安装包所在的目录 mysqlpackagesdir: /usr/local/src/mysql/ #mysql 安装包的名字 #mysqlpackage: mysql-5.6.40-linux-glibc2.12-x8664.tar.gz #mysqlpackage: mysql-5.7.23-linux-glibc2.12-x8664.tar.gz mysqlpackage: mysql-8.0.13-linux-glibc2.12-x8664.tar.xz #linux 系统级别mysql用户相关信息 mysql_user: mysql mysql_group: mysql mysqluseruid: 3306 mysqlusergid: 3306 #mysql 安装目录 mysqlbasedir: /usr/local/mysql/ #mysql 真正的datadir就会是mysqldatadirbase+mysqlport mysqldatadir_base: /database/mysql/data/ mysql_port: 3306 mysqlrootpassword: mtls0352 mysqlzabbixpassword: mtls mysqlrpleuser: repl mysqlrplepassword: repl0352 mysqlmhauser: mha mysqlmhapassword: mtls0352 mysqlappuser: appuser mysqlapppassword: mtls0352 mysqlmonitoruser: monitor mysqlmonitorpassword: monitor0352 mysqlbackupuser: backup mysqlbackuppassword: DX3906 #mysql 配置文件模版 mysqlbinlogformat: row mysqlinnodblogfilesin_group: 16 mysqlinnodblogfilesize: 256M mysqlinnodblogbuffersize: 64M mysqlinnodbopen_files: 65535 mysqlmaxconnections: 1000 mysqlthreadcache_size: 256 mysqlsyncbinlog: 1 mysqlbinlogcache_size: 64K mysqlinnodbonlinealterlogmaxsize: 128M mysqlperformanceschema: 'on' usewriteset: 1 #mysql #### #### zabbix 相关的配置 ##### zabbixserverip: 172.16.192.1315): 下载MySQL 根据上面的配置可以知道MySQL的安装包要保存到/usr/local/src/mysql/目录下、包的版本为mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz 下载地址如下：https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz
cd /usr/local/src/mysql/ wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.21-linux-glibc2.12-x86_64.tar.gzmysqltools快速开始
mysqltools的目录组织是根据它的功能来定的 1. ### mysqltools目录介绍cd /usr/local/mysqltools/
tree ./
├── README.md // 文档
├── config.yaml // 为Mac生成的配置文件(你可能用不着我的开发机是Mac)
├── config.yaml-for-linux // 专为linux生成的个配置文件
├── deploy // 自动化安装与配置的所有实现
├── docs // 文件资源
├── trashCan // 垃圾文件
└── tuning // DBA常用的SQL脚本有助于快速解决问题2. ### 自动化mysql单实例安装 mysqltools/deploy/ansible/ 目录下的每一个子目录都对应一类软件环境的自动化安装、我们这次是要安装MySQL所以应该进入到mysql子目录
--- 1. #### 进入mysql功能目录
cd /usr/local/mysqltools/deploy/ansible/mysql/ ll总用量 24 drwxr-xr-x. 2 root root 4096 3月 19 15:01 common # 通用组件 -rw-r--r--. 1 root root 836 3月 19 15:01 installgroupreplication.yaml # 自动化安装mysql group replication -rw-r--r--. 1 root root 889 3月 19 15:01 installmasterslaves.yaml # 自动化安装mysql 主从复制环境 -rw-r--r--. 1 root root 924 3月 19 15:01 installmultisource_replication.yaml # 自动化安装mysql 多源复制 -rw-r--r--. 1 root root 772 3月 26 13:20 installsinglemysql.yaml # 自动化安装mysql 单机实例 drwxr-xr-x. 3 root root 203 3月 19 15:01 template # 通用模板 -rw-r--r--. 1 root root 892 3月 19 15:01 upgradsinglemysql.yaml # 自动化升级MySQL(不推荐) drwxr-xr-x. 2 root root 99 3月 19 15:01 vars # 自定义变量(重要!) -rw-r--r-- 1 root root 99 3月 19 15:01 uninstall.yaml # 专门用来删库的(无力回天的那种删除)installsinglemysql.yaml的前几行大致如下
---
- hosts: cstudio
remote_user: root
become_user: yes
vars_files:
- ../../../config.yaml
tasks:
- name: create user and config file
importtasks: common/createuserandconfig_file.yaml假设我们要在host132这台主机上安装单机的MySQL所以我们要把cstudio改成host132、注意这里的host132引用的是/etc/ansible/hosts文件、修改后的installsingle_mysql.yaml文件的前几行大致如下
---
- hosts: host_132
remote_user: root
become_user: yes
vars_files:
- ../../../config.yaml
tasks:
- name: create user and config file
importtasks: common/createuserandconfig_file.yamlansible-playbook installsinglemysql.yamlPLAY [host_132] *
TASK [Gathering Facts] **
ok: [host_132]
TASK [create mysql user]
changed: [host_132]
TASK [create and config /etc/my.cnf] **
changed: [host_132]
TASK [install libaio-devel] *
ok: [host_132]
TASK [install numactl-devel] **
ok: [host_132]
TASK [transfer mysql install package to remote host and unarchive to /usr/local/] *
changed: [host_132]
TASK [change owner to mysql user] *
changed: [host_132]
TASK [make link /usr/local/mysql-xx.yy.zz to /usr/local/mysql] **
changed: [host_132]
TASK [export mysql share object (.os)]
ok: [host_132]
TASK [load share object]
changed: [host_132]
TASK [export path env variable] *
ok: [host_132]
TASK [export path env to /root/.bashrc] *
ok: [host_132]
TASK [make link /usr/local/mysql-xx.yy.zz to /usr/local/mysql] **
ok: [host_132]
TASK [create libmysqlclient_r.so file for php-5.6] **
changed: [host_132]
TASK [create datadir] *
changed: [host_132]
TASK [initialize-insecure]
changed: [host_132]
TASK [create systemd config file] *
changed: [host_132]
TASK [start mysql(sytemctl)] **
changed: [host_132]
TASK [config mysql.service start up on boot]
ok: [host_132]
TASK [config sysv start script] *
skipping: [host_132]
TASK [start mysql(service)] *
skipping: [host_132]
TASK [config mysql.service start up on boot]
skipping: [host_132]
TASK [transfer sleep script to /tmp/] *
changed: [host_132]
TASK [sleep 15 seconds] *
changed: [host_132]
TASK [remove /tmp/sleep_15.sh]
changed: [host_132]
TASK [transfer sql statement to remonte]
changed: [host_132]
TASK [make mysql secure]
changed: [host_132]
TASK [remove temp file /tmp/makemysqlsecure.sql] **
changed: [host_132]
PLAY RECAP
host_132 : ok=25 changed=17 unreachable=0 failed=0测试MySQL是否安装成功
mysql -uroot -pmtls0352mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.7.21-log MySQL Community Server (GPL) Copyright (c) 2000, 2018, 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>3. ### 谈谈mysqltools如何实现高效 从上面的自动化mysql单实例安装来看自动化安装的过程中我们只执行了3个命令
cd vim ansible-playbook高效的关键有两点：
1):自动化安装的方式相对于人肉来说要快很多
2):支持批量操作也就是说可以同时在多台主机上执行、只要在hosts: 变量的值是一个主机组就行了
---
4. ### 谈谈mysqltools如何实现高质量
1、mysqltools是流程化的，如上面的安装MySQL就包含20+的小步骤，尽量做到面面俱到
---
2、mysqltools尽可能的在各个小步骤中都保持高的质量、比如/etc/my.cnf各个参数的配置都会根据主机当前的cpu & 内存进行配置
---
以下是一个host_132的/etc/my.cnf 这些都是由mysqltools针对单机环境动态生成的
[mysql]
auto-rehash
[mysqld]
####: for global
user =mysql # mysql
basedir =/usr/local/mysql/ # /usr/local/mysql/
datadir =/database/mysql/data/3306 # /usr/local/mysql/data
server_id =653 # 0
port =3306 # 3306
charactersetserver =utf8 # latin1
explicitdefaultsfor_timestamp =off # off
log_timestamps =system # utc
socket =/tmp/mysql.sock # /tmp/mysql.sock
read_only =0 # off
skipnameresolve =1 # 0
autoincrementincrement =1 # 1
autoincrementoffset =1 # 1
lowercasetable_names =1 # 0
securefilepriv = # null
openfileslimit =65536 # 1024
max_connections =256 # 151
threadcachesize =128 # 9
tableopencache =4096 # 2000
tabledefinitioncache =2000 # 1400
tableopencache_instances =32 # 16
####: for binlog
binlog_format =row # row
log_bin =mysql-bin # off
binlogrowsquerylogevents =on # off
logslaveupdates =on # off
expirelogsdays =7 # 0
binlogcachesize =65536 # 65536(64k)
binlog_checksum =none # CRC32
sync_binlog =1 # 1
slave-preserve-commit-order =ON #
####: for error-log
log_error =err.log # /usr/local/mysql/data/localhost.localdomain.err
general_log =off # off
generallogfile =general.log # hostname.log
####: for slow query log
slowquerylog =on # off
slowquerylog_file =slow.log # hostname.log
logqueriesnotusingindexes =on # off
longquerytime =2.000000 # 10.000000
####: for gtid
gtidexecutedcompression_period =1000 # 1000
gtid_mode =on # off
enforcegtidconsistency =on # off
####: for replication
skipslavestart =0 #
masterinforepository =table # file
relayloginfo_repository =table # file
slaveparalleltype =logicalclock # database | LOGICALCLOCK
slaveparallelworkers =4 # 0
rplsemisyncmasterenabled =1 # 0
rplsemisyncslaveenabled =1 # 0
rplsemisyncmastertimeout =1000 # 1000(1 second)
pluginloadadd =semisync_master.so #
pluginloadadd =semisync_slave.so #
binloggroupcommitsyncdelay =500 # 500(0.05%秒)、默认值0
binloggroupcommitsyncnodelaycount = 13 # 0
####: for innodb
defaultstorageengine =innodb # innodb
defaulttmpstorage_engine =innodb # innodb
innodbdatafile_path =ibdata1:64M:autoextend # ibdata1:12M:autoextend
innodbtempdatafilepath =ibtmp1:12M:autoextend # ibtmp1:12M:autoextend
innodbbufferpoolfilename =ibbufferpool # ibbuffer_pool
innodbloggrouphomedir =./ # ./
innodblogfilesingroup =8 # 2
innodblogfile_size =128M # 50331648(48M)
innodbfileper_table =on # on
innodbonlinealterlogmax_size =128M # 134217728(128M)
innodbopenfiles =65535 # 2000
innodbpagesize =16k # 16384(16k)
innodbthreadconcurrency =0 # 0
innodbreadio_threads =4 # 4
innodbwriteio_threads =4 # 4
innodbpurgethreads =4 # 4
innodbprintall_deadlocks =on # off
innodbdeadlockdetect =on # on
innodblockwait_timeout =50 # 50
innodbspinwait_delay =6 # 6
innodbautoinclock_mode =2 # 1
innodbiocapacity =200 # 200
innodbiocapacity_max =2000 # 2000
#--------Persistent Optimizer Statistics
innodbstatsauto_recalc =on # on
innodbstatspersistent =on # on
innodbstatspersistentsamplepages =20 # 20
innodbbufferpool_instances =1
innodbadaptivehash_index =on # on
innodbchangebuffering =all # all
innodbchangebuffermaxsize =25 # 25
innodbflushneighbors =1 # 1
#innodbflushmethod = #
innodb_doublewrite =on # on
innodblogbuffer_size =128M # 16777216(16M)
innodbflushlogattimeout =1 # 1
innodbflushlogattrx_commit =1 # 1
innodbbufferpool_size =1152M # 134217728(128M)
autocommit =1 # 1
#--------innodb scan resistant
innodboldblocks_pct =37 # 37
innodboldblocks_time =1000 # 1000
#--------innodb read ahead
innodbreadahead_threshold =56 # 56 (0..64)
innodbrandomread_ahead =OFF # OFF
#--------innodb buffer pool state
innodbbufferpooldumppct =25 # 25
innodbbufferpooldumpat_shutdown =ON # ON
innodbbufferpoolloadat_startup =ON # ON
#### for performance_schema
performance_schema =on # on
performanceschemaconsumerglobalinstrumentation =on # on
performanceschemaconsumerthreadinstrumentation =on # on
performanceschemaconsumereventsstages_current =on # off
performanceschemaconsumereventsstages_history =on # off
performanceschemaconsumereventsstageshistorylong =off # off
performanceschemaconsumerstatementsdigest =on # on
performanceschemaconsumereventsstatements_current =on # on
performanceschemaconsumereventsstatements_history =on # on
performanceschemaconsumereventsstatementshistorylong =off # off
performanceschemaconsumereventswaits_current =on # off
performanceschemaconsumereventswaits_history =on # off
performanceschemaconsumereventswaitshistorylong =off # off
performance-schema-instrument ='memory/%=COUNTED'联系我
赞助/交流/技术支持
如果我分享的知识对你有用、可以打赏任意金额；这些也是我花了许多业余时间才写出来的，也许可以请我喝一杯咖啡。如果你需要一名数据库建模、运维方面的专家也可也联系我、当然技术方面的交流也是欢迎的。
---
mysql原生环境安装
原生环境安装主要包含 1、MySQL单机 2、主从复制 3、多源复制 4、组复制(mysql-group-replication) 如果这是你第一次使用mysqltools可以从mysqltools快速开始章节开启你的mysqltools之旅---
1. ### mysql单机 1):进入mysql功能目录
cd /usr/local/mysqltools/deploy/ansible cd mysql #进入mysql功能目录---
2):指定installsinglemysql.yaml中的目标主机
假设我要在host132主机上安装mysql那么installsinglemysql.yaml文件中的hosts变量应该设置为host132
--- - hosts: host_132---
3):执行自动化安装
ansible-playbook installsinglemysql.yaml---
4):验证mysql是否成功安装
mysql -uroot -pmtls0352mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.21-log MySQL Community Server (GPL) Copyright (c) 2000, 2018, 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>---
2. ### mysql主从复制 假设我们要在10.186.19.15，10.186.19.16，10.186.19.17三台主机上建设一个一主两从的主从复制环境，其中10.186.19.15为主库
---
1):增加主机信息到/etc/ansible/hosts
向/etc/ansible/hosts文件中增加如下内容
[repl] replmaster15 ansible_host=10.186.19.15 replslave16 ansible_host=10.186.19.16 replslave17 ansible_host=10.186.19.17---
2):进入mysql功能目录
cd /usr/local/mysqltools/deploy/ansible cd mysql #进入mysql功能目录---
3):指定installmasterslaves.yaml中的目标主机
假设我要在repl主机组上安装mysql复制环境那么installmasterslaves.yaml文件中的hosts变量应该设置为repl
--- - hosts: repl#在创建一主多从环境时会用到的变量 master_ip: 10.186.19.15 slave_ips: - 10.186.19.16 - 10.186.19.17---
4):执行自动化安装
ansible-playbook installmasterslaves.yamlPLAY [repl] * TASK [Gathering Facts] ok: [replmaster15] ok: [replslave16] ok: [replslave17] ... ... ... ... ... ... 省略 ... ... ... ... ... ... PLAY RECAP replmaster15 : ok=28 changed=18 unreachable=0 failed=0 replslave16 : ok=28 changed=19 unreachable=0 failed=0 replslave17 : ok=28 changed=19 unreachable=0 failed=0---
5):检查主从复制环境是否配置完成
mysql -uroot -pmtls0352 show slave status \G 1. row SlaveIOState: Waiting for master to send event Master_Host: 10.186.19.15 Master_User: rple Master_Port: 3306 Connect_Retry: 60 MasterLogFile: mysql-bin.000002 ReadMasterLog_Pos: 595 RelayLogFile: actionsky16-relay-bin.000002 RelayLogPos: 800 RelayMasterLog_File: mysql-bin.000002 SlaveIORunning: Yes SlaveSQLRunning: Yes ... ... ... ... ... ... ... ... RetrievedGtidSet: 8b5ac555-37ec-11e8-b50e-5a3fdb1cf647:1-2 ExecutedGtidSet: 8b5ac555-37ec-11e8-b50e-5a3fdb1cf647:1-2 Auto_Position: 1 ReplicateRewriteDB: Channel_Name: MasterTLSVersion:3. ### mysql多源复制
假设我们要在10.186.19.15，10.186.19.16，10.186.19.17这三台机器上搭建两主1从的多源复制环境、其中15，16两机器上的数据向17同步
---
1):增加主机信息到/etc/ansible/hosts
向/etc/ansible/hosts文件中增加如下内容
[repl] replmaster15 ansible_host=10.186.19.15 replslave16 ansible_host=10.186.19.16 replslave17 ansible_host=10.186.19.17---
2):进入mysql功能目录
cd /usr/local/mysqltools/deploy/ansible cd mysql #进入mysql功能目录---
3):指定installmultisource_replication.yaml中的目标主机
假设我要在repl主机组上安装mysql复制环境那么installmultisource_replication.yaml文件中的hosts变量应该设置为repl
--- - hosts: repl#master_ips 定义多个master主机ip组成的列表 master_ips: - '10.186.19.15' - '10.186.19.16' #定义slave的ip slave_ip: '10.186.19.17'---
4):执行自动化安装
ansible-playbook installmultisource_replication.yaml---
5):检测多源复制环境是否安装成功
mysql -uroot -pmtls0352 show slave status \G 1. row SlaveIOState: Waiting for master to send event Master_Host: 10.186.19.15 MasterUser: rpleuser Master_Port: 3306 Connect_Retry: 60 MasterLogFile: mysql-bin.000002 ReadMasterLog_Pos: 150 RelayLogFile: actionsky17-relay-bin-master1.000002 RelayLogPos: 355 RelayMasterLog_File: mysql-bin.000002 SlaveIORunning: Yes SlaveSQLRunning: Yes 2. row SlaveIOState: Waiting for master to send event Master_Host: 10.186.19.16 MasterUser: rpleuser Master_Port: 3306 Connect_Retry: 60 MasterLogFile: mysql-bin.000002 ReadMasterLog_Pos: 150 RelayLogFile: actionsky17-relay-bin-master2.000002 RelayLogPos: 355 RelayMasterLog_File: mysql-bin.000002 SlaveIORunning: Yes SlaveSQLRunning: Yes---
4. ### mysql组复制 假设我们要在10.186.19.15，10.186.19.16，10.186.19.17这三台机器上搭建一个group replication 环境
---
1):增加主机信息到/etc/ansible/hosts
向/etc/ansible/hosts文件中增加如下内容
[repl] mgr15 ansible_host=10.186.19.15 mgr16 ansible_host=10.186.19.16 mgr17 ansible_host=10.186.19.17---
2):进入mysql功能目录
cd /usr/local/mysqltools/deploy/ansible cd mysql #进入mysql功能目录---
3):指定installgroupreplication.yaml中的目标主机
假设我要在repl主机组上安装mysql复制环境那么installgroupreplication.yaml文件中的hosts变量应该设置为repl
--- - hosts: replmtlswithmysqlgroupreplication: 1 mysqlbinlogformat: row mysqlmgrport: 13306 mysqlmgrhosts: - '10.186.19.15' - '10.186.19.16' - '10.186.19.17'---
4):执行自动化安装
ansible-playbook installgroupreplication.yamlPLAY [repl] * TASK [Gathering Facts] ok: [mgr15] ok: [mgr16] ok: [mgr17] ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... PLAY RECAP mgr15 : ok=28 changed=19 unreachable=0 failed=0 mgr16 : ok=28 changed=18 unreachable=0 failed=0 mgr17 : ok=28 changed=18 unreachable=0 failed=0--- 5):检查group replication集群是否安装成功
mysql -uroot -pmtls0352 select * from performanceschema.replicationgroup_members ;+---------------------------+--------------------------------------+-------------+-------------+--------------+ | CHANNELNAME | MEMBERID | MEMBERHOST | MEMBERPORT | MEMBER_STATE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ | groupreplicationapplier | 08de362c-3802-11e8-9e65-5a3fdb1cf647 | actionsky15 | 3306 | ONLINE | | groupreplicationapplier | ef7f3b61-3801-11e8-886d-9a17854b700d | actionsky17 | 3306 | ONLINE | | groupreplicationapplier | f1649143-3801-11e8-8fd3-5a1f0f06c50d | actionsky16 | 3306 | ONLINE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ 3 rows in set (0.00 sec)当mtlswithmysqlgroupreplication: 1这个开头打开后mysqltools会自动为/etc/my.cnf增加了group replication 相关的配置项
####: for mysql group replication loose-groupreplicationrecoveryretrycount =10 # 10 loose-groupreplicationrecoveryreconnectinterval =60 # 60 loose-groupreplicationallowlocaldisjointgtidsjoin=off # off loose-groupreplicationallowlocallowerversionjoin=off # off loose-groupreplicationip_whitelist =AUTOMATIC # AUTOMATIC loose-transactionwriteset_extraction =XXHASH64 # off loose-groupreplicationgroup_name ="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" # loose-groupreplicationstartonboot =off # off loose-groupreplicationbootstrap_group =off # off loose-groupreplicationsingleprimarymode =on # on loose-groupreplicationenforceupdateeverywhere_checks=off loose-groupreplicationgtidassignmentblock_size =1000000 # 1000000 loose-groupreplicationpollspinloops =0 # 0 loose-groupreplicationcompression_threshold =1024 # 1000000 loose-groupreplicationflowcontrolmode =QUOTA # QUOTA loose-groupreplicationlocal_address ="10.186.19.15:13306" loose-groupreplicationgroup_seeds ="10.186.19.15:13306,10.186.19.16:13306,10.186.19.17:13306"---
5. ### mysql小版本升级 mysqltools为版本升级提供了支持，如果你想使用这个功能，那么你要清楚的知道自己在干什么
---
1、小版本升级并不会去执行mysqlupgrade脚本，这个主要是由于mysqltools诞生环境比较特别，单个实例的数据量是以TB来衡量的，在这种量级的情况下mysqlupgrade可以能执行几天才能完成，所以你懂的
---
1):修改upgradsinglemysql.yaml文件的hosts 变量为你要升级的目标主机
---
- hosts: sqlstudio---
2):执行升级程序
ansible-playbook upgradsinglemysql.yamlPLAY [sqlstudio] TASK [Gathering Facts] ok: [sqlstudio] TASK [stop mysql service] * ok: [sqlstudio] TASK [backup link file] * changed: [sqlstudio] TASK [unarchive new package to /usr/local/] * changed: [sqlstudio] TASK [change owner and group] * changed: [sqlstudio] TASK [make new link file] * changed: [sqlstudio] TASK [start mysql service] ** changed: [sqlstudio] PLAY RECAP ** sqlstudio : ok=7 changed=5 unreachable=0 failed=0---
3):查看升级是否成功 1、看/usr/local/下的变化
升级前
drwxr-xr-x 9 mysql mysql 129 6月 18 14:46 mysql-5.7.21-linux-glibc2.12-x86_64 lrwxrwxrwx 1 root root 35 6月 18 14:53 mysql -> mysql-5.7.21-linux-glibc2.12-x86_64lrwxrwxrwx 1 mysql mysql 46 6月 18 15:30 mysql -> /usr/local/mysql-5.7.22-linux-glibc2.12-x86_64 drwxr-xr-x 9 mysql mysql 129 6月 18 14:46 mysql-5.7.21-linux-glibc2.12-x86_64 drwxr-xr-x 9 mysql mysql 129 6月 18 15:30 mysql-5.7.22-linux-glibc2.12-x86_64 lrwxrwxrwx 1 root root 35 6月 18 14:53 mysql.backup.20180618 -> mysql-5.7.21-linux-glibc2.12-x86_64---
2、连接进数据库进行检察
mysql -uroot -pxxxxxxmysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.22-log MySQL Community Server (GPL)
Copyright (c) 2000, 2018, 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>select @@version;
+------------+
| @@version |
+------------+
| 5.7.22-log |
+------------+
1 row in set (0.00 sec)---
读写分离
本来这里是要加入分库分表的功能的、因为用yaml难以表达mycat的相关配置、最终我还是放弃了、所以目前mysqltools还只有自动化安装配置mycat读写分离的功能---
1. ### mycat读写分离 假设我们已经有了套三结点的集群10.186.19.15，10.186.19.16，10.186.19.17，其中15支持读写，16、17只读；为了方面应用程序我们要在10.186.19.14上安装一个mycat用它做读写分离，这样应用只要配置一个连接字符串就可以了(连接mycat)
---
1):在数据库中创建用户、mycat会有这个用户连接数据库
用户名、密码引用自mysqltools/config.yaml中的mysqlappuser、mysqlapppassword 在读写库上执行如下代码
create user appuser@'%' identified by 'mtls0352'; create database appdb char set utf8; grant all on appdb.* to appuser@'%';---
2):向/etc/ansible/hosts文件增加主机信息 向/etc/ansible/hosts文件增加如下内容
mycat ansible_host=10.186.19.14---
3):更新var/var_mycat.yaml文件
告诉mysqltools那个主机是读写库(masterip)，哪些库是只读库(slaveips)，导出后端哪些schema(schemas)
master_ip: "10.186.19.15" slave_ips: - "10.186.19.16" - "10.186.19.17" schemas: - "appdb"---
4):修改install_mycat.yaml文件中的hosts
--- - hosts: mycat---
5):执行mycat的安装
ansible-playbook install_mycat.yamlPLAY [mycat] ** TASK [Gathering Facts] ok: [mycat] TASK [install java-1.7.0-openjdk] * changed: [mycat] TASK [create mycat user] changed: [mycat] TASK [trasfer mycat-server-1.6.5-linux.tar.gz to remonte host] ** changed: [mycat] TASK [export MYCAT_HOME env to /etc/profile] changed: [mycat] TASK [config schema.xml] changed: [mycat] TASK [config server.xml] changed: [mycat] TASK [transfer start_mycat.sh to remonte /tmp/] * changed: [mycat] TASK [start mycat] changed: [mycat] TASK [remove start_mycat.sh] ** changed: [mycat] PLAY RECAP mycat : ok=10 changed=9 unreachable=0 failed=0---
6):检查mycat是否启动
ps -ef | grep mycatroot 24415 1 0 09:21 ? 00:00:00 /usr/local/mycat/bin/./wrapper-linux-x86-64 /usr/local/mycat/conf/wrapper.conf wrapper.syslog.ident=mycat wrapper.pidfile=/usr/local/mycat/logs/mycat.pid wrapper.daemonize=TRUE wrapper.lockfile=/var/lock/subsys/mycat root 24417 24415 12 09:21 ? 00:00:06 java -DMYCATHOME=. -server -XX:MaxPermSize=64M -XX:+AggressiveOpts -XX:MaxDirectMemorySize=2G -Dcom.sun.management.jmxremote -Dcom.sun.management.jmxremote.port=1984 -Dcom.sun.management.jmxremote.authenticate=false -Dcom.sun.management.jmxremote.ssl=false -Xmx4G -Xms1G -Djava.library.path=lib -classpath lib/wrapper.jar:conf:lib/zookeeper-3.4.6.jar:lib/jline-0.9.94.jar:lib/ehcache-core-2.6.11.jar:lib/log4j-1.2.17.jar:lib/fastjson-1.2.12.jar:lib/curator-client-2.11.0.jar:lib/joda-time-2.9.3.jar:lib/log4j-slf4j-impl-2.5.jar:lib/libwrapper-linux-x86-32.so:lib/netty-3.7.0.Final.jar:lib/druid-1.0.26.jar:lib/log4j-api-2.5.jar:lib/mapdb-1.0.7.jar:lib/slf4j-api-1.6.1.jar:lib/univocity-parsers-2.2.1.jar:lib/hamcrest-core-1.3.jar:lib/objenesis-1.2.jar:lib/leveldb-api-0.7.jar:lib/hamcrest-library-1.3.jar:lib/wrapper.jar:lib/commons-lang-2.6.jar:lib/reflectasm-1.03.jar:lib/mongo-java-driver-2.11.4.jar:lib/guava-19.0.jar:lib/curator-recipes-2.11.0.jar:lib/curator-framework-2.11.0.jar:lib/libwrapper-linux-ppc-64.so:lib/log4j-core-2.5.jar:lib/mysql-binlog-connector-java-0.6.0.jar:lib/netty-common-4.1.9.Final.jar:lib/leveldb-0.7.jar:lib/sequoiadb-driver-1.12.jar:lib/kryo-2.10.jar:lib/jsr305-2.0.3.jar:lib/commons-collections-3.2.1.jar:lib/mysql-connector-java-5.1.35.jar:lib/disruptor-3.3.4.jar:lib/log4j-1.2-api-2.5.jar:lib/velocity-1.7.jar:lib/Mycat-server-1.6.5-release.jar:lib/libwrapper-linux-x86-64.so:lib/dom4j-1.6.1.jar:lib/minlog-1.2.jar:lib/asm-4.0.jar:lib/netty-buffer-4.1.9.Final.jar -Dwrapper.key=sexYToWnzGO4Glh1 -Dwrapper.port=32000 -Dwrapper.jvm.port.min=31000 -Dwrapper.jvm.port.max=31999 -Dwrapper.pid=24415 -Dwrapper.version=3.2.3 -Dwrapper.nativelibrary=wrapper -Dwrapper.service=TRUE -Dwrapper.cpu.timeout=10 -Dwrapper.jvmid=1 org.tanukisoftware.wrapper.WrapperSimpleApp io.mycat.MycatStartup start---
7):测试mycat的可用性
mysql -uappuser -pmtls0352 -h10.186.19.14 -P8066mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.7.200-mycat-1.6.5-release-20171117203123 MyCat Server (OpenCloundDB) Copyright (c) 2000, 2018, 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> show databases; +----------+ | DATABASE | +----------+ | appdb | +----------+ 2 rows in set (0.00 sec)
create table person( id int auto_increment primary key,name varchar(16)); Query OK, 0 rows affected (0.02 sec)
insert into person(name) values('welson'); Query OK, 1 row affected (0.05 sec)
select * from person; +----+--------+ | id | name | +----+--------+ | 1 | welson | +----+--------+
高可用
目前mysql高可用的开源解决方案中比较成熟的应该算mha了、相信不久的将来innodb cluster将会成为主流；mysqltools会把两者都包涵进来---
- ## mha
mysqltools在安装mha时不需要连接外网，主要是因为它把mha用到的包都打包了进来；不幸的是目前我只打包了centos-7.4版本下的包；由于时间上的成本其它系统版本我就没有打包了
---
1. ### 环境规划 130、131、132三个实例组成一个mysql复制环境、其中130是master |主机名 | ip地址 | 操作系统版本 | mysql角色| mha角色| vip | |---------|----------------|------------|---------|--------|------------------| |mhamaster| 192.168.29.130 | centos-7.4 | master | node |192.168.29.100 | |mhaslave1| 192.168.29.131 | centos-7.4 | slave | node | | |mhaslave2| 192.168.29.132 | centos-7.4 | slave | manager| |
---
2. ### 配置mha的一些前置备件 1、已经规范的安装配置了mysql复制环境、可以参考mysql主从复制
2、各个主机之间都已经完成了ssh信任(mha会采用scp的方式从宕机的master主机上采集binlog日志、所以你事先要把ssh信任关系做好)
3、在安装配置的过程中有一些组件要用到gcc编译、mysqltools会去安装gcc gcc-c++ ，所以你要事先在这些主机上配置好yum
---
3. ### 配置ansible的hosts文件 把要配置mha的几台主机作为一个组配置到/etc/ansible/hosts文件中去
[mhacluster] mhamaster ansiblehost=192.168.29.130 ansibleuser=root mhaslave1 ansiblehost=192.168.29.131 ansibleuser=root mhaslave2 ansiblehost=192.168.29.132 ansibleuser=root4. ### 配置mysqltools中mha相关的配置项 mysqltools/config.yaml配置文件中有两项、是用来指明安装mha时所用的安装包的、默认值如下；用默认值就行一定不要改、除非你知道自己在做什么。
mtlsmhanode: mhanode.tar.gz mtlsmhamanager: mhamanager.tar.gz5. ### 配置mha的相关信息 针对单个mha的详细配置都记录在了mysqltools/deploy/ansible/mha/vars/var_mha.yaml这个配置文件中了
master_ip: "192.168.29.130" slave_ips: - "192.168.29.131" - "192.168.29.132" manager_ip: "192.168.29.132" networkinterface: "ens33" vip: "192.168.29.100" os_release: '7.4'2、slave_ips 表示mysql slave库的ip地址列表
3、manager_ip 表示mha manager要安装在主机ip、mysqltools希望你用最后一个slave的ip作为manger要安装的地方、mysqltools还会把第一个slave作为新的master
4、networkinferface 表示vip要绑定到的网卡
5、vip 表示vip地址
6、os_release 表示操作系统的版本、目前还只支持"7.4"这一个rhel版本
---
6. ### 准备安装配置mha mysqltools/deploy/ansible/mha/install_mha.yaml文件中包涵了所有的步骤、修改文件中的host项以告诉ansible把这些步骤应用到哪些主机、根据上面的规划我要把hosts: 修改成如下内容
--- - hosts: mhacluster---
7. ### 安装配置mha
cd mysqltools/deploy/ansible/mha ansible-playbook install_mha.yamlREADME truncated. [View on GitHub
