mysql数据库-备份与还原

备份

mysqldump 命令备份数据

在MySQL中提供了使用命令行，导出数据库数据到文件的一种方便的工具 mysqldump ,我们可以通过命令行直接实现数据库内容的导出。

首先我们简单了解一下mysqldump命令用法:

# MySQLdump常用
/usr/local/mysql/bin/mysqldump -u xx -p databases > /xxx/xxxdb.sql

• mysqldump : mysql安装后bin目录下的执行文件
• -u ：mysql 用户名
• -p ：mysql 密码
• databases ：数据库名称
• ">" : 备份输出的方向
• /xxx/xxxdb.sql ：表示备份到哪个文件

注意：备份多个数据库，需要使用 "--databases" 参数，后面根据数据库名称，使用空格分隔。

mysqldump -uroot -proot --databases db1 db2 > /xxx/xxxdb.sql

mysqldump 常用操作示例

1.备份全部数据库的数据和结构

mysqldump -uroot -p123456 -A > /data/mysqlDump/mydb.sql

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

mysqldump -uroot -p123456 -A -d > /data/mysqlDump/mydb.sql

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

mysqldump -uroot -p123456 -A -t > /data/mysqlDump/mydb.sql

4.备份单个数据库 "将 -A 替换 对应的数据库名即可"。注意： -d -t 效果相同。

mysqldump -uroot-p123456 mydb > /data/mysqlDump/mydb.sql

5.备份多个数据库

mysqldump -uroot -p123456 --databases db1 db2 > /data/mysqlDump/mydb.sql

还原

还原 MySQL 备份内容

有两种只要的方式还原：

​ 第一种使用 mysql 服务命令还原：

• 在系统命令行中，输入如下实现还原：

mysql -uroot -proot databases < /xxx/xxxdb.sql

mysql ：该命令表示mysql的服务程序

注意：-u -p databases 与备份时意义相同。

​ 第二种使用 sql 命令还原：

• 登录进入mysql 库中,通过source指令找到对应系统中的文件进行还原：

mysql> source /xxx/xxxdb.sql

source ：数据库还原命令

/xxx/xxxdb.sql : 表示还原的备份文件

​ 第三种全数据库还原：

mysql -uroot -proot< /xxx/xxxdb.sql

注意：xxxdb.sql 文件一定是全部数据库的备份文件

编写mysql数据库备份脚本

1.创建脚本文件

在Linux中，使用vi或者vim编写脚本内容并命名为：mysql_dump_script.sh

实例：该文件根据我自己需求编写的mysql脚本。（个人请根据情况自行修改）

#!/bin/bash

#保存备份个数，备份31天数据
number=31
#备份保存路径
backup_dir=/back
#日期
dd=`date +%Y-%m-%d-%H-%M-%S`
#备份工具
tool=/usr/local/mysql/bin/mysqldump
#用户名
username=root
#密码
password=root
#将要备份的数据库
database_name=jn-dev

#如果文件夹不存在则创建
if [ ! -d $backup_dir ];
then     
    mkdir -p $backup_dir;
fi

#数据库备份命令 - 简单写法 mysqldump -u root -p123456 users > /root/mysqlbackup/users-$filename.sql
$tool -u $username -p$password $database_name > $backup_dir/$database_name-$dd.sql

#写创建备份日志
echo "create $backup_dir/$database_name-$dd.dupm" >> $backup_dir/log.txt

#找出需要删除的备份
delfile=`ls -l -crt $backup_dir/*.sql | awk '{print $9 }' | head -1`

#判断现在的备份数量是否大于$number
count=`ls -l -crt $backup_dir/*.sql | awk '{print $9 }' | wc -l`

if [ $count -gt $number ]
then
  #删除最早生成的备份，只保留number数量的备份
  rm $delfile
  #写删除文件日志
  echo "delete $delfile" >> $backup_dir/log.txt
fi

如上代码主要含义如下：

1.首先设置各项参数，例如number最多需要备份的数目，备份路径，用户名，密码等。

2.执行mysqldump命令保存备份文件，并将操作打印至同目录下的log.txt中标记操作日志。

3.定义需要删除的文件：通过ls命令获取第九列，即文件名列，再通过实现定义操作时间最晚的那个需要删除的文件。

4.定义备份数量：通过ls命令加上wc -l统计以sql结尾的文件的行数。

5.如果文件超出限制大小，就删除最早创建的sql文件

