mysql 备份与恢复

最新推荐文章于 2022-08-15 17:30:00 发布

愿而自知

最新推荐文章于 2022-08-15 17:30:00 发布

阅读量186

点赞数

文章标签： linux

本文链接：https://blog.csdn.net/weixin_47746630/article/details/109264507

版权

1. 二进制格式mysql安装

mysql下载地址

上网下载安装包
[root@linux131 ~]# ls /usr/src/
debug  kernels  mysql-5.7.31-linux-glibc2.12-x86_64.tar

[root@linux131 src]# du -sh mysql-5.7.31-linux-glibc2.12-x86_64.tar
662M	mysql-5.7.31-linux-glibc2.12-x86_64.tar

创建用户和组
[root@linux131 src]# useradd -r -M -s /sbin/nologin mysql
[root@linux131 src]# id mysql
uid=994(mysql) gid=990(mysql) groups=990(mysql)

解压软件至/usr/local/
[root@linux131 local]# tar -xf mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz 
[root@linux131 local]# ls
bin      lib64                                            sbin
etc      libexec                                          share
games    mysql-5.7.31-linux-glibc2.12-x86_64   src

[root@linux131 local]# ln -s mysql-5.7.31-linux-glibc2.12-x86_64 mysql
[root@linux131 local]# ll   //修改为软链接
total 677452
drwxr-xr-x. 2 root root          6 Aug 12  2018 bin
drwxr-xr-x. 2 root root          6 Aug 12  2018 etc
drwxr-xr-x. 2 root root          6 Aug 12  2018 games
drwxr-xr-x. 2 root root          6 Aug 12  2018 include
drwxr-xr-x. 2 root root          6 Aug 12  2018 lib
drwxr-xr-x. 2 root root          6 Aug 12  2018 lib64
drwxr-xr-x. 2 root root          6 Aug 12  2018 libexec
lrwxrwxrwx  1 root root         35 Oct 22 18:15 mysql -> mysql-5.7.31-linux-glibc2.12-x86_64
drwxr-xr-x  9 7161 31415       129 Jun  2 21:11 mysql-5.7.31-linux-glibc2.12-x86_64
drwxr-xr-x. 2 root root          6 Aug 12  2018 sbin
drwxr-xr-x. 5 root root         49 Aug 21 21:54 share
drwxr-xr-x. 2 root root          6 Aug 12  2018 src

添加环境变量
[root@linux131 local]# vim /etc/profile.d/mysql.sh
export PATH=/usr/local/mysql/bin:$PATH

[root@linux131 local]# source /etc/profile.d/mysql.sh   //重新读取
[root@linux131 local]# echo $PATH
/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin

建立数据存放目录
[root@linux131 local]# mkdir /opt/data
[root@linux131 local]# chown -R mysql.mysql /opt/data
[root@linux131 local]# ll /opt/
total 0
drwxr-xr-x  2 mysql   mysql     6 Oct 22 18:29 data

