MySQL数据的导入导出/备份恢复
1. mysqldump 简介
mysqldump 是 MySQL 自带的逻辑备份工具。它的备份原理是通过协议连接到 MySQL 数据库,将需要备份的数据查询出来,将查询出的数据转换成对应的insert 语句,当我们需要还原这些数据时,只要执行这些 insert 语句,即可将对应的数据还原。
2. MySQL备份命令
(1)导出所有库
mysqldump --all-databases --master-data=2 --single-transaction --quick -R --events -u$user -h$IP -P$port -p$password > ${backup_name}.sql
(2)导出部分库
mysqldump --databases db1 db2 db2 --master-data=2 --single-transaction --quick -R --events -u$user -h$IP -P$port -p$password > ${backup_name}.sql
(3)忽略部分表
mysqldump --databases db1 db2 db3 --ignore-table=mysql.slow_log --ignore-table=mysql.user --master-data=2 --single-transaction --quick -R --events -u$user -h$IP -P$port -p$password > ${backup_name}.sql
(4)导出部分表结构
mysqldump -u$user -h$IP -P$port -p$password -d dbname tab1 tab2 tab3 > ${backup_name}.sql
(5)导出部分表数据
mysqldump -u$user -h$IP -P$port -p$password dbname tab1 tab2 tab3 > ${backup_name}.sql
(6)into outfile方法导出数据
文档推荐:官方参考文档
select * from xxx_table into outfile '/home/xxx.bak' fields terminated by '|' enclosed by '"' lines terminated by '\r\n' ;
参数名 | 缩写 | 含义 |
---|---|---|
--host | -h | 服务器IP地址 |
--port | -P | 服务器端口号 |
--user | -u | MySQL 用户名 |
--pasword | -p | MySQL 密码 |
--databases | -B | 指定要备份的数据库 |
--all-databases | -A | 备份mysql服务器上的所有数据库 |
--compact | 压缩模式,产生更少的输出 | |
--comments | 添加注释信息 | |
--complete-insert | -c | 输出完成的插入语句 |
--lock-tables | -l | 备份前,锁定所有数据库表 |
--no-create-db/--no-create-info | 禁止生成创建数据库语句 | |
--force | 当出现错误时仍然继续备份操作 | |
--default-character-set | 指定默认字符集 | |
--add-locks | 备份数据库表时锁定数据库表 | |
--master-data | 将二进制的信息写入到输出文件中 | |
--single-transaction | 保证数据一致性 | |
--ignore-table | 忽略备份某张表 | |
--routines | -R | 导出存储过程以及自定义函数 |
--quick | -q | 不缓冲查询,直接导出到标准输出 |
--events | -E | 导出事件 |
【注】在mysqldump中指定single-transaction时,会使用可重复读(REPEATABLE READ)事务隔离级别来保证整个dump过程中数据一致性,该选项仅对InnoDB表有用,且不能与ALTER TABLE/CREATE TABLE/DROP TABLE/RENAME TABLE/TRUNCATE TABLE等DDL操作并行。本选项和--lock-tables 选项是互斥的,因为LOCK TABLES 会使任何挂起的事务隐含提交。要想导出大表的话,应结合使用--quick 选项。
3. MySQL还原命令
(1)系统命令导入数据
mysqladmin -u$user -h$IP -P$port -p$password create db_name #DB中没有这个库时,需要先创建 mysql -u$user -h$IP -P$port -p$password db_name < ${backup_name}.sql
(2)soure 方法
mysql > use db_name mysql > source /backup/${backup_name}.sql
(3)load方法
mysql > load data infile '/home/xxxxx.bak' into table xxx_table fields terminated by '|' enclosed by '"' lines terminated by '\r\n' ;
4. MySQL完整复制表到新表
(1)复制表结构
CREATE TABLE new_table1 LIKE table1;
(2)新表导入数据
INSERT INTO new_table1 SELECT * FROM table1;
【注】如果某张非常大的表中绝大多数数据都需要删除,亦可以采用类似的方法,详情参考:
官方文档:https://dev.mysql.com/doc/refman/5.7/en/delete.html
作者:UStarGao
链接:https://www.starcto.com/mysql/105.html
来源: STARCTO
著作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。
UCloud云平台推荐
随便看看
- 2021-08-20PostgreSQL常用命令集合
- 2022-08-05关于网络排查工具MTR那些你必须了解的事情
- 2021-11-30UCloud NVMe机型MySQL数据库性能压测-sysbench
- 2021-10-08 Microsoft SQL Server数据迁移解决方案
- 2022-06-18MongoDB事务开发之写操作事务writeConcern