数据库备份与恢复
mysql数据库备份与恢复
数据库常用备份方案:
全量备份: 全量备份就是对一个时间点上所有的数据进行备份,特点是数据恢复快,但是如果数据量大的话恢复数据的时间会很长
增量备份:增量备份是指上一次备份之后,对发生变化的数据进行的备份。 增量备份的优点:没有重复的备份数据,因此每次增量备份的数据量不大,备份所需的时间很短,但是恢复数据时需要按照顺序恢复使用恢复时间长
差异备份:差异备份就是只备份上一次全备后发生便化或修改的文件,恢复时只恢复上一次全备和最后一次差备,因此备份时间和恢复时间都比较短
备份数据恢复的原理:备份数据就是把数据产生的方式写到一个文件里面,恢复数据时执行文件里面的恢复操作
mysql备份工具mysqldump
常用的选项有:
-u 指定数据库用户名
-h 使用ip地址指定服务器主机
-p 指定数据库用户的密码
-P 指定数据库监听的端口
全量备份:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| huangtianen |
+--------------------+
5 rows in set (0.00 sec)
mysql>
mysql> use huangtianen;
Database changed
mysql>
mysql> show tables;
+-----------------------+
| Tables_in_huangtianen |
+-----------------------+
| student1 |
| student2 |
| student3 |
+-----------------------+
3 rows in set (0.00 sec)
mysql>
mysql> quit
Bye
[root@controller ~]#
[root@controller ~]# mysqldump -uroot -huangtianen -h192.168.10.131 -P3306 --all-databases > all-2023-9-5.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@controller ~]# ls
all-2023-9-5.sql anaconda-ks.cfg
[root@controller ~]#
备份huangtianen数据库里的student1表和student2表
[root@controller ~]# mysqldump -uroot -huangtianen -h192.168.10.131 -P3306 huangtianen student1 student2 > table-2023-9-5.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@controller ~]# ls
all-2023-9-5.sql anaconda-ks.cfg table-2023-9-5.sql
[root@controller ~]#
备份huangtianen数据库
[root@controller ~]# mysqldump -uroot -huangtianen -h192.168.10.131 -P3306 --databases huangtianen > huangtianen.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@controller ~]#
[root@controller ~]# ls
all-2023-9-5.sql anaconda-ks.cfg pass table-2023-9-5.sql xingge.sql
[root@controller ~]#
模拟误删数据库
[root@controller ~]# mysql -e 'drop database huangtianen;'
[root@controller ~]#
[root@controller ~]# mysql -e 'show databases;'
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
[root@controller ~]#
mysql数据恢复
恢复被删xingge数据库里的
[root@controller ~]# mysql -uroot -huangtianen < huangtianen.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@controller ~]#
[root@controller ~]# mysql -e 'show databases;'
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| huangtianen |
+--------------------+
[root@controller ~]#
恢复huangtianen数据库的student1表和student2表
[root@controller ~]# mysql -e 'show tables from huangtianen;'
+-----------------------+
| Tables_in_huangtianen |
+-----------------------+
| student1 |
| student2 |
| student3 |
+-----------------------+
[root@controller ~]#
[root@controller ~]# ls
all-2023-9-5.sql anaconda-ks.cfg table-2023-9-5.sql huangtianen.sql
[root@controller ~]#
[root@controller ~]# mysql -e 'drop table huangtianen.student1;'
[root@controller ~]# mysql -e 'drop table huangtianen.student2;'
[root@controller ~]#
[root@controller ~]# mysql -e 'show tables from huangtianen;'
+-----------------------+
| Tables_in_huangtianen |
+-----------------------+
| student3 |
+-----------------------+
[root@controller ~]#
[root@controller ~]# mysql -uroot -huangtianen
mysql: [Warning] Using a password on the command line interface can be insecure.
...
mysql>
mysql> use huangtianen;
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>
mysql> source table-2023-9-5.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)
mysql>
mysql> show tables;
+-----------------------+
| Tables_in_huangtianen |
+-----------------------+
| student1 |
| student2 |
| student3 |
+-----------------------+
3 rows in set (0.00 sec)
mysql>
模拟删除整个数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| huangtianen |
+--------------------+
5 rows in set (0.00 sec)
mysql> drop database huangtianen;
Query OK, 3 rows affected, 2 warnings (0.00 sec)
mysql>
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
1 row in set (0.00 sec)
mysql>
mysql> quit;
Bye
[root@controller ~]#
恢复整个数据库
[root@controller ~]# ls
all-2023-9-5.sql anaconda-ks.cfg table-2023-9-5.sql huangtianen.sql
[root@controller ~]#
[root@controller ~]# mysql -uroot -huangtianen < all-2023-9-5.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@controller ~]#
[root@controller ~]# mysql -e 'show databases;'
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| huangtianen |
+--------------------+
[root@controller ~]#
差异备份与恢复
mysql差异备份
开启MySQL服务器的二进制日志功能
编辑my.cnf配置文件设置服务器标识符,开启二进制日志功能
[root@controller ~]# vim /etc/my.cnf
[mysqld]
...
server-id = 10
log-bin = mysql_bin
[root@controller ~]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
[root@controller ~]#
先对数据库进行完全备份
[root@controller ~]# mysqldump -uroot -huangtianen --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-202309051036.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@controller ~]#
[root@controller ~]# ls
all-202309051036.sql all-2023-9-5.sql anaconda-ks.cfg pass table-2023-9-5.sql huangtianen.sql
[root@controller ~]#
增加新内容
mysql> select * from student1;
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 6 | zhangshan | 20 |
| 7 | lisi | NULL |
| 8 | chenshou | 10 |
| 9 | wangwu | 3 |
| 10 | qiuyi | 15 |
| 11 | qiuxiaotian | 20 |
+----+-------------+------+
11 rows in set (0.01 sec)
mysql>
mysql> insert student1(name,age) values('laohuang',66);
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> select * from student1;
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 6 | zhangshan | 20 |
| 7 | lisi | NULL |
| 8 | chenshou | 10 |
| 9 | wangwu | 3 |
| 10 | qiuyi | 15 |
| 11 | qiuxiaotian | 20 |
| 12 | laohuang | 66 |
+----+-------------+------+
12 rows in set (0.00 sec)
mysql>
mysql> update student1 set age = null where name = 'laohuang';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql>
mysql> select * from student1;
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 6 | zhangshan | 20 |
| 7 | lisi | NULL |
| 8 | chenshou | 10 |
| 9 | wangwu | 3 |
| 10 | qiuyi | 15 |
| 11 | qiuxiaotian | 20 |
| 12 | laohuang | NULL |
+----+-------------+------+
12 rows in set (0.00 sec)
mysql>
mysql差异备份恢复
模拟误删数据
mysql> drop database huangtianen;
Query OK, 3 rows affected, 2 warnings (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
[root@controller ~]#
恢复数据
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
[root@controller ~]#
[root@controller ~]# ls
all-202309051036.sql all-2023-9-5.sql anaconda-ks.cfg table-2023-9-5.sql huangtianen.sql
[root@controller ~]#
[root@controller ~]# mysql -uroot -huangtianen < all-202309051036.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@controller ~]#
[root@controller ~]# mysql -uroot -huangtianen -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| huangtianen |
+--------------------+
[root@controller ~]#
[root@controller ~]# mysql -uroot -huangtianen -e 'show tables from huangtianen;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------------------+
| Tables_in_huangtianen |
+-----------------------+
| student1 |
| student2 |
| student3 |
+-----------------------+
[root@controller ~]#
恢复差异备份
检查误删数据库的位置在什么地方
[root@controller data]# ls
auto.cnf client-cert.pem ib_buffer_pool ib_logfile1 mysql_bin.000005 mysql.pid public_key.pem sys
ca-key.pem client-key.pem ibdata1 ibtmp1 mysql_bin.000006 performance_schema server-cert.pem huangtianen
ca.pem controller.err ib_logfile0 mysql mysql_bin.index private_key.pem server-key.pem
[root@controller data]#
[root@controller data]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
...
mysql>
mysql> show binlog events in 'mysql_bin.000005';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql_bin.000005 | 4 | Format_desc | 10 | 123 | Server ver: 5.7.39-log, Binlog ver: 4 |
| mysql_bin.000005 | 123 | Previous_gtids | 10 | 154 | |
| mysql_bin.000005 | 154 | Anonymous_Gtid | 10 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql_bin.000005 | 219 | Query | 10 | 293 | BEGIN |
| mysql_bin.000005 | 293 | Table_map | 10 | 350 | table_id: 141 (xingge.student1) |
| mysql_bin.000005 | 350 | Write_rows | 10 | 395 | table_id: 141 flags: STMT_END_F |
| mysql_bin.000005 | 395 | Xid | 10 | 426 | COMMIT /* xid=504 */ |
| mysql_bin.000005 | 426 | Anonymous_Gtid | 10 | 491 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql_bin.000005 | 491 | Query | 10 | 565 | BEGIN |
| mysql_bin.000005 | 565 | Table_map | 10 | 622 | table_id: 141 (xingge.student1) |
| mysql_bin.000005 | 622 | Update_rows | 10 | 679 | table_id: 141 flags: STMT_END_F |
| mysql_bin.000005 | 679 | Xid | 10 | 710 | COMMIT /* xid=506 */ |
| mysql_bin.000005 | 710 | Anonymous_Gtid | 10 | 775 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql_bin.000005 | 775 | Query | 10 | 873 | drop database xingge |
| mysql_bin.000005 | 873 | Rotate | 10 | 920 | mysql_bin.000006;pos=4 |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
15 rows in set (0.00 sec)
mysql>
使用mysqlbinlog恢复差异备份
[root@controller data]# mysqlbinlog --stop-position=775 /opt/data/mysql_bin.000005 |mysql -uroot -huangtianen
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@controller data]#
[root@controller data]# mysql -uroot -huangtianen -e 'select * from huangtianen.student1;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 6 | zhangshan | 20 |
| 7 | lisi | NULL |
| 8 | chenshou | 10 |
| 9 | wangwu | 3 |
| 10 | qiuyi | 15 |
| 11 | qiuxiaotian | 20 |
| 12 | laohuang | NULL |
+----+-------------+------+
[root@controller data]#