MySQL学习——备份和还原

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了MySQL学习——备份和还原，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含5533字，纯文字阅读大概需要8分钟。

内容图文

MySQL学习——备份和还原

摘要：本文主要学习了如何备份和还原数据库。

部分内容来自以下博客：

https://www.cnblogs.com/chenmh/p/5300370.html

常用命令

导出数据

导出2019年1月的数据，数据库是ide，表是user，要求不需要导出建库语句，要求使用完成的插入语句，并且合并插入：

1 [root@localhost ~]# mysqldump -h 127.0.0.1 -P 3306 -uroot -p'123456' --no-create-info --databases ide --tables user \
2 > --where=" date >= '2019-01-01' and date < '2019-02-01' " --complete-insert --extended-insert > '/data/dump/201901.txt'

导入数据

1 [root@localhost ~]# mysql -uroot -p123456 database < db_back.sql

导出数据

导出数据一般使用mysqldump命令在Linux系统执行。

语法

导出全部数据库：

1 mysqldump [OPTIONS] --all-databases [OPTIONS]

导出指定数据库：

1 mysqldump [OPTIONS] --databases DB1 [DB2 DB3...] [OPTIONS]

导出指定数据库的指定表：

1 mysqldump [OPTIONS] --database DB --tables TB [TB2 TB3...] [OPTIONS]

说明

用户名：

登录数据库的用户名，当前用户可省略该配置，“-u”后面紧跟用户名，或者使用“--user=”后面紧跟用户名的方式。

1 --user, -u

密码：

登录数据库的密码，“-p”后面紧跟密码，或使用“--password=”后面紧跟密码的方式。

1 --password, -p

主机地址：

设置数据库所在的主机地址，默认是localhost。

1 --host, -h

端口号：

设置用于连接的端口号。

1 -P, --port

连接协议：

设置使用的连接协议，取值有TCP、SOCKET、PIPE、MEMORY四种。

1 --protocol

套接字文件：

指定当连接localhost时的套接字文件位置，默认路径是/tmp/mysql.sock。

1 -S, --socket

导出全部数据库：

1 --all-databases, -A

导出指定数据库：

1 --databases, -B

导出指定表：

只能针对一个数据库进行导出，并且导出的内容中没有创建数据库的判断语句。

1 --tables

不导出指定表：

指定忽略多个表时，需要重复多次，每次一个表，每个表必须同时指定数据库和表名。

1 --ignore-table

导出全部表空间：

1 --all-tablespaces, -Y

不导出表空间信息：

1 --no-tablespaces, -y

不添加创建数据库语句：

默认添加。

1 --no-create-db, -n

添加删除数据库语句：

默认不添加。

1 --add-drop-database

不添加创建表语句：

默认添加。

1 --no-create-info, -t

添加删除表语句：

默认添加，使用“--skip-add-drop-table”参数取消。

1 --add-drop-table
2 –-skip-add-drop-table

不导出数据只导出表结构：

1 --no-data, -d

导出存储过程以及自定义函数：

1 --routines, -R

在每个表导出前锁表并在导出后解锁：

默认为打开状态，使用“--skip-add-locks”参数取消。

1 --add-locks
2 --skip-add-locks

设置默认的字符集：

默认是utf8。

1 --default-character-set

在客户端和服务器之间启用压缩传递所有信息：

1 --compress, -C

处理换行：

直接输出到指定文件中。该选项应该用在使用回车换行对（\\r\\n）换行的系统上（例如：DOS，Windows）。该选项确保只有一行被使用。

1 --result-file, -r

添加导出时间：

将导出时间添加到输出文件中。默认为打开状态，使用“--skip-dump-date”参数取消。

1 --dump-date
2 --skip-dump-date

只导出符合指定条件的记录：

如果条件包含命令解释符专用空格或字符，一定要将条件引用起来。

1 --where, -w

合并多个插入语句：

默认开启，使用“--skip-extended-insert”关闭。

1 --extended-insert
2 --skip-extended-insert

