mysql 4 基础_MySQL数据库基础(四)grant授权、binlog日志介绍

最新推荐文章于 2022-04-21 22:13:09 发布

weixin_39692761

最新推荐文章于 2022-04-21 22:13:09 发布

阅读量396

点赞数

文章标签： mysql 4 基础

本文链接：https://blog.csdn.net/weixin_39692761/article/details/113240101

版权

一、用户授权

1.1 grant授权

1.2 相关命令授权库

1.3 revoke撤销权限

二、root密码

2.1 恢复root密码(忘记密码)

2.2 重置root密码

三、MySQL备份

3.1 备份概述物理、逻辑备份

3.2 数据备份策略完全备份、增量备份

四、增量备份 binlog日志

4.1 binlog日志概述

4.2 启用日志

4.3 分析日志

4.4 恢复数据

一、用户授权

1.1 grant授权

grant授权︰添加用户并设置权限命令格式

grant 权限列表 on 库名 to 用户名@”客户端地址” identified by “密码” //授权用户密码

with grant option; //有授权权限,可选项

mysql>grant all on db4.*to yaya@"%" identified by "123qqq..A”;

权限列表

all //所有权限

usage //无权限

select,update,insert //I个别权限

select,update (字段1,.. ..，字段N) //指定字段

用户详情的权限列表请参考MySQL官网说明：https://dev.mysql.com/doc/refman/5.7/en/privileges-provided.html

库名

*.* //所有库所有表

库名.* //一个库

库名.表名 //一张表

用户名

授权时自定义要有标识性

存储在mysql库的user表里

客户端地址

% //所有主机

192.168.4.% //网段内的所有主机

192.168.4.1 //1台主机

localhost //数据库服务器本机

应用示例

添加用户mydba，对所有库、表有完全权限

允许从任何客户端连接，密码abc123

且有授权权限

mysql> grant all on *.* to mydba@'%' identified by "abc123" with grant option;

Query OK, 0 rows affected, 1 warning (0.02 sec)

需要注意的是 8.0之后的新版的的mysql版本已经将创建账户和赋予权限的方式分开了

之前创建方式会报错:

mysql> grant all on *.* to mydba@"%" identified by "abc123" with grant option;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'identified by "123qqq...A" with grant option' at line 1

8.0 版本后 grant授权创建账户和赋予权限的需要分两步完成

1.创建账户:create user '用户名'@'访问主机' identified by '密码';

2.赋予权限:grant 权限列表 on 数据库 to '用户名'@'访问主机' ;(修改权限时在后面加with grant option)

添加用户mydba@"%"

mysql> create user mydba@"%" identified by "abc123";

Query OK, 0 rows affected (0.11 sec)

mysql> grant all on *.* to mydba@"%" with grant option;

Query OK, 0 rows affected (0.06 sec)

应用示例

添加admin用户，允许从192.168.4.0/24网段连接，对db3库的user表有查询权限，密码123qqq.….A

添加admin2用户，允许从本机连接，允许对db3库的所有表有查询/更新/插入/删除记录权限，密123qqq....A

mysql> grant select on db3.user to admin@"192.168.4.%" identified by "123qqq...A";

mysql> grant select,insert,update,delete on db3.* to admin2@"localhost" identified by "123qqq.….A";

1.2 相关命令授权库

62a1c0d1633e

授权库 mysql

mysql 库记录授权信息，主要表如下:

user 表记录已有的授权用户及权限

db 表记录已有授权用户对数据库的访问权限

tables_priv 表记录已有授权用户对表的访问权限

columns_priv 表记录已有授权用户对字段的访问权限

查看表记录可以获取用户权限;也可以通过更新记录，修改用户权限

1.3 revoke撤销权限

命令格式

mysql> revoke 权限列表 on 库名.表 from 用户名@"客户端地址";

mysql> revoke insert,drop on test.* FROM sqlero2@'localhost';

Query OK,0 rows affected (0.00 sec)

案例1:用户授权

1．允许192.168.4.0/24网段主机使用root连接数据库服务器，对所有库和所有表有完全权限、密码为 abc123...A

