阿里云RDS迁移，极简安装 MySQL TokuDB 引擎
背景
zabbix 数据库由阿里云 RDS 迁移至自建 DB，迁移过程中发现 RDS 存储引擎为 tokudb ，手把手撸一遍
前置要求 Prerequisites
- 安装 libjemalloc library
yum install jemalloc -y
通过 yum 安装，生成的库文件为 /usr/lib64/libjemalloc.so.1
[root@yunwei /www/server/data]# rpm -qa |grep jemalloc
jemalloc-3.6.0-1.el7.x86_64
[root@yunwei /www/server/data]# rpm -ql jemalloc-3.6.0-1.el7.x86_64
/usr/bin/jemalloc.sh
/usr/lib64/libjemalloc.so.1
/usr/share/doc/jemalloc-3.6.0
/usr/share/doc/jemalloc-3.6.0/COPYING
/usr/share/doc/jemalloc-3.6.0/README
/usr/share/doc/jemalloc-3.6.0/VERSION
/usr/share/doc/jemalloc-3.6.0/jemalloc.html
- Transparent huge pages 关闭内存大页
echo never > /sys/kernel/mm/transparent_hugepage/enabled
echo never > /sys/kernel/mm/transparent_hugepage/defrag
检查结果
root@ptest:~# cat /sys/kernel/mm/transparent_hugepage/enabled
always madvise [never]
root@ptest:~# cat /sys/kernel/mm/transparent_hugepage/defrag
always madvise [never]
安装仓库源
Installing Percona Server for MySQL from Percona yum repository
- Install the Percona repository
$ yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
Retrieving https://repo.percona.com/yum/percona-release-latest.noarch.rpmPreparing... ########################################### [100%]1:percona-release ########################################### [100%]
- To install Percona Server for MySQL with SElinux policies
$ yum install http://repo.percona.com/centos/7/RPMS/x86_64/Percona-Server-selinux-56-5.6.42-rel84.2.el7.noarch.rpm
- 测试仓库可用性
yum list | grep percona
...
Percona-Server-56-debuginfo.x86_64 5.6.25-rel73.1.el6 @percona-release-x86_64
Percona-Server-client-56.x86_64 5.6.25-rel73.1.el6 @percona-release-x86_64
Percona-Server-devel-56.x86_64 5.6.25-rel73.1.el6 @percona-release-x86_64
Percona-Server-server-56.x86_64 5.6.25-rel73.1.el6 @percona-release-x86_64
Percona-Server-shared-56.x86_64 5.6.25-rel73.1.el6 @percona-release-x86_64
Percona-Server-test-56.x86_64 5.6.25-rel73.1.el6 @percona-release-x86_64
Percona-Server-shared-compat.x86_64 5.1.68-rel14.6.551.rhel6 percona-release-x86_64
...
- 安装 Percona Serve
yum install Percona-Server-server-56
安装 TokuDB
You can install the Percona Server for MySQL with TokuDB engine by using the apt/yum commands:
[root@centos ~]# yum install Percona-Server-tokudb-56.x86_64
# debian 系列 OS
root@wheezy:~# apt-get install percona-server-tokudb-5.6
启用 TokuDB 引擎
Enabling the TokuDB Storage EngineOnce the TokuDB server package has been installed following output will be shown:
This release of Percona Server is distributed with TokuDB storage engine. Run the following script to enable the TokuDB storage engine in Percona Server:
ps_tokudb_admin --enable -u <mysql_admin_user> -p[mysql_admin_pass] [-S <socket>] [-h <host> -P <port>]
See http://www.percona.com/doc/percona-server/5.6/tokudb/tokudb_installation.html for more installation details
See http://www.percona.com/doc/percona-server/5.6/tokudb/tokudb_intro.html for an introduction to TokuDB
Percona Server for MySQL 5.6.22-72.0 has implemented ps_tokudb_admin script to make the enabling the TokuDB storage engine easier. This script will automatically disable Transparent huge pages, if they’re enabled, and install and enable the TokuDB storage engine with all the required plugins. You need to run this script as root or with sudo. After you run the script with required parameters:
ps_tokudb_admin --enable -uroot -pPassw0rd
Following output will be displayed:
Checking if Percona server is running with jemalloc enabled...
>> Percona server is running with jemalloc enabled.
Checking transparent huge pages status on the system...
>> Transparent huge pages are currently disabled on the system.
Checking if thp-setting=never option is already set in config file...
>> Option thp-setting=never is not set in the config file.
>> (needed only if THP is not disabled permanently on the system)
Checking TokuDB plugin status...
>> TokuDB plugin is not installed.
Adding thp-setting=never option into /etc/mysql/my.cnf
>> Successfuly added thp-setting=never option into /etc/mysql/my.cnf
Installing TokuDB engine...
>> Successfuly installed TokuDB plugin.
If the script returns no errors, TokuDB storage engine should be successfully enabled on your server. You can check it out by running:
mysql> SHOW ENGINES;
...
| TokuDB | YES | Tokutek TokuDB Storage Engine with Fractal Tree(tm) Technology | YES | YES | YES |
...
To check if all the TokuDB plugins have been installed correctly you should run:
mysql> SHOW PLUGINS;
...
| TokuDB | ACTIVE | STORAGE ENGINE | ha_tokudb.so | GPL |
| TokuDB_file_map | ACTIVE | INFORMATION SCHEMA | ha_tokudb.so | GPL |
| TokuDB_fractal_tree_info | ACTIVE | INFORMATION SCHEMA | ha_tokudb.so | GPL |
| TokuDB_fractal_tree_block_map | ACTIVE | INFORMATION SCHEMA | ha_tokudb.so | GPL |
| TokuDB_trx | ACTIVE | INFORMATION SCHEMA | ha_tokudb.so | GPL |
| TokuDB_locks | ACTIVE | INFORMATION SCHEMA | ha_tokudb.so | GPL |
| TokuDB_lock_waits | ACTIVE | INFORMATION SCHEMA | ha_tokudb.so | GPL |
| TokuDB_background_job_status | ACTIVE | INFORMATION SCHEMA | ha_tokudb.so | GPL |
...
TokuDB storage engine version can be checked with:
mysql> SELECT @@tokudb_version;
+------------------+
| @@tokudb_version |
+------------------+
| 5.6.27-76.0 |
+------------------+
1 row in set (0.00 sec)
TokuDB storage engine has the same version as Percona Server for MySQL after 5.6.26-74.0 release.
mysql 配置文件
# Percona-5.6.17, TokuDB-7.1.6，用于Zabbix数据库参考配置
# 我的服务器配置：E5-2620 * 2，64G内存，1T可用磁盘空间（建议datadir所在分区设置为xfs文件系统）
# TokuDB版本：Percona-5.6.17, TokuDB-7.1.6(插件加载模式)
#
[client]
port = 3306
socket = mysql.sock
#default-character-set=utf8
[mysql]
prompt="\u@\h \D \R:\m:\s [\d]>
#pager="less -i -n -S"
tee=/home/mysql/query.log
no-auto-rehash
[mysqld]
open_files_limit = 8192
max_connect_errors = 100000
#buffer & cache
table_open_cache = 2048
table_definition_cache = 2048
max_heap_table_size = 96M
sort_buffer_size = 2M
join_buffer_size = 2M
tmp_table_size = 96M
key_buffer_size = 8M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 32M
#innodb
#只有部分小表保留InnoDB引擎，因此InnoDB Buffer Pool设置为1G基本上够了
innodb_buffer_pool_size = 1G
innodb_buffer_pool_instances = 1
innodb_data_file_path = ibdata1:1G:autoextend
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 64M
innodb_log_file_size = 256M
innodb_log_files_in_group = 2
innodb_file_per_table = 1
innodb_status_file = 1
transaction_isolation = READ-COMMITTED
innodb_flush_method = O_DIRECT
#tokudb
malloc-lib= /usr/local/mysql/lib/mysql/libjemalloc.so
plugin-dir = /usr/local/mysql/lib/mysql/plugin/
plugin-load=ha_tokudb.so
#把TokuDB datadir以及logdir和MySQL的datadir分开，美观点，也可以不分开，注释掉本行以及下面2行即可
tokudb-data-dir = /data/mysql/zabbix_3306/tokudbData
tokudb-log-dir = /data/mysql/zabbix_3306/tokudbLog
#TokuDB的行模式，建议用 FAST 就足够了，如果磁盘空间很紧张，建议用 SMALL
#tokudb_row_format = tokudb_small
tokudb_row_format = tokudb_fast
tokudb_cache_size = 44G
#其他大部分配置其实可以不用修改的，只需要几个关键配置即可
tokudb_commit_sync = 0
tokudb_directio = 1
tokudb_read_block_size = 128K
tokudb_read_buf_size = 128K
参考
- [TokuDB Installation](https://www.percona.com/doc/percona-server/5.6/tokudb/tokudb_installation.html)
- [Installing Percona Server for MySQL on Red Hat Enterprise Linux and CentOS](https://www.percona.com/doc/percona-server/5.6/installation/yum_repo.html#yum-repo)
- [自建Percona5.7.23同步阿里云RDS(MySQL5.6)TokuDB数据库](https://yq.aliyun.com/articles/667969)
- [RDS MySQL 物理备份文件恢复到自建数据库](https://help.aliyun.com/knowledge_detail/41817.html)
