用户授权
用户授权
grant授权
- 授权：添加用户并设置权限
- 作用：在数据库服务器上添加用户，设置访问权限及登录密码，给客户端连
接使用 - 命令格式：
mysql >grant 权限列表 on 库名 to 用户名@"客户端地址" identified by
"密码" with grant option ; #with grant option ;让新添加的用户也有授权的权限，可选项
mysql> grant all on db4.* to yaya@"%" identified by "123qqq...A";
Query OK, 0 rows affected, 1 warning (0.01 sec)
[root@host52 ~]# mysql -h192.168.4.51 -uyaya -p123qqq...A
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db4 |
+--------------------+
2 rows in set (0.01 sec)
[root@host53 ~]# mysql -h192.168.4.51 -uyaya -p123qqq...A
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db4 |
+--------------------+
2 rows in set (0.01 sec)
在客户端host52（192.168.4.52）上面做测试创建表
mysql> create table db4.t1(id int);
Query OK, 0 rows affected (0.07 sec)
mysql> insert into db4.t1 values(100);
Query OK, 1 row affected (0.02 sec)
mysql> desc db4.t1;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
此时，在host51上面也可以看到新建的表
mysql> use db4
Database changed
mysql> show tables;
+---------------+
| Tables_in_db4 |
+---------------+
| student |
| t1 |
+---------------+
2 rows in set (0.00 sec)
mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
但是此时在host51上面不能用yaya用户登录数据库（5.0版本以下登不上 ，此版本是5.7，可以登陆上）
mysql> show variables like "%version%";
+-------------------------+------------------------------+
| Variable_name | Value |
+-------------------------+------------------------------+
| innodb_version | 5.7.17 |
| protocol_version | 10 |
| slave_type_conversions | |
| tls_version | TLSv1,TLSv1.1 |
| version | 5.7.17 |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
+-------------------------+------------------------------+
8 rows in set (0.01 sec)
mysql> grant all on db4.* to yaya@"localhost" identified by "123qqq...A" ;
Query OK, 0 rows affected, 1 warning (0.00 sec)
[root@host51 ~]# mysql -uyaya -p123qqq...A
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db4 |
+--------------------+
2 rows in set (0.00 sec)- 权限列表
all //所有权限
usage //无权限
select,update，insert //个别权限
select，update（字段1，……，字段N） //指定字段
- 库名
*.* 代表所有库所有表
库名.* 代表一个库下的所有表
库名.表名 代表一个库下的一张表
- 用户名
授权时自定义要有标识性
存储在mysql库里的user表中
- 客户端地址
% //所有主机
192.168.4.% //网段内所有主机
192.168.4.1 //1台主机
localhost //数据库服务器本机
应用示例：
1.添加admin用户，允许从192.168.4.0/24网段连接，对db3库的user表有查询权限，密码为123qqq...A
2.添加admin2用户，允许从本机连接，允许对db3库的所有表有查询/更新/插入/删除记录权限，密码为123qqq...A
mysql> grant select on db3.user to admin@"192.168.4.%" identified by "123qqq...A";
Query OK, 0 rows affected, 1 warning (0.00 sec)
在host52、53上面查看是否能连接数据库，以及权限
[root@host52 ~]# mysql -h192.168.4.51 -uadmin -p123qqq...A
mysql> show grants;
+-------------------------------------------------------+
| Grants for admin@192.168.4.% |
+-------------------------------------------------------+
| GRANT USAGE ON *.* TO 'admin'@'192.168.4.%' |
| GRANT SELECT ON `db3`.`user` TO 'admin'@'192.168.4.%' |
+-------------------------------------------------------+
2 rows in set (0.00 sec)
[root@host53 ~]# mysql -h192.168.4.51 -uadmin -p123qqq...A
mysql> use db3;
mysql> show tables;
+---------------+
| Tables_in_db3 |
+---------------+
| user |
+---------------+
1 row in set (0.00 sec)
mysql> insert into db3.user(name) values("haha");
ERROR 1142 (42000): INSERT command denied to user 'admin'@'192.168.4.53' for table 'user'
mysql> grant select ,insert ,update ,delete on db3.* to admin2@"localhost" identified by"123qqq...A";
Query OK, 0 rows affected, 1 warning (0.00 sec)
在host51、52、53上面查看是否能连接数据库
[root@host52 ~]# mysql -h192.168.4.51 -uadmin2 -p123qqq...A
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'admin2'@'192.168.4.52' (using password: YES)
[root@host53 ~]# mysql -h192.168.4.51 -uadmin2 -p123qqq...A
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'admin2'@'192.168.4.53' (using password: YES)
[root@host51 ~]# mysql -uadmin2 -p123qqq...A
mysql> select user();
+------------------+
| user() |
+------------------+
| admin2@localhost |
+------------------+
1 row in set (0.00 sec)- 相关命令：登录用户使用
| 命令 | 作用 |
| select user(); | 显示登录用户名及客户端地址 |
| show grants; | 用户显示自身访问权限 |
| show grants for 用户名@"客户端地址"; | 管理员查看已有授权用户权限 |
| set password=password("密码"); | 授权用户连接后修改连接密码 |
| set password for 用户名@"客户端地址" =password("密码"); | 管理员重置授权用户连接密码 |
| drop user 用户名@"客户端地址"; | 删除授权用户（必须有管理员权限） |
mysql> grant all on db4.* to yaya@"localhost" identified by "123qqq...A" ; #授权本机也可以登录yaya用户
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> grant all on db5.* to jim@"192.168.4.53" identified
by "123qqq...A"; #授权jim用户在192.168.4.53主机上可以登录
数据库
Query OK, 0 rows affected, 1 warning (0.00 sec)
[root@host52 ~]# mysql -h192.168.4.51 -ujim -p123qqq...A
mysql: [Warning] Using a password on the command line interface
can be insecure. #在host52上jim用户无法登陆
ERROR 1045 (28000): Access denied for user 'jim'@'192.168.4.52'
(using password: YES)
[root@host53 ~]# mysql -h192.168.4.51 -ujim -p123qqq...A
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db5 |
+--------------------+
2 rows in set (0.00 sec)
mysql> grant all on *.* to admin@"localhost" identified by
"123qqq...A";
Query OK, 0 rows affected, 1 warning (0.00 sec)
[root@host52 ~]# mysql -h192.168.4.51 -uadmin -p123qqq...A
mysql: [Warning] Using a password on the command line interface
can be insecure.
ERROR 1045 (28000): Access denied for user 'admin'@'192.168.4.52'
(using password: YES)
[root@host53 ~]# mysql -h192.168.4.51 -uadmin -p123qqq...A
mysql: [Warning] Using a password on the command line interface
can be insecure.
ERROR 1045 (28000): Access denied for user 'admin'@'192.168.4.53'
(using password: YES)
[root@host51 ~]# mysql -uadmin -p123qqq...A
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| db2 |
| db3 |
| db4 |
| db5 |
| mysql |
| performance_schema |
| sys |
+--------------------+
9 rows in set (0.01 sec)
mysql> select host,user from mysql.user; #查看数据库的授权
用户
+--------------+-----------+
| host | user |
+--------------+-----------+
| % | yaya |
| 192.168.4.53 | jim |
| localhost | admin |
| localhost | mysql.sys |
| localhost | root |
| localhost | yaya |
+--------------+-----------+
6 rows in set (0.01 sec)
相关命令：
select user(); //查看当前用户
mysql> select user(); //以host53为例
+-------------------+
| user() |
+-------------------+
| yaya@192.168.4.53 |
+-------------------+
1 row in set (0.00 sec)
mysql> show variables like "%hostname%"; //查看当前主机名
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| hostname | host51 |
+---------------+--------+
1 row in set (0.01 sec)
mysql> select @@hostname;
+------------+
| @@hostname |
+------------+
| host51 |
+------------+
1 row in set (0.00 sec)
mysql> show grants;
+-----------------------------------------------+
| Grants for yaya@% |
+-----------------------------------------------+
| GRANT USAGE ON *.* TO 'yaya'@'%' |
| GRANT ALL PRIVILEGES ON `db4`.* TO 'yaya'@'%' |
+-----------------------------------------------+
2 rows in set (0.00 sec)
在host51主机上面，管理员查看已授权用户的权限
mysql> show grants for jim@"192.168.4.53" ;
+---------------------------------------------------------+
| Grants for jim@192.168.4.53 |
+---------------------------------------------------------+
| GRANT USAGE ON *.* TO 'jim'@'192.168.4.53' |
| GRANT ALL PRIVILEGES ON `db5`.* TO 'jim'@'192.168.4.53' |
+---------------------------------------------------------+
2 rows in set (0.00 sec)
授权用户可以登录后修改自己的密码，以host53为例
mysql> set password=password("A...qqq321");
Query OK, 0 rows affected, 1 warning (0.00 sec)
[root@host53 ~]# mysql -h192.168.4.51 -uyaya -p123qqq...A
mysql: [Warning] Using a password on the command line interface
can be insecure.
ERROR 1045 (28000): Access denied for user 'yaya'@'192.168.4.53'
(using password: YES)
[root@host53 ~]# mysql -h192.168.4.51 -uyaya -pA...qqq321
管理员修改授权用户的密码：
mysql> select user ,host from mysql.user;
+-----------+--------------+
| user | host |
+-----------+--------------+
| yaya | % |
| jim | 192.168.4.53 |
| admin | localhost |
| mysql.sys | localhost |
| root | localhost |
| yaya | localhost |
+-----------+--------------+
6 rows in set (0.00 sec)
mysql> set password for yaya@"%"=password("123qqq...A");
Query OK, 0 rows affected, 1 warning (0.00 sec)
[root@host53 ~]# mysql -h192.168.4.51 -uyaya -pA...qqq321
删除授权
mysql> drop user admin@localhost;
Query OK, 0 rows affected (0.00 sec)
mysql> drop user jim@192.168.4.53;
Query OK, 0 rows affected (0.00 sec)
mysql> drop user yaya@localhost;
Query OK, 0 rows affected (0.00 sec)
mysql> drop user yaya@"%";
Query OK, 0 rows affected (0.00 sec)
mysql> select user ,host from mysql.user;
+-----------+-----------+
| user | host |
+-----------+-----------+
| mysql.sys | localhost |
| root | localhost |
+-----------+-----------+
2 rows in set (0.00 sec)测试with grant option：
mysql> grant all on *.* to root@"192.168.4.52" identified by "123qqq...A" with grant option;
Query OK, 0 rows affected, 1 warning (0.00 sec)
用192.168.4.52测试，查看其权限
[root@host52 ~]# mysql -h192.168.4.51 -uroot -p123qqq...A
mysql> show grants;
+------------------------------------------------------------------------+
| Grants for root@192.168.4.52 |
+------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.4.52' WITH GRANT OPTION |
+------------------------------------------------------------------------+
1 row in set (0.01 sec)
在host52主机上授权tian用户对tian库下可以进行任何操作
mysql> grant all on tian.* to tian@"localhost" identified by "123qqq...A";
Query OK, 0 rows affected, 1 warning (0.00 sec)
[root@host51 ~]# mysql -utian -p123qqq...A
mysql> show grants;
+--------------------------------------------------------+
| Grants for tian@localhost |
+--------------------------------------------------------+
| GRANT USAGE ON *.* TO 'tian'@'localhost' |
| GRANT ALL PRIVILEGES ON `tian`.* TO 'tian'@'localhost' |
+--------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)
mysql> create database tian; #如果没有tian库，可以创建
Query OK, 1 row affected (0.00 sec)
mysql> create database aaa; #该用户只能对tian库下进行操作
ERROR 1044 (42000): Access denied for user 'tian'@'localhost' to database 'aaa'授权库
- mysql库记录授权信息，主要表如下：
user表 记录已有的授权用户的权限
db表 记录已有授权用户对数据库的访问权限
tables_priv表 记录已有授权用户对表的访问权限
columns_priv表 记录已有授权用户对字段的访问权限
查看表记录可以获取用户权限，也可以通过更新记录，修改用户权限
查看本机上已有的授权用户，以host51为例
mysql> select host ,user from mysql.user;
+--------------+-----------+
| host | user |
+--------------+-----------+
| 192.168.4.% | admin |
| 192.168.4.52 | root |
| localhost | admin2 |
| localhost | mysql.sys |
| localhost | root |
| localhost | tian |
+--------------+-----------+
6 rows in set (0.00 sec)
mysql> show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)
#GRANT PROXY可以将自己的权限复制给其他用户 GRANT ALL PRIVILEGES，它的权限记录在mysql库下的user表
mysql> select * from mysql.user where host="localhost" and user="root" \G #查看root用户的权限
*************************** 1. row ***************************
Host: localhost
User: root
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: Y
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
Event_priv: Y
Trigger_priv: Y
Create_tablespace_priv: Y
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
password_expired: N
password_last_changed: 2020-02-13 17:06:50
password_lifetime: NULL
account_locked: N
1 row in set (0.06 sec)
db表：
mysql> select host ,user ,db from mysql.db;
+-----------+-----------+------+
| host | user | db |
+-----------+-----------+------+
| localhost | admin2 | db3 |
| localhost | mysql.sys | sys |
| localhost | tian | tian |
+-----------+-----------+------+
3 rows in set (0.00 sec)
mysql> show grants for tian@"localhost";
+--------------------------------------------------------+
| Grants for tian@localhost |
+--------------------------------------------------------+
| GRANT USAGE ON *.* TO 'tian'@'localhost' |
| GRANT ALL PRIVILEGES ON `tian`.* TO 'tian'@'localhost' |
+--------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> select * from mysql.db where db="tian" \G
*************************** 1. row ***************************
Host: localhost
Db: tian
User: tian
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Grant_priv: N
References_priv: Y
Index_priv: Y
Alter_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Execute_priv: Y
Event_priv: Y
Trigger_priv: Y
1 row in set (0.00 sec)
mysql> update mysql.db set insert_priv="N" ,delete_priv="N" where host="localhost" and user="tian" and db="tian";
#修改授权，让tian用户取消修改和删除权限
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from mysql.db where db="tian" \G #查看权限
*************************** 1. row ***************************
Host: localhost
Db: tian
User: tian
Select_priv: Y
Insert_priv: N
Update_priv: Y
Delete_priv: N
Create_priv: Y
Drop_priv: Y
Grant_priv: N
References_priv: Y
Index_priv: Y
Alter_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Execute_priv: Y
Event_priv: Y
Trigger_priv: Y
1 row in set (0.00 sec)
mysql> show grants for tian@localhost; #此事再次查看tian用户的权限，发现依旧没有变化
+--------------------------------------------------------+
| Grants for tian@localhost |
+--------------------------------------------------------+
| GRANT USAGE ON *.* TO 'tian'@'localhost' |
| GRANT ALL PRIVILEGES ON `tian`.* TO 'tian'@'localhost' |
+--------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> flush privileges; #刷新
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for tian@localhost; #再次查看，发现此时权限发生了变化
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for tian@localhost |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'tian'@'localhost' |
| GRANT SELECT, UPDATE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `tian`.* TO 'tian'@'localhost' |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.01 sec)
tables_priv表:
mysql> select * from mysql.tables_priv \G
*************************** 1. row ***************************
Host: localhost
Db: sys
User: mysql.sys
Table_name: sys_config #授权时间
Grantor: root@localhost #授权用户
Timestamp: 2020-02-13 17:01:31
Table_priv: Select #对表的访问权限
Column_priv:
*************************** 2. row ***************************
Host: 192.168.4.%
Db: db3
User: admin
Table_name: user
Grantor: root@localhost
Timestamp: 0000-00-00 00:00:00
Table_priv: Select
Column_priv:
2 rows in set (0.00 sec)
mysql> show grants for admin@"192.168.4.%";
+-------------------------------------------------------+
| Grants for admin@192.168.4.% |
+-------------------------------------------------------+
| GRANT USAGE ON *.* TO 'admin'@'192.168.4.%' |
| GRANT SELECT ON `db3`.`user` TO 'admin'@'192.168.4.%' |
+-------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> update mysql.tables_priv set table_priv="select,update,insert" where host="192.168.4.%" and user="admin";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for admin@"192.168.4.%";
+-----------------------------------------------------------------------+
| Grants for admin@192.168.4.% |
+-----------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'admin'@'192.168.4.%' |
| GRANT SELECT, INSERT, UPDATE ON `db3`.`user` TO 'admin'@'192.168.4.%' |
+-----------------------------------------------------------------------+
2 rows in set (0.00 sec)
columns_priv表：
mysql> select * from mysql.columns_priv;
Empty set (0.00 sec)
mysql> grant select , update(name) on db3.user to haha@"%" identified by "123qqq...A"; #授权haha用户对数据库有查看权限，对name字段有更新权限
Query OK, 0 rows affected, 1 warning (0.05 sec)
mysql> select * from mysql.columns_priv;
+------+-----+------+------------+-------------+---------------------+-------------+
| Host | Db | User | Table_name | Column_name | Timestamp | Column_priv |
+------+-----+------+------------+-------------+---------------------+-------------+
| % | db3 | haha | user | name | 0000-00-00 00:00:00 | Update |
+------+-----+------+------------+-------------+---------------------+-------------+
1 row in set (0.00 sec)撤销权限
命令格式：revoke 权限列表 on 库名.表 from 用户名@"客户端地址"
mysql> select user ,host from mysql.user;
+-----------+--------------+
| user | host |
+-----------+--------------+
| haha | % |
| webadmin | % |
| admin | 192.168.4.% |
| root | 192.168.4.52 |
| admin2 | localhost |
| mysql.sys | localhost |
| root | localhost |
| tian | localhost |
+-----------+--------------+
8 rows in set (0.00 sec)
mysql> show grants for webadmin@"%";
+-----------------------------------------------------------------------+
| Grants for webadmin@% |
+-----------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'webadmin'@'%' |
| GRANT ALL PRIVILEGES ON `bbsdb`.* TO 'webadmin'@'%' WITH GRANT OPTION |
+-----------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> revoke grant option on bbsdb.* from webadmin@"%";
Query OK, 0 rows affected (0.01 sec)
mysql> show grants for webadmin@"%";
+-----------------------------------------------------+
| Grants for webadmin@% |
+-----------------------------------------------------+
| GRANT USAGE ON *.* TO 'webadmin'@'%' |
| GRANT ALL PRIVILEGES ON `bbsdb`.* TO 'webadmin'@'%' |
+-----------------------------------------------------+
2 rows in set (0.00 sec)
mysql> revoke update ,insert on bbsdb.* from webadmin@"%";
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for webadmin@"%";
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for webadmin@% |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'webadmin'@'%' |
| GRANT SELECT, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `bbsdb`.* TO 'webadmin'@'%' |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)root密码
- root密码忘了怎么办？
- 停止MySQL服务程序
- 跳过授权表启动MySQL服务程序
- 修改root密码
- 以正常方式重新启动MySQL服务程序
（只适合线下修改）
[root@host51 ~]# systemctl stop mysqld
[root@host51 ~]# vim /etc/my.cnf
[mysqld]
#skip-grant-tables
secure_file_priv=/myload
#validate_password_policy=0
#validate_password_length=6
[root@host51 ~]# systemctl start mysqld
[root@host51 ~]# mysql
mysql> update mysql.user set
authentication_string=password("123qqq...A") where user="root" and host="localhost";
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
[root@host51 ~]# vim /etc/my.cnf
[mysqld]
#skip-grant-tables
secure_file_priv=/myload
validate_password_policy=0
validate_password_length=6
[root@host51 ~]# systemctl restart mysqld
[root@host51 ~]# mysql -uroot -p123qqq...A
mysql> alter user root@"localhost" identified by "123456";
Query OK, 0 rows affected (0.01 sec)
[root@host51 ~]# mysql -uroot -p123456修改管理员root密码的其他方法
方法一：以root用户登录mysql后，使用set password指令设置，这个与新安装MySQL-server后首次修改密码时的要求方式相同，平时也可以使用
mysql> SET PASSWORD FOR root@localhost=PASSWORD('123456');
Query OK, 0 rows affected, 1 warning (0.00 sec)方法二：以root用户登录mysql后，使用grant授权工具的设置
mysql> GRANT all ON *.* TO root@localhost IDENTIFIED BY '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)方法三：以root登录后，使用update更新相应的表记录，这种方法与恢复密码时的操作相同
mysql> UPDATE mysql.user SET authentication_string=PASSWORD('123456')
-> WHERE user='root' AND host='localhost'; //重设root的密码
Query OK, 0 rows affected, 1 warning (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('123456');
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 '123456';
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement- 重置root密码
在shell命令行修改登录密码，需要验证旧密码
[root@host51 ~]# mysqladmin -uroot -p"123456" password "tiantian"
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. #此处警告，最好使用ssl服务更安全
[root@host51 ~]# mysql -uroot -p"tiantian" #也可以-p之后回车交互式输入密码
完全备份
备份概述
数据备份方式
- 物理备份：冷备：cp、tar
- 逻辑备份：根据备份时已有的数据生成的对应的SQL命令，保存到指定的文件里
也可以分为冷备和热备
- 冷备：在进行备份时不允许做其他的任何操作
- 热备：在服务器执行时可以同时进行备份
物理备份及恢复
备份操作
- 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/mysql
- chown -R mysql:mysql /var/lib/mysql
[root@host51 ~]# cp -r /var/lib/mysql /root
[root@host51 ~]# ls /root/mysql
auto.cnf db1 ib_buffer_pool mysql public_key.pem
ca-key.pem db2 ibdata1 mysql.sock server-cert.pem
ca.pem db3 ib_logfile0 mysql.sock.lock server-key.pem
client-cert.pem db4 ib_logfile1 performance_schema sys
client-key.pem db5 ibtmp1 private_key.pem tian
[root@host51 ~]# scp -r /root/mysql root@192.168.4.53:/root
[root@host53 ~]# systemctl stop mysqld
[root@host53 ~]# ls /var/lib/mysql
auto.cnf client-cert.pem ibdata1 mysql public_key.pem sys
ca-key.pem client-key.pem ib_logfile0 performance_schema server-cert.pem
ca.pem ib_buffer_pool ib_logfile1 private_key.pem server-key.pem
[root@host53 ~]# rm -rf /var/lib/mysql
[root@host53 ~]# ls
mysql
[root@host53 ~]# cp -r /root/mysql /var/lib
[root@host53 ~]# chown -R mysql:mysql /var/lib/mysql
[root@host53 ~]# ls /var/lib/mysql
auto.cnf db1 ib_buffer_pool mysql server-cert.pem
ca-key.pem db2 ibdata1 mysql.sock.lock server-key.pem
ca.pem db3 ib_logfile0 performance_schema sys
client-cert.pem db4 ib_logfile1 private_key.pem tian
client-key.pem db5 ibtmp1 public_key.pem
[root@host53 ~]# systemctl start mysqld
[root@host53 ~]# mysql -uroot -p123456逻辑备份策略
- 完全备份：备份所有数据
- 增量备份：备份上次备份后所有新产生的新数据
- 差异备份：备份完全备份后所有新产生的数据
完全备份，完全备份+差异备份 ，完全备份+增量备份的区别
完全备份
假设，每天都把当天的内容全部备份一遍
完全备份+增量备份
18:00 user 文件 备份
1 完全 10 1.sql 10
2 增量 18:00 5 2.sql 5
3 增量 18:00 10 3.sql 10
4 增量 18:00 20 4.sql 20
5 增量 18:00 1 5.sql 1
6 增量 18:00 10 6.sql 10
7 增量 18:00 5 7.sql 5
完全备份+差异备份18:00 user 文件 备份
1 完全 10 1.sql 10
2 差异 18:00 5 2.sql 5
3 差异 18:00 10 3.sql 15
4 差异 18:00 20 4.sql 35
5 差异 18:00 1 5.sql 36
6 差异 18:00 10 6.sql 46
7 差异 18:00 5 7.sql 51
完全备份及恢复
- 完全备份：mysqldump -uroot -p密码 库名 > 目录/xxx.sql
- 完全恢复：mysql -uroot -p密码 [库名] < 目录/xxx.sql
- 备份时库名的表示方式：
--all-databases 或 -A //所有库
数据库名 //单个库
数据库名 表名 //单张表
-B 数据库1 数据库2 //多个库
注意事项：无论是备份还是恢复，都要验证用户权限！！！
应用示例：
将所有库备份为allbak.sql文件
将db3库备份为db3.user文件
[root@host51 ~]# mkdir /mybak
[root@host51 ~]# mysqldump -uroot -p"123456" -A > /mybak/fulldata.sql
[root@host51 ~]# wc -l /mybak/fulldata.sql
1370 /mybak/fulldata.sql
[root@host51 ~]# scp /mybak/fulldata.sql root@192.168.4.52:/opt
[root@host52 ~]# ls /opt
fulldata.sql
[root@host52 ~]# mysql -uroot -p123qqq...A < /opt/fulldata.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@host52 ~]# mysql -uroot -p123qqq...A
[root@host52 ~]# ls /var/lib/mysql
auto.cnf db1 ib_buffer_pool mysql public_key.pem
ca-key.pem db2 ibdata1 mysql.sock server-cert.pem
ca.pem db3 ib_logfile0 mysql.sock.lock server-key.pem
client-cert.pem db4 ib_logfile1 performance_schema sys
client-key.pem db5 ibtmp1 private_key.pem tian
[root@host52 ~]# systemctl stop mysqld
[root@host52 ~]# systemctl start mysqld
[root@host52 ~]# mysql -uroot -p123qqq...A
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[root@host52 ~]# mysql -uroot -p123456
[root@host51 ~]# mysqldump -uroot -p"123456" db1 > /mybak/db1.sql
[root@host51 ~]# mysqldump -uroot -p"123456" -B db2 db3 db5 > /mybak/thdb.sql
[root@host51 ~]# mysqldump -uroot -p"123456" -B db3 user > /mybak/db3_user.sql
[root@host51 ~]# ls /mybak/
db1.sql db3_user.sql fulldata.sql thdb.sql
[root@host52 ~]# mysql -uroot -p123456
mysql> drop database db1;
[root@host51 ~]# scp /mybak/db1.sql root@192.168.4.52:/root
[root@host52 ~]# ls
db1.sql
[root@host52 ~]# mysql -uroot -p123456 db1 < /root/db1.sql #一定要在数据库内创建新的db1库
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1049 (42000): Unknown database 'db1'
[root@host52 ~]# mysql -uroot -p123456
mysql> create database db1;
[root@host52 ~]# mysql -uroot -p123456 db1 < /root/db1.sql
*********************************************************************
[root@host52 ~]# mysql -uroot -p123456
mysql> use db3;
mysql> show tables;
+---------------+
| Tables_in_db3 |
+---------------+
| user |
+---------------+
1 row in set (0.00 sec)
mysql> drop table user;
mysql> show tables;
Empty set (0.00 sec
[root@host51 ~]# scp /mybak/db3_user.sql root@192.168.4.52:/root
[root@host52 ~]# ls
db1.sql
db3_user.sql
[root@host52 ~]# mysql -uroot -p123456 db3 < /root/db3_user.sql
[root@host52 ~]# mysql -uroot -p123456
mysql> use db3;
mysql> show tables;
+---------------+
| Tables_in_db3 |
+---------------+
| user |
+---------------+
1 row in set (0.01 sec)
mysql> select count(*) from db3.user;
+----------+
| count(*) |
+----------+
| 26 |
+----------+
1 row in set (0.00 sec)
备份单个表，单个库时要指定库名，多个库，所有库时不需要指定库名
如果导入的库名与现在数据库的库名重复，那么现在数据库该库的数据会被覆盖
完全备份的缺点：如果一直每天用完全备份，在执行第一天和第二天的备份之间，如果不小心删除了数据，只能找前一天的数据进行恢复，但完全备份只备份了前一天的数据，新产生的数据恢复不了增量备份
binlog日志
- 什么是binlog日志？
- 也称作二进制日志
- MySQL服务日志文件的一种
- 记录除查询（show，select，desc）之外的所有SQL命令
- 可用于数据备份与恢复
- 配置mysql主从同步的必要条件
- 启用日志
| 配置项 | 用途 |
| server_id=数字 | 指定id值（1-255） |
| log_bin=[目录名/文件名] | 启用binlog日志 |
| max_binlog_size=数值m | 指定日志文件容量，默认1G |
修改主配置文件
[root@host51 ~]# vim /etc/my.cnf #修改配置文件，可以将17行的注释打开，也可以在[mysql]下面添加log_bin以及server_id号
4 [mysqld]
5 #skip-grant-tables
6 secure_file_priv=/myload
7 validate_password_policy=0
8 validate_password_length=6
9 server_id=51
17 log_bin
[root@host51 ~]# ls /var/lib/mysql #修改完之后查看主目录，没有任何变化
auto.cnf db1 ib_buffer_pool mysql public_key.pem
ca-key.pem db2 ibdata1 mysql.sock server-cert.pem
ca.pem db3 ib_logfile0 mysql.sock.lock server-key.pem
client-cert.pem db4 ib_logfile1 performance_schema sys
client-key.pem db5 ibtmp1 private_key.pem tian
[root@host51 ~]# systemctl restart mysqld #重新起服务
[root@host51 ~]# ls /var/lib/mysql #此时，目录中多了两个以host51开头的二进制文件
auto.cnf db1 host51-bin.000001 ib_logfile1 performance_schema sys
ca-key.pem db2 host51-bin.index ibtmp1 private_key.pem tian
ca.pem db3 ib_buffer_pool mysql public_key.pem
client-cert.pem db4 ibdata1 mysql.sock server-cert.pem
client-key.pem db5 ib_logfile0 mysql.sock.lock server-key.pem
#host51-bin.000001为第一个日志文件 host51-bin.index为索引文件
[root@host51 ~]# cd /var/lib/mysql
[root@host51 mysql]# cat host51-bin.index
./host51-bin.000001
[root@host51 ~]# mysql -uroot -p123456
mysql> show master status; #查看日志文件大小，初始为154
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| host51-bin.000001 | 154 | | | |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)
mysql> show databases; #查看库
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| db2 |
| db3 |
| db4 |
| db5 |
| mysql |
| performance_schema |
| sys |
| tian |
+--------------------+
10 rows in set (0.00 sec)
mysql> show master status; #查询命令不会记录在日志文件
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| host51-bin.000001 | 154 | | | |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| host51-bin.000001 | 154 | | | |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> insert into db3.user(name) values("haha"); #插入数据
Query OK, 1 row affected (0.06 sec)
mysql> show master status; #此时再次查看日志，日志文件大小发生了改变
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| host51-bin.000001 | 431 | | | |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)- binlog相关文件
主机名-bin.index 索引文件
主机名-bin.000001 第一个二进制文件
主机名-bin.000002 第二个二进制文件
- 手动生成新的日志文件
- 只有当文件大于1G才会自动生成，默认数据都会写在新生成的日志文件中，初始的日志文件大小为15mysql> flush logs;
第一种方法：进入数据库，输入flush logs命令
ysql> flush logs; #进入数据库，输入flush logs命令
Query OK, 0 rows affected (0.01 sec)
mysql> show master status; #查看，此时生成了一个新的日志文件
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| host51-bin.000002 | 154 | | | |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
[root@host51 lib]# ls /var/lib/mysql #再次查看数据库主目录，此时多出了一个002的新的二进制文件
auto.cnf db1 host51-bin.000001 ib_logfile0 mysql.sock.lock server-key.pem
ca-key.pem db2 host51-bin.000002 ib_logfile1 performance_schema sys
ca.pem db3 host51-bin.index ibtmp1 private_key.pem tian
client-cert.pem db4 ib_buffer_pool mysql public_key.pem
client-key.pem db5 ibdata1 mysql.sock server-cert.pem第二种方法：
[root@host51 ~]# mysql -uroot -p123456 -e"flush logs"
[root@host51 ~]# ls /var/lib/mysql
auto.cnf db4 ib_logfile0 public_key.pem
ca-key.pem db5 ib_logfile1 server-cert.pem
ca.pem host51-bin.000001 ibtmp1 server-key.pem
client-cert.pem host51-bin.000002 mysql sys
client-key.pem host51-bin.000003 mysql.sock tian
db1 host51-bin.index mysql.sock.lock
db2 ib_buffer_pool performance_schema
db3 ibdata1 private_key.pem第三种方法：
[root@host51 ~]# mysqldump -uroot -p123456 --flush-logs db1 > /mybak/db1.sql
[root@host51 ~]# ls /var/lib/mysql
auto.cnf db4 ibdata1 private_key.pem
ca-key.pem db5 ib_logfile0 public_key.pem
ca.pem host51-bin.000001 ib_logfile1 server-cert.pem
client-cert.pem host51-bin.000002 ibtmp1 server-key.pem
client-key.pem host51-bin.000003 mysql sys
db1 host51-bin.000004 mysql.sock tian
db2 host51-bin.index mysql.sock.lock
db3 ib_buffer_pool performance_schema
[root@host51 ~]# mysqldump -uroot -p123456 --flush-logs -B db2 db3 > /mybak/db1.sql #导出多个数据库，会自动生成相对应的新的日志文件
[root@host51 ~]# ls /var/lib/mysql
auto.cnf db4 host51-bin.index mysql.sock.lock
ca-key.pem db5 ib_buffer_pool performance_schema
ca.pem host51-bin.000001 ibdata1 private_key.pem
client-cert.pem host51-bin.000002 ib_logfile0 public_key.pem
client-key.pem host51-bin.000003 ib_logfile1 server-cert.pem
db1 host51-bin.000004 ibtmp1 server-key.pem
db2 host51-bin.000005 mysql sys
db3 host51-bin.000006 mysql.sock tian第四种方法：
[root@host51 ~]# systemctl restart mysqld
[root@host51 ~]# ls /var/lib/mysql
auto.cnf db5 ib_buffer_pool private_key.pem
ca-key.pem host51-bin.000001 ibdata1 public_key.pem
ca.pem host51-bin.000002 ib_logfile0 server-cert.pem
client-cert.pem host51-bin.000003 ib_logfile1 server-key.pem
client-key.pem host51-bin.000004 ibtmp1 sys
db1 host51-bin.000005 mysql tian
db2 host51-bin.000006 mysql.sock
db3 host51-bin.000007 mysql.sock.lock
db4 host51-bin.index performance_schema清理日志
删除指定编号之前的binlog日志文件
Mysql > purge master logs to "binlog文件名";
删除所有binlog日志，重新建日志
Mysql > reset master;
[root@host51 ~]# ls /var/lib/mysql
auto.cnf db5 ib_buffer_pool private_key.pem
ca-key.pem host51-bin.000001 ibdata1 public_key.pem
ca.pem host51-bin.000002 ib_logfile0 server-cert.pem
client-cert.pem host51-bin.000003 ib_logfile1 server-key.pem
client-key.pem host51-bin.000004 ibtmp1 sys
db1 host51-bin.000005 mysql tian
db2 host51-bin.000006 mysql.sock
db3 host51-bin.000007 mysql.sock.lock
db4 host51-bin.index performance_schema
[root@host51 ~]# mysql -uroot -p123456
mysql> purge master logs to "host51-bin.000003"; #删除编号是000003之前的binlog日志
[root@host51 ~]# ls /var/lib/mysql
auto.cnf db4 ib_buffer_pool performance_schema
ca-key.pem db5 ibdata1 private_key.pem
ca.pem host51-bin.000003 ib_logfile0 public_key.pem
client-cert.pem host51-bin.000004 ib_logfile1 server-cert.pem
client-key.pem host51-bin.000005 ibtmp1 server-key.pem
db1 host51-bin.000006 mysql sys
db2 host51-bin.000007 mysql.sock tian
db3 host51-bin.index mysql.sock.lock
mysql> reset master; #删除所有binlog日志，重建新的日志
Query OK, 0 rows affected (0.01 sec)
[root@host51 ~]# ls /var/lib/mysql
auto.cnf db1 host51-bin.000001 ib_logfile1 performance_schema sys
ca-key.pem db2 host51-bin.index ibtmp1 private_key.pem tian
ca.pem db3 ib_buffer_pool mysql public_key.pem
client-cert.pem db4 ibdata1 mysql.sock server-cert.pem
client-key.pem db5 ib_logfile0 mysql.sock.lock server-key.pem自定义日志目录和名称
[root@host51 ~]# vim /etc/my.cnf
17 log_bin=/mylog/tian
[root@host51 ~]# mkdir /mylog
[root@host51 ~]# chown mysql /mylog
[root@host51 ~]# ls -ld /mylog
drwxr-xr-x. 2 mysql root 6 2月 18 14:10 /mylog
[root@host51 ~]# systemctl restart mysqld
[root@host51 ~]# ls /mylog
tian.000001 tian.index
[root@host51 ~]# mysql -uroot -p123456 -e"flush logs"
[root@host51 ~]# ls /mylog
tian.000001 tian.000002 tian.index
[root@host51 ~]# mysql -uroot -p123456
mysql> show master status;
+-------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------+----------+--------------+------------------+-------------------+
| tian.000002 | 154 | | | |
+-------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> flush logs;
mysql> show master status;
+-------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------+----------+--------------+------------------+-------------------+
| tian.000003 | 154 | | | |
+-------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> flush logs;
mysql> show master status;
+-------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------+----------+--------------+------------------+-------------------+
| tian.000004 | 154 | | | |
+-------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> purge master logs to "tian.000004";
[root@host51 ~]# ls /mylog
tian.000004 tian.index
当想用某个日志文件只记录某个库下的数据，可以使用生成日志文件恢复数据
分析日志
- 查看日志当前的记录格式
mysql > show variables like "binlog_format"
- 日志的三种记录方式
1.statement 报表模式
2.row 行模式（默认）
3.mixed 混合模式
- 修改日志记录格式
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
....
binlog_format="记录方式名"
[root@localhost ~]# systemctl restart mysqld
- 查看日志内容
mysqlbinlog [选项] binlog日志文件名
| 选项 | 用途 |
| --strat-datetime="yyyy-mm-dd hh:mm:ss" | 起始时间 |
| --stop-datetime="yyyy-mm-dd hh:mm:ss" | 结束时间 |
| --start-position | 起始偏移量 |
| --stop-postion | 结束偏移量 |
mysql> show variables like "%binlog%";
+-----------------------------------------+----------------------+
| Variable_name | Value |
+-----------------------------------------+----------------------+
| binlog_cache_size | 32768 |
| binlog_checksum | CRC32 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_error_action | ABORT_SERVER |
| binlog_format | ROW |
| binlog_group_commit_sync_delay | 0 |
| binlog_group_commit_sync_no_delay_count | 0 |
| binlog_gtid_simple_recovery | ON |
| binlog_max_flush_queue_time | 0 |
| binlog_order_commits | ON |
| binlog_row_image | FULL |
| binlog_rows_query_log_events | OFF |
| binlog_stmt_cache_size | 32768 |
| innodb_api_enable_binlog | OFF |
| innodb_locks_unsafe_for_binlog | OFF |
| log_statements_unsafe_for_binlog | ON |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 1073741824 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| sync_binlog | 1 |
+-----------------------------------------+----------------------+
20 rows in set (0.02 sec)
[root@host51 ~]# vim /etc/my.cnf
4 [mysqld]
5 #skip-grant-tables
6 secure_file_priv=/myload
7 validate_password_policy=0
8 validate_password_length=6
9 server_id=51
10 binlog_format="mixed"
[root@host51 ~]# systemctl restart mysqld
[root@host51 ~]# mysql -uroot -p123456
mysql> show master status;
+-------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------+----------+--------------+------------------+-------------------+
| tian.000006 | 154 | | | |
+-------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> show variables like "%binlog%";
+-----------------------------------------+----------------------+
| Variable_name | Value |
+-----------------------------------------+----------------------+
| binlog_cache_size | 32768 |
| binlog_checksum | CRC32 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_error_action | ABORT_SERVER |
| binlog_format | MIXED |
| binlog_group_commit_sync_delay | 0 |
| binlog_group_commit_sync_no_delay_count | 0 |
| binlog_gtid_simple_recovery | ON |
| binlog_max_flush_queue_time | 0 |
| binlog_order_commits | ON |
| binlog_row_image | FULL |
| binlog_rows_query_log_events | OFF |
| binlog_stmt_cache_size | 32768 |
| innodb_api_enable_binlog | OFF |
| innodb_locks_unsafe_for_binlog | OFF |
| log_statements_unsafe_for_binlog | ON |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 1073741824 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| sync_binlog | 1 |
+-----------------------------------------+----------------------+
20 rows in set (0.00 sec)
mysql> show variables like "binlog_format";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+
1 row in set (0.03 sec)恢复数据
- 基本思路
使用mysqlbinlog提取历史SQL操作
通过管道交给mysql命令执行
- 命令格式
mysqlbinlog 日志文件 | mysql -uroot -p密码
- 恢复所有数据
[root@host51 ~]# ls /mylog
tian.000004 tian.index
[root@host51 ~]# mysqldump -uroot -p123456 --flush-logs db3 user > /root/db3_user.sql
[root@host51 ~]# ls -l /root/db3_user.sql
-rw-r--r--. 1 root root 3699 2月 18 14:31 /root/db3_user.sql
[root@host51 ~]# ls
db3_user.sql
[root@host51 ~]# mysql -uroot -p123456
mysql> show master status;
+-------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------+----------+--------------+------------------+-------------------+
| tian.000005 | 154 | | | |
+-------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> select count(*) from db3.user;
+----------+
| count(*) |
+----------+
| 28 |
+----------+
1 row in set (0.00 sec)
mysql> insert into db3.user(name,uid) values("aaaa",1);
Query OK, 1 row affected (0.00 sec)
...
mysql> show master status;
+-------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------+----------+--------------+------------------+-------------------+
| tian.000005 | 3529 | | | |
+-------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> select count(*) from db3.user;
+----------+
| count(*) |
+----------+
| 40 |
+----------+
1 row in set (0.00 sec)
[root@host51 ~]# scp /root/db3_user.sql root@192.168.4.52:/root
[root@host51 ~]# scp /mylog/tian.000005 root@192.168.4.52:/root
[root@host52 ~]# ls
db3_user.sql tian.000005
[root@host52 ~]# mysql -uroot -p123456
mysql> drop database db3;
[root@host52 ~]# mysql -uroot -p123456 -e "show databases"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| db2 |
| db4 |
| db5 |
| mysql |
| performance_schema |
| sys |
| tian |
+--------------------+
[root@host52 ~]# mysql -uroot -p123456 -e "create database db3"
[root@host52 ~]# mysql -uroot -p123456 db3 < /root/db3_user.sql
[root@host52 ~]# mysql -uroot -p123456 -e "select count(*) from db3.user"
+----------+
| count(*) |
+----------+
| 28 |
+----------+
[root@host52 ~]# mysqlbinlog /root/tian.000005 | mysql -uroot -p123456
[root@host52 ~]# mysql -uroot -p123456 -e "select count(*) from db3.user"
+----------+
| count(*) |
+----------+
| 40 |
+----------+- 恢复部分新产生的数据
查看和修改日志文件记录的命令格式
区分记录的多条SQL命令：偏移量，binlog日志文件的默认初始偏移量为154；时间
mysql> show variables like "%binlog%";
+-----------------------------------------+----------------------+
| Variable_name | Value |
+-----------------------------------------+----------------------+
| binlog_cache_size | 32768 |
| binlog_checksum | CRC32 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_error_action | ABORT_SERVER |
| binlog_format | ROW |
| binlog_group_commit_sync_delay | 0 |
| binlog_group_commit_sync_no_delay_count | 0 |
| binlog_gtid_simple_recovery | ON |
| binlog_max_flush_queue_time | 0 |
| binlog_order_commits | ON |
| binlog_row_image | FULL |
| binlog_rows_query_log_events | OFF |
| binlog_stmt_cache_size | 32768 |
| innodb_api_enable_binlog | OFF |
| innodb_locks_unsafe_for_binlog | OFF |
| log_statements_unsafe_for_binlog | ON |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 1073741824 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| sync_binlog | 1 |
+-----------------------------------------+----------------------+
20 rows in set (0.02 sec)
[root@host51 ~]# vim /etc/my.cnf
4 [mysqld]
5 #skip-grant-tables
6 secure_file_priv=/myload
7 validate_password_policy=0
8 validate_password_length=6
9 server_id=51
10 binlog_format="mixed"
[root@host51 ~]# systemctl restart mysqld
[root@host51 ~]# mysql -uroot -p123456
mysql> show master status;
+-------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------+----------+--------------+------------------+-------------------+
| tian.000006 | 154 | | | |
+-------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> show variables like "binlog_format";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+
1 row in set (0.03 sec)
mysql> insert into db3.user(name,uid) values("bob" ,888);
mysql> insert into db3.user(name,uid) values("bob" ,888);
mysql> insert into db3.user(name,uid) values("bob" ,888);
mysql> delete from db3.user where name like "aaa%";
Query OK, 5 rows affected (0.03 sec)
mysql> update db3.user set gid=2020 where id<=10;
Query OK, 10 rows affected (0.02 sec)
Rows matched: 10 Changed: 10 Warnings: 0
mysql> show master status;
+-------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------+----------+--------------+------------------+-------------------+
| tian.000006 | 1696 | | | |
+-------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
[root@host51 ~]# ls /mylog
tian.000004 tian.000005 tian.000006 tian.index
[root@host51 ~]# scp /mylog/tian.000006 root@192.168.4.52:/root
#######在host52（192.168.4.52）主机上读取日志内容恢复数据
[root@host52 ~]# mysqlbinlog /root/tian.000006 #读取日志文件内容，查看其起始偏移量以及结束偏移量
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#200218 15:23:44 server id 51 end_log_pos 123 CRC32 0x5e885f9a Start: binlog v 4, server v 5.7.17-log created 200218 15:23:44 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
AJFLXg8zAAAAdwAAAHsAAAABAAQANS43LjE3LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAkUteEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AZpfiF4=
'/*!*/;
# at 123
#200218 15:23:44 server id 51 end_log_pos 154 CRC32 0xf9838a23 Previous-GTIDs
# [empty]
# at 154
#200218 15:26:17 server id 51 end_log_pos 219 CRC32 0x4aecb3cc Anonymous_GTID last_committed=0 sequence_number=1
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#200218 15:26:17 server id 51 end_log_pos 293 CRC32 0xde3a9032 Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1582010777/*!*/;
SET @@session.pseudo_thread_id=3/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 293
# at 325
#200218 15:26:17 server id 51 end_log_pos 325 CRC32 0xa1ecd5ea Intvar
SET INSERT_ID=75/*!*/;
#200218 15:26:17 server id 51 end_log_pos 446 CRC32 0xf0564a48 Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1582010777/*!*/;
insert into db3.user(name,uid) values("bob" ,888)
/*!*/;
# at 446
#200218 15:26:17 server id 51 end_log_pos 477 CRC32 0x39358b0d Xid = 6
COMMIT/*!*/;
# at 477
#200218 15:26:18 server id 51 end_log_pos 542 CRC32 0x405ebe42 Anonymous_GTID last_committed=1 sequence_number=2
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 542
#200218 15:26:18 server id 51 end_log_pos 616 CRC32 0x30796267 Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1582010778/*!*/;
BEGIN
/*!*/;
# at 616
# at 648
#200218 15:26:18 server id 51 end_log_pos 648 CRC32 0xade407c5 Intvar
SET INSERT_ID=76/*!*/;
#200218 15:26:18 server id 51 end_log_pos 769 CRC32 0xe5bfc8f4 Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1582010778/*!*/;
insert into db3.user(name,uid) values("bob" ,888)
/*!*/;
# at 769
#200218 15:26:18 server id 51 end_log_pos 800 CRC32 0xf8b13fe7 Xid = 7
COMMIT/*!*/;
# at 800
#200218 15:26:18 server id 51 end_log_pos 865 CRC32 0x9029a771 Anonymous_GTID last_committed=2 sequence_number=3
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 865
#200218 15:26:18 server id 51 end_log_pos 939 CRC32 0x7b608b92 Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1582010778/*!*/;
BEGIN
/*!*/;
# at 939
# at 971
#200218 15:26:18 server id 51 end_log_pos 971 CRC32 0x27c7f729 Intvar
SET INSERT_ID=77/*!*/;
#200218 15:26:18 server id 51 end_log_pos 1092 CRC32 0x78ab151e Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1582010778/*!*/;
insert into db3.user(name,uid) values("bob" ,888)
/*!*/;
# at 1092
#200218 15:26:18 server id 51 end_log_pos 1123 CRC32 0x613af939 Xid = 8
COMMIT/*!*/;
# at 1123
#200218 15:26:59 server id 51 end_log_pos 1188 CRC32 0xb2ef9056 Anonymous_GTID last_committed=3 sequence_number=4
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1188
#200218 15:26:59 server id 51 end_log_pos 1262 CRC32 0x913ad69e Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1582010819/*!*/;
BEGIN
/*!*/;
# at 1262
#200218 15:26:59 server id 51 end_log_pos 1380 CRC32 0xeb0f4e12 Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1582010819/*!*/;
delete from db3.user where name like "aaa%"
/*!*/;
# at 1380
#200218 15:26:59 server id 51 end_log_pos 1411 CRC32 0x2f718d62 Xid = 9
COMMIT/*!*/;
# at 1411
#200218 15:27:43 server id 51 end_log_pos 1476 CRC32 0x9022267b Anonymous_GTID last_committed=4 sequence_number=5
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1476
#200218 15:27:43 server id 51 end_log_pos 1550 CRC32 0x99883f65 Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1582010863/*!*/;
BEGIN
/*!*/;
# at 1550
#200218 15:27:43 server id 51 end_log_pos 1665 CRC32 0xb4a84776 Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1582010863/*!*/;
update db3.user set gid=2020 where id<=10
/*!*/;
# at 1665
#200218 15:27:43 server id 51 end_log_pos 1696 CRC32 0xb21b5029 Xid = 10
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@host52 ~]# mysqlbinlog --start-position=325 --stop-position=1123 /root/tian.000006 | mysql -uroot -p123456
[root@host52 ~]# mysql -uroot -p123456
mysql> select * from db3.user where name regexp "^bob";
+----+------+------+----------+------+------+---------+---------+-------+
| id | name | age | password | uid | gid | comment | homedir | shell |
+----+------+------+----------+------+------+---------+---------+-------+
| 55 | bob | 20 | NULL | NULL | NULL | NULL | NULL | NULL |
| 75 | bob | 20 | NULL | 888 | NULL | NULL | NULL | NULL |
| 76 | bob | 20 | NULL | 888 | NULL | NULL | NULL | NULL |
| 77 | bob | 20 | NULL | 888 | NULL | NULL | NULL | NULL |
+----+------+------+----------+------+------+---------+---------+-------+
4 rows in set (0.00 sec)
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