使用完整的插入语句：

使用包含列名称的完整插入语句。这么做能提高插入效率，但是可能会受到max_allowed_packet参数的影响而导致插入失败。

1 --complete-insert, -c

忽略插入错误：

默认不添加，在插入语句中添加忽略错误语句。

1 --insert-ignore

服务器发送和接受的最大包长度：

客户端/服务器之间通信的缓存区的最大大小，最大为1GB。

1 --max_allowed_packet

强制插入：

在表转储过程中，即使出现SQL错误也继续。

1 --force, -f

实例

导出全部数据库：

1 [root@localhost ~]# mysqldump -h127.0.0.1 -uroot -p --all-databases > db_back.sql

导出指定数据库：

1 [root@localhost ~]# mysqldump -h127.0.0.1 -uroot -p --databases test demo > db_back.sql

导出指定表：

1 [root@localhost ~]# mysqldump -h127.0.0.1 -uroot -p --database test --tables demo user > db_back.sql

不导出指定表：

1 [root@localhost ~]# mysqldump -h127.0.0.1 -uroot -p --all-databases \
2 > --ignore-table=database.table1 --ignore-table=database.table2 > db_back.sql

设置默认字符集：

1 [root@localhost ~]# mysqldump -h127.0.0.1 -uroot -p --all-databases --default-character-set=latin1 > db_back.sql

处理换行：

1 [root@localhost ~]# mysqldump -h127.0.0.1 -uroot -p --all-databases --result-file=/tmp/mysqldump_result_file.txt

只导出符合指定条件的记录：

1 [root@localhost ~]# mysqldump -h127.0.0.1 -uroot -p --all-databases --where=" id=1 limit 10"

使用完整的插入语句：

1 [root@localhost ~]# mysqldump -h127.0.0.1 -uroot -p --all-databases --complete-insert

导入数据

导入数据有两种方式，一种是在Linux系统使用<命令导入，另一种是在MySQL数据库使用source命令导入。

语法

在Linux系统使用<命令：

1 mysql -u用户名 -p密码 数据库名 < 文件路径

在MySQL数据库使用source命令：

1 source 文件路径

实例

在Linux系统使用<命令：

1 [root@localhost ~]# mysql -uroot -p123456 database < db_back.sql

在MySQL数据库使用source命令：

1 mysql> source /home/user/data/db_back.sql

效率比较

在文件较小的情况下，source速度比mysql高。在导入大的文件时，建议使用mysql命令。

优化导入速度

对于百M级以上文件，根据MySQL官方建议，有几个措施可以极大提高导入的速度。

修改配置文件

配置如下：

1 [mysqld]
2 bulk_insert_buffer_size=2G;
3 innodb_log_buffer_size=2G;
4 innodb_autoinc_lock_mode=2;

说明：

对于MyISAM，调整系统参数：bulk_insert_buffer_size。改为至少单个文件大小的2倍以上。

对于InnoDB，调整系统参数：innodb_log_buffer_size。改为至少单个文件大小的2倍以上，导入完成后可以改回默认的8M，注意不是innodb_buffer_pool_size。

有自增列的，设置：innodb_autoinc_lock_mode的值为2。

删除索引

除主键外，删除其他索引，导入完成后重建索引。

执行批量脚本

bash脚本内容：

1 for SQL in *.sql;
2 do
3 echo $SQL;
4 sed -i '1i\SET autocommit=0;\nSET unique_checks=0;\nSET foreign_key_checks=0;' $SQL
5 sed -i '$a\COMMIT;\nSET autocommit=1;\nSET unique_checks=1;\nSET foreign_key_checks=1;' $SQL
6 done

说明：

关闭自动提交：autocommit=0。

关闭唯一索引检查：unique_checks=0。

关闭外键检查：foreign_key_checks=0。

内容总结

以上是互联网集市为您收集整理的MySQL学习——备份和还原全部内容，希望文章能够帮你解决MySQL学习——备份和还原所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端