Mysql基础篇-18-mysqldump命令大全

Alan0517

已于 2023-05-29 23:35:45 修改

阅读量1.3k

点赞数

文章标签： mysql

于 2021-01-11 22:12:27 首次发布

本文链接：https://blog.csdn.net/Hmj050117/article/details/112434310

版权

Mysql-基础篇专栏收录该内容

41 篇文章 5 订阅

订阅专栏

1. MySQLdump常用

mysqldump -u root -p --databases 数据库1 数据库2 > xxx.sql

还原：系统命令行： MySQL -uroot -p123456 < f:\all.sql

常见选项：

–all-databases, -A：备份所有数据库
–databases, -B：用于备份多个数据库，如果没有该选项，
mysqldump把第一个名字参数作为数据库名，后面的作为表名。使用该选项，mysqldum把每个名字都当作为数据库名。
–force, -f：即使发现sql错误，仍然继续备份
–host=host_name, -h host_name：备份主机名，默认为localhost
–no-data, -d：只导出表结构
–password[=password], -p[password]：密码
–port=port_num, -P port_num：制定TCP/IP连接时的端口号
–quick, -q：快速导出
–tables：覆盖 --databases or -B选项，后面所跟参数被视作表名
–user=user_name, -u user_name：用户名
–xml, -X：导出为xml文件
-R, --routines: 导出存储过程以及自定义函数。默认是关闭的
–triggers: 导出表的触发器脚本，默认就是启用状态。使用–skip-triggers禁用它。
-E, --events: 输出event。

2. 导出

注意：以下备份结构只是表结构，如若要备份函数存储过程以及events，触发器等，另外加参数，详情看上面

2.1 备份全部数据库的数据和结构

mysqldump -uroot -p123456 -A >F:\all.sql

2.2备份全部数据库的结构（加 -d 参数）

mysqldump -uroot -p123456 -A-d>F:\all_struct.sql

2.3 备份全部数据库的数据(加 -t 参数)

mysqldump -uroot -p123456 -A-t>F:\all_data.sql

2.4.备份单个数据库的数据和结构(,数据库名mydb)

mysqldump -uroot-p123456 mydb>F:\mydb.sql

2.5 备份单个数据库的结构

mysqldump -uroot -p123456 mydb -d>F:\mydb.sql

2.6 备份单个数据库的数据

mysqldump -uroot -p123456 mydb-t>F:\mydb.sql

2.7.备份多个表的数据和结构（数据，结构的单独备份方法与上同）

mysqldump -uroot -p123456 mydb t1 t2>f:\multables.sql

2.8.一次备份多个数据库

mysqldump -uroot -p123456 --databases db1 db2>f:\muldbs.sql

3. 导入

还原部分分

mysql命令行source方法
系统命令行方法

3.1 还原全部数据库

mysql命令行：mysql>source f:\all.sql
系统命令行： mysql -uroot -p123456 <f:\all.sql

3. 2.还原单个数据库(需指定数据库)

mysql>use mydb
mysql>source f:\mydb.sql
mysql -uroot -p123456 mydb <f:\mydb.sql

3. 3.还原单个数据库的多个表(需指定数据库)

mysql>use mydb
mysql>source f:\multables.sql
mysql -uroot -p123456 mydb<f:\multables.sql

3. 4.还原多个数据库

（一个备份文件里有多个数据库的备份，此时不需要指定数据库）

mysql命令行：mysql>source f:\muldbs.sql
系统命令行： mysql -uroot -p123456<f:\muldbs.sql

4. 全部命令