2．添加用户dba001，对所有库和所有表有完全权限、且有授权权限，密码为abc123...A 客户端为网络中的所有主机。

3．撤销root从本机访问权限，然后恢复。

4．允许任意主机使用webuser用户连接数据库服务器，仅对webdb库有完全权限，密码为abc123...A．撤销webuser的权限，使其仅有查询记录权限。

1)允许192.168.4.0/24网段主机使用root连接数据库服务器，对所有库和所有表有完全权限、密码为 abc123...A

192.168.4.100远程登陆MySQL

[root@case100 ~]# mysql -u root -p -h 192.168.4.151

Enter password: \\输入密码登陆报错

ERROR 1045 (28000): Access denied for user 'root'@'192.168.4.100' (using password: YES)

添加192.168.4.0/24访问权限

[root@mysql ~]# mysql -uroot -p"123456"

mysql> grant all on *.* to root@'192.168.4.%' identified by "abc123...A";

Query OK, 0 rows affected, 1 warning (0.00 sec)

再次从192.168.4.0/24网段的客户机访问时，输入正确的密码后可登入

[root@case100 ~]# mysql -u root -p -h 192.168.4.151

Enter password:

mysql> select host,user from mysql.user ; \\登陆成功

+-------------+-----------+

| host | user |

+-------------+-----------+

| % | mydba |

| 192.168.4.% | root |

| localhost | mysql.sys |

| localhost | root |

+-------------+-----------+

4 rows in set (0.01 sec)

mysql> create database rootdb;

Query OK, 1 row affected (0.01 sec)

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| rootdb | //新建的rootdb库

| sys |

+--------------------+

5 rows in set (0.01 sec)

2)添加用户dba001，对所有库和所有表有完全权限、且有授权权限，密码为abc123...A 客户端为网络中的所有主机。

mysql> grant all on *.* to dba001@"%" identified by "abc123...A" with grant option; //添加用户并授权

Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> show grants for dba001@"%"; //查看dba001权限

+---------------------------------------------------------------+

| Grants for dba001@% |

+---------------------------------------------------------------+

| GRANT ALL PRIVILEGES ON *.* TO 'dba001'@'%' WITH GRANT OPTION |

+---------------------------------------------------------------+

1 row in set (0.00 sec)

3)撤销root从本机访问权限，然后恢复。

注意：如果没有事先建立其他管理账号，请不要轻易撤销root用户的本地访问权限，否则恢复起来会比较困难，甚至不得不重装数据库。

mysql> revoke all on *.* from root@"localhost"; //撤销root@"localhost"所有权限

Query OK, 0 rows affected (0.01 sec)

mysql> show grants for root@localhost; //查看root@localhost权限

+--------------------------------------------------------------+

| Grants for root@localhost |

+--------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'root'@'localhost' WITH GRANT OPTION |

| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |

+--------------------------------------------------------------+

2 rows in set (0.01 sec)

mysql> exit

Bye

[root@mysql ~]# mysql -uroot -p"123456" //重装登陆测试

mysql> drop database rootdb; //失败报错

ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'rootdb'

尝试以当前的root用户恢复权限，也会失败(无权更新授权表)：

mysql> grant all on *.* to root@localhost with grant option;

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

mysql> exit

Bye

由管理账号dba001重新为root添加本地访问权限

[root@mysql ~]# mysql -udba001 -p"abc123...A"

mysql> grant all on *.* to root@localhost with grant option;

Query OK, 0 rows affected (0.01 sec)

mysql> exit

Bye

[root@mysql ~]# mysql -uroot -p"123456" //root帐号重新登陆测试

mysql> drop database rootdb; //权限恢复删除成功

Query OK, 0 rows affected (0.02 sec)

4)允许任意主机使用webuser用户连接数据库服务器，仅对webdb库有完全权限，密码为1abc123...A．撤销webuser的权限，使其仅有查询记录权限。

mysql> create database webdb; //新建库webdb

Query OK, 1 row affected (0.01 sec)

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| sys |

