MySQL数据库数据的备份与恢复

最新推荐文章于 2023-08-08 16:58:57 发布

阿辉爱学习

最新推荐文章于 2023-08-08 16:58:57 发布

阅读量163

点赞数 5

分类专栏： Linux MySQL 文章标签： linux mysql

本文链接：https://blog.csdn.net/weixin_48191060/article/details/108564737

版权

Linux 同时被 2 个专栏收录

48 篇文章 1 订阅

订阅专栏

MySQL

11 篇文章 0 订阅

订阅专栏

备份与恢复

1. 数据备份的重要性
2. 数据库备份的分类
3. 常见的备份方法
4. 完全备份
5. 数据库完全备份分类
6. 实例操作
- 6.0.1 MySQL数据库完全备份操作
- 6.0.2 mysqldump备份与恢复

1. 数据备份的重要性

在生产环境中，数据的安全性至关重要
任何数据的丢失都可能产生严重的后果
造成数据丢失的原因
- 程序错误
- 人为操作错误
- 远算错误
- 磁盘故障
- 灾难（如火灾，地震）和盗窃

2. 数据库备份的分类

从物理与逻辑的角度，备份可分为
- 物理备份：对数据库操作系统的物理文件（如数据文件，日志文件等）的备份
  - 物理备份方法
    - 冷备份（脱机备份）：是在关闭数据库的时候进行的
    - 热备份（联机备份）：数据库处于运行状态，依赖于数据库的日志文件
    - 温备份：数据库锁定表格（不可写入但可读）的状态下进行备份操作
- 逻辑备份：对数据库逻辑组件（如：表等数据库对象）的备份
从数据库的备份策略角度，备份可分为
- 完全备份：每次对数据库进行完整的备份
- 差异备份：备份自从上次完全备份之后被修改过的文件
- 增量备份：只有在上次完全备份或者增量备份后被修改的文件才会被备份

3. 常见的备份方法

物理冷备
- 备份时数据库处于关闭状态，直接打包数据库文件
- 备份速度快，恢复时也是最简单的
专用备份工具mydump或mysqlhotcopy
- mysqldump常用的逻辑备份工具
- mysqlhotcopy仅拥有备份MyISAM和ARCHIVE表
启用二进制日志进行增量备份
- 进行增量备份，需要刷新二进制日志
第三方工具备份
- 免费的MySQL热备份软件 Percona XtraBackup

4. 完全备份

是对整个数据库，数据库结构和文件结构的备份
保存的是备份完成时刻的数据库
是差异备份与增量备份的基础
优点
- 备份与恢复操作简单方便
缺点
- 数据存在大量的重复
- 占用大量的备份空间
- 备份与恢复时间长

5. 数据库完全备份分类

物理冷备份与恢复
- 关闭MySQL数据库
- 使用tar命令直接打包数据库文件夹
- 直接替换现有MySQL目录即可
mysqldump备份与恢复
- MySQL自带的备份工具，可方便实现对MySQL的备份
- 可以将指定的库，表导出为SQL脚本
- 使用命令MySQL导入备份的数据

6. 实例操作

6.0.1 MySQL数据库完全备份操作

物理冷备份与恢复（tar命令直接打包数据库文件夹）

[root@localhost ~]# systemctl stop mysqld                                ###关闭数据库####
[root@localhost ~]# mkdir /backup                                        ###在根下新建个backup目录
[root@localhost ~]# tar zcf /backup/mysql_all-$(date +%F).tar.gz /usr/local/mysql/data/           #####/usr/local/mysql/data备份压缩
[root@localhost ~]# cd /backup/                                               ###进入/backup/         
[root@localhost backup]# ll                                                        ##查看备份
总用量 1328
-rw-r--r-- 1 root root 1359664 8月  12 22:39 mysql_all-2018-08-12.tar.gz

备份数据库：
模拟故障

