MYSQL数据备份之mysqldump命令详解（附脚本定时备份）

一、常见备份命令介绍

备份命令	备份速度	恢复速度	介绍	功能	一般用于
cp	快	快	物理备份、灵活性低	很弱	少量数据备份
mysqldump	慢	慢	逻辑备份、适用所有存储引擎	一般	中小型数据量备份
xtrabackup	较快	较快	实现innodb热备、对存储引擎有要求	强大	较大规模的备份

热备份指的是当数据库进行备份时, 数据库的读写操作均不是受影响
温备份指的是当数据库进行备份时, 数据库的读操作可以执行,但是不能执行写操作
冷备份指的是当数据库进行备份时, 数据库不能进行读写操作, 即数据库要下线

二、mysqldump备份

2.1、mysqldump命令介绍

[root@]> mysqldump -help

Usage: mysqldump [OPTIONS] database_name [tables]
OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR     mysqldump [OPTIONS] --all-databases [OPTIONS]
For more options, use mysqldump --help

1.登录选项：
    -u user：指定用户
    -h host：指定主机
    -p：表示要使用密码
    -E, --events: 备份事件调度器
    -R, --routines: 备份存储过程和存储函数
2.备份选项：
    --all-databases：备份所有数据库
    --databases db1 db2：备份指定的数据库
    --single-transaction：对事务引擎执行热备
    --flush-logs：更新二进制日志文件
    --master-data=2
        1：每备份一个库就生成一个新的二进制文件(默认)
        2：只生成一个新的二进制文件
    --quick：在备份大表时指定该选项

2.2、查看所属数据库、用户的权限

1、查看mysql数据库中的所有用户：

mysql>SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;

2、查看数据库中具体某个用户的权限:

mysql> show grants for 'user'@'localhost'; 
或
mysql> select * from mysql.user where user='root'\G; 

3、修改用户权限：

mysql> grent all on *.* to `user`@`localhost`;
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec).

4、向数据库施加读锁

mysql> FLUSH TABLES WITH READ LOCK;   
Query OK, 0 rows affected (0.00 sec)

三、命令备份示例

**导出命令大全**
#导出education数据库里面的users表的表数据和表结构
	mysqldump -u[用户名] -h[ip] -p[密码] -P[端口号] 数据库名 表名 >导出的文件名.sql
	mysqldump -uroot -h127.0.0.1 -proot -P3306 education users>d:/user.sql
#导出包括系统数据库在内的所有数据库（all.sql默认保存在bin文件夹下面）
	mysqldump -uroot -proot --all-databases>all.sql
#导出多张表：
	mysqldump -uroot -proot --databases test --tables t1 t2>two.sql
#只导出表结构不导表数据，添加“-d”命令参数
    mysqldump -uroot -h127.0.0.1 -proot -P3306 -d education users>d:/user.sql
#只导出表数据不导表结构，添加“-t”命令参数
    mysqldump -uroot -h127.0.0.1 -proot -P3306 -t education users>d:/user.sql
#只导出test数据库的表结构
 导出：mysqldump -uroot -proot --no-data --databases test>s1.sql
 导入：mysql -uroot -proot -h127.0.0.1 -P3306 test<s1.sql
 
**导入命令大全**
 格式：mysql -h[ip] -P[(大写)端口] -u[用户名] -p[密码]  [数据库名] < d:XX.sql(路径) 
 	mysql -uroot -proot -h127.0.0.1 -P3306 education<d:/database.sql
 	mysql -uroot -proot -h127.0.0.1 -P3306 <d:/all_database.sql 
#命令行导入
  mysql> use test;
  mysql> source /home/test/database.sql

四、脚本备份示例：

#!/bin/bash
#NAME:数据库备份
#DATE:*/*/*
#USER:***

#设置本机数据库登录信息
mysql_user="user"
mysql_password="passwd"
mysql_host="localhost"
mysql_port="3306"
mysql_charset="utf8mb4"
date_time=`date +%Y-%m-%d-%H-%M`

#保存目录中的文件个数
count=10
#备份路径
path=/***/

#备份数据库sql文件并指定目录
mysqldump --all-databases --single-transaction --flush-logs --master-data=2 -h$mysql_host -u$mysql_user -p$mysql_password > $path_$(date +%Y%m%d_%H:%M).sql
[ $? -eq 0 ] && echo "-----------------数据备份成功_$date_time-----------------" || echo "-----------------数据备份失败-----------------"

#找出需要删除的备份
delfile=`ls -l -crt $path/*.sql | awk '{print $9 }' | head -1`
#判断现在的备份数量是否大于阈值
number=`ls -l -crt $path/*.sql | awk '{print $9 }' | wc -l`
if [ $number -gt $count ]
then
          rm $delfile  #删除最早生成的备份，只保留count数量的备份
                                           #更新删除文件日志
            echo "-----------------已删除过去备份sql $delfile-----------------"
    fi

增加定时备份

crontab -e

*    *    *    *    *
-    -    -    -    -
|    |    |    |    |
|    |    |    |    +----------星期中星期几 (0 - 6) (星期天 为0)
|    |    |    +---------------月份 (1 - 12) 
|    |    +--------------------一个月中的第几天 (1 - 31)
|    +-------------------------小时 (0 - 23)
+------------------------------分钟 (0 - 59)

添加定时任务(每天12:50以及23:50执行备份操作)
50 12,23 * * * cd /home/;sh backup.sh >> log.txt