-?, --help: 显示帮助信息，英文的；
-u, --user: 指定连接的用户名；
-p, --password: 指定用户的密码，可以交互输入密码；
-S , --socket: 指定socket文件连接，本地登录才会使用。
-h, --host: 指定连接的服务器名称或者IP。
-P, --port=: 连接数据库监听的端口。
–default-character-set: 设置字符集，默认是UTF8。
-A, --all-databases: 导出所有数据库。不过默认情况下是不会导出information_schema库。
-B, --databases: 导出指定的某个/或者某几个数据库，参数后面所有名字参量都被看作数据库名，包含CREATE DATABASE创建- 库的语句。
–tables: 导出指定表对象，参数格式为“库名表名”，默认该参数将覆盖-B/–databases参数。
-w, --where: 只导出符合条件的记录。
-l, --lock-tables: 默认参数，锁定读取的表对象，想导出一致性备份的话最后使用该参数，会导致无法对表执行写入操作。
–single-transaction:
该选项在导出数据之前提交一个BEGIN SQL语句，BEGIN 不会阻塞任何应用程序且能保证导出时数据库的一致性状态。它只适用于多版本存储引擎，仅InnoDB。本选项和–lock-tables 选项是互斥的，因为LOCK TABLES 会使任何挂起的事务隐含提交，使用参数–single-transaction会自动关闭该选项。
在InnoDB导出时会建立一致性快照，在保证导出数据的一致性前提下，又不会堵塞其他会话的读写操作，相比–lock-tables参数来说锁定粒度要低，造成的影响也要小很多。指定这个参数后，其他连接不能执行ALTER TABLE、DROP TABLE 、RENAME TABLE、TRUNCATE TABLE这类语句，事务的隔离级别无法控制DDL语句。
-d, --no-data: 只导出表结构，不导出表数据。
-t, --no-create-info: 只导出数据，而不添加CREATE TABLE 语句。
-f, --force: 即使遇到SQL错误，也继续执行，功能类似Oracle exp命令中的ignore参数。
-F, —flush-logs: 在执行导出前先刷新日志文件，视操作场景，有可能会触发多次刷新日志文件。一般来说，如果是全库导出，建议先刷新日志文件，否则就不用了。
–master-data[=#]: 该选项将二进制日志的位置和文件名写入到输出中。该选项要求有RELOAD权限，并且必须启用二进制日志。如果该选项值等于1，位置和文件名被写入CHANGE MASTER语句形式的转储输出，如果你使用该SQL转储主服务器以设置从服务器，从服务器从主服务器二进制日志的正确位置开始。如果选项值等于2，CHANGE MASTER语句被写成SQL注释。如果value被省略，这是默认动作。
–master-data选项会启用–lock-all-tables，除非还指定–single-transaction(在这种情况下，只在刚开始转储时短时间获得全局读锁定。又见–single-transaction。在任何一种情况下，日志相关动作发生在转储时。该选项自动关闭–lock-tables。
所以，我在INNODB引擎的数据库备份时，我会同时使用–master-data=2 和 –single-transaction两个选项。
-x, --lock-all-tables: 在导出任务执行期间锁定所有数据库中的所有表，以保证数据的一致性。这是一个全局锁定，并且自动关闭–single-transaction 和–lock-tables 选项。这个参数副作用比较大，这是全库锁定，备份执行过程中，该库无法进行读写操作，不是所有业务场景都能接受的。请慎用。
-n, --no-create-db: 不生成建库的语句CREATE DATABASE … IF EXISTS，即使指定–all-databases或–databases这类参数。
–triggers: 导出表的触发器脚本，默认就是启用状态。使用–skip-triggers禁用它。
-R, --routines: 导出存储过程以及自定义函数。
在转储的数据库中转储存储程序(函数和程序)。
-E, --events: 输出event。
–ignore-table: 指定的表对象不做导出，参数值的格式为[db_name,tblname]，注意每次只能指定一个值，如果有多个表对象都不进行导出操作的话，那就需要指定多个–ignore-table参数，并为每个参数指定不同的参数值。
–add-drop-database: 在任何创建库语句前，附加DROP DATABASE 语句。
–add-drop-table: 在任何创建表语句前，附加DROP TABLE语句。这个参数是默认启用状态，可以使用– skip-add-drop-table参数禁用该参数。
–add-drop-trigger: 创建任何触发器前，附加DROP TRIGGER语句。
–add-locks: 在生成的INSERT语句前附加LOCK语句，该参数默认是启用状态。使用–skip-add-locks参数禁用。
-K, --disable-keys: 在导出的文件中输出 ‘/!40000 ALTER TABLE tb_name DISABLE KEYS */; 以及
‘/!40000 ALTER TABLE tb_name ENABLE KEYS */; ‘ 等信息。这两段信息会分别放在INSERT语句的前后，也就是说，在插入数据前先禁用索引，等完成数据插入后再启用索引，目的是为了加快导入的速度。该参数默认就是启用状态。可以通过–skip-disable-keys参数来禁用。
–opt: 功能等同于同时指定了 –add-drop-table, –add-locks, –create-options, –quick, –extended-insert, –lock-tables, –set-charset, 以及 –disable-keys这些参数。默认就是启用状态。使用–skip-opt来禁用该参数。
–skip-opt: 禁用–opt选项，相当于同时禁用 –add-drop-table, –add-locks, –create-options, –quick, –extended-insert, –lock-tables, –set-charset, 及 –disable-keys这些参数。
-q, --quick: 导出时不会将数据加载到缓存，而是直接输出。默认就是启用状态。可以使用–skip-quick 来禁用该参数。

5. mysqldump 常用方法

(1) 获取一个完整备份,不锁库备份

$ mysqldump -uusername -p --triggers --routines --events -A -B --single-transaction --master-data=2 > backup.$(date +%F).sql

(2) 导出指定库

$ mysqldump -uusername -p -B dbname > backup.$(date +%F).sql

如果是导出单库也可以不使用-B 参数，无非就是没有创建库的语句。
如果是多个库可以使用如下命令，但是必须使用-B参数

$ mysqldump -uusername -p -B DB1 DB2 DB3 > backup.$(date +%F).sql

(3) 导出指定表的数据和结构

$ mysqldump -uusername -p DBNAME table1 table2 > tablename.sql
或者使用 –tables 参数
$ mysqldump -uusername -p --tables DBNAME table1 table2 > backup.$(date +%F).sql
或者
$ mysqldump -uusername -p DBNAME --tables table1 table2 table3 > tablename.sql

(4) 导出指定表的结构
不包含数据

$ mysqldump -ubackup -p --no-data DBNAME table1 table2 > backup.$(date +%F).sql
或者使用–tables参数
$ mysqldump -ubackup -p --no-data DBNAME --tables table1 table2 > backup.$(date +%F).sql
或者
$ mysqldump -ubackup -p --no-data --tables DBNAME table1 table2 > backup.$(date +%F).sql

(5) 导出指定表的数据
不包含表结构

$ mysqldump -uusername -p --no-create-info DBNAME table1 table2 table3 > backup.$(date +%F).sql
或者使用–tables 参数
$ mysqldump -uusername -p --no-create-info DBNAME --tables table1 table2 table3 > backup.$(date +%F).sql
或者
$ mysqldump -uusername -p --no-create-info --tables DBNAME table1 table2 table3 > backup.$(date +%F).sql

(6) 导出整个数据库结构 (包括表结构)
不包含数据

$ mysqldump -uusername -p --no-data DBNAME > backup.$(date +%F).sql

(7) 导出数据库表结构和数据时排除某些表
使用 –ignore-table 参

$ mysqldump -uusername -p --single-transaction --master-data=2 --add-drop-database -B DBNAME --ignore-table=DBNAME.table1 --ignore-table=DBNAME.table2 > backup.$(date +%F).sql

(8) 导出数据直接压缩

$ mysqldump -uusername -p -B DBNAME | gzip > backup.sql.gz
解压命令：
$ gunzip backup.sql.gz

6. 工作中使用过的脚本

6.1 创建库

#! /bin/sh

echo "please input the dbName(eg:esfdbfe)"
read dbname
echo "please input the userName(eg:root)"
read user
echo "please input the hostIP(eg:10.114.30.33)"
read IP


echo "**********【Is Createing the $dbname from the Ip $IP】***********"

/home/mysql/mysql/bin/mysqladmin -h $IP -u$user -p create $dbname

echo "*********【 Done Successful】*********"

6.2 导入表结构

#! /bin/sh

echo "please input the dbName(eg:esfdbfe)"
read dbname
echo "please input the userName(eg:root)"
read user
echo "please input the hostIP(eg:10.114.30.33)"
read IP


echo "***************【Is loading to $IP about $dbname 's table-DDL】"

/home/mysql/mysql/bin/mysql -h $IP -u$user -p $dbname <${dbname}-d.sql

echo "************【Done Successful】**************"

6.3 导入表数据

echo "please input the dbName(eg:esfdbfe)"
read dbname
echo "please input the userName(eg:root)"
read user
echo "please input the hostIP(eg:10.114.30.33)"
read IP


echo "***************【Is loading to $IP about $dbname 's table-SQL】"

/home/mysql/mysql/bin/mysql -h $IP -u$user -p $dbname <${dbname}-t.sql

echo "************【Done Successful】**************"

6.4 删除指定库

echo "please input the dbName(eg:esfdbfe)"
read dbname
echo "please input the userName(eg:root)"
read user
echo "please input the hostIP(eg:10.114.30.33)"
read IP


echo "please think it over before do it"

/home/mysql/mysql/bin/mysqladmin -h $IP -u$user -p drop  $dbname 

echo "************【Done Successful】**************"

6.5 备份表结构

echo "please input the dbName(eg:esfdbfe)"
read dbname
echo "please input the userName(eg:root)"
read user
echo "please input the hostIP(eg:10.114.30.33)"
read IP


echo "**************【Is starting to Copy the ddl】"

/home/mysql/mysql/bin/mysqldump -h $IP -u$user -p $dbname -d -R -E --triggers  >${dbname}-d.sql

echo "************【Done Successful】**************"

6.6 备份表数据

echo "please input the dbName(eg:esfdbfe)"
read dbname
echo "please input the userName(eg:root)"
read user
echo "please input the hostIP(eg:10.114.30.33)"
read IP



echo "**************【Is starting to Copy the ddl】"

/home/mysql/mysql/bin/mysqldump -h $IP -u$user -p $dbname -t  >${dbname}-t.sql

echo "************【Done Successful】**************"

6.7 备份表数据和结构

echo "please input the dbName(eg:esfdbfe)"
read dbname
echo "please input the userName(eg:root)"
read user
echo "please input the hostIP(eg:10.114.30.33)"
read IP


echo "**************【Is starting to Copy the ddl】"

/home/mysql/mysql/bin/mysqldump -h $IP -u$user -p $dbname  -R -E --triggers  >${dbname}-d-t.sql

echo "************【Done Successful】**************"

6.8 创建用户

#! /bin/sh

echo "please input the userName(eg:root)"
read user
echo "please input the userPasswd"
read passwd
echo "please input the user you want to create(eg:esb)"
read newUser
echo "please input the user's passwd you want to create(eg:rootroot)"
read newUserPwd

echo "please input the dbtabase you want to grant to newUser(eg: esfdbfe)"
read dbtable

export log=createUser.log

/home/mysql/mysql/bin/mysql -u$user -p$passwd -e "CREATE USER '$newUser'@'%' IDENTIFIED BY '$newUserPwd';" >>$log
/home/mysql/mysql/bin/mysql -u$user -p$passwd -e "GRANT ALL ON $dbtable.* TO '$newUser'@'%';" >>$log
/home/mysql/mysql/bin/mysql -u$user -p$passwd -e "FLUSH PRIVILEGES;" >>$log