[root@localhost backup]# mkdir /bak                                            ####在根下新建bak目录
[root@localhost backup]# mv /usr/local/mysql/data/ /bak/          ####将目录data数据移动到bak 里面去
[root@localhost backup]# cd /usr/local/mysql/                             #####仅mysql目录查看data
[root@localhost mysql]# ll                                                              ####查看后data目录被移走 ，模拟故障成功
。。。。。。。

数据库恢复

[root@localhost mysql]# mkdir /restore                                                                                        ####根目录新建restore 目录
[root@localhost mysql]# tar xzvf /backup/mysql_all-2018-08-12.tar.gz -C /restore/              ####将备份数据库解压到 /restore目录下  不要无脑刷，时间节点不一样
usr/local/mysql/data/
usr/local/mysql/data/ibdata1
usr/local/mysql/data/ib_logfile1
usr/local/mysql/data/ib_logfile0
[root@localhost mysql]# mv /restore/usr/local/mysql/data/ /usr/local/mysql/          ####将备份数据移动到到/usr/local/mysql中
[root@localhost mysql]# systemctl start mysqld                                                          ####启动mysql数据库
[root@localhost mysql]# systemctl status mysqld                                                        ####查看mysql数据库启动状态  ，日志文件显示正常。

6.0.2 mysqldump备份与恢复

mysqldump备份与恢复
将指定的库、表、或全部的库导出为SQL脚本
备份操作

mysqldump备份需要和mysql进行数据交互，如果关闭>mysql 则无法备份和恢复
mysqldump [选项] 库名 [表名1] [表名2] … > /备份路径/备份文件名
mysqldump [选项] --databases 库名1 [库名2] … > /备份路径/备份文件名
mysqldump [选项] --all-databases > /备份路径/备份文件名

创建环境

mysql> create database auth;                              	####创建auth 这个数据库
mysql> use auth;                              		####进入auth 这个数据库
mysql> create table users (user_name CHAR(16) NOT NULL, user_passwd CHAR(48) DEFAULT '', PRIMARY KEY (user_name));
Database changed
mysql> insert into users(user_name,user_passwd) values('zhangsan', password('123456'));
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> insert into users values('lisi', password('123456'));
Query OK, 1 row affected, 1 warning (0.01 sec)
######列子####
[root@localhost /]# mysqldump -u root -p mysql user >mysql-user.sql     ###备份数据路mysql中的user表  ，文件保存在当前操作目录下面
Enter password:                                                                                                  ###输入密码abc123            

######列子#####
[root@localhost /]# mysqldump -u root -p --databases auth >auth.sql      ####备份auth数据库     文件保存在当前操作目录下面
Enter password:                                                                                             ####输入密码abc123 

