数据库备份

w2001
149 阅读16分钟

Docker使用mysqldump命令备份导出mysql容器中的数据

1、查看Docker中运行的容器

[root@localhost ~]# docker ps

2、使用mkdir 创建备份目录

[root@localhost ~]# mkdir -p /data/reggie/dbbak/

3、数据库备份脚本

#!/bin/bash
​
#在使用之前，请提前创建以下各个目录#获取当前时间
date_now=$(date "+%Y%m%d-%H%M%S")
backUpFolder=/data/reggie/dbbak/
username="root" 
db_name="reggie"
password="1234"
​
#定义备份文件名
fileName="${db_name}_${date_now}.sql"
​
#定义备份文件目录
backUpFileName="${backUpFolder}/${fileName}"
echo "starting backup mysql ${db_name} at ${date_now}."
​
#进入容器执行备份命令
docker exec -it mysql /usr/bin/mysqldump -u${username} --lock-all-tables --flush-logs ${db_name} > ${backUpFileName}
​
#进入到备份文件目录
cd ${backUpFolder}
​
#压缩备份文件
tar zcvf ${fileName}.tar.gz ${fileName}
​
rm -f ${fileName}
​
#rm backup the latest 7 days
find ${backUpFolder} -mtime +7 -name "${db_name}_*.sql.tar.gz" -exec rm -f {} ;

4、上传至服务器

chmod a+x mysqlbak.sh
./mysqlbask.sh

5、创建定时任务

crontab -e
0 3 * * *  sh /data/reggie/dbbak/mysqlbak.sh
/sbin/service crond start

mysqldump命令

工作原理

  • 查出需要备份的表的结构
  • 在文本文件中生成一条CREATE语句，还原数据时就可以使用其中的CREATE语句来创建表
  • 将表中的所有记录转换成一条INSERT语句，使用其中的INSERT语句来还原数据。

命令格式

mysqldump [参数] 数据库名 [表名] > 脚本名

参数

