mysql只备份部分表数据

1. 查看zabbix数据库中各个表的大小

SELECT TABLE_SCHEMA, TABLE_NAME , DATA_LENGTH / 1024 / 1024 / 1024 AS DATA_SIZE_GB , INDEX_LENGTH / 1024 / 1024 / 1024 AS INDEX_SIZE_GB , (DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024 / 1024 AS TABLE_SIZE_GB , TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'zabbix' ORDER BY TABLE_SIZE_GB ASC;

2. 时间戳换算

date -d "2023-11-19 23:59:59" +%s
date -d @1700409599 +"%Y/%m/%d %H:%M:%S"

3. 只复制表结构

create table history_tmp like history;
create table history_log_tmp like history_log;
create table history_str_tmp like history_str;
create table history_text_tmp like history_text;
create table history_uint_tmp like history_uint;
create table trends_tmp like trends;
create table trends_uint_tmp like trends_uint;
create table proxy_history_tmp like proxy_history;

4. 复制旧表中的数据到新表中（假设：两个表的表结构是一样的）

INSERT INTO history_old SELECT * FROM history WHERE 'clock'>'1700841599';
INSERT INTO history_log_old SELECT * FROM history_log WHERE 'clock'>'1700841599';
INSERT INTO history_str_old SELECT * FROM history_str WHERE 'clock'>'1700841599';
INSERT INTO history_text_old SELECT * FROM history_text WHERE 'clock'>'1700841599';
INSERT INTO history_uint_old SELECT * FROM history_uint WHERE 'clock'>'1700841599';
INSERT INTO trends_old SELECT * FROM trends WHERE 'clock'>'1700841599';
INSERT INTO trends_uint_old SELECT * FROM trends_uint WHERE 'clock'>'1700841599';
INSERT INTO proxy_history_old SELECT * FROM proxy_history WHERE 'clock'>'1700841599';

5. 删除旧的历史数据

drop table history;
drop table history_log;
drop table history_str;
drop table history_text;
drop table history_uint;
drop table trends;
drop table trends_uint;
drop table proxy_history;

6. 备份与还原数据库

mysqldump -uroot zabbix > /tmp/create_server.sql
gzip -c /tmp/create_server.sql > /tmp/create_server.sql.gz
zcat /usr/share/zabbix-sql-scripts/mysql/server.sql.gz | mysql -h 127.0.0.1 -uzabbix -p$DPassword zabbix;
cat /usr/share/zabbix-sql-scripts/mysql/server.sql | mysql -h 127.0.0.1 -uzabbix -p$DPassword zabbix;

mysqldump --databases zabbix --ignore-table=zabbix.history --ignore-table=zabbix.history_log --ignore-table=zabbix.history_str --ignore-table=zabbix.history_text --ignore-table=zabbix.history_uint --ignore-table=zabbix.trends --ignore-table=zabbix.trends_uint --ignore-table=zabbix.proxy_history > /opt/zabbix.sql

---