mysql的备份和恢复
mysql的配置文件
mysql的配置文件为/etc/my.cnf
配置文件查找次序：若在多个配置文件中均有设定，则最后找到的最终生效
/etc/my.cnf --> /etc/mysql/my.cnf --> --default-extra-file=/PATH/TO/CONF_FILE --> ~/.my.cnf
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将无法正常处理连接请求 |
数据库的备份方式
| 方式 | 特点 |
|---|---|
| 全量备份 | 全量备份就是指对某一个时间点上的所有数据或应用进行的一个完全拷贝。 数据恢复快。 备份时间长 |
| 增量备份 | 增量备份是指在一次全备份或上一次增量备份后，以后每次的备份只需备份 与前一次相比增加和者被修改的文件。这就意味着，第一次增量备份的对象是进行全备后所产生的增加和修改的文件；第二次增量备份的对象是进行第一次增量备份后所产生的增加和修改的文件，如此类推。 没有重复的备份数据 备份时间短 恢复数据时必须按一定的顺序进行 |
| 差异备份 | 备份上一次的完全备份后发生变化的所有文件。差异备份是指在一次全备份后到进行差异备份的这段时间内对那些增加或者修改文件的备份。在进行恢复时，我们只需对第一次全量备份和最后一次差异备份进行恢复。 |
mysql的备份工具mysqldump
语法：
mysqldump [OPTIONS] database [tables ...] //备份某个数据库中的表
mysqldump [OPTIONS] --all-databases [OPTIONS] //所有的数据库都备份
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...] //备份某几个数据库
示例：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| zlb |
| zlb1 |
+--------------------+
6 rows in set (0.00 sec)
mysql> use zlb;
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_zlb |
+---------------+
| test0 |
| test1 |
+---------------+
2 rows in set (0.00 sec)
mysql>
[root@zlb10 ~]# mysqldump -hlocalhost zlb test0 > 1.20190221.sql
[root@zlb10 ~]# ls
1.20190221.sql anaconda-ks.cfg httpd-2.4.34 httpd-2.4.34.tar.bz2 pass t5.sh
[root@zlb10 ~]# mysqldump -hlocalhost --all-databases > 2.20190221.sql
[root@zlb10 ~]# ls
1.20190221.sql anaconda-ks.cfg httpd-2.4.34.tar.bz2 t5.sh
2.20190221.sql httpd-2.4.34 pass
[root@zlb10 ~]# mysqldump -hlocalhost --databases zlb zlb1 > 3.2019.sql
[root@zlb10 ~]# ls
1.20190221.sql 3.2019.sql httpd-2.4.34 pass
2.20190221.sql anaconda-ks.cfg httpd-2.4.34.tar.bz2 t5.sh
[root@zlb10 ~]#
数据库的恢复
//表的恢复
mysql> drop table test0;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+---------------+
| Tables_in_zlb |
+---------------+
| test1 |
+---------------+
1 row in set (0.00 sec)
mysql> source 1.20190221.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)
mysql> show tables;
+---------------+
| Tables_in_zlb |
+---------------+
| test0 |
| test1 |
+---------------+
2 rows in set (0.00 sec)
mysql>
//多个数据库的恢复
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| zlb |
| zlb1 |
+--------------------+
6 rows in set (0.00 sec)
mysql> drop database zlb;
Query OK, 2 rows affected (0.01 sec)
mysql> drop database zlb1;
Query OK, 0 rows affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql>
mysql> source 3.2019.sql
//过程略。。。
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| zlb |
| zlb1 |
+--------------------+
6 rows in set (0.01 sec)
mysql>
差异备份
差异备份是先全部备份一次然后每隔一段时间就备份一次，等到要恢复的时候就可以把全备到那段时间备份的恢复。
[root@zlb10 ~]# vim /etc/my.cnf
[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
//在配置文件后面添加下面2行
server-id=1 //设置服务器标识符
log-bin=mysql_bin //开启二进制日志功能
[root@zlb10 ~]# service mysqld restart //重启数据库的服务
Shutting down MySQL.... SUCCESS!
Starting MySQL. SUCCESS!
先完成一次全备
[root@zlb10 ~]# mysqldump --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-2019.sql
mysql> use zlb;
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> insert into test0 values(3,'dsz',11),(4,'asd',30);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from test0 ;
+----+---------+------+
| id | name | age |
+----+---------+------+
| 1 | people0 | 20 |
| 2 | people1 | 23 |
| 3 | dsz | 11 |
| 4 | asd | 30 |
+----+---------+------+
4 rows in set (0.00 sec) //最后的2条记录是之后加上去的
mysql>
mysql> update test0 set age = 25 where id = 3; //修改某条记录
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from test0 ;
+----+---------+------+
| id | name | age |
+----+---------+------+
| 1 | people0 | 20 |
| 2 | people1 | 23 |
| 3 | dsz | 25 |
| 4 | asd | 30 |
+----+---------+------+
4 rows in set (0.00 sec)
mysql>
[root@zlb10 ~]# mysql -e 'drop database zlb;' //模拟删除数据库
[root@zlb10 ~]# mysql -e 'show databases;'
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| zabbix |
| zlb1 |
+--------------------+
[root@zlb10 ~]# ll /opt/data/
total 188544
-rw-r----- 1 mysql mysql 56 Feb 20 23:48 auto.cnf
-rw-r----- 1 mysql mysql 4479 Feb 23 00:34 ib_buffer_pool
-rw-r----- 1 mysql mysql 79691776 Feb 23 00:58 ibdata1
-rw-r----- 1 mysql mysql 50331648 Feb 23 00:58 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Feb 20 23:48 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 Feb 23 00:47 ibtmp1
drwxr-x--- 2 mysql mysql 4096 Feb 21 22:15 mysql
-rw-r----- 1 mysql mysql 855 Feb 23 00:58 mysql_bin.000002 //此时这里还只要一条日志记录
-rw-r----- 1 mysql mysql 19 Feb 23 00:47 mysql_bin.index
-rw-r----- 1 mysql mysql 6 Feb 23 00:34 mysql.pid
drwxr-x--- 2 mysql mysql 8192 Feb 20 23:48 performance_schema
drwxr-x--- 2 mysql mysql 8192 Feb 20 23:48 sys
drwxr-x--- 2 mysql mysql 12288 Feb 22 23:21 zabbix
drwxr-x--- 2 mysql mysql 20 Feb 21 23:23 zlb1
-rw-r----- 1 mysql mysql 57765 Feb 23 00:34 zlb10.err
[root@zlb10 ~]#
[root@zlb10 ~]# mysqladmin -uroot -pzlb flush-logs //刷新日志文件
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@zlb10 ~]# ll /opt/data/
total 188548
-rw-r----- 1 mysql mysql 56 Feb 20 23:48 auto.cnf
-rw-r----- 1 mysql mysql 4479 Feb 23 00:34 ib_buffer_pool
-rw-r----- 1 mysql mysql 79691776 Feb 23 00:58 ibdata1
-rw-r----- 1 mysql mysql 50331648 Feb 23 00:58 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Feb 20 23:48 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 Feb 23 00:47 ibtmp1
drwxr-x--- 2 mysql mysql 4096 Feb 21 22:15 mysql
-rw-r----- 1 mysql mysql 902 Feb 23 01:01 mysql_bin.000002
-rw-r----- 1 mysql mysql 154 Feb 23 01:01 mysql_bin.000003 //此处又多了一条日志记录
-rw-r----- 1 mysql mysql 38 Feb 23 01:01 mysql_bin.index
-rw-r----- 1 mysql mysql 6 Feb 23 00:34 mysql.pid
drwxr-x--- 2 mysql mysql 8192 Feb 20 23:48 performance_schema
drwxr-x--- 2 mysql mysql 8192 Feb 20 23:48 sys
drwxr-x--- 2 mysql mysql 12288 Feb 22 23:21 zabbix
drwxr-x--- 2 mysql mysql 20 Feb 21 23:23 zlb1
-rw-r----- 1 mysql mysql 57866 Feb 23 01:00 zlb10.err
[root@zlb10 ~]#
[root@zlb10 ~]# mysql < all-2019.sql //恢复全备
[root@zlb10 ~]# mysql -e 'show databases;'
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| zabbix |
| zlb | //出现了我们删除的数据库
| zlb1 |
+--------------------+
[root@zlb10 ~]# mysql -e 'show tables from zlb;' //数据库中的表也恢复过来了
+---------------+
| Tables_in_zlb |
+---------------+
| test0 |
| test1 |
+---------------+
[root@zlb10 ~]# mysql -e 'select * from zlb.test0;' //有这些记录说明数据库已经全部恢复了
+----+---------+------+
| id | name | age |
+----+---------+------+
| 1 | people0 | 20 |
| 2 | people1 | 23 |
+----+---------+------+
[root@zlb10 ~]#
//查看一下在什么位置删除的
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.23-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 | 341 | table_id: 321 (zlb.test0) |
| mysql_bin.000002 | 341 | Write_rows | 1 | 396 | table_id: 321 flags: STMT_END_F |
| mysql_bin.000002 | 396 | Xid | 1 | 427 | COMMIT /* xid=5487 */ |
| 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 | 614 | table_id: 321 (zlb.test0) |
| mysql_bin.000002 | 614 | Update_rows | 1 | 670 | table_id: 321 flags: STMT_END_F |
| mysql_bin.000002 | 670 | Xid | 1 | 701 | COMMIT /* xid=6404 */ |
| mysql_bin.000002 | 701 | Anonymous_Gtid | 1 | 766 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql_bin.000002 | 766 | Query | 1 | 855 | drop database zlb | //此处就是删除数据库的位置，对应的pos位置是766
| mysql_bin.000002 | 855 | Rotate | 1 | 902 | mysql_bin.000003;pos=4 |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
15 rows in set (0.00 sec)
mysql>
恢复差异数据
[root@zlb10 ~]# mysqlbinlog --stop-position=766 /opt/data/mysql_bin.000002 | mysql
[root@zlb10 ~]# mysql -uroot -pzlb -e 'select * from zlb.test0;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+---------+------+
| id | name | age |
+----+---------+------+
| 1 | people0 | 20 |
| 2 | people1 | 23 |
| 3 | dsz | 25 |
| 4 | asd | 30 |
+----+---------+------+
[root@zlb10 ~]#
扫一扫
6969
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
