mysql冷热分离的技术_数据归档，冷热数据分离

一.关于tokudb引擎

1.tokudb引擎特点

1.高压缩比，高写入性能2.在线创建索引和字段3.在线事务4.支持索引同步

2.tokudb安装步骤

1.yum install jemalloc -y

2.vim/etc/my.cnf #添加如下

[mysqld_safe]

malloc-lib=/usr/lib64/libjemalloc.so.1

3.

echo never >/sys/kernel/mm/transparent_hugepage/enabled

echo never >/sys/kernel/mm/transparent_hugepage/defrag

4.yum install Percona-Server-tokudb-57.x86_64

5.启动引擎ps-admin --enable -uroot -p

6.重启mysql服务

service mysql restart

7.

ps-admin --enable -uroot -p

8.查看引擎

执行mysql -u root -p，登录mysql，执行show engines

3.创建测试表

1.在源数据库创建表

CREATE TABLE t_purchase (

id INT UNSIGNED PRIMARY KEY,

purchase_price DECIMAL (10, 2) NOT NULL,

purchase_num INT UNSIGNED NOT NULL,

purchase_sum DECIMAL (10, 2) NOT NULL,

purchase_buyer INT UNSIGNED NOT NULL,

purchase_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

company_id INT UNSIGNED NOT NULL,

goods_id INT UNSIGNED NOT NULL,

KEY idx_company_id ( company_id ),

KEY idx_goods_id ( goods_id )

);

2.在归档数据库中创建归档表

CREATE TABLE t_purchase_201807 (

id INT UNSIGNED PRIMARY KEY,

purchase_price DECIMAL ( 10, 2 ) NOT NULL,

purchase_num INT UNSIGNED NOT NULL,

purchase_sum DECIMAL ( 10, 2 ) NOT NULL,

purchase_buyer INT UNSIGNED NOT NULL,

purchase_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

company_id INT UNSIGNED NOT NULL,

goods_id INT UNSIGNED NOT NULL,

KEY idx_company_id ( company_id ),

KEY idx_goods_id ( goods_id )

) ENGINE = TokuDB;

4.安装pt-archiver

yum install percona-toolkit -y

pt-archiver --version

pt-archiver --help

5.归档数据

pt-archiver --source h=192.168.192.135,P=3306,u=root,p=123456,D=test1,t=t_purchase

--dest h=192.168.192.129,P=3306,u=admin,p=Abc_123456,D=test1,t=t_purchase_201807

--no-check-charset

--where 'purchase_date

--progress 5000 --bulk-delete --bulk-insert --limit=1000 --statistics

#执行完成后，源数据库的冷数据就归档到归档数据库中了

6.总结

1.使用tokudb引擎保存归档数据，拥有告诉写入特性2.使用双机热备方案搭建归档数据库，具备高可用性3.使用pt-archiver执行数据归档，简便易行