MySQL数据库全量备份

1.检查数据库

查表个数
SELECT COUNT(*) TABLES, table_schema FROM information_schema.TABLES GROUP BY table_schema;

查触发器个数
SELECT trigger_schema,count(1) FROM information_schema.TRIGGERS GROUP BY trigger_schema;

查询数据库中的事件
select * from information_schema.EVENTS

查看函数个数
select specific_schema,count(distinct specific_name) from information_schema.PARAMETERS where routine_type=‘PROCEDURE’ group by specific_schema;

查询数据库表中的条目数
SELECT TABLE_NAME,TABLE_ROWS,TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA in(‘zhihui’,‘mi’,‘jingyin’);

2.1.2.数据库备份

备份zhihui库
mysqldump -uroot -p -R -E --triggers --single-transaction --skip-add-locks --default-character-set=utf8 zhihui --max_allowed_packet=32M --net_buffer_length=16M >zhihui20210905.sql

2.操作步骤
2.1.备份步骤
2.1.1.检查要备份数据库中各库个数

2.2.还原步骤
2.2.1.创建数据库