参数名 缩写 含义
–host -h 服务器IP地址
–port -P 服务器端口号
–user -u MySQL 用户名
–pasword -p MySQL 密码
–databases 指定要备份的数据库
–all-databases 备份mysql服务器上的所有数据库
–compact 压缩模式，产生更少的输出
–comments 添加注释信息
​
--single-transaction
该选项在导出数据之前，设置事务隔离模式为 REPEATABLE READ，并提交一个 START TRANSACTION SQL 语句，BEGIN 不会阻塞任何应用程序且能保证导出时数据库的一致性状态。
它只适用于 InnoDB。
本选项和 --lock-tables 选项是互斥的，因为 LOCK TABLES 会使任何挂起的事务隐含提交。
要想导出大表的话，应结合使用 --quick 选项。
示例：mysqldump -uroot -p --host=localhost --all-databases --single-transaction
​
--tables
覆盖 --databases 或 -B 参数，指定需要导出的表名。
示例：mysqldump -uroot -p --host=localhost --databases test --tables test
​
--all-tablespaces, -Y
导出全部表空间。示例：mysqldump -uroot -p --all-databases --all-tablespaces
​
--no-tablespaces, -y
不导出任何表空间信息。示例：mysqldump -uroot -p --all-databases --no-tablespaces
​
--add-drop-database
每个数据库创建之前添加 drop 数据库语句。
示例：mysqldump -uroot -p --all-databases --add-drop-database
​
--add-drop-table
每个数据表创建之前添加 drop 数据表语句。
默认为打开状态，使用 --skip-add-drop-table 取消选项。
示例：mysqldump -uroot -p --all-databases --skip-add-drop-table (取消drop语句)
​
--add-locks
在每个表导出之前增加 LOCK TABLES 并且之后 UNLOCK TABLES。
默认为打开状态，使用 --skip-add-locks 取消选项。
示例：mysqldump -uroot -p --all-databases (默认添加 LOCK 语句)；
mysqldump -uroot -p --all-databases --skip-add-locks (取消 LOCK 语句)
​
--allow-keywords
允许创建是关键词的列名字。它由列名前冠以表名实现。
示例：mysqldump -uroot -p --all-databases --allow-keywords
​
--apply-slave-statements
在从库导出时，在 'CHANGE MASTER' 前添加 'STOP SLAVE'，并且在导出的最后添加'START SLAVE'。
示例：mysqldump -uroot -p --all-databases --apply-slave-statements
​
--character-sets-dir=dir_name
字符集文件的目录。
示例：mysqldump -uroot -p --all-databases --character-sets-dir=/usr/local/mysql/share/mysql/charsets
​
--comments, -i
附加注释信息，默认为打开，可以用 --skip-comments 取消。
示例：mysqldump -uroot -p --all-databases (默认记录注释)；
mysqldump -uroot -p --all-databases --skip-comments (取消注释)
​
--compatible=name
导出的数据将和其它数据库或旧版本的 MySQL 相兼容。
值可以为 ansi、mysql323、mysql40、postgresql、oracle、mssql、db2、maxdb、no_key_options、no_tables_options、no_field_options 等。
要使用几个值，用逗号将它们隔开。它并不保证能完全兼容，而是尽量兼容。
示例：mysqldump -uroot -p --all-databases --compatible=ansi
​
--compact
导出更少的输出信息(用于调试)。去掉注释和头尾等结构。该选项使 --skip-add-drop-table --skip-add-locks --skip-comments --skip-disable-keys 生效。
示例：mysqldump -uroot -p --all-databases --compact
​
--complete-insert,  -c
使用完整的 insert 语句(包含列名称)。
这么做能提高插入效率，但是可能会受到 max_allowed_packet 参数的影响而导致插入失败。
示例：mysqldump -uroot -p --all-databases --complete-insert
​
--compress, -C
在客户端和服务器之间启用压缩传递所有信息。
示例：mysqldump -uroot -p --all-databases --compress
​
--create-options
在 CREATE TABLE 语句中包括所有 MySQL 特性选项(默认为打开状态)。
示例：mysqldump -uroot -p --all-databases
​
--debug[=debug_options], -# [debug_options]
输出 debug 信息，用于调试。默认值为：d:t:o,/tmp/mysqldump.trace。
示例：mysqldump -uroot -p --all-databases --debug；
mysqldump -uroot -p --all-databases --debug=” d:t:o,/tmp/debug.trace”
​
--debug-check
程序退出时，打印一些调试信息。
示例：mysqldump -uroot -p --all-databases --debug-check
​
--debug-info
程序退出时，打印调试信息和内存、CPU 使用数据。
示例：mysqldump -uroot -p --all-databases --debug-info
​
--default-character-set
设置默认字符集，默认值为 utf8。
示例：mysqldump -uroot -p --all-databases --default-character-set=latin1
​
--delete-master-logs
master 备份后删除日志。这个参数将自动激活 --master-data。
示例：mysqldump -uroot -p --all-databases --delete-master-logs
​
--disable-keys, -K
对于每个表，在 INSERT 语句前后使用 /*!40000 ALTER TABLE tbl_name DISABLE KEYS */; 和 /*!40000 ALTER TABLE tbl_name ENABLE KEYS */;。
这样可以更快地导入 dump 出来的文件，因为它是在插入所有行后创建索引。该选项只适合 MyISAM 表，默认为打开状态。
示例：mysqldump -uroot -p --all-databases 
​
--dump-slave[=value]
该选项将导致主的 binlog 位置和文件名追加到导出数据的文件中。
设置为 1 时，将会以 CHANGE MASTER 命令输出到数据文件；设置为 2 时，在命令前增加说明信息。
该选项将会打开--lock-all-tables，除非 --single-transaction 被指定。
该选项会自动关闭 --lock-tables 选项。默认值为0。
示例：mysqldump -uroot -p --all-databases --dump-slave=1
​
--events, -E
导出事件。示例：mysqldump -uroot -p --all-databases --events
​
--extended-insert, -e
使用具有多个 VALUES 列的 INSERT 语法。这样使导出文件更小，并加快导入时的速度。
默认为打开状态，使用 --skip-extended-insert 取消选项。
示例：mysqldump -uroot -p --all-databases；
mysqldump -uroot -p --all-databases--skip-extended-insert (取消选项)
​
--fields-terminated-by=..., --fields-enclosed-by=..., --fields-optionally-enclosed-by=..., --fields-escaped-by=...
这些选项和 --tab 选项一起使用，和 LOAD DATA INFILE 的 FIELDS 语法类似。
--fields-terminated-by 列以指定符合分隔。
--fields-enclosed-by 输出文件中的各个字段用指定字符包裹。
--fields-optionally-enclosed-by 输出文件中的各个字段用给定字符选择性包裹。
--fields-escaped-by 输出文件中的各个字段忽略给定字符。
不能用于 --databases 和 --all-databases 选项。
示例：mysqldump -uroot -p test test --tab=”/home/mysql” --fields-terminated-by=”#” --fields-enclosed-by=”#” --fields-optionally-enclosed-by=”#” --fields-escaped-by=”#”
​
--flush-logs, -F
开始导出之前刷新日志。
请注意：假如一次导出多个数据库(使用选项 --databases 或者 --all-databases)，将会逐个数据库刷新日志。
当使用 --lock-all-tables 或者 --master-data 时，只刷新一次日志，相应的所有表同时被加上读锁。
因此，如果你想导出操作和刷新日志操作发生在同一时刻，应该将 --flush-logs 和 --lock-all-tables，--master-data 一起使用，或者 --flush-logs 和 --single-transaction 一起使用。
示例：mysqldump -uroot -p --all-databases --flush-logs
​
--flush-privileges
在导出 mysql 数据库之后，发出一条 FLUSH PRIVILEGES 语句。
为了正确恢复，该选项应该用于导出 mysql 数据库和依赖 mysql 数据库数据的任何时候。
示例：mysqldump -uroot -p --all-databases --flush-privileges
​
--force, -f
在导出过程中忽略出现的 SQL 错误。
示例：mysqldump -uroot -p --all-databases --force
​
--help, -?
显示帮助信息并退出。示例：mysqldump --help
​
--hex-blob
使用十六进制格式导出二进制字段。如果有二进制数据就必须使用该选项。
影响到的字段类型有BINARY、VARBINARY、BLOB。
示例：mysqldump -uroot -p --all-databases --hex-blob
​
--ignore-table
不导出指定表。指定忽略多个表时，需要重复多次，每次一个表。每个表必须同时指定数据库和表名。
例如：--ignore-table=database.table1 --ignore-table=database.table2。
示例：mysqldump -uroot -p --host=localhost --all-databases --ignore-table=mysql.user
​
--include-master-host-port
在 --dump-slave 产生的 'CHANGE MASTER TO..' 语句中增加 'MASTER_HOST=<host>，MASTER_PORT=<port>'。
示例：mysqldump -uroot -p --host=localhost --all-databases --include-master-host-port
​
--insert-ignore
在插入行时使用 INSERT IGNORE 语句。
示例：mysqldump -uroot -p --host=localhost --all-databases --insert-ignore
​
--lines-terminated-by
输出文件的每行用给定字符串划分。
与 --tab 选项一起使用，不能用于 --databases 和 --all-databases 选项。
示例：mysqldump -uroot -p --host=localhost test test --tab=”/tmp/mysql” --lines-terminated-by=”##”
​
--lock-all-tables, -x
提交请求锁定所有数据库中的所有表，以保证数据的一致性。
这是一个全局读锁，并且自动关闭 --single-transaction 和 --lock-tables 选项。
示例：mysqldump -uroot -p --host=localhost --all-databases --lock-all-tables
​
--lock-tables, -l
开始导出前，锁定所有表。用 READ LOCAL 锁定表以允许 MyISAM 表并行插入。
对于支持事务的表，例如 InnoDB，--single-transaction 是一个更好的选择，因为它根本不需要锁定表。
请注意，当导出多个数据库时，--lock-tables 分别为每个数据库锁定表。
因此，该选项不能保证导出文件中的表在数据库之间的逻辑一致性。不同数据库表的导出状态可以完全不同。
示例：mysqldump -uroot -p --host=localhost --all-databases --lock-tables
​
--log-error
附加警告和错误信息到给定文件。
示例：mysqldump -uroot -p --host=localhost --all-databases --log-error=/tmp/mysqldump_error_log.err
​
--master-data
该选项将 binlog 的位置和文件名追加到输出文件中。
如果为 1，将会输出 CHANGE MASTER 命令；
如果为 2，输出的 CHANGE MASTER 命令前添加注释信息。
该选项将打开 --lock-all-tables 选项，除非 --single-transaction 也被指定
（在这种情况下，全局读锁在开始导出时获得很短的时间；
其他内容参考 --single-transaction 选项）。
该选项自动关闭 --lock-tables选项。
示例：mysqldump -uroot -p --host=localhost --all-databases --master-data=1
​
max_allowed_packet
客户端/服务器通信的最大 buffer size。默认值为 24MB，最大值为 1GB。
示例：mysqldump -uroot -p --host=localhost --all-databases max_allowed_packet=10240
​
net_buffer_length
TCP/IP 和 socket 连接的缓存大小。
示例：mysqldump -uroot -p --host=localhost --all-databases net_buffer_length=1024
​
--no-autocommit
使用 autocommit/commit 语句包裹表。
示例：mysqldump -uroot -p --host=localhost --all-databases --no-autocommit
​
--no-create-db, -n
只导出数据，而不添加 CREATE DATABASE 语句。
示例：mysqldump -uroot -p --host=localhost --all-databases --no-create-db
​
--no-create-info, -t
只导出数据，而不添加 CREATE TABLE 语句。
示例：mysqldump -uroot -p --host=localhost --all-databases --no-create-info
​
--no-data, -d
不导出任何数据，只导出数据库表结构。
示例：mysqldump -uroot -p --host=localhost --all-databases --no-data
​
--no-set-names, -N
关闭 --set-charset 设置，等同于 --skip-set-charset。
示例：mysqldump -uroot -p --host=localhost --all-databases --no-set-names
​
--opt
等同于 --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset。
该选项默认开启，可以用 --skip-opt 禁用。
示例：mysqldump -uroot -p --host=localhost --all-databases --opt
​
--skip-opt
禁用 --opt 选项。
示例：mysqldump -uroot -p --host=localhost --all-databases --skip-opt
​
--order-by-primary
按主键导出每个表的记录，或者按照第一个唯一键，对每个表的记录进行排序。
在导出 MyISAM 表到 InnoDB 表时有用，但会使得导出工作花费很长时间。
示例：mysqldump -uroot -p --host=localhost --all-databases --order-by-primary
​
--pipe, -W 
在 windows 系统，使用命名管道连接 mysql。该选项只适用于服务器支持命名管道连接的情况。
示例：mysqldump -uroot -p --host=localhost --all-databases --pipe
​
--protocol={TCP|SOCKET|PIPE|MEMORY}
使用的连接协议，包括：tcp, socket, pipe, memory。
示例：mysqldump -uroot -p --host=localhost --all-databases --protocol=tcp
​
--quick, -q
不缓冲查询，直接导出到标准输出。默认为打开状态，使用 --skip-quick 取消该选项。
示例：mysqldump -uroot -p --host=localhost --all-databases；
mysqldump -uroot -p --host=localhost --all-databases --skip-quick
​
--quote-names, -Q
使用（`）引起表和列名。默认为打开状态，使用 --skip-quote-names 取消该选项。
示例：mysqldump -uroot -p --host=localhost --all-databases；
mysqldump -uroot -p --host=localhost --all-databases --skip-quote-names
​
--replace
使用 REPLACE INTO 取代 INSERT INTO。
示例：mysqldump -uroot -p --host=localhost --all-databases --replace
​
--result-file=file_name, -r file_name
直接输出到指定文件中。该选项应该用在使用回车换行的系统上（例如：DOS，Windows）。
该选项确保只有一行被使用。
示例：mysqldump -uroot -p --host=localhost --all-databases --result-file=/tmp/mysqldump_result_file.txt
​
--routines, -R
导出存储过程以及自定义函数。
示例：mysqldump -uroot -p --host=localhost --all-databases --routines
​
--set-charset
添加 'SET NAMES default_character_set' 到输出文件。
默认为打开状态，使用 --skip-set-charset 关闭选项。
示例：mysqldump -uroot -p --host=localhost --all-databases；
mysqldump -uroot -p --host=localhost --all-databases --skip-set-charset
​
--dump-date
将导出时间添加到输出文件中。默认为打开状态，使用 --skip-dump-date 关闭选项。
示例：mysqldump -uroot -p --host=localhost --all-databases；
mysqldump -uroot -p --host=localhost --all-databases --skip-dump-date
​
--socket, -S
指定连接 mysql 的 socket 文件位置，默认路径 /tmp/mysql.sock。
示例：mysqldump -uroot -p --host=localhost --all-databases --socket=/tmp/mysqld.sock
​
--tab=dir_name, -T dir_name
为每个表在给定路径创建 tab 分割的文本文件。
注意：仅仅用于 mysqldump 和 mysqld 服务器运行在相同机器上。
示例：mysqldump -uroot -p --host=localhost test test --tab="/home/mysql"
​
--where='where_condition', -w 'where_condition'
只转储给定的 WHERE 条件选择的记录。
请注意如果条件包含命令解释符专用空格或字符，一定要将条件引用起来。
示例：mysqldump -uroot -p --host=localhost --all-databases --where=” user=’root’”
​
--triggers
导出触发器。该选项默认启用，用 --skip-triggers 禁用它。
示例：mysqldump -uroot -p --host=localhost --all-databases --triggers
​
--tz-utc
在导出顶部设置时区 TIME_ZONE='+00:00'，以保证在不同时区导出的 TIMESTAMP 数据或者
数据被移到其他时区时的正确性。
示例：mysqldump -uroot -p --host=localhost --all-databases --tz-utc
​
--verbose, -v
输出多种平台信息。
​
--version, -V
输出 mysqldump 版本信息并退出。
​
--xml, -X
导出 XML 格式。
示例：mysqldump -uroot -p --host=localhost --all-databases --xml
​
--set-gtid-purged=value
该选项通过是否添加 SET @@global.gtid_purged 语句来控制是否向导出文件中写入全局事务ID（GTID）。
这个选项还可能导致在输出中写入一条语句，该语句在重新加载转储文件时禁用二进制日志记录。
该选项的值可能为：OFF、ON、AUTO。默认值为 AUTO。
各项取值含义如下所示：
OFF：不向输出文件中添加 SET 命令（跳过导 gtid）；
ON：向输出文件中添加 SET 命令。如果服务端不支持 GTIDs 会报错；
AUTO：如果服务端支持 GTIDs，会向输出文件中添加 SET 命令。
来源：https://blog.csdn.net/piaoranyuji/article/details/116193100

备份一个数据库

mysqldump –u username -p dbname table1 table2 …>BackupName.sql
  • dbname参数表示数据库的名称；
  • table1和table2参数表示表的名称；没有该参数时将备份整个数据库；
  • BackupName.sql参数表示备份文件的名称；文件名前面可以加上一个绝对路径。通常将数据库备份成一个后缀名为.sql的文件。

说明

mysqldump命令备份的文件并非一定要求后缀名为.sql，备份成其他格式的文件也是可以的，例如，后缀名为.txt的文件。但是，通常情况下是备份成后缀名为.sql的文件。因为，后缀名为.sql的文件给人第一感觉就是与数据库有关的文件。

例子

下面使用root用户备份test数据库下的student表，命令如下。

mysqldump –u root –p test student >D:\ student.sql

在DOS命令窗口中执行上面的命令时，将提示输入连接数据库的密码，输入密码后将完成数据备份，这时可以在D:\中找到student.sql文件。

上面student.sql文件中没有创建数据库的语句，因此，student.sql文件中的所有表和记录必须还原到一个已经存在的数据库中。还原数据时，CREATE TABLE语句会在数据库中创建表，然后执行INSERT语句向表中插入记录。

备份多个数据库

mysqldump –u username –p --databases dbname1 dbname2  >BackupName.sql

例子

下面使用root用户备份test数据库和mysql数据库，命令如下。

mysqldump –u root -p --databases test mysql  >D:\backup.sql

备份所有数据库

mysqldump命令备份所有数据库的语法如下。

mysqldump –u username –p --all –databases >BackupName.sql

使用--all –databases选项就可以备份所有数据库了。

例子

mysqldump –u root -p --all -databases  >D:\all.sql

数据库备份出现的问题

mysqldump命令不存在的问题

问题描述

command not found mysqldump

原因

系统默认会查找/usr/bin下的命令,如果这个命令不在这个目录下,当然会找不到命令。

解决方法：1、知道mysql命令或mysqldump命令的完整路径,可以使用find命令查找

find / -name mysqldump -print

比如mysql的路径是:/usr/local/mysql/bin/mysqldump

2、映射一个链接到/usr/bin目录下,相当于建立一个软链接文件

ln -s /usr/local/mysql/bin/mysqldump  /usr/bin

此时再使用mysqldump命令就可以了

crontab定时任务

什么是定时任务

任务调度的crond常驻命令 crond 是linux用来定期执行程序的命令。当安装完成操作系统之后，默认便会启动此任务调度命令。crond命令每分锺会定期检查是否有要执行的工作，如果有要执行的工作便会自动执行该工作。而linux任务调度的工作主要分为以下两类：

  • 系统执行的工作：系统周期性所要执行的工作，如备份系统数据、清理缓存
  • 个人执行的工作：某个用户定期要做的工作，例如每隔10分钟检查邮件服务器是否有新信，这些工作可由每个用户自行设置
  • Crontab是UNIX系统下的定时任务触发器，其使用者的权限记载在下列两个文件中：文件含义
/etc/cron.deny 该文件中所列的用户不允许使用Crontab命令
/etc/cron.allow 该文件中所列的用户允许使用Crontab命令
/var/spool/cron/ 是所有用户的crontab文件
/var/spool/cron/crontabs

控制定时任务命令

/sbin/service crond start //启动服务
/sbin/service crond stop //关闭服务
/sbin/service crond restart //重启服务
/sbin/service crond reload //重新载入配置
/sbin/service crond status //查看服务状态

crontab命令

crontab [-u username] [-l|-e|-r]
参数：
-u: 只有root才能进行这个任务，也即帮其他用户新建/删除crontab工作调度;
-e: 编辑crontab 的工作内容;
-l: 查阅crontab的工作内容;
-r: 删除所有的crontab的工作内容，若仅要删除一项，请用-e去编辑。

例子

  1. 键入 crontab -e 编辑crontab服务文件
*/2 * * * * /bin/sh/home/admin/jiaoben/buy/deleteFile.sh
  1. 查看该用户下的crontab服务是否创建成功， 用 crontab -l 命令
 crontab  -l
  1. 启动crontab服务
一般启动服务用 /sbin/service crond start 若是根用户的cron服务可以用 sudo service crond start
  1. 查看服务是否已经运行用 ps -ax | grep cron

时间格式

f1 f2 f3 f4 f5 program
  • 其中 f1 是表示分钟，f2 表示小时，f3 表示一个月份中的第几日，f4 表示月份，f5 表示一个星期中的第几天。program 表示要执行的程序。
  • 当 f1 为 * 时表示每分钟都要执行 program，f2 为 * 时表示每小时都要执行程序，其余类推
  • 当 f1 为 a-b 时表示从第 a 分钟到第 b 分钟这段时间内要执行，f2 为 a-b 时表示从第 a 到第 b 小时都要执行，其余类推
  • 当 f1 为 */n 时表示每 n 分钟个时间间隔执行一次，f2 为 */n 表示每 n 小时个时间间隔执行一次，其余类推
  • 当 f1 为 a, b, c,... 时表示第 a, b, c,... 分钟要执行，f2 为 a, b, c,... 时表示第 a, b, c...个小时要执行，其余类推

image-20230413163009262

avatar
文章
阅读
粉丝