| webdb |

+--------------------+

5 rows in set (0.01 sec)

mysql> grant all on webdb.* to webuser@'%' identified by "abc123...A"; //对用户webuser授权

Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> show grants for webuser@'%';

+----------------------------------------------------+

| Grants for webuser@% |

+----------------------------------------------------+

| GRANT USAGE ON *.* TO 'webuser'@'%' |

| GRANT ALL PRIVILEGES ON `webdb`.* TO 'webuser'@'%' |

+----------------------------------------------------+

2 rows in set (0.00 sec)

mysql> revoke all on webdb.* from webuser@'%'; //撤销webuser@"%"所有权限

Query OK, 0 rows affected (0.02 sec)

mysql> show grants for webuser@'%';

+-------------------------------------+

| Grants for webuser@% |

+-------------------------------------+

| GRANT USAGE ON *.* TO 'webuser'@'%' |

+-------------------------------------+

1 row in set (0.00 sec)

二、root密码

2.1 恢复root密码(忘记密码)

root密码忘了怎么办?

1.停止MySQL服务程序

2.跳过授权表启动MySQL服务程序

3.修改root密码

4.以正常方式重启MySQL服务程序

主要操作过程

]# vim /etc/my.cnf

[mysqld]

......

skip_grant_tables //配置中追加跳过权限检测

]# systemctl restart mysqld

]# mysql

mysql> update mysql.user set authentication_string=password(“密码”)

->where user="root" and host="localhost"; //修改密码

mysql> flush privileges; //刷新立即生效,后面我们需要重启数据库,这步其实可以省略

mysql> quit ;

2.2 重置root密码

修改管理员root密码有很多种方法以下介绍几种常用的

1)方法1，在Shell命令行下设置

[root@mysql ~]# mysqladmin -uroot -p password 'abc321...A'

Enter password:

mysqladmin: [Warning] Using a password on the command line interface can be insecure.

Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.

2)方法2，以root登入mysql> 后，使用SET PASSWORD指令设置

这个与新安装MySQL-server后首次修改密码时要求的方式相同，平时也可以用：

mysql> SET PASSWORD FOR root@localhost=PASSWORD('1234567');

Query OK,0 rows affected,1warning(0.00 sec)

3)方法3，以root登入mysql> 后，使用GRANT授权工具设置,这个是最常见的用户授权方式：

mysql> GRANT all ON *.* TO root@localhost IDENTIFIED BY '1234567';

Query OK,0 rows affected,1warning(0.00 sec)

4)方法4，以root登入mysql> 后，使用UPDATE更新相应的表记录

这种方法与恢复密码时的操作相同：

mysql> UPDATE mysql.user SET authentication_string=PASSWORD('1234567')

-> WHERE user='root' AND host='localhost'; //重设root的密码

Query OK,0 rows affected,1warning(0.00 sec)

Rows matched:1 Changed:0 Warnings:1

mysql> FLUSH PRIVILEGES; //刷新授权表

Query OK,0 rows affected(0.00 sec)

在上述方法中，需要特别注意：当MySQL服务程序以 skip-grant-tables 选项启动时，如果未执行“FLUSH PRIVILEGES;”操作，是无法通过SET PASSWORD或者GRANT方式来设置密码的。比如，验证这两种方式时，都会看到ERROR 1290的出错提示：

mysql> SET PASSWORD FOR root@localhost=PASSWORD('1234567');

ERROR 1290(HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

mysql> GRANT all ON *.* TO root@localhost IDENTIFIED BY '1234567';

ERROR 1290(HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

案例2: root密码

具体要求如下:

1.恢复管理员root密码123qqq...A

2.重置管理员root密码 A...qqq321

[root@mysql ~]# systemctl stop mysqld

[root@mysql ~]# systemctl status mysqld

● mysqld.service - MySQL Server

Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)

Active: inactive (dead) since 二 2020-12-22 17:38:12 CST; 6s ago

Docs: man:mysqld(8)

http://dev.mysql.com/doc/refman/en/using-systemd.html

Process: 21258 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)