初始化数据库
[root@linux131 ~]# mysqld --initialize --user=mysql --datadir=/opt/data/
2020-10-22T10:35:21.818164Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-10-22T10:35:22.084992Z 0 [Warning] InnoDB: New log files created, LSN=45790
2020-10-22T10:35:22.125413Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2020-10-22T10:35:22.133577Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 49b3f236-1452-11eb-b0ee-000c29a3a2cf.
2020-10-22T10:35:22.134587Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2020-10-22T10:35:22.799186Z 0 [Warning] CA certificate ca.pem is self signed.
2020-10-22T10:35:22.974799Z 1 [Note] A temporary password is generated for root@localhost: Xpe1ngOo(jq?
//请注意，这个命令的最后会生成一个临时密码，随机的，此处密码是Xpe1ngOo(jq?
//最好把密码写进一个文档里，避免忘记
[root@linux131 ~]# echo 'Xpe1ngOo(jq?' > pass   
[root@linux131 ~]# ls
anaconda-ks.cfg  pass

生成配置文件
[root@linux131 ~]# ls /etc/my.cof    //没有这个文件就自己生成
ls: cannot access '/etc/my.cof': No such file or directory
[root@linux131 ~]# vim /etc/my.cof

[mysqld]
basedir = /usr/local/mysql     //数据库安装位置
datadir = /opt/data            //数据存放位子
socket = /tmp/mysql.sock       //套接字
port = 3306                    //默认端口
pid-file = /opt/data/mysql.pid //进程文件放置地
user = mysql                   //那个用户启动
skip-name-resolive             //跳过名称解析

配置服务启动脚本
[root@linux131 ~]# cp -a /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld    //复制信息

[root@linux131 ~]# vim /etc/init.d/mysqld    //找到这两行加上后面的路径
basedir=/usr/local/mysql
datadir=/opt/data

启动mysql服务
[root@linux131 ~]# service mysqld start
Starting MySQL.Logging to '/opt/data/linux131.err'.
 SUCCESS! 
[root@linux131 ~]# ss -antl
State  Recv-Q   Send-Q      Local Address:Port      Peer Address:Port  
LISTEN 0        32                      *:21                   *:*     
LISTEN 0        64                   [::]:2049              [::]:*     
LISTEN 0        80                      *:3306                 *:*     
LISTEN 0        50                   [::]:139               [::]:*   
[root@linux131 ~]# ps -ef|grep mysql
root       1805      1  0 19:01 pts/2    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/opt/data --pid-file=/opt/data/linux131.pid
mysql      1900   1805  0 19:01 pts/2    00:00:00 /usr/local/mysql/binmysqld --basedir=/usr/local/mysql --datadir=/opt/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=linux131.err --pid-file=/opt/data/linux131.pid
root       1935   1730  0 19:02 pts/2    00:00:00 grep --color=auto mysql

设置开机自动启动
[root@linux131 ~]# chkconfig --add mysqld
[root@linux131 ~]# chkconfig --list

Note: This output shows SysV services only and does not include native
      systemd services. SysV configuration data might be overridden by native
      systemd configuration.

      If you want to list systemd services use 'systemctl list-unit-files'.
      To see services enabled on particular target use
      'systemctl list-dependencies [target]'.

mysqld         	0:off	1:off	2:on	3:on	4:on	5:on	6:off

安装这两个包
[root@linux131 ~]# yum -y install ncurses-devel
[root@linux131 ~]# yum -y install ncurses-compat-libs

[root@linux131 ~]# rpm -qa|grep ncurses
ncurses-base-6.1-7.20180224.el8.noarch
ncurses-c++-libs-6.1-7.20180224.el8.x86_64
ncurses-6.1-7.20180224.el8.x86_64
ncurses-libs-6.1-7.20180224.el8.x86_64
ncurses-devel-6.1-7.20180224.el8.x86_64
ncurses-compat-libs-6.1-7.20180224.el8.x86_64

登录数据库，修改密码
[root@linux131 ~]# mysql -uroot -pXpe1ngOo(jq?
-bash: syntax error near unexpected token `('   //报错的话按照以下修改

[root@linux131 ~]# mysql -uroot -pXpe1ngOo\(\jq?   //带括号的需要加\转译下
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.31

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

修改密码
mysql> set password = password('lp123456');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> quit
Bye

重新登录验证密码
[root@linux131 ~]# mysql -uroot -plp123456

2. mysql配置文件

mysql的配置文件为/etc/my.cnf

配置文件查找次序：若在多个配置文件中均有设定，则最后找到的最终生效

/etc/my.cnf --> /etc/mysql/my.cnf --> --default-extra-file=/PATH/TO/CONF_FILE --> ~/.my.cnf

家目录写一个配置文件，把用户和密码写进去
[root@linux131 ~]# vim .my.cnf
[client]
user = root
password = lp123456

验证登录，不用密码
[root@linux131 ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.31 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> quit
Bye
[root@linux131 ~]# mysql -e 'show database;'
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'database' at line 1
[root@linux131 ~]# mysql -e 'show databases;'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

注意：用那个账号登录，就把密码写到那个用户的家目录

mysql常用配置文件参数：

参数	说明
port = 3306	设置监听端口
socket=/tmp/mysql.sock	指定套接字文件位置
basedir=/usr/local/mysql	指定MySQL的安装路径
datadir = /data/mysql	指定MySQL的数据存放路径
pid-file=/data/mysql/mysql.pid	指定进程ID文件存放路径
user = mysql	指定MySQL以什么用户的身份提供服务
skip-name-resolve	禁止MySQL对外部连接进行DNS解析使用这一选项可以消除MySQL进行DNS解析的时间。若开启该选项，则所有远程主机连接授权都要使用IP地址方式否则MySQL将无法正常处理连接请求

3. mysql数据库备份与恢复

3.1 数据库常用备份方案

数据库备份方案：

全量备份
增量备份
差异备份

备份方案	特点
全量备份	全量备份就是指对某一个时间点上的所有数据或应用进行的一个完全拷贝。数据恢复快。备份时间长
增量备份	增量备份是指在一次全备份或上一次增量备份后，以后每次的备份只需备份与前一次相比增加和者被修改的文件。这就意味着，第一次增量备份的对象是进行全备后所产生的增加和修改的文件；第二次增量备份的对象是进行第一次增量备份后所产生的增加和修改的文件，如此类推。没有重复的备份数据备份时间短恢复数据时必须按一定的顺序进行
差异备份	备份上一次的完全备份后发生变化的所有文件。差异备份是指在一次全备份后到进行差异备份的这段时间内对那些增加或者修改文件的备份。在进行恢复时，我们只需对第一次全量备份和最后一次差异备份进行恢复。

数据少可以用全量备份，数据多就用差异备份，周天全量备份

3.2 mysql备份工具mysqldump

//语法：
    mysqldump [OPTIONS] database [tables ...]        //备份指定数据库表格
    mysqldump [OPTIONS] --all-databases [OPTIONS]    //备份所有数据库
    mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]  //备份指定的整个数据库
    
//常用的OPTIONS：
    -uUSERNAME      //指定数据库用户名
    -hHOST          //指定服务器主机，请使用ip地址
    -pPASSWORD      //指定数据库用户的密码
    -P#             //指定数据库监听的端口，这里的#需用实际的端口号代替，如-P3307
    
注意：用source恢复表格要进到指定要恢复的数据库里，也可以用mysql在数据库外执行恢复备份

备份恢复一个表

创建数据库
mysql> create database school;
Query OK, 1 row affected (0.00 sec)

mysql> use school;
Database changed

创建表
mysql> create table student(id int not null primary key auto_increment,name varchar(50),score float);
Query OK, 0 rows affected (0.05 sec)

mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(50) | YES  |     | NULL    |                |
| score | float       | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.03 sec)

mysql> select * from student;
Empty set (0.01 sec)

表格内插入信息
mysql> insert student(name,score) values('tom',20),('jerr',80),('zhangs(han',60),('lisi',90);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from student;
+----+-----------+-------+
| id | name      | score |
+----+-----------+-------+
|  1 | tom       |    20 |
|  2 | jerr      |    80 |
|  3 | zhangshan |    60 |
|  4 | lisi      |    90 |
+----+-----------+-------+
4 rows in set (0.00 sec)

再创建一个表
mysql> create table student1(id int not null primary key auto_increment,,name varchar(50),score float);
Query OK, 0 rows affected (0.03 sec)

mysql> desc student1;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(50) | YES  |     | NULL    |                |
| score | float       | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> select * from student1;
Empty set (0.00 sec)

备份root账户里，school数据库里，student这张表里的数据，保存到student.sql里
[root@linux131 ~]# mysqldump -uroot -plp123456 school student > student.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

查看备份student表的详细信息
[root@linux131 ~]# ls
anaconda-ks.cfg  pass  student.sql

[root@linux131 ~]# less student.sql

-- MySQL dump 10.13  Distrib 5.7.31, for linux-glibc2.12 (x86_64)
--
-- Host: localhost    Database: school
-- ------------------------------------------------------
-- Server version       5.7.31

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `student`
--

删除student表
mysql> drop table student;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| student1         |
+------------------+
1 row in set (0.00 sec)

重新读取备份文件student.sql
mysql> source student.sql;      
...

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

查看表信息，student表已经恢复
mysql> show tables;          
+------------------+
| Tables_in_school |
+------------------+
| student          |
| student1         |
+------------------+
2 rows in set (0.00 sec)

mysql> select * from student;
+----+-----------+-------+
| id | name      | score |
+----+-----------+-------+
|  1 | tom       |    20 |
|  2 | jerr      |    80 |
|  3 | zhangshan |    60 |
|  4 | lisi      |    90 |
+----+-----------+-------+
4 rows in set (0.00 sec)

也可以在数据库外恢复表格，得加上指定恢复到那个库
[root@linux131 ~]# mysql school < student.sql

备份一个数据库里的两个表格

[root@linux131 ~]# cat .my.cnf
[client]
user = root
password = lp123456

前面把用户和密码写到文件里，后面执行备份不用加，备份名加上日期，后期好查找恢复
备份两个表就在库后面跟上两个表名
[root@linux131 ~]# mysqldump school student student1 > student-202010241135.sql
[root@linux131 ~]# less student-202010241135.sql

-- MySQL dump 10.13  Distrib 5.7.31, for linux-glibc2.12 (x86_64)
--
-- Host: localhost    Database: school
-- ------------------------------------------------------
-- Server version       5.7.31

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `student`

备份两个数据库的内容

创建两个数据库
mysql> create database runtime;
Query OK, 1 row affected (0.01 sec)

mysql> create database xiaozhan;
Query OK, 1 row affected (0.00 sec)

进入runtime，创建表格info，插入内容
mysql> use runtime;
Database changed
mysql> insert into info(name,salary) values('tom',7000),('jerry',10000);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from info;
+----+-------+--------+
| id | name  | salary |
+----+-------+--------+
|  1 | tom   |   7000 |
|  2 | jerry |  10000 |
+----+-------+--------+
2 rows in set (0.00 sec)

进入xiaozhan，创建表格student，插入内容
mysql> use xiaozhan;
Database changed
mysql> create table student(id int not null primary key auto_increment,name varchar(50),salary float);
Query OK, 0 rows affected (0.04 sec)

mysql> insert student(name,salary) values('zhangshan',5000),('lisi',8000);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from student;
+----+-----------+--------+
| id | name      | salary |
+----+-----------+--------+
|  1 | zhangshan |   5000 |
|  2 | lisi      |   8000 |
+----+-----------+--------+
2 rows in set (0.01 sec)

查看所有数据库
[root@linux131 ~]# mysql -e 'show databases;'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| runtime            |
| school             |
| sys                |
| xiaozhan           |
+--------------------+

备份runtime和xiaozhan这两个数据库，加上--databases
[root@linux131 ~]# mysqldump --databases runtime xiaozhan > multi_database-20201024.sql
[root@linux131 ~]# ls
anaconda-ks.cfg              student-202010241127.sql
multi_database-20201024.sql  student-202010241135.sql
pass                         student.sql

查看数据库备份过程，要先删除，再备份所有数据
[root@linux131 ~]# less multi_database-20201024.sql

-- MySQL dump 10.13  Distrib 5.7.31, for linux-glibc2.12 (x86_64)
--
-- Host: localhost    Database: runtime
-- ------------------------------------------------------
-- Server version       5.7.31

DROP TABLE IF EXISTS `student`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `salary` float DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
-- Dumping data for table `student`
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Current Database: `runtime`

删除runtime和xiaozhan数据库
mysql> drop database runtime;
Query OK, 1 row affected (0.04 sec)

mysql> drop database xiaozhan;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

恢复这两个数据库
[root@linux131 ~]# mysql < multi_database-20201024.sql

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| runtime            |
| school             |
| sys                |
| xiaozhan           |
+--------------------+
7 rows in set (0.00 sec)

注意：备份到哪里去用大于号>，恢复到哪里去用小于号<

全量备份 --all-databases

先删除之前的备份信息

备份所有数据库
[root@linux131 ~]# mysqldump --all-databases > all-202010241210.sql
[root@linux131 ~]# ls
all-202010241210.sql  anaconda-ks.cfg  pass

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| runtime            |
| school             |
| sys                |
| xiaozhan           |
+--------------------+
7 rows in set (0.00 sec)

删除数据库
mysql> drop database runtime;
Query OK, 1 row affected (0.00 sec)

mysql> drop database xiaozhan;
Query OK, 1 row affected (0.01 sec)

mysql> drop database school;
Query OK, 2 rows affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

注意：系统自带的数据库不要删，自己创建的可以删除

恢复备份
[root@linux131 ~]# mysql < all-202010241210.sql
[root@linux131 ~]# mysql -e 'show databases;'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| runtime            |
| school             |
| sys                |
| xiaozhan           |
+--------------------+

进入school，查看student表
mysql> use school;
Database changed
mysql> select * from student;
+----+-----------+-------+
| id | name      | score |
+----+-----------+-------+
|  1 | tom       |    20 |
|  2 | jerr      |    80 |
|  3 | zhangshan |    60 |
|  4 | lisi      |    90 |
+----+-----------+-------+
4 rows in set (0.00 sec)

修改信息
mysql> update student set score = 100 where name = 'tom';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from student;
+----+-----------+-------+
| id | name      | score |
+----+-----------+-------+
|  1 | tom       |   100 |
|  2 | jerr      |    80 |
|  3 | zhangshan |    60 |
|  4 | lisi      |    90 |
+----+-----------+-------+
4 rows in set (0.00 sec)

再恢复备份
[root@linux131 ~]# mysql < all-202010241210.sql

查看表信息，恢复到修改之前
[root@linux131 ~]# mysql -e 'select * from school.student;'
+----+-----------+-------+
| id | name      | score |
+----+-----------+-------+
|  1 | tom       |    20 |
|  2 | jerr      |    80 |
|  3 | zhangshan |    60 |
|  4 | lisi      |    90 |
+----+-----------+-------+

再次修改信息
mysql> update student set score = 100 where name = 'tom';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from student;
+----+-----------+-------+
| id | name      | score |
+----+-----------+-------+
|  1 | tom       |   100 |
|  2 | jerr      |    80 |
|  3 | zhangshan |    60 |
|  4 | lisi      |    90 |
+----+-----------+-------+
4 rows in set (0.00 sec)

再全量备份
[root@linux131 ~]# mysqldump --all-databases > all-202010241228.sql
删除数据库
[root@linux131 ~]# mysql -e 'drop database school;'
恢复备份
[root@linux131 ~]# mysql < all-202010241228.sql

mysql> select * from student;
+----+-----------+-------+
| id | name      | score |
+----+-----------+-------+
|  1 | tom       |   100 |
|  2 | jerr      |    80 |
|  3 | zhangshan |    60 |
|  4 | lisi      |    90 |
+----+-----------+-------+
4 rows in set (0.00 sec)

注意：恢复备份只是恢复全量备份备份数据里的内容，之后做的操作对恢复后的备份没有影响

3.4 差异备份与恢复

3.4.1. mysql差异备份

开启MySQL服务器的二进制日志功能

编辑配置文件，加上最后两行
[root@linux131 ~]# cat /etc/my.cof 
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve

server-id = 6          //设置服务器标示符
log-bin = mysql_bin    //启动二进制日志记录的功能
       
重启服务  
[root@linux131 ~]# service mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 

查看现有数据库和里面的内容
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| lixian             |
| mysql              |
| performance_schema |
| school             |
| sys                |
| xiaozhan           |
+--------------------+
7 rows in set (0.00 sec)

mysql> select * from xiaozhan.student;
+----+-----------+--------+
| id | name      | salary |
+----+-----------+--------+
|  1 | zhangshan |   5000 |
|  2 | lisi      |   8000 |
+----+-----------+--------+
2 rows in set (0.00 sec)

mysql> select * from school.student;
+----+------------+-------+
| id | name       | score |
+----+------------+-------+
|  1 | tom        |    20 |
|  2 | jerr       |    80 |
|  3 | zhangs(han |    60 |
|  4 | lisi       |    90 |
+----+------------+-------+
4 rows in set (0.00 sec)

刷新日志，记录到事务日志里，全量备份
[root@linux131 ~]# mysqldump -uroot -plp123456 --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-20201024.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@linux131 ~]# ls
all-20201024.sql  anaconda-ks.cfg  pass

mysql> use school;
Database changed

student插入新内容
mysql> insert student(name,score) values('yangmi',22),('xixi',19);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

student修改内容
mysql> update student set score = 66 where name = 'tom';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from student;
+----+------------+-------+
| id | name       | score |
+----+------------+-------+
|  1 | tom        |    66 |
|  2 | jerr       |    80 |
|  3 | zhangs(han |    60 |
|  4 | lisi       |    90 |
|  5 | yangmi     |    22 |
|  6 | xixi       |    19 |
+----+------------+-------+
6 rows in set (0.00 sec)

3.4.2. mysql差异备份恢复

模拟误删数据

删除数据库school
[root@linux131 ~]# mysql -e 'drop database school;'
[root@linux131 ~]# mysql -e 'show databases;'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| lixian             |
| mysql              |
| performance_schema |
| sys                |
| xiaozhan           |
+--------------------+

刷新二进制日志
[root@linux131 ~]# ll /opt/data
total 122976
drwxr-x--- 2 mysql mysql     4096 Oct 24 16:18 mysql
-rw-r----- 1 mysql mysql      898 Oct 24 17:00 mysql_bin.000002
-rw-r----- 1 mysql mysql       19 Oct 24 16:52 mysql_bin.index

刷新日志文件 mysql_bin.000002这个文件记录了数据库最后发生修改的动作
[root@linux131 ~]# mysqladmin -uroot -plp123456 flush-logs
[root@linux131 ~]# ll /opt/data
drwxr-x--- 2 mysql mysql     4096 Oct 24 16:18 mysql
-rw-r----- 1 mysql mysql      945 Oct 24 17:03 mysql_bin.000002
-rw-r----- 1 mysql mysql      154 Oct 24 17:03 mysql_bin.000003
-rw-r----- 1 mysql mysql       38 Oct 24 17:03 mysql_bin.index

恢复第一次的全量备份
[root@linux131 ~]# mysql -uroot -plp123456 < all-20201024.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@linux131 ~]# mysql -e 'show databases;'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| lixian             |
| mysql              |
| performance_schema |
| school             |
| sys                |
| xiaozhan           |
+--------------------+

[root@linux131 ~]# mysql -e 'show tables from school;'
+------------------+
| Tables_in_school |
+------------------+
| student          |
| student1         |
+------------------+

[root@linux131 ~]# mysql -e 'select * from school.student;'
+----+------------+-------+
| id | name       | score |
+----+------------+-------+
|  1 | tom        |    20 |
|  2 | jerr       |    80 |
|  3 | zhangs(han |    60 |
|  4 | lisi       |    90 |
+----+------------+-------+

[root@linux131 ~]# ll /opt/data/mysql_bin*
-rw-r----- 1 mysql mysql    945 Oct 24 17:03 /opt/data/mysql_bin.000002
-rw-r----- 1 mysql mysql 846253 Oct 24 17:05 /opt/data/mysql_bin.000003
-rw-r----- 1 mysql mysql     38 Oct 24 17:03 /opt/data/mysql_bin.index

查看误删的进程位置，误删的位置进程在898，恢复误删就得恢复前面一个进程800
mysql> mysql> show binlog events in 'mysql_bin.000002';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql_bin.000002 |   4 | Format_desc    |         6 |         123 | Server ver: 5.7.31-log, Binlog ver: 4 |
| mysql_bin.000002 | 123 | Previous_gtids |         6 |         154 |                                       |
| mysql_bin.000002 | 154 | Anonymous_Gtid |         6 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000002 | 219 | Query          |         6 |         293 | BEGIN                                 |
| mysql_bin.000002 | 293 | Table_map      |         6 |         350 | table_id: 145 (school.student)        |
| mysql_bin.000002 | 350 | Write_rows     |         6 |         415 | table_id: 145 flags: STMT_END_F       |
| mysql_bin.000002 | 415 | Xid            |         6 |         446 | COMMIT /* xid=549 */                  |
| mysql_bin.000002 | 446 | Anonymous_Gtid |         6 |         511 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000002 | 511 | Query          |         6 |         585 | BEGIN                                 |
| mysql_bin.000002 | 585 | Table_map      |         6 |         642 | table_id: 145 (school.student)        |
| mysql_bin.000002 | 642 | Update_rows    |         6 |         704 | table_id: 145 flags: STMT_END_F       |
| mysql_bin.000002 | 704 | Xid            |         6 |         735 | COMMIT /* xid=551 */                  |
| mysql_bin.000002 | 735 | Anonymous_Gtid |         6 |         800 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000002 | 800 | Query          |         6 |         898 | drop database school                  |
| mysql_bin.000002 | 898 | Rotate         |         6 |         945 | mysql_bin.000003;pos=4                |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
15 rows in set (0.00 sec)

使用mysqlbinlog恢复差异备份
[root@linux131 ~]# mysqlbinlog --stop-position=800 /opt/data/mysql_bin.000002 |mysql

查看恢复结果
[root@linux131 ~]# mysql -e 'select * from school.student;'
+----+------------+-------+
| id | name       | score |
+----+------------+-------+
|  1 | tom        |    66 |
|  2 | jerr       |    80 |
|  3 | zhangs(han |    60 |
|  4 | lisi       |    90 |
|  5 | yangmi     |    22 |
|  6 | xixi       |    19 |
+----+------------+-------+

注意：恢复差异备份后在做一次全量备份，数据就不会因为误删没有