何可的博客

0%

【MySQL】TokuDB引擎安装及数据迁移

发表于 更新于 分类于 Valine：
本文字数： 12k 阅读时长 ≈ 11 分钟

背景

zabbix 数据库由阿里云 RDS 迁移至自建 DB，迁移过程中发现 RDS 为 tokudb 引擎

Prerequisites

libjemalloc library

1
yum install jemalloc -y

通过 yum 安装，生成的库文件为/usr/lib64/libjemalloc.so.1

1
2
3
4
5
6
7
8
9
10
[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

关闭内存大页

1
2
echo never > /sys/kernel/mm/transparent_hugepage/enabled
echo never > /sys/kernel/mm/transparent_hugepage/defrag

查看下

1
2
3
4
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

  1. Install the Percona repository

You can install Percona yum repository by running the following command as a root user or with sudo:

1
$ yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm

Output example

1
2
3
Retrieving https://repo.percona.com/yum/percona-release-latest.noarch.rpm
Preparing...                ########################################### [100%]
1:percona-release        ########################################### [100%]

To install Percona Server for MySQL with SELinux policies, you also need the Percona-Server-selinux-*.noarch.rpm package:

1
$ yum install http://repo.percona.com/centos/7/RPMS/x86_64/Percona-Server-selinux-56-5.6.42-rel84.2.el7.noarch.rpm
  1. Testing the repository

Make sure packages are now available from the repository, by executing the following command:

1
yum list | grep percona

You should see output similar to the following:

1
2
3
4
5
6
7
8
9
...
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
...
  1. Install the packages

You can now install Percona Server for MySQL by running:

1
yum install Percona-Server-server-56

install

You can install the Percona Server for MySQL with TokuDB engine by using the apt/yum commands:

1
[root@centos ~]# yum install Percona-Server-tokudb-56.x86_64

or

1
root@wheezy:~# apt-get install percona-server-tokudb-5.6

Enabling the TokuDB Storage Engine

Once 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 -p[mysql_admin_pass] [-S ] [-h -P ]

    • 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:

1
ps_tokudb_admin --enable -uroot -pPassw0rd

Following output will be displayed:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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:

1
2
3
4
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:

1
2
3
4
5
6
7
8
9
10
11
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 Version
TokuDB storage engine version can be checked with:

1
2
3
4
5
6
7
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.

my.cnf

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
# 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(插件加载模式)
# 
# created by yejr(http://imysql.com), 2014/06/24
# 
[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

innobackupex 数据迁移

前提条件

  • 部署 mysql
  • 下载 RDS 物理备份文件

数据迁移

  1. 执行如下命令，解压已下载的数据备份文件。

    说明

    • 本文以自定义路径/home/mysql/data为例，您可以根据实际情况将其替换成实际路径。
    • innobackupex解压命令需要安装qpress，您可以使用命令yum install qpress -y安装。
    目前物理备份集文件有3种格式：
  • tar 压缩包 （.tar.gz 后缀）
  • xbstream 压缩包 （.xb.gz 后缀）
  • xbstream 文件包（_qp.xb 后缀）

    说明

    2019年2月20日后创建的MySQL 5.6实例，数据备份文件的格式为xbstream文件包（_qp.xb 后缀）。

    对于tar 压缩包 （.tar.gz 后缀），使用命令：
    1
    tar -izxvf <数据备份文件名>.tar.gz -C /home/mysql/data
    对于xbstream 压缩包 （.xb.gz 后缀），使用命令：
    1
    gzip -d -c <数据备份文件名>.xb.gz | xbstream -x -v -C /home/mysql/data
    对于xbstream 文件包（_qp.xb 后缀），使用命令：
    1
    2
    3
    4
    ## 解包
    cat <数据备份文件名>_qp.xb | xbstream -x -v -C /home/mysql/data
    ## 解压
    innobackupex --decompress --remove-original /home/mysql/data

    说明 -C：指定文件要解压到的目录。可选参数，若不指定就解压到当前目录。

  1. 为避免版本问题，需修改backup-my.cnf参数，具体操作步骤如下。

    1. 执行如下命令，以文本方式编辑backup-my.cnf文件。
      1
      vi /home/mysql/data/backup-my.cnf
    2. 自建数据库不支持如下参数，需要注释掉。
      1
      2
      3
      4
      5
      6
      7
      8
      #innodb_log_checksum_algorithm
      #innodb_fast_checksum
      #innodb_log_block_size
      #innodb_doublewrite_file
      #rds_encrypt_data
      #innodb_encrypt_algorithm
      #redo_log_version
      #master_key_id
      • 如果自建数据库使用的是MyISAM引擎，和阿里云的InnoDB不兼容，需要多注释掉如下参数并增加skip-grant-tables参数：
        1
        2
        3
        #innodb_log_checksum_algorithm=strict_crc32
        #redo_log_version=1
        skip-grant-tables
      • 如果自建数据库使用的是MyIAM引擎，且对系统表进行操作时报错（存储引擎相关），请按如下操作进行存储引擎的转换：
        1
        alter engine <表名> engine=myisam;

  2. 执行如下命令，恢复解压好的备份文件。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    [root@yunwei /www/server/data]# innobackupex --defaults-file=/www/server/data/backup-my.cnf --apply-log /www/server/data/
    191226 13:42:51 innobackupex: Starting the apply-log operation

    IMPORTANT: Please check that the apply-log run completes successfully.
               At the end of a successful apply-log run innobackupex
               prints "completed OK!".

    innobackupex version 2.3.10 based on MySQL server 5.6.24 Linux (x86_64) (revision id: bd0d4403f36)
    xtrabackup: cd to /www/server/data/
    xtrabackup: This target seems to be not prepared yet.
    xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(276623660719)
    xtrabackup: using the following InnoDB configuration for recovery:
    xtrabackup:   innodb_data_home_dir = ./
    xtrabackup:   innodb_data_file_path = ibdata1:200M:autoextend
    xtrabackup:   innodb_log_group_home_dir = ./
    xtrabackup:   innodb_log_files_in_group = 1
    xtrabackup:   innodb_log_file_size = 2097152
    xtrabackup: using the following InnoDB configuration for recovery:
    xtrabackup:   innodb_data_home_dir = ./
    xtrabackup:   innodb_data_file_path = ibdata1:200M:autoextend
    xtrabackup:   innodb_log_group_home_dir = ./
    xtrabackup:   innodb_log_files_in_group = 1
    xtrabackup:   innodb_log_file_size = 2097152
    xtrabackup: Starting InnoDB instance for recovery.
    xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
    InnoDB: Using atomics to ref count buffer pool pages
    InnoDB: The InnoDB memory heap is disabled
    InnoDB: Mutexes and rw_locks use GCC atomic builtins
    InnoDB: Memory barrier is not used
    InnoDB: Compressed tables use zlib 1.2.7
    InnoDB: Using CPU crc32 instructions
    InnoDB: Initializing buffer pool, size = 100.0M
    InnoDB: Completed initialization of buffer pool
    InnoDB: Highest supported file format is Barracuda.
    InnoDB: Log scan progressed past the checkpoint lsn 276623660719
    InnoDB: Database was not shutdown normally!
    InnoDB: Starting crash recovery.
    InnoDB: Reading tablespace information from the .ibd files...
    InnoDB: Restoring possible half-written data pages 
    InnoDB: from the doublewrite buffer...
    InnoDB: Doing recovery: scanned up to log sequence number 276625422981 (94%)
    InnoDB: Starting an apply batch of log records to the database...
    InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 
    InnoDB: Apply batch completed
    InnoDB: 128 rollback segment(s) are active.
    InnoDB: Waiting for purge to start
    InnoDB: 5.6.24 started; log sequence number 276625422981

    xtrabackup: starting shutdown with innodb_fast_shutdown = 1
    InnoDB: FTS optimize thread exiting.
    InnoDB: Starting shutdown...
    InnoDB: Shutdown completed; log sequence number 276625423497
    xtrabackup: using the following InnoDB configuration for recovery:
    xtrabackup:   innodb_data_home_dir = ./
    xtrabackup:   innodb_data_file_path = ibdata1:200M:autoextend
    xtrabackup:   innodb_log_group_home_dir = ./
    xtrabackup:   innodb_log_files_in_group = 2
    xtrabackup:   innodb_log_file_size = 1048576000
    InnoDB: Using atomics to ref count buffer pool pages
    InnoDB: The InnoDB memory heap is disabled
    InnoDB: Mutexes and rw_locks use GCC atomic builtins
    InnoDB: Memory barrier is not used
    InnoDB: Compressed tables use zlib 1.2.7
    InnoDB: Using CPU crc32 instructions
    InnoDB: Initializing buffer pool, size = 100.0M
    InnoDB: Completed initialization of buffer pool
    InnoDB: Setting log file ./ib_logfile101 size to 1000 MB
    InnoDB: Progress in MB: 100 200 300 400 500 600 700 800 900 1000
    InnoDB: Setting log file ./ib_logfile1 size to 1000 MB
    InnoDB: Progress in MB: 100 200 300 400 500 600 700 800 900 1000
    InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
    InnoDB: New log files created, LSN=276625423497
    InnoDB: Highest supported file format is Barracuda.
    InnoDB: 128 rollback segment(s) are active.
    InnoDB: Waiting for purge to start
    InnoDB: 5.6.24 started; log sequence number 276625423884
    xtrabackup: starting shutdown with innodb_fast_shutdown = 1
    InnoDB: FTS optimize thread exiting.
    InnoDB: Starting shutdown...
    InnoDB: Shutdown completed; log sequence number 276625427858
    191226 13:43:28 completed OK!

    说明 请确保您的Percona XtraBackup版本正确：
    MySQL 5.6及之前的版本需要安装 Percona XtraBackup 2.3，安装指导请参见官方文档Percona XtraBackup 2.3。
    MySQL 5.7版本需要安装 Percona XtraBackup 2.4，安装指导请参见官方文档Percona XtraBackup 2.4。
    MySQL 8.0版本需要安装 Percona XtraBackup 8.0，安装指导请参见官方文档Percona XtraBackup 8.0。

参考

  • TokuDB Installation
  • Installing Percona Server for MySQL on Red Hat Enterprise Linux and CentOS
  • 自建Percona5.7.23同步阿里云RDS(MySQL5.6)TokuDB数据库
  • RDS MySQL 物理备份文件恢复到自建数据库

写在最后

关于RDS迁移后自动备份，请参考使用python实现innobackup自动化备份、清理、通知

坚持原创技术分享，您的支持将鼓励我继续创作！