数据归档，冷热数据分离

一.关于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<"2019-02-01 0:0:0"' 
--progress 5000 --bulk-delete --bulk-insert --limit=1000 --statistics 

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

6.总结

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