Process: 21240 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)

Main PID: 21261 (code=exited, status=0/SUCCESS)

12月 22 11:45:03 mysql systemd[1]: Starting MySQL Server...

12月 22 11:45:04 mysql systemd[1]: Started MySQL Server.

12月 22 17:38:09 mysql systemd[1]: Stopping MySQL Server...

12月 22 17:38:12 mysql systemd[1]: Stopped MySQL Server.

[root@mysql ~]# vim /etc/my.cnf

skip_grant_tables

......

[root@mysql ~]# systemctl start mysqld

[root@mysql ~]# mysql

mysql> update mysql.user set authentication_string=password('abc123...B') where user="root" and host="localhost";

Query OK, 0 rows affected, 1 warning (0.02 sec)

Rows matched: 1 Changed: 0 Warnings: 1

mysql> flush privileges;

Query OK, 0 rows affected (0.01 sec)

mysql> exit

[root@mysql ~]# vim /etc/my.cnf

#skip_grant_tables

......

[root@mysql ~]# vim /etc/my.cnf

#skip_grant_tables //删除skip_grant_tables 重启服务

......

[root@mysql ~]# systemctl restart mysqld

[root@mysql ~]# mysql -uroot -p"abc123...B"

mysql>

三、MySQL备份

3.1 备份概述物理、逻辑备份

备份概述

数据备份方式

物理备份

冷备:cp、tar、...

逻辑备份

mysqldump //备份命令

mysql //恢复命令

物理备份及恢复

备份操作

cp -r /var/lib/mysql 备份目录/mysql.bak

tar -zcvf /root/mysql.tar.gz /var/lib/mysql/*

恢复操作

cp -r 备份目录/mysql.bak /var/lib/mysql/

tar -zxvf /root/mysql.tar.gz -C /var/lib/mysq1/

chown -R mysql:mysql /var/lib/mysql

逻辑备份

数据备份策略

完全备份

备份所有数据

增量备份

备份上次备份后，所有新产生的数据

差异备份

备份完全备份后，所有新产生的数据

完全备份及恢复

完全备份

]#mysqldump -uroot -p密码库名 > 目录/xxx.sql

完全恢复

]#mysql -uroot -p密码[库名] < 目录/xxx.sql

备份时库名表示方式

--all-databases 或 -A //所有库

数据库名 //单个库

数据库名表名 //单张表

-B 数据库1 数据库2 //多个库

注意事项

无论备份还是恢复，都要验证用户权限!!!

完全备份及恢复应用示例1

-将所有的库备份为allbak.sql文件

-将db3库备份为db3.sql文件

[root@dbsvr1 ~]# mysqldump -uroot -p密码 -A > allbak.sql

[root@dbsvr1 ~]# mysqldump -uroot -p密码 db3 > db3.sql

[root@dbsvr1 ~]# ls -lh *.sql

-rw-r--r--.1 root root 595K 1月2 13:54 allbak.sql-rw-r--r--. 1 root root 4.1K 1月2 13:55 db3.sql

案例3:数据备份与恢复

具体要求如下∶

1.练习mysqldump命令的使用

2.使用mysql命令恢复删除的数据

1)备份MySQL服务器上的所有库

将所有的库备份为mysql-all.sql文件

[root@mysql ~]# mysqldump -u root -p --all-databases >/root/alldb.sql //备份所有库

Enter password:

[root@mysql ~]# file /root/alldb.sql //确定备份文件类型

/root/alldb.sql: UTF-8 Unicode text, with very long lines

[root@mysql ~]# cat /root/alldb.sql|head -15 //查看备份文件alldb.sql的部分内容：

-- MySQL dump 10.13 Distrib 8.0.22, for Linux (x86_64)

-- Host: localhost Database:

-- ------------------------------------------------------

-- Server version 8.0.22

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!50503 SET NAMES utf8mb4 */;

/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;

/*!40103 SET TIME_ZONE='+00:00' */;

/*!50606 SET @OLD_INNODB_STATS_AUTO_RECALC=@@INNODB_STATS_AUTO_RECALC */;

