mysql 备份耗资源不_mysql完整性备份 还原 及备份注意事项

mysql完全备份操作：案例

mysql数据库的完全备份可以采用多种方式，物理冷备份一般采用tar命令直接打包数据库文件夹(数据目录)，而在备份前需要先停库。

直接打包数据库文件夹，源码包的位置/usr/local/mysql/data/，rpm包的位置/var/lib/mariadb/

systemctl start mariadb  //启动mariadb服务

ls /var/lib/mysql/  //查看一下mysql下内容

#mysqladmin -u root password 123456  //给数据库设置一个密码，若有密码就不需要操作这步

mysql -uroot -p000000  //连接mysql

create database auth;   //创建一个auth的数据库

use auth  //进入auth数据库里

create table user(name char(10) not null,ID int(48));   //创建表，第一列name长度10位不能为空，第二列ID整数(int)48位

insert into user values('crushlinux','123');   // 插入一个名字为crushlinux，ID位123的数据

select * from user;   //查一下名为user的表

systemctl stop mariadb   //退出数据库停止服务

ls /var/lib/mysql/   //查看一下是否有auth

ls /var/lib/mysql/auth/   //查看auth下是否有user

yum 0y install xz  //安装xz

mkdir backup   //建立一个叫backup的目录

tar Jcf backup/mysql_all-$(date +%F).tar.xz /var/lib/mysql/   //(Jcf创建打包压缩的文件包)，创建一个叫mysql并带有年月日的压缩包放入backup下

ls backup/  //查看一下backup下的压缩包(备份了整个mysql的目录)

模拟数据丢失：

rm -rf /var/lib/mysql/auth/   //删除auth

ls /var/lib/mysql/   //查看，此时缺少一个库

systemctl start mariadb  //启动服务

show databases;   //进入mysql查看所有库，已经没有了删除的那个库

mkdir restore   //退出后创建一个文件夹(名字随意)

tar xf backup/mysql_all-2019-10-14.tar.xz -C restore/    //将之前备份的压缩包解压到创建的目录下

cd restore/

ls

cd var/lib/mysql/

ls

mv auth/ /var/lib/mysql/      //将auth移动到/var/lib/mysql/下，重新进入mysql后进行查看，此时auth库已经恢复

select * from auth.user;    //查看auth库内容，此时内容也已经恢复

使用专用备份工具mysqldump：

mysql自带的备份工具mysqldump，可以很方便的对mysql进行备份。通过命令该工具可以将数据库、数据表或全部的库导出为sql脚本。

便于该命令在不同版本的mysql服务器上使用。例如，当需要升级mysql服务器时，可以先使用mysqldump命令将原有库信息导出，然后直接在升级后的mysql服务器中导入即可。

1.对单个库进行完全备份

格式：mysqldump -u用户名 -p[密码] [选项] --databases [数据库名] > /备份路径/备份文件名

示例： mysqldump -uroot -p000000 --databases auth > backup/auth-$(date +%Y%m%d).sql

cat backup/auth-20191014.sql    //查看一下备份内容

2.对多个库进行完全备份

格式：mysqldump -u用户名 -p [密码] [选项] --databases 库名1 [库名2]… > /备份路径/备份文件名

示例：mysqldump -uroot -p000000 --databases auth > backup/auth-$(date +%Y%m%d).sql

过滤一下备份内容

grep -Ev "^/|^$|^-" backup/auth-20191014.sql    //将内容中的以‘/’’ 开头的，空行、‘-’开头的去掉

3.对所有库进行完全备份

格式：mysqldump -u用户名 -p [密码] [选项] --opt --all-databases > /备份路径/备份文件名

示例：mysqldump -uroot -p000000 --events --opt --all-databases > backup/mysql01_all.$(date +%Y%m%d).sql      //--opt加快备份速度，当备份数据量大时使用

4.对表进行完全备份

格式：mysqldump -u用户名 -p [密码] [选项] 数据库名 表名 > /备份路径/备份文件名

示例：mysqldump -uroot -p000000 auth user > backup/auth2_user-$(date +%Y%m%d).sql

5.对标的结构进行备份【面试题】

格式：mysqldump -u用户名 -p [密码] -d 数据库名 表名 > /备份路径/备份文件名

示例： mysqldump -uroot -p000000 -d mysql user > backup/desc01_mysql_user-$(date +%Y%m%d).sql

使用mysqldump备份后，恢复数据库

1.source命令：

登录到mysql数据库，执行source备份sql脚本路径

示例：

进入数据库查看所有库

drop database auth;  //删除auth库

ll backup/auth1-20191014.sql    //查看一下备份的auth库

source backup/auth1-20191014.sql   //在mysql中执行source命令

show databases;   //再一次查看所有库，此时auth已经恢复

select * from auth.user;   //查看auth库，库内数据也已经恢复

2.mysql命令

格式一：

mysql -u 用户名 -p[密码] < 库备份脚本路径

示例：

4删除auth库，使其不存在auth，为下面实验做准备

mysql -uroot -p000000 < backup/auth1-20191014.sql    //连接并重定向输入到mysql

重新登录mysql，并查看库和库内数据

格式二：

mysql -u 用户名 -p[密码] 库名 < 表备份脚本路径

示例：

MySQL备份思路：

1、定期实施备份，指定备份计划或策略，并严格遵守

2、除了进行完全备份，开启MySQL服务器的binlog日志功能是很重要的(完全备份加上日志，可以对MySQL进行最大化还原)

3、使用统一和易理解的备份名称，推荐使用库名或者表名加上时间的命名规则，如mysql_user-20181214.sql，不要使用backup1或者abc之类没有意义的名字。

4.定期抽查备份的可靠性，做还原测试或者检查文件大小等方式

5.通过异地或者跨机房等方式来存放备份数据，防止源数据和备份文件一起损坏。