2.设置脚本的执行权限

进入脚本文件存在目录下执行

chmod u+x *.sh

chmod详解

chmod：文件/目录权限设置命令

用包含字母和操作符表达式的文字设定法

其语法格式为：chmod [who] [opt] [mode] 文件/目录名

chmod u+x *.sh

• who代表对象，是以下字母中的一个或组合：

  u：User，即文件或目录的拥有者。
  g：Group，即文件或目录的所属群组。
  o：Other，除了文件或目录拥有者或所属群组之外，其他用户皆属于这个范围。
  a：All，即全部的用户，包含拥有者，所属群组以及其他用户。

• opt则是代表操作，可以为：
  +：添加某个权限
  -：取消某个权限
  =：赋予给定的权限，并取消原有的权限

• mode则代表权限：
  r：可读
  w：可写
  x：可执行

• 符号：

​ "*"：通配符

注意 ：为了保证脚本文件的正确性可以测试一下。（下面的目录的写法是在该文件的同级目录下的）

sh mysql_dump_script.sh

使用crontab定期执行备份脚本

价绍

1. 在 Linux 中，周期执行的任务一般由cron这个守护进程来处理[ps -ef|grep cron]
2. cron读取一个或多个配置文件，这些配置文件中包含了命令行及其调用时间
3. cron的配置文件称为“crontab”，是“cron table”的简写
4. cron该服务默认是开机启动的

crontab的使用

crontab [-u username]　　　　//省略用户表表示操作当前用户的crontab
    -e      (编辑工作表)
    -l      (列出工作表里的命令)
    -r      (删除工作作)

cron服务

• CentOS6上的cron命令

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

• CentOS7上的cron命令

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

//或者
crond start
crond stop
crond restart
crond reload
crond status

lock情况

错误：crond: can’t lock /var/run/crond.pid

rm -rf /var/run/crond.pid

crontab的使用

crontab的命令构成为 ：时间+动作

时间 ：从前到后一次为分、时、日、月、周五种，操作符有 ：

• "*" 取值范围内的所有数字

• "/" 每过多少个数字

• "-"从X到Z

• ","散列数字

动作 ：指定任务脚本。

/back/mysql_dump_script.sh

注意 ：

• 使用/etc/crontab文件时 需要在动作前面加上用户。

• 时间 用户/动作 >> 日志地址

创建定时任务

使用crontab创建定时任务有两种方式：

第一种：crontab -e (是某个用户的周期计划任务)

1.执行命令

crontab -e

2.执行之后会打开一个文件，在文件中增加定时任务。

*/5 * * * * /back/mysql_dump_script.sh >> /back/mysql_dump_script.log
<--mysql_dump_script.log 定时任务执行的日志文件-->
<-- 五分钟执行一次-->

第二种：vi /etc/crontab (/etc/crontab是系统的周期任务)

1.执行命令

vi /etc/crontab

2.执行之后会打开一个文件，在文件中增加定时任务（注意 root 是用户）。

*/5 * * * * root/back/mysql_dump_script.sh >> /back/mysql_dump_script.log
<--mysql_dump_script.log 定时任务执行的日志文件-->
<-- 五分钟执行一次-->

/etc/crontab文件和crontab -e命令区别

• 格式不同 ：crontab -e与/etc/crontab修改语法格式不一样，/etc/crontab多一个user指定
• crontab -e 是针对用户的cron来设计的，任何用户都可以编辑任务。需要注意的是：crontab -e的作用其实是/usr/bin/crontab这个执行文件。
• /etc/crontab 是针对系统的cron来设计的。如果是系统的例行性任务，则编辑到/etc/crontab文件就可以了，但是/etc/crontab是个纯文本文件，只能使用root的身份编辑这个文件。

注意：修改编辑以上两种方式的文件都需要重启一下cron服务

取消定时任务

1. crontab -l 表示列出全部的定时任务

   crontab -l 
   

2. 取消定时任务

• 全部取消 crontab -r

  crontab -r
  

• 取消某个 crontab -e 配置文件，删除要取消的哪一行配置

   crontab -e
  

mysql常见错误

mysql服务未启动

mysqldump: Got error: 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) when trying to connect
当试图连接时，无法通过套接字‘/tmp/mysql.sock’（2）连接到本地MySQL服务器

密码风险提示

mysql: [Warning] Using a password on the command line interface can be insecure.
在命令行界面上使用密码可能是不安全的。