/*!50606 SET GLOBAL INNODB_STATS_AUTO_RECALC=OFF */;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;

[root@mysql ~]#

注意：若数据库都使用MyISAM存储引擎，可以采用冷备份的方式，直接复制对应的

数据库目录即可；恢复时重新复制回来就行。

2)只备份指定的某一个库

[root@mysql ~]# mysqldump -uroot -p db1 > db1.sql //备份db1

Enter password:

[root@mysql ~]# cat /root/db1.sql|head -15

-- MySQL dump 10.13 Distrib 8.0.22, for Linux (x86_64)

-- Host: localhost Database: db1

-- ------------------------------------------------------

-- Server version 8.0.22

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!50503 SET NAMES utf8mb4 */;

/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;

/*!40103 SET TIME_ZONE='+00:00' */;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

3)同时备份指定的多个库

[root@mysql ~]# mysqldump -u root -p -B mysql db1 db2 >mysql.db1.db2.sql //备份db1 db2

Enter password:

[root@mysql ~]# ll /root/mysql.db1.db2.sql

-rw-r--r-- 1 root root 1130849 12月 23 15:18 /root/mysql.db1.db2.sql

4)使用mysql 命令恢复删除的数据

以恢复db1库为例，可参考下列操作把数据恢复到另一台数据库上,如果是在原数据库操作通常不建议直接覆盖旧库，而是采用建立新库并导入逻辑备份的方式执行恢复，待新库正常后即可废弃或删除旧库

mysql> create databases db1bak;

Query OK, 1 row affected (0.01 sec)

mysql> exit

[root@mysql ~]# mysql -u root -p db1bak < /root/db1.sql //恢复所有库到db1bak

Enter password:

[root@mysql ~]# mysql -uroot -p"abc321...A"

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| db1bak |

| mysql |

| performance_schema |

| sys |

| webdb |

+--------------------+

6 rows in set (0.00 sec)

mysql> use db1bak

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_db1bak |

+------------------+

| gz |

| school |

| t1 |

| t3 |

| t4 |

| t5 |

| t6 |

| t8 |

| tea4 |

| yg |

+------------------+

10 rows in set (0.00 sec)

mysql> select * from t1;

+------+---------+

| name | homedir |

+------+---------+

| bob | USA |

+------+---------+

1 row in set (0.00 sec)

四、增量备份 binlog日志

4.1 binlog日志概述

-binlog日志也称做二进制日志

-MySQL服务日志文件的一种

-记录除查询之外的所有SQL命令

-可用于数据备份和恢复

-配置mysql主从同步的必要条件

62a1c0d1633e

启用日志主要操作

[root@mysql ~]# vim /etc/my.cnf

[mysqld]

...

log_bin //启用binlog日志

server_id=100 //指定id值

[root@mysql ~]# systemctl restart mysqld

启用日志

binlog相关文件

主机名-bin.index \\索引文件

主机名-bin.000001 \\第1个二进制日志

主机名-bin.000002 \\第2个二进制日志

手动生成新的日志文件:

方法1. ]# systemctl restart mysqld

方法2. mysql> flush logs; 或 ]# mysql -uroot -p密码 -e'flush log'

方法3.mysqldump --flush-logs

清理日志

删除指定编号之前的binlog日志文件

Mysql> purge master logs to "binlog文件名"; \\删除所有binlog日志，重建新日志

Mysql> reset master;

案例4 : binlog日志

启用binlog日志，具体要求如下:

启用binlog日志，把日志文件存放到系统的/mylog目录下，日志文件为db50

手动创建3个新的日志文件

删除编号3之前的日志文件

[root@mysql ~]# vim /etc/my.cnf

[mysqld]

......

log_bin=/mylog/db50

server_id=1

[root@mysql ~]# systemctl restart mysqld

[root@mysql ~]# ll /mylog/

总用量 8

-rw-r----- 1 mysql mysql 154 12月 23 16:49 db50.000001

