mysql

mysqldump 深入浅出

目录
  1. 1. 快速使用
  2. 2. 参数
  3. 3. 实例
    1. 3.1. 导出某个表
    2. 3.2. 数据表条件导出
    3. 3.3. 忽略某张表
    4. 3.4. 导入某个表数据脚本
  4. 4. 备份脚本
  5. 5. 恢复备份

mysqldump 是用于转存储 mysql 数据库的实用程序。

主要产生一个 SQL 脚本，其中包含从头重新创建数据库的所有命令。

导出脚本过程大概如下：创建数据库判断语句、删除表、创建表、锁表、禁用索引、插入数据、启用索引、解锁表。

快速使用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
# game 是库名

# 完整导出一个库
# 包括建库语句、表结构、数据
mysqldump -uroot -proot --host=127.0.0.1 --port=3306 --databases game > test.sql

# 只导出表结构
mysqldump -uroot -proot --host=127.0.0.1 --port=3306 -d game > test.sql

# 只导出数据
mysqldump -uroot -proot --host=127.0.0.1 --port=3306 -t game > test.sql

# 导出一个数据库中多个表的数据和结构
mysqldump -uroot -proot --host=127.0.0.1 game --tables articles users > test.sql
mysqldump -uroot -proot --host=127.0.0.1 game articles users > test.sql

# 恢复导出数据
mysql -u username -proot databse < backup.sql

参数

--user=user_name, -u user_name

连接数据库的用户名。

--password=password, -p[password]

连接数据库的密码，如果使用 -p 缩写，和密码之间不能有空格。

--opt or --compact

  • 使用 --opt 等于使用这些参数 --add-drop-table, --add-locks, --create-options, --disable-keys, --extended-insert, --lock-tables, --quick, and --set-charset，默认情况下即使不加 --opt 参数也是开启的。
  • 使用 --compact 等于使用这些参数 --skip-add-drop-table, --skip-add-locks, --skip-comments, --skip-disable-keys, and --skip-set-charset，默认不开启。

--databases, -B

指定导出的库名。

--all-databases

表示导出所有的库。

--tables

会覆盖 --databases or -B 选项，指定导出某个表就会忽略库选项。

--no-data, -d

不导出数据，只导出表结构。

Do not dump table contents

--no-create-info, -t

只导出数据，不导出表结构，不添加 CREATE TABLE 语句。

--no-create-db, -n

不添加 CREATE DATABASE 建库语句。

--routines, -R

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

实例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
// 导出一个数据库
// 包括建库语句、删表语句、建表语句、插入数据
mysqldump -uroot -proot --host=127.0.0.1 --port=3306 --databases game > test.sql

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `game` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `game`;

--
-- Table structure for table `address`
--

DROP TABLE IF EXISTS `address`;
CREATE TABLE `address` (
        ...
)

LOCK TABLES `address` WRITE;
/*!40000 ALTER TABLE `address` DISABLE KEYS */;
INSERT INTO ...
/*!40000 ALTER TABLE `address` ENABLE KEYS */;
UNLOCK TABLES;
...

// -d 不导出数据，导出所有的建表删表语句，不包括建库语句，
mysqldump -uroot -proot --host=127.0.0.1 --port=3306 -d game > test.sql

DROP TABLE IF EXISTS `address`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `address` (
        ...
)

// -t 不导出结构，导出所有表中的数据，不包括建库语句，都是插入语句
mysqldump -uroot -proot --host=127.0.0.1 --port=3306 -t game > test.sql

LOCK TABLES `address` WRITE;
/*!40000 ALTER TABLE `address` DISABLE KEYS */;
/*!40000 ALTER TABLE `address` ENABLE KEYS */;
UNLOCK TABLES;

// 导出一个数据库的表结构以及数据，不包括建库语句
mysqldump -uroot -proot --host=127.0.0.1 --port=3306 game > test.sql

// 导出多个数据库的表结构以及数据，包括建库语句
mysqldump -uroot -proot --host=127.0.0.1 -B game game2 > test.sql

// 导出多个数据库的结构，包括建库语句
mysqldump -uroot -proot --host=127.0.0.1 -d -B game game2 > test.sql

// 导出多个数据库的数据，包括建库语句
mysqldump -uroot -proot --host=127.0.0.1 -t -B game game2 > test.sql

导出某个表

1
2
3
4
5
6
7
8
9
10
11
12
// 导出一个数据库中一个表的结构，导出 articles 表的结构和数据
mysqldump -uroot -proot --host=127.0.0.1 game articles > test.sql

// 导出一个数据库中多个表的数据和结构
mysqldump -uroot -proot --host=127.0.0.1 game --tables articles users > test.sql
mysqldump -uroot -proot --host=127.0.0.1 game articles users > test.sql

// 导出一个数据库中多个表的结构
mysqldump -uroot -proot --host=127.0.0.1 -d game articles users > test.sql

