Mysql备份与恢复

一、备份准备

1.1.备份环境

1.1.1.系统环境

cat /etc/centos-release
CentOS Linux release 7.5.1804 (Core)
docker version(我的mysql是通过docker-compose部署)
Version:1.13.1

1.1.2.软件环境

Server version: 5.7.23 MySQL Community Server (GPL)

1.2.备份工具

  • mysqldump/mysql
  • select into outfile 和 load data infile
  • 第三方客户端工具,如Navicat

1.3.备份场景

在日常工作中不谈场景就直接使用所知道的方法来备份就是坑自己,理论与实际相结合是万古不变的道理,mysql的备份尤其如此,下面是我工作当中碰到的一些场景

  1. 小数据量 + 无函数 + 无存储过程 + 无视图 此种情况直接使用第一种工具即可
  2. 小数据量 + 函数 + 存储过程 + 视图 第一种和第三种结合使用
  3. 大数据量 + 函数 + 存储过程 + 视图 第二种和第三种结合使用
  4. 大数据量 + 无函数 + 无存储过程 + 无视图 第二种工具(碰到的概率较低)
    ps:查看数据库的大小
    use information_schema;
    select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='dbname';

1.4.查看函数存储过程和视图

1.4.1.命令查看

查看函数
select `name` from mysql.proc where db = 'dbname' and type = 'FUNCTION';
查看存储过程
select `name` from mysql.proc where db = 'dbname' and type = 'PROCEDURE';
查看视图
show table status where comment='view';查看所有存在的视图
查看视图的具体信息
show create view viewname\G

1.4.2.第三方工具查看

可以直接使用Navicat连接源数据库查看数据库相关信息

二、开始备份

2.1.数据库说明

源数据库信息

  • 源数据库服务器容器名 mysql_source
  • 源数据库名 dbname
  • 源数据库用户 username1
  • 源数据库用户密码 password1
    目标数据库信息
  • 目标据库服务器容器名 mysql_backup
  • 目标据库名 dbname
  • 目标据库用户 username2
  • 目标据库用户密码 password2

2.2.场景一

  1. 将源数据库备份到源宿主机/backup下面
    docker exec mysql_source mysqldump -uusername1 -ppassword1 --databases dbname > /backup/dbname.sql
  2. 将备份出来的数据文件传输到目标服务器的/backup
    scp /backup/dbname.sql 目标服务器IP:/backup
  3. 通过mysql客户端使用目的主机目录/backup进行恢复或者把传过来的文件映射到容器里面然后进入容器去恢复
  • mysql -h ip -u username2 -p passowrd2 -P port --database dbname < /backup/dbname.sql
  • docker exec -it mysql_backup bash
    mysql -uusername2 -ppassword2 --database dbname < /backup/dbname.sql

2.3.场景二

  1. 同场景一的1、2、3
  2. 备份源数据库的函数及存储过程
    docker exec mysql_source mysqldump -uusername1 -ppassword1 -ntd -R dbname > /backup/profun.sql
  3. 同场景一的2
  4. 还原函数及存储过程到目标数据库
    mysql -u username2 -h IP -p -P port dbname < /backup/profun.sql
  5. 视图也是表,但它是虚拟的表,本身不存储数据,而是从其他的表中取数据,是其他表的抽象,两种方式实现视图的导出导入
  • 通过Navicat导出整个数据库的表结构,然后以记事本的方式找到相关视图语句复制,再通过命令在目标数据库中创建视图
  • 通过命令在源数据库中查看视图的具体定义,再到目标数据库中进行视图创建

2.4.场景三

为了方便描述,下面我以一个真实的例子来记录我是如何在该场景下进行备份恢复的
具体需求:把服务器172.168.1.111上的mysql里的数据库hhldb迁移到服务器172.168.1.209上
具体分析:当时通过命令查询到改数据库的大小为200G,如果我们以第一种方式或者第三种方式来进行备份恢复将花费几天的时间,这是不可取的,如果使用第三种方式来备份数据库的结构,然后以第二种方式来导出导入数据,将使时间缩短20倍
具体思路:

  • 利用Navicat查看hhldb数据库的整体信息,表、视图、函数
  • 利用Navicat或者命令导出数据库结构,再将其恢复至目标数据库
  • 通过命令查询mysql_source的安全目录
  • 将mysql_source的安全目录映射到宿主机上的目录/backup,需要赋予该目录777的权限
  • 使用select into outfile导出数据到mysql_source的安全目录
  • 传输/backup里的数据到目标服务器上/backup中
  • 通过命令查询mysql_backup的安全目录
  • 将/backup目录映射到mysql_backup的安全目录
  • 使用load data infile将数据从mysql_backup安全目录导入到目标数据库中
    具体步骤:
  1. hhldb通过Navicat查看,发现有88个表,但是通过命令发现有93各表,其实这是因为命令查看的时候把视图也当作一张表显示出来了,我们只需要导出除视图外的其他表数据即可
  2. 第三方工具导出数据库结构就不说了,这里主要说一说使用命令来导出单个数据库的结构
  • 备份:docker exec mysql_source mysqldump -uusernmae1 -ppassword1 dbname -d > /backup/jiegou.sql
  • 恢复:msyql -h ip -u username2 -p password2 dbname < /backup/jiegou.sql
  1. 查看mysql_source的安全目录
    为什么要查看这个目录,这是因为如果用第二种方式来导出数据必须指定其安全目录作为其导出数据的路径,查看命令 show variables like "%secure%";
    10bu-huo
  2. 表过多我们使用了脚本来批量导出表数据
  • 先将所有的表的名称按行输入到一个文件中,这里就叫做line.txt
    cat line.txt
