云服务器安装mysql(阿里云RDS迁移)

背景zabbix 数据库由阿里云 RDS 迁移至自建 DB,迁移过程中发现 RDS 存储引擎为 tokudb ,手把手撸一遍前置要求 Prerequisites安装 libjemalloc libraryyum 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.htmlTransparent 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 repositoryInstall the Percona repositorynbsp;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 policiesnbsp;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 Serveyum install Percona-Server-server-56安装 TokuDBYou 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 detailsSee http://www.percona.com/doc/percona-server/5.6/tokudb/tokudb_intro.html for an introduction to TokuDBPercona 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 -pPassw0rdFollowing 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)

本文出自快速备案,转载时请注明出处及相应链接。

本文永久链接: https://kuaisubeian.cc/34074.html

kuaisubeian