Linux运维实战技术
资源放送
MySQL备份视频教程讲解
↓ 扫一扫 视频在线观看↓
数据对于企业的重要性，不言而喻。所以在使用Mysql数据库的过程中，经常需要使用到数据库的备份和导出功能，非常重要！！！
1、物理备份文件
物理备份:对数据库操作系统的物理文件(如数据文件、日志文件等)；
冷备份(脱机备份) :是在关闭数据库的时候进行的；
热备份(联机备份) :数据库处于运行状态，依赖于数据库的日志文件；
温备份:数据库锁定表格(不可写入但可读)的状态下进行备份操作。
2、逻辑备份
逻辑备份:对数据库逻辑组件(如: 表等数据库对象)的备份。
从数据库的备份策略角度，备份可分为：
完全备份：每次对数据进行完整的备份；
差异备份：备份那些自从上次完全备份之后被修改过的文件；
增量备份：只有那些在上次完全备份或者增量备份后被修改的文件才会被备份。
在生产环境中，数据的安全性至关重要。
任何数据的丢失都可能产生严重的后果:
2.1、造成数据丢失的原因
程序错误；
人为操作错误；
运算错误；
磁盘故障；
灾难（如火灾、地址）和盗窃。
3.1、物理冷备
备份时数据库处于关闭状态，直接打包数据库文件；
备份速度快，恢复时也是最简单的。
3.2、专用备份工具mydump或mysqlhotcopy
mysqldump常用的逻辑备份工具；
mysqlhotcopy仅拥有备份MyISAM和ARCHIVE表。
3.3、启用二进制日志进行增量备份
进行增量备份，需要刷新_二进制日志。
3.4、第三方工具备份
免费的MySQl热备份软件Percona XtraBackup。
4.1、什么是完全备份？
完全备份是对整个数据库的备份、数据库结构和文件结构的备份；
完全备份保存的是备份完成时刻的数据库；
完全备份是增量备份的基础。
4.1.1优点：
安全性高；
备份与恢复操作简单方便。
4.1.2缺点
数据存在大量的重复；
占用大量的备份空间，空间利用率低；
备份与恢复时间长。
5.1、物理冷备份与恢复
关闭MySQL数据库，使用tar命令直接打包数据库文件夹，直接替换现有MySQL目录即可。
mysql> use tom;
Database changed
mysql> create table chengji (name VARCHAR(10),point INT(10));
Query OK, 0 rows affected (0.02 sec)
mysql> desc chengji;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(10) | YES | | NULL | |
| point | int(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> insert into chengji values('xiaowang',77),('xiaoli',75);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from chengji
-> ;
+----------+-------+
| name | point |
+----------+-------+
| xiaowang | 77 |
| xiaoli | 75 |
+----------+-------+
2 rows in set (0.00 sec)先关闭数据库服务，再打包备份。
[root@server3 ~]# tar zcf /backup/mysql_all-$(date +%F).tar.gz /usr/local/mysql/data/ tar: 从成员名中删除开头的“/” [root@server3 ~]# cd /backup/ [root@server3 backup]# ll 总用量 1376 -rw-r--r--. 1 root root 1406517 10月 13 14:48 mysql_all-2020-10-13.tar.gz
将原来的数据移走到备份文件夹中，解压刚才备份的tar包到/restore目录下，再移动到mysql服务的文件夹中。
[root@server1 ~]# mkdir bak [root@server1 ~]# mv /usr/local/mysql/data/ /bak #将数据库的文件移动至/bak文件夹中 [root@server1 ~]# mkdir restore [root@server1 ~]# tar zxf /backup/mysql_all-2020-10-23.tar.gz -C restore [root@server1 ~]# mv restore/usr/local/mysql/data/ /usr/local/mysql/ #将之前备份的文件移动至mysql服务的文件夹中
重启mysql服务，登录mysql，查看数据是否恢复：
mysql> use tom; 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> select * from chengji; +----------+-------+ | name | point | +----------+-------+ | xiaowang | 77 | | xiaoli | 75 | +----------+-------+ 2 rows in set (0.00 sec)
5.2、mysqldump备份与恢复
MySQL自带的备份工具，可方便实现对MySQL的备份，可以将指定的库、表导出为SQL脚本，使用命令mysql导入备份的数据。
mysqldump -u root -p --all-databses > all-data-$(date +%F).sql ###备份所有数据库 mysqldump -u root -p -databases auth mysql > auth-mysql.sql ###备份auth和mysql库 mysqldump -u root -p auth > auth-$(data +%F).sql ###备份auth数据库 mysqldump -u root -p mysql user > mysql-user-$(date +%F).sql ###备份mysql的user表 mysqldump -u root -p -d mysql user > /tmp/desc-mysql-user.sql ###备份mysql库user表的结构
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | myadm | | mysql | | performance_schema | | student | | sys | | tom | +--------------------+ 7 rows in set (0.00 sec) [root@server3 opt]# mysqldump -u root -p tom > /opt/tom.sql Enter password: [root@server3 opt]# ls tom.sql # 导出的备份文件
5.3、对所有库进行完全备份
[root@server3 opt]# mysqldump -uroot -pabc123 --all-databases > /backup/all.sql mysqldump: [Warning] Using a password on the command line interface can be insecure.
5.4、mysqldump备份数据表
musqldump可针对库内特定的表进行备份；
使用mysqldump备份表的操作。
mysqldump -u 用户名 -p 【密码】【选项】选项库名 表名 > /备份路径/备份文件名
示例：
mysql> use tom; 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_tom | +---------------+ | chengji | +---------------+ 1 row in set (0.00 sec) mysql> select * from chengji; +----------+-------+ | name | point | +----------+-------+ | xiaowang | 77 | | xiaoli | 75 | +----------+-------+ 2 rows in set (0.00 sec) #复制tom表 name字段 张三内容 生成一张新表pp mysql> create table pp as select * from chengji where name='xiaowang'; Query OK, 1 row affected (0.03 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> show tables; +---------------+ | Tables_in_tom | +---------------+ | chengji | | pp | +---------------+ 2 rows in set (0.00 sec) #新生成表 mysql> select *from pp; +----------+-------+ | name | point | +----------+-------+ | xiaowang | 77 | +----------+-------+ 1 row in set (0.00 sec)
[root@server3 ~]# mysql -u root -p123123 chengji pp > /opt/pp.sql mysql: [Warning] Using a password on the command line interface can be insecure. [root@server3 ~]# ls /opt pp.sql
6.1、使用mysqldump导出的脚本，可使用导入的方法
source命令【作用于mysql模式下】
mysql命令【作用于于linux模式下】
6.2、使用source恢复数据库的步骤
登录到mysql数据库，执行source备份sql脚本的路径。
source恢复的示例:
MYSQL[(none)]> source /backup/all-data.sql
模拟删除表:
mysql> use tom; Database changed #删除表 mysql> drop table chengji; Query OK, 0 rows affected (0.02 sec) mysql> drop table pp; Query OK, 0 rows affected (0.01 sec)
进行恢复：
mysql> use tom;
Database changed
mysql> use tom;
Database changed
mysql> show tables;
+---------------+
| Tables_in_tom |
+---------------+
| tom |
+---------------+
1 row in set (0.00 sec)
mysql> select * from tom;
+----+----------+----------+
| id | name | address |
+----+----------+----------+
| 1 | zhangsan | hangzhou |
+----+----------+----------+
1 row in set (0.00 sec)
mysql> insert into tom (name,address) values('lisi','wuxi');
Query OK, 1 row affected (0.00 sec)
mysql> select * from tom;
+----+----------+----------+
| id | name | address |
+----+----------+----------+
| 1 | zhangsan | hangzhou |
| 2 | lisi | wuxi |
+----+----------+----------+
2 rows in set (0.00 sec)
mysql> create table pp as select * from tom where name='zhangsan';
Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> show tables;
+---------------+
| Tables_in_tom |
+---------------+
| pp |
| tom |
+---------------+
2 rows in set (0.00 sec)
mysql> select * from pp;
+----+----------+----------+
| id | name | address |
+----+----------+----------+
| 1 | zhangsan | hangzhou |
+----+----------+----------+
1 row in set (0.00 sec)
mysql> Ctrl-C -- exit!
Aborted
[root@server3 opt]# mysqldump -u root -p tom pp > /opt/pp.sql
Enter password:
[root@server3 opt]# ls /opt/
all.sql opt.sql pp.sql rh tom.tom
[root@server3 opt]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.6.36-log Source distribution
Copyright (c) 2000, 2017, 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> drop table tom;
ERROR 1046 (3D000): No database selected
mysql> use tom;
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> drop table tom;
Query OK, 0 rows affected (0.01 sec)
mysql> drop table pp;
Query OK, 0 rows affected (0.00 sec)
```java
mysql> show tables;
Empty set (0.00 sec)
#恢复
mysql> source /opt/all.sql;
..省略内容
mysql> use tom;
Database changed
mysql> show tables;
+---------------+
| Tables_in_tom |
+---------------+
| tom |
+---------------+
1 row in set (0.00 sec)
mysql> source /opt/pp.sql;
mysql> show tables;
+---------------+
| Tables_in_tom |
+---------------+
| pp |
| tom |
+---------------+
2 rows in set (0.00 sec)
mysql> select * from pp;
+----+----------+----------+
| id | name | address |
+----+----------+----------+
| 1 | zhangsan | hangzhou |
+----+----------+----------+
1 row in set (0.00 sec)
#已经恢复 这边我们是恢复所有数据库
#也可以单独的对标进行备份恢复7.1、使用mysqldump命令进行完全备份存在的问题
备份数据中有重复数据；
备份时间与恢复时间长。
7.2、增量备份优缺点
优点：
没有重复数据，效率高，空间利用率最大化；
备份量不大，时间短。
缺点：
恢复麻烦：需要上次完全备份及完全备份之后所有的增量备份才能恢复，而且要对所有增量备份进行逐个反推恢复；
安全性较低。
7.3、如何实现MySQL增量备份？
MySQL没有提供直接的增量备份方法，可以通过 MySQL提供的二进制日志（ binary logs）间接实现增量备份，二进制日志保存了所有更新或者可能更新数据库的操作，二进制日志在启动MySQL服务器后开始记录，并在文件达到max_binlog_size所设置的大小或者接收到flush logs命令后重新创建新的日志文件，只需定时执行flush logs方法重新创建新的日志，生成二进制文件序列，并及时把这些日志保存到安全的地方就完成了一个时间段的增量备份。
7.4、增量备份的方法
7.4.1一般恢复
mysqbinlog [--no-defaults] 增量备份文件 | mysql -u 用户名 -p
#显示表cc mysql> select * from cc; +------+-------+ | id | name | +------+-------+ | 1 | tom | | 2 | jerry | +------+-------+ 2 rows in set (0.00 sec) [root@localhost ~]# mysqldump -u root -p --all-databases > all-data.sql Enter password: [root@localhost ~]# ll 总用量 48464 -rw-r--r--. 1 root root 780979 11月 4 17:26 all-data.sql [root@localhost ~]# vi /etc/my.cnf #末行添加 log_bin=/usr/local/mysql/data/mysql_bin #开启增量备份 [root@localhost ~]# cd /usr/local/mysql/data/ [root@localhost data]# systemctl restart mysqld.service [root@localhost data]# ls auto.cnf ib_logfile1 server3.err ibdata1 mysql mysql-bin.index test ib_logfile0 mysql-bin.000001 performance_schema tom [root@localhost data]# mysqladmin -uroot -p flush-logs #查看日志 [root@localhost data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql_bin.000002 mysql> insert into cc values(3,'qiaozhi'); Query OK, 1 row affected (0.01 sec) mysql> delete from cc where id =1; Query OK, 1 row affected (0.00 sec) mysql> insert into cc values(4,'suxi'); Query OK, 1 row affected (0.01 sec) mysql> select * from cc; +------+---------+ | id | name | +------+---------+ | 2 | jerry | | 3 | qiaozhi | | 4 | suxi | +------+---------+ 3 rows in set (0.00 sec) [root@localhost data]# mysqladmin -uroot -p flush-logs Enter password: [root@localhost data]# ls auto.cnf ib_logfile0 mysql_bin.000001 performance_schema bb ib_logfile1 mysql_bin.000002 sys ib_buffer_pool ibtmp1 mysql_bin.000003 ibdata1 mysql mysql_bin.index [root@localhost data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql_bin.000002
mysql> drop table cc; #删表Query OK, 0 rows affected (0.02 sec)[root@localhost data]# mysql -u root -p bb < /root/all-data.sql #恢复表mysql> select * from cc;+------+-------+| id | name |+------+-------+| 1 | tom || 2 | jerry |+------+-------+2 rows in set (0.00 sec)#恢复[root@localhost data]# mysqlbinlog --no-defaults --stop-datetime='2020-11-04 17:32:41' /usr/local/mysql/data/mysql_bin.000002 | mysql -u root -pEnter password: [root@localhost data]# mysqlbinlog --no-defaults --start-datetime='2020-11-04 17:32:53' /usr/local/mysql/data/mysql_bin.000002 | mysql -u root -pEnter password: mysql> select * from bb.cc;+------+---------+| id | name |+------+---------+| 1 | tom || 2 | jerry || 3 | qiaozhi || 4 | suxi |+------+---------+4 rows in set (0.00 sec)#已经跳过错误操作
位置点恢复：
mysql> delete from cc where id=1; Query OK, 1 row affected (0.01 sec) mysql> delete from cc where id=2; Query OK, 1 row affected (0.01 sec) [root@server1 ~]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql_bin.000003 ###查询该二进制日志内容
[root@localhost data]# mysqlbinlog --no-defaults --stop-position='765210' /usr/local/mysql/data/mysql_bin.000003 | mysql -u root -pEnter password: [root@localhost data]# mysqlbinlog --no-defaults --start-position='765589' /usr/local/mysql/data/mysql_bin.000003 | mysql -u root -pEnter password: [root@localhost data]# mysqlmysql> select * from bb.cc;+------+---------+| id | name |+------+---------+| 1 | tom || 2 | jerry || 3 | qiaozhi || 4 | suxi |+------+---------+4 rows in set (0.00 sec)
推荐阅读
