在Linux系统中定时备份MySQL
数据备份是数据安全中一项很重要的工作,定时备份可以在数据遭受误删除后顺利恢复。将损失降到最低。
1.准备MySQL备份文件夹
一般会创建类似 /var/backups/
的目录来保存备份的数据
sudo mkdir /var/backups/
如果备份的账号不是root那么就需要修改/var/backups
目录的所有者和所属组
sudo chown $(whoami):$(whoami) /var/backups
2. mysqldump导出工具
mysqldump
命令可以用来执行逻辑备份,生成SQL语句。
用法如下:
mysqldump -u [username] –p[password] [database_name] > /path/to/[database_name].sql
-
-u [username]
= MySQL用户。 -
–p[password]
= 密码。-p
注意和密码之间没有空格。 -
[database_name]
= 数据库名称 -
>
= 输出转储到/path/to/[database_name].sql
-
– 转储文件的路径和名称。
[database_name]
可以是任何你想要的。 -
关于 –p[密码]的说明
mysqldump可以在命令行输入密码,但是不建议这样写,因为会直接泄漏数据库密码,推荐以下方式。
将密码存储在~/.my.cnf
其中,用–p[password]
从命令中省略该选项。用--defaults-extra-file=/path/to/.my.cnf
选项来间接获取密码。
3. MySQL 备份
(1).备份所有数据库
可以使用该 --all-databases
选项转储所有 MySQL 数据库。在以下示例中,我们将所有数据库转储到 /var/backups/
目录.
mysqldump -u root -p[password] --all-databases > /var/backups/all-databases.sql
(2).备份单个数据库
要备份单个 MySQL 数据库,只需替换[database_name]
成指定的数据库名。
mysqldump -u root -p[password] [database_name] > /var/backups/[database_name].sql
(3).备份多个数据库
mysqldump -u root -p[password] [database_1_name] [database_2_name] > /var/backups/[database_names].sql
(4).备份单个表
要从 MySQL 数据库备份单个表,只需在数据库名称后输入表名即可。
mysqldump -u root -p[password] [database_name] [table_name] > /var/backups/[table_name].sql
(5).备份多个表
要从 MySQL 数据库备份多个表,只需在数据库名称后输入表名,用空格分隔。
mysqldump -u root -p[password] [database_name] [table_1_name] [table_2_name] > /var/backups/[table_names].sql
(6).备份远程数据库
要备份远程 MySQL 数据库,只需使用-h
后跟远程 IP 地址或主机名的选项。
mysqldump -h [ip_or_hostname] -u root -p[password] [database_name] > /var/backups/[database_name].sql
(7).使用压缩备份数据库
我们可以在备份数据的同时用gzip
命令压缩备份文件,节省磁盘空间。
mysqldump -u root -p[password] [database_name] | gzip -c > /var/backups/[database_name].sql.gz
恢复MySQL
用mysql
命令将 .sql 文件还原到数据库。
mysql -u [username] –p[password] [database_name] < /path/to/[database_name].sql
-
-u [username]
= MySQL用户名 -
–p[password]
= 用户密码。-p
注意和密码之间没有空格 。 -
[database_name]
= 数据库名称 -
<
后面跟上/path/to/[database_name].sql
备份文件
自动 MySQL 备份
cron
是 Linux 中用于调度自动命令的服务。这些存储在一个名为 crontab
sudo crontab -e
00 01 * * * mysqldump -u root -p[password] [database_name] | gzip -c > /var/backups/[database_name].sql.gz
00 01 * * *
将在凌晨 1 点运行命令并覆盖.sql.gz
文件 ,保存并关闭 crontab
,到时间就会生成sql.gz
文件。
ls -l /var/backups/
-rw-r--r-- 1 root root 66M July 3 16:33 [database_name].sql.gz
MySQL 自动备份
每日 MySQL 备份
如果你只想每天对数据库进行 MySQL 备份,请使用下面的 crontab 在每天凌晨 1 点创建备份。
00 01 * * * mysqldump -u root -p[password] [database_name] | gzip -c > /var/backups/[database_name].sql.gz
7 天滚动 MySQL 备份
在这种情况下,凌晨1点运行MySQL数据库备份命令。
00 01 * * * mysqldump -u root -p[password] [database_name] | gzip -c > /var/backups/[database_name].`date +\%a`.sql.gz
将在凌晨1点启动一次备份。这部分 date +\%a
将在文件名中添加星期几(周一、周二、周三等)。这样就有了sql.gz
过去7天的每一天的文件,不用考虑删除过多的备份文件。注意这里用到了%
符号,必须使用\
进行转义。
以下是生成的文件列表
ls -l /var/backups/
-rw-r--r-- 1 root root 69M July 3 01:00 A1.Mon.sql.gz
-rw-r--r-- 1 root root 70M July 4 01:00 A1.Tue.sql.gz
-rw-r--r-- 1 root root 70M July 5 01:00 A1.Wed.sql.gz
-rw-r--r-- 1 root root 70M July 6 01:00 A1.Thu.sql.gz
-rw-r--r-- 1 root root 72M July 7 01:00 A1.Fri.sql.gz
-rw-r--r-- 1 root root 73M July 8 01:00 A1.Sat.sql.gz
-rw-r--r-- 1 root root 73M July 9 01:00 A1.Sun.sql.gz