mysql数据库备份与恢复
数据库常用备份方案
数据库备份方案：
- 全量备份
- 增量备份
- 差异备份
| 备份方案 | 特点 |
|---|---|
| 全量备份 | 全量备份就是指对某一个时间点上的所有数据或应用进行的一个完全拷贝。 数据恢复快。 备份时间长 |
| 增量备份 | 增量备份是指在一次全备份或上一次增量备份后，以后每次的备份只需备份与前一次相比增加和者被修改的文件。这就意味着，第一次增量备份的对象是进行全备后所产生的增加和修改的文件；第二次增量备份的对象是进行第一次增量备份后所产生的增加和修改的文件，如此类推。 没有重复的备份数据 备份时间短 恢复数据时必须按一定的顺序进行 |
| 差异备份 | 备份上一次的完全备份后发生变化的所有文件。 差异备份是指在一次全备份后到进行差异备份的这段时间内 对那些增加或者修改文件的备份。在进行恢复时，我们只需对第一次全量备份和最后一次差异备份进行恢复 |
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
//备份整个数据库(全备)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| yc |
+--------------------+
5 rows in set (0.00 sec)
mysql> use yc;
Database changed
mysql> show tables;
+----- --------+
| Tables_in_yc |
+------- ------+
| student |
| student1 |
| student2 |
+--------------+
3 rows in set (0.00 sec)
[root@localhost ~]# ls
anaconda-ks.cfg
[root@localhost ~]# mysqldump -uroot -p -h192.168.23,130 --all-databases > all20201230.sql
Enter password:
[root@localhost ~]# ls
all20201230.sql anaconda-ks.cfg
//备份yc库的student1表和student2表
[root@localhost ~]# mysqldump -uroot -p -h192.168.23.130 yc student1 student2 > table-20201230.sql
Enter password:
[root@localhost ~]# ls
all-20201230.sql anaconda-ks.cfg table-20201230.sql
//备份yc库
[root@localhost ~]# mysqldump -uroot -p -h192.168.23.130 --databases yc > yc-20201230.sql
Enter password:
[root@localhost ~]# ls
all-20201230.sql table-20201230.sql anaconda-ks.cfg yc-20201230.sql
//模拟误删yc数据库
mysql> drop database yc;
Query OK, 3 rows affected (0.02 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql数据恢复
//恢复yc数据库
[root@localhost ~]# ls
all-20201230.sql table-20201230.sql anaconda-ks.cfg wq-20201230.sql
[root@localhost ~]# mysql -uroot -p -h192.168.23.130 < all-20201230.sql
Enter password:
[root@localhost ~]# mysql -uroot -p -h192.168.23.130 -e 'show databases;'
Enter password:
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| yc |
+--------------------+
//恢复yc数据库的student1表和student2表
mysql> use yc;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> source table-20201230.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)
......
......
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+--------------+
| Tables_in_yc |
+--------------+
| student |
| student1 |
| student2 |
+--------------+
3 rows in set (0.00 sec)
//模拟删除整个数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| yc |
+--------------------+
5 rows in set (0.00 sec)
mysql> drop database yc;
Query OK, 3 rows affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
//恢复整个数据库
[root@localhost ~]# ls
all-20201230.sql table-20201230.sql anaconda-ks.cfg yc-20201230.sql
[root@localhost ~]# mysql -uroot -p -h192.168.23.130 < all-20201230.sql
Enter password:
[root@localhost ~]# mysql -uroot -p -h192.168.23.130 -e 'show databases;'
Enter password:
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| yc |
+--------------------+
差异备份与恢复
mysql差异备份
开启MySQL服务器的二进制日志功能
[root@localhost ~]# vi /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
user = mysql
pid-file = /tmp/mysql.pid
skip-name-resolve
server-id=1 //设置服务器标识符
log-bin=mysql_bin //开启二进制日志功能
[root@localhost ~]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
对数据库进行完全备份
mysql> show databases ;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| yc |
+--------------------+
3 rows in set (0.01 sec)
mysql> use yc;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+--------------+
| Tables_in_yc |
+--------------+
| student |
| teacher |
+--------------+
2 rows in set (0.00 sec)
mysql> select * from student;
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | aa | 18 |
| 2 | bb | 19 |
| 3 | cc | 20 |
| 4 | dd | 21 |
+----+------+------+
4 rows in set (0.00 sec)
mysql> select * from teacher;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | yan1 | 36 |
| 2 | yan2 | 44 |
| 3 | yan3 | 39 |
+----+--------+------+
3 rows in set (0.00 sec)
// 完全备份
[root@localhost ~]# mysqldump -uroot -p --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-20201230.sql
[root@localhost ~]# ls
all-20201230.sql anaconda-ks.cfg
//增加新内容
mysql> select * from teacher;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | yan1 | 36 |
| 2 | yan2 | 44 |
| 3 | yan3 | 39 |
+----+--------+------+
3 rows in set (0.00 sec)
mysql> update teacher set age = 59 where id = 3;
Query OK, 1 rows affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> select * from teacher;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | yan1 | 36 |
| 2 | yan2 | 44 |
| 3 | yan3 | 59 |
+----+--------+------+
mysql差异备份恢复
模拟误删数据
mysql> drop database yc;
Query OK, 3 rows affected (0.02 sec)
mysql> show databases ;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
3 rows in set (0.01 sec)
刷新创建新的二进制日志
[root@localhost ~]# mysqladmin -uroot -p123456 flish-logs //刷新创建新的二进制文件
恢复完全备份
[root@localhost ~]# mysql -uroot -p < all-20201230.sql
Enter password:
[root@localhost ~]# mysql -uroot -p -e 'show databases;'
Enter password:
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| yc |
+--------------------+
[root@localhost ~]# mysql -uroot -p -e 'select * from yc.teacher;'
Enter password:
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | yan1 | 36 |
| 2 | yan2 | 44 |
| 3 | yan3 | 39 |
+----+--------+------+
//检查误删数据库的位置在什么地方
mysql> show binlog events in 'mysql_bin.000002'G
*************************** 14. row ***************************
Log_name: mysql_bin.000002
Pos: 799
Event_type: Query
Server_id: 1
End_log_pos: 897
Info: drop database school
mysql> exit
//使用mysqlbinlog恢复差异备份
[root@localhost ~]# mysqlbinlog --stop-position=799 /opt/data/mysql_bin.000002|mysql -uroot -p123456
mysql> select * from teacher;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | yan1 | 36 |
| 2 | yan2 | 44 |
| 3 | yan3 | 59 |
+----+--------+------+