-rw-r----- 1 mysql mysql 19 12月 23 16:49 db50.index

[root@mysql ~]# mysql -uroot -p"abc321...A"

mysql> flush logs; //每执行一次都会生成新的日志文件

Query OK, 0 rows affected (0.08 sec)

mysql> flush logs;

Query OK, 0 rows affected (0.02 sec)

mysql> flush logs;

Query OK, 0 rows affected (0.02 sec)

mysql> system ls /mylog/

db50.000001 db50.000002 db50.000003 db50.000004 db50.index

mysql> show master status; //查看当前使用的日志文件

+-------------+----------+--------------+------------------+-------------------+

+-------------+----------+--------------+------------------+-------------------+

| db50.000004 | 154 | | | |

+-------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

mysql> purge master logs to "db50.000003"; //删除db50.000003之前的日志文件

Query OK, 0 rows affected (0.05 sec)

mysql> system ls /mylog/

db50.000003 db50.000004 db50.index

mysql> cat /mylog/db50.index //查看日志索引

/mylog/db50.000003

/mylog/db50.000004

4.3 分析日志

查看日志当前记录格式

mysql> show variables like "binlog_format";

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| binlog_format | MIXED |

+---------------+-------+

1 row in set (0.03 sec)

三种记录方式:

1.statement报表模式

2.row行模式

3.mixed混合模式

以上3种模式具体差异可自行查找,推荐mixed混合模式结合了1,2的优势

修改日志记录格式操作

[root@localhost ~]# vim /etc/my.cnf

[mysqld]

.. ..

binlog_format=“名称”

[root@localhost ~]# systemctl restart mysqld

查看日志内容

mysqlbinlog [选项] binlog 日志文件名

选项

用途

--start-datetime="yyyy-mm-dd hh:mm:ss” 起始时间从二进制日志中读取指定等于时间戳或者晚于本地计算机的时间

--stop-datetime="yyyy-mm-dd hh:mm:ss"结束时间从二进制日志中读取指定小于时间戳或者等于本地计算机的时间

--start-position=数字起始偏移量从二进制日志中读取指定position 事件位置作为开始。

--stop-position=数字结束偏移量从二进制日志中读取指定position 事件位置作为事件截至

在使用binlog数据恢复时,推荐使用事件位置来确定开始与截至段会更精确

时间的方式只精确到秒,如果一秒内同时发生了添加和删除操作恢复会失败

4.4 恢复数据

基本思路

使用mysqlbinlog提取历史SQL操作,通过管道交给mysql命令执行

·命令格式

mysqlbinlog 日志文件│mysql -uroot -p密码

应用示例

使用编号为1的日志文件恢复数据

]# cd /var/lib/mysql

]# mysqlbinlog mysql-bin.000001 | mysql -uroot -p123456

案例5:使用binlog日志恢复数据

利用binlog恢复库表，要求如下∶

1．启用binlog日志、并修改格式为mixed

2．创建db1库和tb1表并插入3条记录

3．删除tb1表中刚插入的3条记录

4．使用binlog日志恢复删除的3条记录

[root@mysql ~]# vim /etc/my.cnf

......

binlog_format="mixed"

[root@mysql ~]# systemctl restart mysqld

[root@mysql ~]# ll /var/lib/mysql/mysql-bin.*

-rw-r----- 1 mysql mysql 177 12月 23 16:30 /var/lib/mysql/mysql-bin.000001

-rw-r----- 1 mysql mysql 154 12月 23 17:09 /var/lib/mysql/mysql-bin.000002

-rw-r----- 1 mysql mysql 38 12月 23 17:09 /var/lib/mysql/mysql-bin.index

[root@mysql ~]# systemctl restart mysqld //每次重启服务都会生成新的日志文件

[root@mysql ~]# ls /var/lib/mysql/mysql-bin.*

/var/lib/mysql/mysql-bin.000001 /var/lib/mysql/mysql-bin.000003

/var/lib/mysql/mysql-bin.000002 /var/lib/mysql/mysql-bin.index

[root@mysql ~]# mysql -uroot -p"abc321...A"

