文章目录
1.mysql数据库备份与恢复
1.1 数据库常用备份方案
数据库备份方案：
- 全量备份
- 增量备份
- 差异备份
| 备份方案 | 特点 |
|---|---|
| 全量备份 | 全量备份就是指对某一个时间点上的所有数据或应用进行的一个完全拷贝。 数据恢复快。 备份时间长 |
| 增量备份 | 增量备份是指在一次全备份或上一次增量备份后，以后每次的备份只需备份 与前一次相比增加和者被修改的文件。这就意味着，第一次增量备份的对象是进行全备后所产生的增加和修改的文件；第二次增量备份的对象是进行第一次增量备份后所产生的增加和修改的文件，如此类推。 没有重复的备份数据 备份时间短 恢复数据时必须按一定的顺序进行 |
| 差异备份 | 备份上一次的完全备份后发生变化的所有文件。 差异备份是指在一次全备份后到进行差异备份的这段时间内 对那些增加或者修改文件的备份。在进行恢复时，我们只需对第一次全量备份和最后一次差异备份进行恢复。 |
1.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
//备份整个数据库(全备)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| cwt |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> use cwt;
Database changed
mysql> show tables;
+---------------+
| Tables_in_cwt |
+---------------+
| student |
+---------------+
1 row in set (0.00 sec)
[root@cwt ~]# mysqldump -uroot -pcen123 --all-databases > all-20190818.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@cwt ~]# ls
all-20190818.sql anaconda-ks.cfg mysql_cwt.sh pass Tetris.sh
//备份cwt库的student表
[root@cwt ~]# mysqldump -uroot -pcen123 cwt student > table-20190818.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@cwt ~]# ls
all-20190818.sql anaconda-ks.cfg mysql_cwt.sh pass table-20190818.sql Tetris.sh
//备份cwt库
[root@cwt ~]# mysqldump -uroot -pcen123 --databases cwt > cwt-20190818.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@cwt ~]# ls
all-20190818.sql cwt-20190818.sql pass Tetris.sh
anaconda-ks.cfg mysql_cwt.sh table-20190818.sql
//模拟误删cwt数据库
mysql> drop database cwt;
Query OK, 1 row affected (0.36 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
1.3 mysql数据恢复
//恢复cwt数据库
[root@cwt ~]# mysql -uroot -pcen123 < all-20190818.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@cwt ~]# mysql -uroot -pcen123 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| cwt |
| mysql |
| performance_schema |
| sys |
+--------------------+
//恢复cwt数据库的student表
mysql> use cwt;
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-20190818.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)
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+---------------+
| Tables_in_cwt |
+---------------+
| student |
+---------------+
1 row in set (0.00 sec)
//模拟删除整个数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| cwt |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.01 sec)
mysql> drop database cwt;
Query OK, 1 row affected (0.01 sec)
mysql> drop database cwt;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
//恢复整个数据库
[root@cwt ~]# mysql -uroot -pcen123 < all-20190818.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@cwt ~]# mysql -uroot -pcen123 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| cwt |
| mysql |
| performance_schema |
| sys |
+--------------------+
1.4 差异备份与恢复
1.4.1. mysql差异备份
开启MySQL服务器的二进制日志功能
[root@cwt ~]# vim /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@cwt ~]# service mysqld restart
Shutting down MySQL.... SUCCESS!
Starting MySQL. SUCCESS!
对数据库进行完全备份
mysql> show databases;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 2
Current database: *** NONE ***
+--------------------+
| Database |
+--------------------+
| information_schema |
| cwt |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> show tables from cwt;
+---------------+
| Tables_in_cwt |
+---------------+
| student |
| sun |
+---------------+
2 rows in set (0.00 sec)
mysql> select * from cwt.sun;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | tom | 10 |
| 2 | jerry | 30 |
+----+-------+------+
2 rows in set (0.00 sec)
mysql> select * from cwt.student;
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 6 | lisi | NULL |
| 7 | chenshuo | 10 |
| 8 | wangwu | 3 |
| 9 | qiuyi | 15 |
| 10 | qiuxiaotian | 20 |
+----+-------------+------+
10 rows in set (0.00 sec)
//完全备份
[root@cwt ~]# mysqldump -uroot -pcen123 --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-20190818.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@cwt ~]# ll
总用量 828
-rw-r--r--. 1 root root 803297 8月 18 23:54 all-20190818.sql
-rw-------. 1 root root 1478 4月 12 10:43 anaconda-ks.cfg
-rw-r--r--. 1 root root 2166 8月 18 23:36 cwt-20190818.sql
-rw-r--r--. 1 root root 1642 8月 18 23:21 mysql_cwt.sh
-rw-r--r--. 1 root root 802 8月 18 23:23 pass
-rw-r--r--. 1 root root 2030 8月 18 23:35 table-20190818.sql
-rwxr-xr-x. 1 root root 18681 4月 13 09:47 Tetris.sh
//增加新内容
mysql> use cwt;
Database changed
mysql> insert into sun values(3,'hehe',20),(4,'xixi',50);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from sun;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | tom | 10 |
| 2 | jerry | 30 |
| 3 | hehe | 20 |
| 4 | xixi | 50 |
+----+-------+------+
4 rows in set (0.00 sec)
mysql> update sun set age = 40 where id =3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from sun;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | tom | 10 |
| 2 | jerry | 30 |
| 3 | hehe | 40 |
| 4 | xixi | 50 |
+----+-------+------+
4 rows in set (0.00 sec)
1.4.2. mysql差异备份恢复
模拟误删数据
[root@cwt ~]# mysql -uroot -pcen123 -e 'drop database cwt;'
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@cwt ~]# mysql -uroot -pcen123 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
//由上可以看出cwt这个数据库已被删除
刷新创建新的二进制日志
[root@cwt ~]# ll /opt/data/
总用量 122952
-rw-r-----. 1 mysql mysql 56 8月 18 23:23 auto.cnf
-rw-r-----. 1 mysql mysql 20973 8月 18 23:47 cwt.err
-rw-r-----. 1 mysql mysql 960 8月 18 23:47 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 8月 18 23:58 ibdata1
-rw-r-----. 1 mysql mysql 50331648 8月 18 23:58 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 8月 18 23:23 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 8月 18 23:54 ibtmp1
drwxr-x---. 2 mysql mysql 4096 8月 18 23:45 mysql
-rw-r-----. 1 mysql mysql 855 8月 18 23:58 mysql_bin.000002
-rw-r-----. 1 mysql mysql 19 8月 18 23:54 mysql_bin.index
-rw-r-----. 1 mysql mysql 6 8月 18 23:47 mysql.pid
drwxr-x---. 2 mysql mysql 8192 8月 18 23:23 performance_schema
drwxr-x---. 2 mysql mysql 8192 8月 18 23:23 sys
//刷新创建的二进制日志
[root@cwt ~]# mysqladmin -uroot -pcen123 flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@cwt ~]# ll /opt/data/
总用量 122956
-rw-r-----. 1 mysql mysql 56 8月 18 23:23 auto.cnf
-rw-r-----. 1 mysql mysql 20973 8月 18 23:47 cwt.err
-rw-r-----. 1 mysql mysql 960 8月 18 23:47 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 8月 18 23:58 ibdata1
-rw-r-----. 1 mysql mysql 50331648 8月 18 23:58 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 8月 18 23:23 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 8月 18 23:54 ibtmp1
drwxr-x---. 2 mysql mysql 4096 8月 18 23:45 mysql
-rw-r-----. 1 mysql mysql 902 8月 19 00:00 mysql_bin.000002
-rw-r-----. 1 mysql mysql 154 8月 19 00:00 mysql_bin.000003
-rw-r-----. 1 mysql mysql 38 8月 19 00:00 mysql_bin.index
-rw-r-----. 1 mysql mysql 6 8月 18 23:47 mysql.pid
drwxr-x---. 2 mysql mysql 8192 8月 18 23:23 performance_schema
drwxr-x---. 2 mysql mysql 8192 8月 18 23:23 sys
恢复完全备份
[root@cwt ~]# mysql -uroot -pcen123 < all-20190818.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@cwt ~]# mysql -uroot -pcen123 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| cwt |
| mysql |
| performance_schema |
| sys |
+--------------------+
[root@cwt ~]# mysql -uroot -pcen123 -e 'show tables from cwt;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+
| Tables_in_cwt |
+---------------+
| student |
| sun |
+---------------+
[root@cwt ~]# mysql -uroot -pcen123 -e 'select * from cwt.sun;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | tom | 10 |
| 2 | jerry | 30 |
+----+-------+------+
[root@cwt ~]# mysql -uroot -pcen123 -e 'select * from cwt.student;'
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 | lisi | NULL |
| 7 | chenshuo | 10 |
| 8 | wangwu | 3 |
| 9 | qiuyi | 15 |
| 10 | qiuxiaotian | 20 |
+----+-------------+------+
恢复差异备份
[root@cwt ~]# ll /opt/data/
总用量 123976
-rw-r-----. 1 mysql mysql 56 8月 18 23:23 auto.cnf
drwxr-x---. 2 mysql mysql 88 8月 19 00:01 cwt
-rw-r-----. 1 mysql mysql 20973 8月 18 23:47 cwt.err
-rw-r-----. 1 mysql mysql 960 8月 18 23:47 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 8月 19 00:01 ibdata1
-rw-r-----. 1 mysql mysql 50331648 8月 19 00:01 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 8月 18 23:23 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 8月 18 23:54 ibtmp1
drwxr-x---. 2 mysql mysql 4096 8月 19 00:01 mysql
-rw-r-----. 1 mysql mysql 902 8月 19 00:00 mysql_bin.000002
-rw-r-----. 1 mysql mysql 785942 8月 19 00:01 mysql_bin.000003
-rw-r-----. 1 mysql mysql 38 8月 19 00:00 mysql_bin.index
-rw-r-----. 1 mysql mysql 6 8月 18 23:47 mysql.pid
drwxr-x---. 2 mysql mysql 8192 8月 18 23:23 performance_schema
drwxr-x---. 2 mysql mysql 8192 8月 18 23:23 sys
//检查误删数据库的位置在什么地方
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 | 1 | 123 | Server ver: 5.7.22-log, Binlog ver: 4 |
| mysql_bin.000002 | 123 | Previous_gtids | 1 | 154 | |
| mysql_bin.000002 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql_bin.000002 | 219 | Query | 1 | 290 | BEGIN |
| mysql_bin.000002 | 290 | Table_map | 1 | 339 | table_id: 113 (cwt.sun) |
| mysql_bin.000002 | 339 | Write_rows | 1 | 396 | table_id: 113 flags: STMT_END_F |
| mysql_bin.000002 | 396 | Xid | 1 | 427 | COMMIT /* xid=478 */ |
| mysql_bin.000002 | 427 | Anonymous_Gtid | 1 | 492 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql_bin.000002 | 492 | Query | 1 | 563 | BEGIN |
| mysql_bin.000002 | 563 | Table_map | 1 | 612 | table_id: 113 (cwt.sun) |
| mysql_bin.000002 | 612 | Update_rows | 1 | 670 | table_id: 113 flags: STMT_END_F |
| mysql_bin.000002 | 670 | Xid | 1 | 701 | COMMIT /* xid=480 */ |
| mysql_bin.000002 | 701 | Anonymous_Gtid | 1 | 766 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql_bin.000002 | 766 | Query | 1 | 855 | drop database cwt |
| mysql_bin.000002 | 855 | Rotate | 1 | 902 | mysql_bin.000003;pos=4 |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
15 rows in set (0.00 sec)
//使用mysqlbinlog恢复差异备份
[root@cwt ~]# mysqlbinlog --stop-position=766 /opt/data/mysql_bin.000002 |mysql -uroot -pcen123
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@cwt ~]# mysql -uroot -pcen123 -e 'select * from cwt.sun;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | tom | 10 |
| 2 | jerry | 30 |
| 3 | hehe | 40 |
| 4 | xixi | 50 |
+----+-------+------+
扫一扫
446
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
