MySQL数据备份和数据恢复

一、备份的相关名词

1.1.物理备份

        原始数据在操作系统呈什么样的表现形式，备份出来也是什么样。

1.2.逻辑备份

       数据库的逻辑备份保存的是数据库逻辑结构（CREATE DATABASE，CREATE TABLE语句），以及所存储的数据（转换成INSERT语句或者定界文件）。

       备份集是基于查询MySQL服务获得的数据库结构及内容信息；

1.3.联机备份

      数据库服务运行期间执行备份操作。

1.4.脱机备份

       冷备份，数据库服务停止运行的时候进行备份。

1.5.本地备份

        MySQL服务器上执行备份。

1.6.远程备份

       远程服务器执行备份。

1.7.完整备份

        完整备份也叫全量备份，指备份数据库中的所有数据。

1.8.增量备份

        只备份指定时间点后发生的修改。

二、备份工具

 2.1复制表相关的文件的方式创建备份集，这中备份方式只是适合MyISAM引擎     

mysql> create database backtest;
Query OK, 1 row affected (0.01 sec)

mysql> use backtest;
Database changed

sql> create table myisambak(id int) engine=myisam;
Query OK, 0 rows affected (0.01 sec)

[root@localhost mysql]# cd backtest/
[root@localhost backtest]# ls
db.opt myisambak.frm myisambak.MYD myisambak.MYI

mysql> flush tables myisambak with read lock;
Query OK, 0 rows affected (0.02 sec)

[root@localhost backtest]# cp myisambak.* /var/lib/databack/

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

 2.2使用SQL语句创建备份

     这中备份方式不包含表结构。

mysql> select * from myisambak into outfile '/var/lib/mysql-files/myisambak.text';
Query OK, 4 rows affected (0.00 sec)

2.3冷复制方式创建物理备份

        关闭mysql数据库，复制data目录。

三、mysqldump备份与恢复

 3.1 mysqldump备份，

！！！！！！！！！！！！！默认会锁表，要加参数但是如果加上–single-transaction会加上事务，不会锁表

[root@localhost ~]# mysqldump -uroot -p gis

  备份到指定的目录下面

[root@localhost ~]# mysqldump -uroot -p gis > /var/lib/mysql-files/gisbak.sql

备份多个数据库

[root@localhost ~]# mysqldump -uroot -p --databases heng heng2 > /tmp/hengbak.sql

输出定界格式文件，当以定界格式输出时候，mysqldump输出信息默认就不在是标准输出，而是根据对象，生成两个文件，一个扩展名为.txt，存数据，另外一个是存储结构扩展名为.sql

[root@localhost ~]# mysqldump -uroot -p --tab=/var/lib/mysql-files gis

指定分隔符

[root@localhost tmp]# mysqldump -uroot -p --tab=/tmp/bak backtest myisambak --fields-terminated-by=',' --fields-enclosed-by='"'
Enter password:

查看结果：

[root@localhost bak]# cat myisambak.txt
"2","aa"
"3","dd"
"4","bb"
"5","cc"

3.2 mysqldump恢复

通过mysqldump命令附加--tab参数导出后，每个表对象会有两个同名的文件，扩展名为.sql，保存对象创建的脚本，另外哪个为.txt保存的是数据，恢复时候首先打开.sql的文件，

 

[root@localhost bak]# mysql -uroot -p backtest < /tmp/bak/myisambak.sql
Enter password: 

恢复数据文件

mysql> load data infile '/tmp/bak/myisambak.txt' into table myisambak fields terminated by ',' enclosed by '"';
Query OK, 4 rows affected (0.00 sec)
Records: 4  Deleted: 0  Skipped: 0  Warnings: 0

用source命令恢复sql语句：

mysql> source /tmp/bak/myisambak.sql
Query OK, 0 rows affected (0.00 sec)

3.3自动化备份策略

库级备份

[root@localhost tmp]# mysqldump -uroot -p -A -R -x --default-character-set=utf8 | gzip > /tmp/dbfulbak_`date +%F`.sql.gz
Enter password:

-A   all  database;    -R   Dump stored routines (functions and procedures)     -x     lock-all-tables

 3.4XtraBackup备份

下载;

[root@localhost tmp]# wget -P /tmp http://www.percona.com/redir/downloads/XtraBackup/XtraBackup-2.0.7/binary/Linux/x86_64/percona-xtrabackup-2.0.7-552.tar.gz

  [root@localhost tmp]# tar -zxvf percona-xtrabackup-2.0.7-552.tar.gz -C /usr/local/

  [root@localhost ~]# echo "export PATH=/usr/local/percona-xtrabackup-2.0.7/bin:\$PATH" >> /root/.bash_profile

  mysql> create user xtrabak@'localhost' identified by 'Password@123';
  Query OK, 0 rows affected (0.00 sec)

 

mysql> grant reload,lock tables,Replication client,super on *.* to xtrabak@'localhost';
Query OK, 0 rows affected (0.05 sec)

备份报错：

[root@localhost bin]# ./xtrabackup_56 --defaults-file=/etc/my.cnf --backup --target-dir=/tmp/bak20220614
./xtrabackup_56: error while loading shared libraries: libssl.so.6: cannot open shared object file: No such file or directory

[root@localhost bin]# yum -y install openssl*
已加载插件：fastestmirror, langpacks
Loading mirror speeds from cached hostfile
my | 3.6 kB 00:00:00
软件包 1:openssl-libs-1.0.2k-19.el7.x86_64 已安装并且是最新版本
软件包 1:openssl-1.0.2k-19.el7.x86_64 已安装并且是最新版本
正在解决依赖关系
--> 正在检查事务