mysql> create database db1; //新建库db1

Query OK, 1 row affected (0.01 sec)

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| db1 |

| db1bak |

| mysql |

| performance_schema |

| sys |

| webdb |

+--------------------+

7 rows in set (0.00 sec)

mysql> use db1;

Database changed

mysql> create table tb1( id int(4) not null,name varchar(24));

Query OK, 0 rows affected (0.12 sec)

mysql> insert into db1.tb1 values

-> (1,"Jack");

Query OK, 1 row affected (0.15 sec)

mysql> insert into db1.tb1 values //写入数据

-> (2,"Kenthy"),

-> (3,"Bob");

Query OK, 2 rows affected (0.01 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from tb1;

+----+--------+

| id | name |

+----+--------+

| 1 | Jack |

| 2 | Kenthy |

| 3 | Bob |

+----+--------+

3 rows in set (0.02 sec)

mysql> delete from tb1;

Query OK, 3 rows affected (0.07 sec)

mysql> select * from tb1;

Empty set (0.00 sec)

mysql> exit

Bye

[root@mysql ~]# ls /var/lib/mysql/mysql-bin.*

/var/lib/mysql/mysql-bin.000001 /var/lib/mysql/mysql-bin.000003

/var/lib/mysql/mysql-bin.000002 /var/lib/mysql/mysql-bin.index

[root@mysql ~]# mysqlbinlog /var/lib/mysql/mysql-bin.000003 //查看mysql-bin.000003日志内容

......

# at 310

#201223 17:23:29 server id 1 end_log_pos 375 CRC32 0xeb6b5cae Anonymous_GTID last_committed=1 sequence_number=2

SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;

# at 375

#201223 17:23:29 server id 1 end_log_pos 501 CRC32 0x8378de25 Query thread_id=3 exec_time=0 error_code=0

use `db1`/*!*/;

SET TIMESTAMP=1608715409/*!*/;

create table tb1( id int(4) not null,name varchar(24))

/*!*/;

# at 501

#201223 17:26:25 server id 1 end_log_pos 566 CRC32 0xbe733bf7 Anonymous_GTID last_committed=2 sequence_number=3

SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;

# at 566

#201223 17:26:25 server id 1 end_log_pos 643 CRC32 0xc08d9b7f Query thread_id=3 exec_time=0 error_code=0

SET TIMESTAMP=1608715585/*!*/;

BEGIN

/*!*/;

# at 643 //起启位置为643

#201223 17:26:25 server id 1 end_log_pos 752 CRC32 0xc2cee70c Query thread_id=3 exec_time=0 error_code=0

SET TIMESTAMP=1608715585/*!*/;

insert into db1.tb1 values

(1,"Jack")

/*!*/;

# at 752

#201223 17:26:25 server id 1 end_log_pos 783 CRC32 0xf25ad0e7 Xid = 17

COMMIT/*!*/;

# at 783

#201223 17:27:25 server id 1 end_log_pos 848 CRC32 0x35f44d85 Anonymous_GTID last_committed=3 sequence_number=4

SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;

# at 848

#201223 17:27:25 server id 1 end_log_pos 925 CRC32 0xbf81905c Query thread_id=3 exec_time=0 error_code=0

SET TIMESTAMP=1608715645/*!*/;

BEGIN

/*!*/;

# at 925

#201223 17:27:25 server id 1 end_log_pos 1047 CRC32 0x494b097c Query thread_id=3 exec_time=0 error_code=0

SET TIMESTAMP=1608715645/*!*/;

insert into db1.tb1 values

(2,"Kenthy"),

(3,"Bob")

/*!*/;

# at 1047

#201223 17:27:25 server id 1 end_log_pos 1078 CRC32 0x45782a98 Xid = 18

COMMIT/*!*/;

# at 1078 //以1078为截至

#201223 17:28:48 server id 1 end_log_pos 1143 CRC32 0x92d54ab2 Anonymous_GTID last_committed=4 sequence_number=5

SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;

# at 1143

#201223 17:28:48 server id 1 end_log_pos 1220 CRC32 0xc58763f7 Query thread_id=3 exec_time=0 error_code=0

SET TIMESTAMP=1608715728/*!*/;

BEGIN

/*!*/;

# at 1220

#201223 17:28:48 server id 1 end_log_pos 1307 CRC32 0xc2402c25 Query thread_id=3 exec_time=0 error_code=0

SET TIMESTAMP=1608715728/*!*/;

delete from tb1

/*!*/;

# at 1307

#201223 17:28:48 server id 1 end_log_pos 1338 CRC32 0x9be4cbf8 Xid = 20

COMMIT/*!*/;

SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;

......

也可以通过 show binlog命令查看位置点更清晰

mysql> show binlog events in "mysql-bin.000003";

+------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+

+------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+

| mysql-bin.000003 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.17-log, Binlog ver: 4 |

| mysql-bin.000003 | 123 | Previous_gtids | 1 | 154 | |

| mysql-bin.000003 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |

| mysql-bin.000003 | 219 | Query | 1 | 310 | create database db1 |

| mysql-bin.000003 | 310 | Anonymous_Gtid | 1 | 375 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |

| mysql-bin.000003 | 375 | Query | 1 | 501 | use `db1`; create table tb1( id int(4) not null,name varchar(24)) |

| mysql-bin.000003 | 501 | Anonymous_Gtid | 1 | 566 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |

| mysql-bin.000003 | 566 | Query | 1 | 643 | BEGIN //起启位置为643 |

| mysql-bin.000003 | 643 | Query | 1 | 752 | use `db1`; insert into db1.tb1 values

(1,"Jack") |

| mysql-bin.000003 | 752 | Xid | 1 | 783 | COMMIT /* xid=17 */ |

| mysql-bin.000003 | 783 | Anonymous_Gtid | 1 | 848 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |

| mysql-bin.000003 | 848 | Query | 1 | 925 | BEGIN |

| mysql-bin.000003 | 925 | Query | 1 | 1047 | use `db1`; insert into db1.tb1 values

(2,"Kenthy"),

(3,"Bob") |

| mysql-bin.000003 | 1047 | Xid | 1 | 1078 | COMMIT /* xid=18 */ //以1078为截至 |

| mysql-bin.000003 | 1078 | Anonymous_Gtid | 1 | 1143 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |

| mysql-bin.000003 | 1143 | Query | 1 | 1220 | BEGIN |

| mysql-bin.000003 | 1220 | Query | 1 | 1307 | use `db1`; delete from tb1 |

| mysql-bin.000003 | 1307 | Xid | 1 | 1338 | COMMIT /* xid=20 */ |

| mysql-bin.000003 | 1338 | Anonymous_Gtid | 1 | 1403 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |

注意:起启和截至位置要包含需要恢复的位置段,不能等于需要恢复位置比如以上的起启位置不能为752 结束不能为1047

[root@mysql ~]# mysqlbinlog --start-position="643" --stop-position="1078" /var/lib/mysql/mysql-bin.000003|mysql -u root -p"abc321...A"

mysql: [Warning] Using a password on the command line interface can be insecure.

[root@mysql ~]# mysql -uroot -p"abc321...A"

mysql> use db1

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 db1.tb1; //恢复成功

+----+--------+

| id | name |

+----+--------+

| 1 | Jack |

| 2 | Kenthy |

| 3 | Bob |

+----+--------+

3 rows in set (0.01 sec)

weixin_39692761

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql 4 基础_MySQL数据库基础(四)grant授权、binlog日志介绍

目录一、用户授权1.1 grant授权1.2 相关命令授权库1.3 revoke撤销权限二、root密码2.1 恢复root密码(忘记密码)2.2 重置root密码三、MySQL备份3.1 备份概述物理、逻辑备份3.2 数据备份策略完全备份、增量备份四、增量备份 binlog日志4.1 binlog日志概述4.2 启用日志4.3 分析日志4.4 恢复数据一、用户授权1.1 grant授权grant...
复制链接

扫一扫