advanceinfo
associatedgenes
category_associate_trait
chrlg
chromosome_list
cuser
db_config
description_info
dna_gens
dna_groups
dna_run
dynamic_info
fpkm
fpkm_associate_indel
fpkm_associate_snp
fpkm_new
gene_regularity_network
gens_anno_go
gens_anno_ipr
gens_anno_kegg
gens_baseinfo
gens_family
gens_family_rel
gens_family_structure
gens_for_first
gens_offset
gens_sequence
gens_structure
gens_transcript_seq
group_type
group_type_attribute
homologous_gene
indel
indel_consequencetype
index_explain
login_info
marker
marker_position
mrna_gens
qtl
qtl_gene
qtlref
resc
role
role_resc
snp
snp_
snp_chr01
snp_chr02
snp_chr03
snp_chr04
snp_chr05
snp_chr06
snp_chr07
snp_chr08
snp_chr09
snp_chr10
snp_chr11
snp_chr12
snp_chr13
snp_chr14
snp_chr15
snp_chr16
snp_chr17
snp_chr18
snp_chr19
snp_chr20
snp_consequencetype
snp_test
snp_test01
snp_test02
snp_test03
snp_test04
snp_test05
soybean
study
tables
token
trait_category
trait_list
user
user_associate_trait_fpkm
user_role
zs_exondb
zs_goanno
zs_ipranno
zs_pfamdb
zs_tfdb
  • 编写批量导出脚本
    cat exportmysql.sh
#!/bin/bash

