MySQL数据库基础(四)grant授权、binlog日志介绍
一、用户授权
1.1 grant授权
- grant授权︰添加用户并设置权限 命令格式
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/ref... - 库名
\*.*//所有库所有表 库名.*//一个库 库名.表名//一张表
- 用户名
授权时自定义要有标识性
存储在mysql库的user表里 - 客户端地址
%//所有主机 192.168.4.%//网段内的所有主机 192.168.4.1//1台主机 localhost//数据库服务器本机
添加用户mydba，对所有库、表有完全权限
允许从任何客户端连接，密码abc123
且有授权权限
mysql> grant all on *.*tomydba@'%'identifiedby"abc123"with grantoption;
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 18.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 toadmin@"192.168.4.%"identifiedby"123qqq...A";
mysql> grant select,insert,update,deleteondb3.*toadmin2@"localhost"identifiedby"123qqq.….A";
1.2 相关命令授权库
文章图片
- 授权库 mysql
- mysql 库记录授权信息，主要表如下:
user 表记录已有的授权用户及权限
db 表记录已有授权用户对数据库的访问权限
tables_priv 表记录已有授权用户对表的访问权限
columns_priv 表记录已有授权用户对字段的访问权限
1.3 revoke撤销权限
- 命令格式
mysql> revoke 权限列表 on 库名.表 from 用户名@"客户端地址";
mysql> revoke insert,drop ontest.*FROMsqlero2@'localhost'; Query OK,0 rows affected (0.00 sec)
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 *.*todba001@"%"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@localhostwith 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@localhostwith 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)【MySQL数据库基础(四)grant授权、binlog日志介绍】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:1Changed:0Warnings: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" andhost="localhost";
Query OK, 0 rows affected, 1 warning (0.02 sec)
Rows matched: 1Changed: 0Warnings: 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 -Rmysql: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.13Distrib 8.0.22, for Linux (x86_64)
--
-- Host: localhostDatabase:
-- ------------------------------------------------------
-- Server version8.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.13Distrib 8.0.22, for Linux (x86_64)
--
-- Host: localhostDatabase: db1
-- ------------------------------------------------------
-- Server version8.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.sql4）使用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 -ADatabase 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主从同步的必要条件
文章图片
启用日志主要操作
[root@mysql ~]# vim /etc/my.cnf
[mysqld]
...
log_bin//启用binlog日志
server_id=100//指定id值[root@mysql ~]# systemctl restart mysqld2.2 启用日志
- 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;
启用binlog日志，具体要求如下:
1) 启用binlog日志，把日志文件存放到系统的/mylog目录下，日志文件为db50
2) 手动创建3个新的日志文件
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 mysql19 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.000001db50.000002db50.000003db50.000004db50.indexmysql> show master status;
//查看当前使用的日志文件
+-------------+----------+--------------+------------------+-------------------+
| File| Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------+----------+--------------+------------------+-------------------+
| 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.000003db50.000004db50.index
mysql>cat /mylog/db50.index//查看日志索引
/mylog/db50.000003
/mylog/db50.0000044.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 事件位置作为事件截至
时间的方式只精确到秒,如果一秒内同时发生了添加和删除操作恢复会失败
4.4 恢复数据
- 基本思路
使用mysqlbinlog提取历史SQL操作,通过管道交给mysql命令执行
·命令格式
mysqlbinlog 日志文件│mysql -uroot -p密码 - 应用示例
使用编号为1的日志文件恢复数据
]# cd /var/lib/mysql
]# mysqlbinlog mysql-bin.000001 | mysql -uroot -p123456
利用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 mysql38 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: 2Duplicates: 0Warnings: 0mysql> 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 1end_log_pos 375 CRC32 0xeb6b5caeAnonymous_GTIDlast_committed=1sequence_number=2
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 375
#201223 17:23:29 server id 1end_log_pos 501 CRC32 0x8378de25Querythread_id=3exec_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 1end_log_pos 566 CRC32 0xbe733bf7Anonymous_GTIDlast_committed=2sequence_number=3
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 566
#201223 17:26:25 server id 1end_log_pos 643 CRC32 0xc08d9b7fQuerythread_id=3exec_time=0 error_code=0
SET TIMESTAMP=1608715585/*!*/;
BEGIN
/*!*/;
# at 643//起启位置为643
#201223 17:26:25 server id 1end_log_pos 752 CRC32 0xc2cee70cQuerythread_id=3exec_time=0 error_code=0
SET TIMESTAMP=1608715585/*!*/;
insert into db1.tb1 values
(1,"Jack")
/*!*/;
# at 752
#201223 17:26:25 server id 1end_log_pos 783 CRC32 0xf25ad0e7Xid = 17
COMMIT/*!*/;
# at 783
#201223 17:27:25 server id 1end_log_pos 848 CRC32 0x35f44d85Anonymous_GTIDlast_committed=3sequence_number=4
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 848
#201223 17:27:25 server id 1end_log_pos 925 CRC32 0xbf81905cQuerythread_id=3exec_time=0 error_code=0
SET TIMESTAMP=1608715645/*!*/;
BEGIN
/*!*/;
# at 925
#201223 17:27:25 server id 1end_log_pos 1047 CRC32 0x494b097cQuerythread_id=3exec_time=0error_code=0
SET TIMESTAMP=1608715645/*!*/;
insert into db1.tb1 values
(2,"Kenthy"),
(3,"Bob")
/*!*/;
# at 1047
#201223 17:27:25 server id 1end_log_pos 1078 CRC32 0x45782a98Xid = 18
COMMIT/*!*/;
# at 1078//以1078为截至
#201223 17:28:48 server id 1end_log_pos 1143 CRC32 0x92d54ab2Anonymous_GTIDlast_committed=4sequence_number=5
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1143
#201223 17:28:48 server id 1end_log_pos 1220 CRC32 0xc58763f7Querythread_id=3exec_time=0error_code=0
SET TIMESTAMP=1608715728/*!*/;
BEGIN
/*!*/;
# at 1220
#201223 17:28:48 server id 1end_log_pos 1307 CRC32 0xc2402c25Querythread_id=3exec_time=0error_code=0
SET TIMESTAMP=1608715728/*!*/;
delete from tb1
/*!*/;
# at 1307
#201223 17:28:48 server id 1end_log_pos 1338 CRC32 0x9be4cbf8Xid = 20
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
......也可以通过 show binlog命令查看位置点 更清晰
mysql> show binlog events in "mysql-bin.000003";
+------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name| Pos| Event_type| Server_id | End_log_pos | Info|
+------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+
| 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 -ADatabase changed
mysql> select * from db1.tb1;
//恢复成功
+----+--------+
| id | name|
+----+--------+
|1 | Jack|
|2 | Kenthy |
|3 | Bob|
+----+--------+
3 rows in set (0.01 sec)推荐阅读
- Docker应用:容器间通信与Mariadb数据库主从复制
- Python基础|Python基础 - 练习1
- Java|Java基础——数组
- 数据库设计与优化
- Java基础-高级特性-枚举实现状态机
- 数据库总结语句
- 营养基础学20180331(课间随笔）??
- iOS面试题--基础
- HTML基础--基本概念--跟着李南江学编程
- py连接mysql