// 导出一个数据库中多个表的数据
mysqldump -uroot -proot --host=127.0.0.1 -t game articles users > test.sql

数据表条件导出

1
2
// 导出 articles 表 id = 1 的数据
mysqldump -uroot -proot --host=127.0.0.1 --where='id=1' game articles > test.sql

忽略某张表

1
--ignore-table database.tableName

多次添加忽略多张表。

导入某个表数据脚本

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# 数据来源
src_user="root" # 用户名
src_password="root" # 密码
src_host="localhost" # Host
src_port="3306" # 端口
src_database="test" # 数据库名
src_table="edu" # 表名

# 数据导入的数据库配置
dst_user="root" # 用户名
dst_password="root" # 密码
dst_host="localhost" # Host
dst_port="3306" # 端口
dst_database="test" # 数据库名

mysqldump --host=$src_host --port=$src_port -u$src_user -p$src_password $src_database --tables $src_table | mysql --host=$dst_host --port=$dst_port -u$dst_user -p$dst_password $dst_database

备份脚本

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
#!/bin/bash

# 以下配置信息请自己修改
mysql_user="root" #MySQL备份用户
mysql_password="root" #MySQL备份用户的密码
mysql_host="localhost"
mysql_port="3306"
mysql_charset="utf8mb4" #MySQL编码
backup_db_arr=("db1" "db2") #要备份的数据库名称，多个用空格分开隔开 如("db1" "db2" "db3")
backup_location=/var/www/mysql  #备份数据存放位置，末尾请不要带"/",此项可以保持默认，程序会自动创建文件夹
expire_backup_delete="OFF" #是否开启过期备份删除 ON为开启 OFF为关闭
expire_days=3 #过期时间天数 默认为三天，此项只有在expire_backup_delete开启时有效

# 本行开始以下不需要修改
backup_time=`date +%Y%m%d%H%M`  #定义备份详细时间
backup_Ymd=`date +%Y-%m-%d` #定义备份目录中的年月日时间
backup_3ago=`date -d '3 days ago' +%Y-%m-%d` #3天之前的日期
backup_dir=$backup_location/$backup_Ymd  #备份文件夹全路径
welcome_msg="Welcome to use MySQL backup tools!" #欢迎语

# 判断MYSQL是否启动,mysql没有启动则备份退出
mysql_ps=`ps -ef |grep mysql |wc -l`
mysql_listen=`netstat -an |grep LISTEN |grep $mysql_port|wc -l`
if [ [$mysql_ps == 0] -o [$mysql_listen == 0] ]; then
        echo "ERROR:MySQL is not running! backup stop!"
        exit
else
        echo $welcome_msg
fi

# 连接到mysql数据库，无法连接则备份退出
mysql -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password <<end
use mysql;
select host,user from user where user='root' and host='localhost';
exit
end

flag=`echo $?`
if [ $flag != "0" ]; then
        echo "ERROR:Can't connect mysql server! backup stop!"
        exit
else
        echo "MySQL connect ok! Please wait......"
        # 判断有没有定义备份的数据库，如果定义则开始备份，否则退出备份
        if [ "$backup_db_arr" != "" ];then
                #dbnames=$(cut -d ',' -f1-5 $backup_database)
                #echo "arr is (${backup_db_arr[@]})"
                for dbname in ${backup_db_arr[@]}
                do
                        echo "database $dbname backup start..."
                        `mkdir -p $backup_dir`
                        `mysqldump -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password $dbname --default-character-set=$mysql_charset | gzip > $backup_dir/$dbname-$backup_time.sql.gz`
                        flag=`echo $?`
                        if [ $flag == "0" ];then
                                echo "database $dbname success backup to $backup_dir/$dbname-$backup_time.sql.gz"
                        else
                                echo "database $dbname backup fail!"
                        fi

                done
        else
                echo "ERROR:No database to backup! backup stop"
                exit
        fi
        # 如果开启了删除过期备份，则进行删除操作
        if [ "$expire_backup_delete" == "ON" -a  "$backup_location" != "" ];then
                 #`find $backup_location/ -type d -o -type f -ctime +$expire_days -exec rm -rf {} \;`
                 `find $backup_location/ -type d -mtime +$expire_days | xargs rm -rf`
                 echo "Expired backup data delete complete!"
        fi
        echo "All database backup success! Thank you!"
        exit
fi

修改shell脚本的属性：

1
2
chmod 600 /root/mysql_backup.sh
chmod +x /root/mysql_backup.sh

设置好属性之后，把命令加入 crontab，设置每天 00:00 定时自动备份。

1
00 00 * * * /path/to/mysql_backup.sh

恢复备份

1
2
3
4
5
6
7
8
9
10
11
12
mysql -u username -proot databse < backup.sql

// 和建库语句一起导入
mysql -uroot -proot --host=127.0.0.1 --port=33006  < global.sql

或者

mysql -u root -p

use dbname;

source dbname.sql