a=$(cat line.txt)
b=($a)
for db in ${b[@]}
do
docker exec mysql_source mysql -uusename1 -ppassword1 -e "use hhldb;SELECT * FROM ${db} INTO OUTFILE '/data/output/${db}.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'  LINES TERMINATED BY '\n';"
done
  1. scp /backup/* 172.168.1.209:/backup/
  2. 查看mysql_backup的安全目录
    10sandnd
  3. 导入表数据到目标数据库中
    cat line.txt同前面
    cat importmysql.sh
#!/bin/bash

a=$(cat /data/importmysql/line.txt)
b=($a)
for db in ${b[@]}
do
docker exec mysql_backup mysql -uusername2 -ppassword2 -e "use hhldb;LOAD DATA INFILE '/var/lib/mysql-files/${db}.txt' INTO TABLE ${db} FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'  LINES TERMINATED BY '\n';"
done
  1. 比较导入的数据与源数据库的大小,通过Navicat来打开目标数据库中的表、视图,同时与源数据库的表视图进行比较,以此来确认导入数据是否完整

2.5.场景四

这个没啥说的,按照场景三导出导入数据的部分来就可以了

2.6.需要注意的问题

2.6.1.问题一

在进行导入导出的时候不以安全目录为路径会发生错误ERROR 1290 (HY000) at line 1: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement ,如果mysql是部署在宿主机上,那么安全目录最好777权限

2.6.2.问题二

在导入数据的时候发生错误ERROR 1452 (23000) at line 1: Cannot add or update a child row: a foreign key constraint fails (hhldb.category_associate_trait, CONSTRAINT category_associate_trait_ibfk_1 FOREIGN KEY (trait_category_id) REFERENCES trait_category (id))这是因为表trait_category是category_associate_trait的父表,我们在导入数据的时候要先导入父表的数据,不过即使发生这种错误也不用担心,只需要完成脚本导入后,在此手动导入这两个表的数据即可

2.6.3.问题三

一个关于视图的错误,当我把数据全部导入后,发现snp_score这个视图无法打开,提示我'username2'@'%'could not ...,这是因为原视图创建的时候带了usernmae1的信息,我们目标数据库使用username2来连接目标数据库的,所以我们需要在目标数据库上删除该视图,然后再次使用命令来创建该视图,但是需要把视图定义语句中的usernmae1修改成username2,下面是原来的视图语句

CREATE ALGORITHM=UNDEFINED DEFINER=`username1`@`%` SQL SECURITY DEFINER VIEW `snp_score` AS select `a`.`gene_id` AS `gene_id`,(case when isnull((sum((`f`.`score` * `e`.`consequencetype_count`)) / count(`e`.`snp_id`))) then 0 else (sum((`f`.`score` * `e`.`consequencetype_count`)) / count(`e`.`snp_id`)) end) AS `snp_score` from ((`fpkm` `a` left join `fpkm_associate_snp` `e` on((`e`.`fpkm_id` = `a`.`id`))) left join `snp_consequencetype` `f` on((`e`.`snp_id` = `f`.`id`))) where (`e`.`snp_id` not in (6,13,14)) group by `a`.`gene_id`

将其中的username1改成username2然后再次创建视图

2.6.4.问题四

我们在手动创建视图的时候报错“ERROR 1054 (42S22): Unknown column 'fpkm.tracheobronchiallymph_node' in 'field list'”,解决办法:找到对应的表和字段,然后修改sql语句里的错误字段,比如这个错误我们先通过navicat找到表fpkm的正确字段tracheobronchiallymph_node,注意这里肯定不是tracheobronchiallymph_node,而是类似的一个字段,可能只差一个下划线,然后把创建视图的语句用记事本打开,然后全局替换为正确的字段

2.6.5.问题五

前面通过docker exec 容器名。。。类似的命令可以进行备份到宿主机上,但是恢复数据使用这种方式失败了,最后还是使用在宿主机上安装mysql客户端来恢复

2.6.6.问题六

恢复函数的时候报错“This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable)”,解决办法:
set global log_bin_trust_function_creators=TRUE;

2.7.补充

2.7.1.补充一

函数存储过程视图属于数据库的结构,备份数据库结构恢复时就会把函数存储过程和视图恢复,但是使用mysqldump和mysql来备份恢复的是无法备份恢复函数存储过程的,只能备份和恢复视图

2.7.2.补充二

前面的几个场景全都是针对整个数据库备份然后恢复至另外的数据库服务器上,如果目标数据库上已经有数据了,要求把源数据库的数据添加到目标数据库上该如何操作,我的思路如下:

  • 首先将源数据库的每一个表的结构导出来
  • 然后导出每一个表的数据
  • 目标数据库上导入每一个表的结构
  • 目标数据库上导入每一个表的数据
  1. 导出单个表的结构
    docker exec mysql_source mysqldump -uusername1 -ppassword1 -d dbname tablename > /backup/tablename.sql
    批量导出表结构脚本
    cat jiegouexport.sh
#!/bin/bash

a=$(cat line.txt)
b=($a)
for table in ${b[@]}
do
docker exec mysql_source mysqldump -uusername1 -ppassword1 -d dbname ${table}  > ./backup/${table}.sql
done
  1. 导出数据参考场景三
  2. 导入单个表的结构
    mysql -u username2 -h ip -p -P 33066 dbname < /backup/table.sql
    批量导入表结构脚本
    cat jiegouimport.sh
#!/bin/bash

a=$(cat line.txt)
b=($a)
for table in ${b[@]}
do
mysql -u username2 -h ip -ppassword2 -P 33066 dbname <  /backup/${table}.sql
done
  1. 导入数据参考场景三

2.7.3.补充三

在不同数据库之间导入导出数据推荐使用第二种工具,方便写脚本批量导出导入

2.7.4.补充四

当我们想导出某几张表(包含数据和结构)且表的数据量不是很大的时候使用方法二就大材小用了,mysql本身就支持单独备份表恢复表的功能
下面是一个实际例子

  1. 批量备份某几张表
    cat line.txt
gene_anno_go
gene_anno_ipr
gene_baseinfo
gene_sequence            
gene_structure           
gene_transcript_sequence 
genome        
version_download_info

cat export.sh

#!/bin/bash

a=$(cat line.txt)
b=($a)
for table in ${b[@]}
do
docker exec mysql_source mysqldump -uusername1 -ppassword1 dbname ${table}  > ./${table}.sql
done

2、批量恢复某几张表
cat import.sh

#!/bin/bash

a=$(cat line.txt)
b=($a)
for table in ${b[@]}
do
mysql -uusername2 -h IP -ppassword2 -P port dbname  <  ./${table}.sql
done

2.8.参考链接

select into outfile & load data infile
安全目录
视图相关