#####列子######
查看备份文件
[root@localhost /]# grep -v "^--" auth.sql | grep -v "^/" | grep -v "^$"
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `auth` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `auth`;
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `user_name` char(16) NOT NULL,
  `user_passwd` char(48) DEFAULT '',
  PRIMARY KEY (`user_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
LOCK TABLES `users` WRITE;
INSERT INTO `users` VALUES ('zhangsan','*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9');
UNLOCK TABLES;

#####恢复操作#####
mysql [选项] [库名] [表名] < /备份路径/备份文件名

#####列子###恢复表
[root@localhost /]# mysql -u root -p                  ###进入数据库###
Enter password:                                                    ###输入密码####
mysql> show databases;                                      ###查看数据库####
+--------------------+
| Database           |
+--------------------+
| information_schema |
| auth               |
| bdqn               |
| myadm              |
| mysql              |
| performance_schema |
| sys                |

mysql> create database text ;                           ####新建数据库text ,等下导表用###
Query OK, 1 row affected (0.00 sec)
mysql> exit                                                      ####退出#####

[root@localhost opt]# mysql -u root -p text < mysql-user.sql 
Enter password: 

[root@localhost /]# mysql -u root -p -e 'show tables from text'    #######验证输出结果####
Enter password:                                                                               ######输入密码
+----------------+
| Tables_in_text |
+----------------+
| user           |                                                                                   ####导进去的表
+----------------+

######列子数据库恢复#####
[root@localhost /]# mysql -u root -p -e 'drop database auth'            ######模拟故障删除auth数据库
Enter password: 
[root@localhost /]# mysql -u root -p -e 'show databases'                  ######查看databases所有数据库有没有auth，发现没有了
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bdqn               |
| myadm              |
| mysql              |
| performance_schema |
| sys                |
| text               |

[root@localhost /]# mysql -u root -p < ./auth.sql                             ####导入数据库auth
Enter password:                                                                                  ####输入密码
[root@localhost /]# mysql -u root -p -e 'show databases'                ######查看databases所有数据库有没有auth，发现有了
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| auth               |                                                                                  ####导进去的auth数据库
| bdqn               |
| myadm              |
| mysql              |
| performance_schema |
| sys                |
| text               |
+--------------------+



##########MySQL数据库增量备份恢复##############
1、增量备份的特点
与完全备份完全不同，增量备份没有重复数据，备份量不大，时间短；但其恢复麻烦，需要上次完成完全备份及备份之后的所有增量备份才能恢复。

2、MySQL数据库二进制日志对备份的意义
二进制日志保存了所有更新或者可能更新数据的操作。二进制日志在启动mysql服务器后开始记录，并在文件达到二进制日志所设置的最大值
或者接受到flush logs命令后重新创建新的日志文件，生成二进制的文件序列，并及时把这些日志文件保存到安全的存储位置，即可完成一个时间段
的增量备份
[root@localhost mysql]# vim /etc/my.cnf                                               #####编辑my.cnf配置文件
[client]
port = 3306
default-character-set=utf8
socket = /usr/local/mysql/mysql.sock

[mysql]
port = 3306
default-character-set=utf8
socket = /usr/local/mysql/mysql.sock

[mysqld]                                                                                                       #####找到这个模块
user = mysql
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port = 3306
character_set_server=utf8
pid-file = /usr/local/mysql/mysqld.pid
socket = /usr/local/mysql/mysql.sock
server-id = 1
log-bin=/usr/local/mysql/data/mysql-bin                            ####这个地方开启二进制日志功能，这个和书上的不一样

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,PIPES_AS_CONCAT,ANSI_QUOTES
                                      
[root@localhost mysql]# systemctl restart mysqld                                           ####重启数据库

[root@localhost ~]# ll /usr/local/mysql/data/
总用量 122924
-rw-r----- 1 mysql mysql       56 8月   9 16:16 auto.cnf
-rw-r----- 1 mysql mysql      308 8月  15 05:50 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 8月  15 05:50 ibdata1
-rw-r----- 1 mysql mysql 50331648 8月  15 05:50 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 8月   9 16:16 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 8月  15 05:50 ibtmp1
drwxr-x--- 2 mysql mysql     4096 8月   9 16:16 mysql
-rw-r----- 1 mysql mysql      154 8月  15 05:50 mysql-bin.000001                            ####二进制日志文件
-rw-r----- 1 mysql mysql       39 8月  15 05:50 mysql-bin.index
drwxr-x--- 2 mysql mysql     8192 8月   9 16:16 performance_schema
drwxr-x--- 2 mysql mysql     8192 8月   9 16:16 sys



####################增量恢复的方法######################
#####一般恢复####
mysqlbinlog [--no-defaults] 增量备份文件 | mysql -u 用户名 -p



####基于位置的恢复###
1、恢复数据到指定位置
mysqlbinlog --stop-position=’操作 id’ 二进制日志 |mysql -u 用户名 -p 密码


2、从指定的位置开始恢复数据
mysqlbinlog --start-position=’操作 id’ 二进制日志 |mysql -u 用户名 -p 密码






#####基于时间点的恢复#####
1、从日志开头截止到某个时间点的恢复
mysqlbinlog [--no-defaults] --stop-datetime=’年-月-日 小时:分钟:秒’ 二进制日志 | mysql -u 用户名 -p 密码

2、从某个时间点到日志结尾的恢复
mysqlbinlog [--no-defaults] --start-datetime=’年-月-日 小时:分钟:秒’ 二进制日志 | mysql -u 用户名 -p 密码

3、从某个时间点到某个时间点的恢复
mysqlbinlog [--no-defaults] --start-datetime=’年-月-日 小时:分钟:秒’ --stop-datetime=’年-月-日小时:分钟:秒’ 二进制日志 | mysql -u 用户名 -p 密码


#########mysql企业备份案例######
1、一般恢复（丢什么数据，找什么数据）
[root@localhost opt]# mysql -u root -p
Enter password: 
mysql> create database client;
Query OK, 1 row affected (0.01 sec)
mysql> use client;
mysql> create table user_info(身份证 char(20) not null,姓名 char(20) not null,姓别 char(4),用户ID号 char(10) not null,资费 int(10));
mysql> insert into user_info values('000006','张三','男','016','10');
mysql> insert into user_info values('000007','李四','女','017','91');
mysql> insert into user_info values('000008','王五','女','018','23');
mysql> select * from user_info;

mysql> show master logs;                                    ####查看当前数据库binlog文件
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |      1492 |
+------------------+-----------+
1 row in set (0.00 sec)





2、进行一次完全备份
mysql> flush tables with read lock;                        #####备份前需要将数据库加读锁，防止数据在备份时写入。
Query OK, 0 rows affected (0.01 sec)
mysql> exit
Bye

[root@localhost ~]# mkdir /mysql_bak                       ###创建/mysql_bak 目录

[root@localhost opt]# mysqldump -u root -p client user_info >/mysql_bak/client_userinfo-$(date +%F).sql      ####这边括号中%F 的F必须是大写的
Enter password: 
[root@localhost ~]# ll /mysql_bak/
总用量 4
-rw-r--r-- 1 root root 2055 8月  15 05:55 client_userinfo-2018-08-15.sql

[root@localhost mysql_bak]# mysqladmin -u root -p flush-logs
[root@localhost ~]#  ll /usr/local/mysql/data/
总用量 122928
-rw-r----- 1 mysql mysql       56 8月   9 16:16 auto.cnf
drwxr-x--- 2 mysql mysql       62 8月  15 06:04 client
-rw-r----- 1 mysql mysql      308 8月  15 06:03 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 8月  15 06:04 ibdata1
-rw-r----- 1 mysql mysql 50331648 8月  15 06:04 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 8月   9 16:16 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 8月  15 06:05 ibtmp1
drwxr-x--- 2 mysql mysql     4096 8月   9 16:16 mysql
-rw-r----- 1 mysql mysql     1539 8月  15 06:05 mysql-bin.000001
-rw-r----- 1 mysql mysql      154 8月  15 06:05 mysql-bin.000002
-rw-r----- 1 mysql mysql       78 8月  15 06:05 mysql-bin.index
drwxr-x--- 2 mysql mysql     8192 8月   9 16:16 performance_schema
drwxr-x--- 2 mysql mysql     8192 8月   9 16:16 sys


3、继续录入新的内容并进行增量备份
[root@localhost mysql_bak]# mysql -u root -p
Enter password: 
mysql> unlock tables;             #######解除表锁。
Query OK, 0 rows affected (0.00 sec)
mysql> use client;
mysql> insert into user_info values('000009','赵六','男','019','37');
mysql> insert into user_info values('000010','孙七','男','020','36');
mysql> select * from user_info;
+-----------+--------+--------+-------------+--------+
| 身份证    | 姓名   | 姓别   | 用户ID号    | 资费   |
+-----------+--------+--------+-------------+--------+
| 000006    | 张三   | 男     | 016         |     10 |
| 000007    | 李四   | 女     | 017         |     91 |
| 000008    | 王五   | 女     | 018         |     23 |
| 000009    | 赵六   | 男     | 019         |     37 |
| 000010    | 孙七   | 男     | 020         |     36 |
+-----------+--------+--------+-------------+--------+
5 rows in set (0.00 sec)

mysql> exit
Bye
[root@localhost mysql_bak]# mysqladmin -u root -p flush-logs
Enter password: 

[root@localhost ~]# ll /usr/local/mysql/data
总用量 122932
-rw-r----- 1 mysql mysql       56 8月   9 16:16 auto.cnf
drwxr-x--- 2 mysql mysql       62 8月  15 06:04 client
-rw-r----- 1 mysql mysql      308 8月  15 06:03 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 8月  15 06:07 ibdata1
-rw-r----- 1 mysql mysql 50331648 8月  15 06:07 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 8月   9 16:16 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 8月  15 06:05 ibtmp1
drwxr-x--- 2 mysql mysql     4096 8月   9 16:16 mysql
-rw-r----- 1 mysql mysql     1539 8月  15 06:05 mysql-bin.000001
-rw-r----- 1 mysql mysql      797 8月  15 06:07 mysql-bin.000002
-rw-r----- 1 mysql mysql      154 8月  15 06:07 mysql-bin.000003
-rw-r----- 1 mysql mysql      117 8月  15 06:07 mysql-bin.index
drwxr-x--- 2 mysql mysql     8192 8月   9 16:16 performance_schema
drwxr-x--- 2 mysql mysql     8192 8月   9 16:16 sys


[root@localhost ~]# cp /usr/local/mysql/data/mysql-bin.000002 /mysql_bak/

4、模拟误操作删除 user_info表
[root@localhost mysql_bak]# mysql -u root -p -e 'drop table client.user_info'
Enter password: 
[root@localhost mysql_bak]# mysql -u root -p -e 'select * from client.user_info'
Enter password: 
ERROR 1146 (42S02) at line 1: Table 'client.user_info' doesn't exist'

5、恢复操作
[root@localhost mysql_bak]# mysql -u root -p client < /mysql_bak/client_userinfo-2018-08-14.sql 
Enter password: 
[root@localhost mysql_bak]# mysql -u root -p -e'select * from client.user_info'
Enter password: 
+-----------+--------+--------+-------------+--------+
| 身份证    | 姓名   | 姓别   | 用户ID号    | 资费   |
+-----------+--------+--------+-------------+--------+

| 000006    | 张三   | 男     | 016         |     10 |
| 000007    | 李四   | 女     | 017         |     91 |
| 000008    | 王五   | 女     | 018         |     23 |
+-----------+--------+--------+-------------+--------+


[root@localhost ~]# mysqlbinlog --no-defaults /mysql_bak/mysql-bin.000002 |mysql -u root -p
Enter password: 
[root@localhost ~]#  mysql -u root -p -e'select * from client.user_info'
Enter password: 
+-----------+--------+--------+-------------+--------+
| 身份证    | 姓名   | 姓别   | 用户ID号    | 资费   |
+-----------+--------+--------+-------------+--------+
| 000006    | 张三   | 男     | 016         |     10 |
| 000007    | 李四   | 女     | 017         |     91 |
| 000008    | 王五   | 女     | 018         |     23 |
| 000009    | 赵六   | 男     | 019         |     37 |
| 000010    | 孙七   | 男     | 020         |     36 |
+-----------+--------+--------+-------------+--------+



######基于位置恢复#####
[root@localhost mysql_bak]# mysql -uroot -p -e 'drop table client.user_info'    ### 删除表user_info
Enter password: 

[root@localhost mysql_bak]# mysql -uroot -p -e 'select * from client.user_info'
Enter password: 
ERROR 1146 (42S02) at line 1: Table 'client.user_info' doesn't exist



[root@localhost mysql_bak]# mysql -u root -p client < /mysql_bak/client_userinfo-2018-08-14.sql 
Enter password:

[root@localhost ~]# mysql -u root -p -e'select * from client.user_info'
Enter password: 
+-----------+--------+--------+-------------+--------+
| 身份证    | 姓名   | 姓别   | 用户ID号    | 资费   |
+-----------+--------+--------+-------------+--------+
| 000006    | 张三   | 男     | 016         |     10 |
| 000007    | 李四   | 女     | 017         |     91 |
| 000008    | 王五   | 女     | 018         |     23 |
+-----------+--------+--------+-------------+--------+




[root@localhost ~]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /mysql_bak/mysql-bin.000002              ####--base64-output=decode-rows    64解码    ###  -v   换行显示


[root@localhost ~]# mysqlbinlog --no-defaults --stop-position='517' /mysql_bak/mysql-bin.000002 |mysql -u root -p      ####操作的节点附近即可
[root@localhost ~]# mysql -u root -p -e'select * from client.user_info'
Enter password: 
+-----------+--------+--------+-------------+--------+
| 身份证    | 姓名   | 姓别   | 用户ID号    | 资费   |
+-----------+--------+--------+-------------+--------+
| 000006    | 张三   | 男     | 016         |     10 |
| 000007    | 李四   | 女     | 017         |     91 |
| 000008    | 王五   | 女     | 018         |     23 |
| 000009    | 赵六   | 男     | 019         |     37 |
+-----------+--------+--------+-------------+--------+

#######
[root@localhost ~]# mysql -u root -p -e 'drop table client.user_info'
Enter password: 
[root@localhost ~]# mysql -u root -p client < /mysql_bak/client_userinfo-2018-08-15.sql 
Enter password: 
[root@localhost ~]# mysqlbinlog --no-defaults --start-position='657' /mysql_bak/mysql-bin.000002 |mysql -u root -p        ####操作的节点附近即可
Enter password: 
[root@localhost ~]# mysql -u root -p -e'select * from client.user_info'
Enter password: 
+-----------+--------+--------+-------------+--------

| 身份证    | 姓名   | 姓别   | 用户ID号    | 资费   |
+-----------+--------+--------+-------------+--------+
| 000006    | 张三   | 男     | 016         |     10 |
| 000007    | 李四   | 女     | 017         |     91 |
| 000008    | 王五   | 女     | 018         |     23 |
| 000010    | 孙七   | 男     | 020         |     36 |
+-----------+--------+--------+-------------+--------+
#######总结##
基于位置恢复的操作，节点不要选择太靠近操作的节点，否则容易恢复失败。




######基于时间恢复#####  20200913  0:01:10
mysqlbinlog --no-defaults --stop-datetime='2018-8-15 6:07:39' /mysql_bak/mysql-bin.000002 |mysql -u root -p    ##恢复赵六
mysqlbinlog --no-defaults --start-datetime='2018-8-15 6:07:39' /mysql_bak/mysql-bin.000002 |mysql -u root -p    ##恢复孙七


mysqlbinlog --no-defaults --stop-datetime='2020-9-13 0:01:10' /mysql_bak/mysql-bin.000002 |mysql -u root -p    ##恢复赵六
mysqlbinlog --no-defaults --start-datetime='2020-9-13 0:01:10' /mysql_bak/mysql-bin.000002 |mysql -u root -p    ##恢复孙七


mysqlbinlog --no-defaults --start-datetime='2019-3-13 18:16:30' /mysql_bak/mysql-bin.000003 |mysql -u root -p
18:16:30

####列子###
[root@localhost ~]# mysql -u root -p -e 'drop table client.user_info'
Enter password: 
[root@localhost ~]# mysql -u root -p client < /mysql_bak/client_userinfo-2018-08-15.sql 
Enter password: 
[root@localhost ~]# mysqlbinlog --no-defaults --stop-datetime='2018-8-15 6:07:39' /mysql_bak/mysql-bin.000002 |mysql -u root -p          ######时间戳不要弄错了
Enter password: 
[root@localhost ~]# mysql -u root -p -e'select * from client.user_info'
Enter password: 
+-----------+--------+--------+-------------+--------+
| 身份证    | 姓名   | 姓别   | 用户ID号    | 资费   |
+-----------+--------+--------+-------------+--------+
| 000006    | 张三   | 男     | 016         |     10 |
| 000007    | 李四   | 女     | 017         |     91 |
| 000008    | 王五   | 女     | 018         |     23 |
| 000009    | 赵六   | 男     | 019         |     37 |
+-----------+--------+--------+-------------+--------+


####列子###
[root@localhost ~]# mysql -u root -p -e 'drop table client.user_info'
Enter password: 
[root@localhost ~]# mysql -u root -p client < /mysql_bak/client_userinfo-2018-08-15.sql 
Enter password: 
[root@localhost ~]# mysqlbinlog --no-defaults --start-datetime='2018-8-15 15:33:48' /mysql_bak/mysql-bin.000002 |mysql -u root -p    ######时间戳不要弄错了，根据的你的日志文件来##
Enter password: 
[root@localhost ~]# mysql -u root -p -e'select * from client.user_info'
Enter password: 
+-----------+--------+--------+-------------+--------+
| 身份证    | 姓名   | 姓别   | 用户ID号    | 资费   |
+-----------+--------+--------+-------------+--------+
| 000006    | 张三   | 男     | 016         |     10 |
| 000007    | 李四   | 女     | 017         |     91 |
| 000008    | 王五   | 女     | 018         |     23 |
| 000010    | 孙七   | 男     | 020         |     36 |
+-----------+--------+--------+-------------+--------+





#####验证表的锁定是否产生二进制文件####

mysqladmin -u root -p flush-logs
[root@localhost ~]# mysql -uroot -p -e 'show master logs'
Enter password: 
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |      1539 |
| mysql-bin.000002 |       797 |
| mysql-bin.000003 |      9438 |
| mysql-bin.000004 |       154 |
+------------------+-----------+
mysql> use client;

mysql> insert into user_info values('000009','老八','男','025','23');
mysql> select * from user_info;
mysql>LOCK TABLES tbl_name READ;

mysql> insert into user_info values('000010','赵9','男','030','23');


[root@localhost ~]# mysqladmin -u root -p flush-logs
Enter password: 
[root@localhost ~]# mysql -uroot -p -e 'show master logs'
Enter password: 
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |      1539 |
| mysql-bin.000002 |       797 |
| mysql-bin.000003 |      9438 |
| mysql-bin.000004 |       499 |
| mysql-bin.000005 |       154 |
+------------------+-----------+

[root@localhost ~]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /mysql_bak/mysql-bin.000004              ####--base64-output=decode-rows    64解码    ###  -v   换行显示







###解锁##

第一种

show processlist;

找到锁进程，kill id ;

 

第二种

mysql>UNLOCK TABLES;

###锁表###

锁定数据表，避免在备份过程中，表被更新

mysql>LOCK TABLES tbl_name READ;

为表增加一个写锁定：

mysql>LOCK TABLES tbl_name WRITE;

阿辉爱学习

关注

5
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
2
评论
MySQL数据库数据的备份与恢复

1. 数据备份的重要性在生产环境中，数据的安全性至关重要任何数据的丢失都可能产生严重的后果造成数据丢失的原因程序错误人为操作错误远算错误磁盘故障灾难（如火灾，地震）和盗窃2. 数据库备份的分类从物理与逻辑的角度，备份可分为物理备份：对数据库操作系统的物理文件（如数据文件，日志文件等）的备份物理备份方法冷备份（脱机备份）：是在关闭数据库的时候进行的热备份（联机备份）：数据库处于运行状态，依赖于数据库的日志文件温备份：数据库锁定表格（不可写入但可读）的状态下进行备
复制链接

扫一扫