MySQL权限与备份管理(精简笔记)

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，目前属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一，该笔记用于生产环境快速查阅.

MariaDB 是一个多用户数据库,具有功能强大的访问控制系统,可以为不同用户指定允许的权限.MariaDB用户可以分为普通用户和ROOT用户.ROOT用户是超级管理员,拥有所有权限,包括创建用户、删除用户和修改用户的密码等管理权限,普通用户只拥有被授予的各种权限.

MariaDB 权限概述

MariaDB 服务器通过权限表来控制用户对数据库的访问,权限表存放在MariaDB的数据库中,由MySQL_install_db脚本初始化，,存储账户权限信息表主要有:user、db、host、table_priv,columns_priv和procs_priv.

◆USER表◆

user表是MariaDB中最重要的一个权限表,记录允许连接到服务器的账号信息,里面的权限是全局的,MariaDB中user表一共有42个字段,用户列可分为4类,分别是用户列,权限列,安全列和资源控制列,下面我们介绍介个常用列的作用.

字段名	数据类型	默认值
Host	char	NULL
User	char	NULL
Password	char	NULL
ssl_cipher	blob	NULL
x509_issuer	blob	NULL
x509_subject	blob	NULL
max_questions	int	0
max_updates	int	0
max_connections	int	0
max_user_connections	int	0

用户列:

user表的用户列包括Host、User、Password,分别表示主机名、用户名和密码.其中User和Host为User表的联合主键,当用户与服务器之间建立连接时,输入的账户信息中的用户名称、主机名和密码必须匹配User表中对应的字段,只有3个值都匹配的时候,才允许连接建立.这3个字段的值就是创建账户时保存的账户信息,修改用户密码时,实际就是修改user表的Password字段的值.

权限列:

权限列的字段决定了用户的权限,描述了在全局范围内允许对数据和数据库进行的操作.包括查询权限、修改权限等普通权限,还包括了关闭服务器、超级权限和加载用户等高级权限,普通权限用于操作数据库,高级权限用于数据库管理.

安全列:

安全列只有6个字段,其中两个是SSL相关的,两个是 x509 相关的,另外两个是授权插件相关的,SSL用于加密, x509 标准可用于标识用户,Plugin字段标识可以用于验证用户身份的插件,如果该字段为空,服务器使用内建授权验证机制验证用户身份.

资源控制列:

资源控制列用来限制用户使用的资源,包括以下4个字段,分别为:

max_questions:用户每小时允许执行查询次数.
max_updates:用户每小时允许执行更新次数.
max_connections:用户每小时允许执行的连接次数.
max_user_connection:用户允许同时建立的连接次数.

◆DB权限表◆

DB表和HOST表,在数据库中非常重要的权限表,DB表中存储了用户对某个数据库的操作权限,决定用户能从哪个主机存取哪个数据库.host表中存储了某个主机对数据库的操作权限,配合db权限表对给定主机上数据库级操作权限做更细致的控制.这个权限表不受GRANT 和 REVOKE语句的影响,db表比较常用,host表一般很少使用.db表和host表结构相似,字段大致可以分为两类:用户列和权限列.

select_priv,Insert_priv,update_priv,delete_priv,Create_priv,Drop_priv,Alter_priv,Grant_priv

字段名	数据类型	默认值
Host	char	NULL
DB	char	NULL
USER	char	NULL
select_priv	enum(N,Y)	NO
insert_priv	enum(N,Y)	NO
update_priv	enum(N,Y)	NO
delete_priv	enum(N,Y)	NO
create_priv	enum(N,Y)	NO
drop_priv	enum(N,Y)	NO
alter_priv	enum(N,Y)	NO
grant_priv	enum(N,Y)	NO

DB表用户列有3个字段,分别是Host、User、Db标识从某个主机连接某个用户对某个数据库的操作权限,这3个字段的组合构成了db表的主键.host表不存储用户名称,用户列只有2个字段,分别是Host和DB,表示从某个主机连接的用户对某个数据库的操作权限,其主键包括Host和Db两个字段,host很少用到,一般情况下db表就可以满足权限控制需求了.

MariaDB 账户管理

MariaDB提供了许多语句来管理用户账号,这些语句可以用来管理包括登陆和退出MariaDB服务器,创建用户,删除用户,密码管理和权限管理等,MariaDB数据库的安全性,需要通过账户管理来保证.

◆查询在线用户◆

本地查询: 当我们本地登录到数据库时,可以使用本地查询,查询SQL语句如下.

MariaDB [(none)]> show processlist;
+----+---------+-----------+------+---------+------+-------+------------------+----------+
| Id | User    | Host      | db   | Command | Time | State | Info             | Progress |
+----+---------+-----------+------+---------+------+-------+------------------+----------+
|  2 | root    | localhost | NULL | Query   |    0 | NULL  | show processlist |    0.000 |
|  5 | lyshark | localhost | NULL | Sleep   |    4 |       | NULL             |    0.000 |
+----+---------+-----------+------+---------+------+-------+------------------+----------+

2 rows in set (0.08 sec)

远程查询: 如果在远程终端机上查询远程数据库,前提是数据库开启了远程授权我们可以使用以下SQL语句.

[root@localhost ~]# mysqladmin -uroot -p123 processlist
+----+---------+-----------+----+---------+------+-------+------------------+----------+
| Id | User    | Host      | db | Command | Time | State | Info             | Progress |
+----+---------+-----------+----+---------+------+-------+------------------+----------+
| 5  | lyshark | localhost |    | Sleep   | 154  |       |                  | 0.000    |
| 11 | root    | localhost |    | Query   | 0    |       | show processlist | 0.000    |
+----+---------+-----------+----+---------+------+-------+------------------+----------+

[root@localhost ~]#

查全部用户: 我们通过构建Select语句查询指定字段(Host,User,Password),查询mysql.user这个数据表,SQL语句如下.

MariaDB [none]> select Host,User,Password from mysql.user;
+-----------+---------+-------------------------------------------+
| Host      | User    | Password                                  |
+-----------+---------+-------------------------------------------+
| localhost | root    | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| 127.0.0.1 | root    | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| ::1       | root    | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| localhost | lyshark | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+-----------+---------+-------------------------------------------+

4 rows in set (0.01 sec)

去重查询: 通过使用distinct命令使查询结果不重复,自动过滤重复的记录.

MariaDB [(none)]> select distinct User,Password from mysql.user;
+---------+-------------------------------------------+
| User    | Password                                  |
+---------+-------------------------------------------+
| root    | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| lyshark | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+---------+-------------------------------------------+

2 rows in set (0.00 sec)

◆新建普通用户◆

创建新用户,必须有相应的权限来执行创建操作.在MariaDB数据库中,有两种方式创建新用户:一种是使用CREATE USER或GRANT语句,另一种是直接操作MariaDB授权表,最好的方法是使用GRANT语句,因为这样更精确,如果使用create语句创建用户后用户无权限,需要手动添加权限,而直接使用grant语句可以一步到位.

使用create user语句创建新用户:

1.使用create user创建一个用户,名称为jeffrey,密码是mypass,指定开启%远程权限.

MariaDB [(none)]> create user 'jeffrey'@'%' identified by 'mypass';
Query OK, 0 rows affected (0.05 sec)

2.接着继续创建一个新用户jeffreys,密码是mypass,并指定使用localhost本地权限.

MariaDB [(none)]> select password('mypass');
+-------------------------------------------+
| password('mypass')                        |
+-------------------------------------------+
| *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 |
+-------------------------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> create user 'jeffreys'@'localhost' identified by password '*6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4';
Query OK, 0 rows affected (0.00 sec)

使用grant user语句创建新用户:

使用grant语句创建一个新用户myuser,密码是123123,并授予用户对所有表的select和update权限,SQL语句如下:

MariaDB [(none)]> grant select,update ON *.* TO 'myuser'@'localhost' identified by '123123';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> select Host,User,Select_priv,Update_priv from mysql.user where user='myuser';
+-----------+--------+-------------+-------------+
| Host      | User   | Select_priv | Update_priv |
+-----------+--------+-------------+-------------+
| localhost | myuser | Y           | Y           |
+-----------+--------+-------------+-------------+
1 row in set (0.00 sec)

◆删除普通用户◆

使用drop user语句删除用户:

MariaDB [(none)]> select distinct User,Host from mysql.user;
+---------+-----------+
| User    | Host      |
+---------+-----------+
| root    | 127.0.0.1 |
| root    | ::1       |
| lyshark | localhost |
| root    | localhost |
+---------+-----------+
4 rows in set (0.07 sec)

MariaDB [(none)]> drop user lyshark@"localhost";
Query OK, 0 rows affected (0.37 sec)

MariaDB [(none)]> select distinct User,Host from mysql.user;
+------+-----------+
| User | Host      |
+------+-----------+
| root | 127.0.0.1 |
| root | ::1       |
| root | localhost |
+------+-----------+
3 rows in set (0.00 sec)

MariaDB [(none)]>

使用delete语句删除用户:

MariaDB [(none)]> delete from mysql.user where host='localhost' and user='myuser';
Query OK, 1 row affected (0.01 sec)

◆修改用户密码◆

修改自身密码: 修改自己用户的用户名和密码.

MariaDB [(none)]> set password=password("123123");
Query OK, 0 rows affected (0.00 sec)

修改指定用户的密码: 修改lyshark用户授权方式为localhost的密码为123123

MariaDB [(none)]> set password for "lyshark"@"localhost"=password("123123");
Query OK, 0 rows affected (0.00 sec)

◆ROOT密码找回◆

1.关闭MariaDB数据库

[root@localhost ~]# systemctl stop mariadb
[root@localhost ~]# systemctl status mariadb

2.修改MariaDB的主配置文件,在Mysqld区域添加skip-grant-table语句,保存退出

[root@localhost etc]# ll /etc/my.cnf
-rw-r--r--. 1 root root 570 6月   8 2017 /etc/my.cnf
[root@localhost etc]#
[root@localhost etc]# vim /etc/my.cnf

[mysqld]

skip-grant-table       #添加我就可以了

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks

3.重启MariaDB数据库

[root@localhost ~]# systemctl restart mariadb
[root@localhost ~]# systemctl status mariadb

4.免密码登陆数据库,并执行修改数据库的SQL语句

[root@localhost ~]# mysql -uroot -p
MariaDB [(none)]> update mysql.user set password=password("123") where user="root";
MariaDB [(none)]> Ctrl-C -- exit!

5.修改完成后将主配置文件的skip-grant-table属性去掉,重启数据库即可,下次使用密码登陆即可

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

[root@localhost ~]# mysql -uroot -p123

MariaDB 权限管理

权限管理主要是对登录到MariaDB的用户进行权限验证所有用户的权限都存储在MariaDB的权限表中,不合理的权限规划会给MariaDB服务器带来安全隐患,数据库管理员要对所有用户的权限进行合理规划管理.

◆查看权限◆

查询所有权限: 查询数据库中所有用户列表和权限信息.

MariaDB [(none)]> select distinct concat("用户:",user," 权限:",host," : ") as query from mysql.user;
+------------------------------------+
| query                              |
+------------------------------------+
| 用户:root 权限:localhost :         |
| 用户:root 权限:127.0.0.1 :         |
| 用户:root 权限:::1 :               |
| 用户:lyshark 权限:localhost :      |
| 用户:lyshark 权限:% :              |
+------------------------------------+
8 rows in set (0.46 sec)

MariaDB [(none)]>

查询指定权限: 查询关于lyshark的所有权限信息

MariaDB [(none)]> show grants for lyshark;
+----------------------------------------------+
| Grants for lyshark@%                         |
+----------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'lyshark'@'%' |
+----------------------------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]>

查询指定权限: 查询lyshark用户的远程%权限,和本地localhost权限

MariaDB [(none)]> show grants for "lyshark"@"%";
+----------------------------------------------+
| Grants for lyshark@%                         |
+----------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'lyshark'@'%' |
+----------------------------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> show grants for "lyshark"@"localhost";
+----------------------------------------------------------------------------------------------------------------+
| Grants for lyshark@localhost                                                                                   |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'lyshark'@'localhost' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' |
+----------------------------------------------------------------------------------------------------------------+
1 row in set (0.37 sec)

MariaDB [(none)]>

◆授予权限◆

创建用户并授权: 创建wang用户并给予%远程登陆的权限,并对所有数据库全部授权

MariaDB [(none)]> grant all on *.* to "wang"@"%" identified by "123";
Query OK, 0 rows affected (0.15 sec)

MariaDB [(none)]> show grants for "wang"@"%";
+--------------------------------------------------------------------------------------------------------------+
| Grants for wang@%                                                                                            |
+--------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'wang'@'%' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' |
+--------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

创建用户并授权: 创建用户名wang1,并允许localhost本机访问,对mysql库中的所有表具有select查询权限,密码为123

MariaDB [(none)]> grant select on mysql.* to "wang1"@"localhost" identified by "123";
Query OK, 0 rows affected (0.36 sec)

MariaDB [(none)]> show grants for "wang1"@"localhost";
+--------------------------------------------------------------------------------------------------------------+
| Grants for wang1@localhost                                                                                   |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'wang1'@'localhost' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' |
| GRANT SELECT ON `mysql`.* TO 'wang1'@'localhost'                                                             |
+--------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

MariaDB [(none)]>

创建用户并授权: 创建用户名wang2,且可在任意主机%登陆,并对所有数据库有(增删改查)权限,密码为123

MariaDB [(none)]> grant insert,delete,update,select on *.* to "wang2"@"%" identified by "123";
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show grants for "wang2"@"%";
+-------------------------------------------------------------------------------------------------------------------------------+
| Grants for wang2@%                                                                                                            |
+-------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'wang2'@'%' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' |
+-------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]>

创建用户并授权: 创建用户名wang3,且只能在192.168.1.59上登陆,并对mysql数据库有(查)权限,密码为123

MariaDB [(none)]> grant select on mysql.* to "wang3"@"192.168.1.59" identified by "123";
Query OK, 0 rows affected (0.15 sec)

MariaDB [(none)]> show grants for "wang3"@"192.168.1.59";
+-----------------------------------------------------------------------------------------------------------------+
| Grants for wang3@192.168.1.59                                                                                   |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'wang3'@'192.168.1.59' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' |
| GRANT SELECT ON `mysql`.* TO 'wang3'@'192.168.1.59'                                                             |
+-----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.50 sec)

MariaDB [(none)]>

创建用户并授权: 创建一个普通用户wang4,且仅有mysql库的(查)权限,密码为123

MariaDB [(none)]> grant usage,select on mysql.* to "wang4"@"localhost" identified by "123";
Query OK, 0 rows affected (0.35 sec)

只授权用户权限: 授权用户wang4,对所有数据库的全部权限,密码123

MariaDB [(none)]> grant all privileges on *.* to "wang4"@"localhost" identified by "123";
Query OK, 0 rows affected (0.36 sec)

只授权用户权限: 授权一个已存在账号允许远程登陆最大权限

MariaDB [(none)]> grant all on *.* to "root"@"%";
Query OK, 0 rows affected (0.07 sec)

◆收回权限◆

收回用户授权: 撤销lyshark用户,对所有数据库的远程%用户权限

MariaDB [(none)]> show grants for lyshark;
+----------------------------------------------+
| Grants for lyshark@%                         |
+----------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'lyshark'@'%' |
+----------------------------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> revoke all on *.* from "lyshark"@"%";
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show grants for lyshark;
+-------------------------------------+
| Grants for lyshark@%                |
+-------------------------------------+
| GRANT USAGE ON *.* TO 'lyshark'@'%' |
+-------------------------------------+
1 row in set (0.00 sec)

收回用户授权: 撤销lyshark用户的远程登陆权限

MariaDB [(none)]> revoke create on *.* from "lyshark"@"%";
Query OK, 0 rows affected (0.01 sec)

刷新权限: 修改完成以后,记得执行权限的刷新操作.

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.01 sec)

MariaDB 数据备份与恢复

尽管采取了一些管理措施来保证数据库的安全,但是不确定的意外情况总是有可能造成数据的损失,例如意外的停电、管理员不小心的操作失误都可能会造成数据的丢失,保证数据安全的最重要的一个措施是确保对数据进行定期备份,如果数据库中的数据丢失或者出现错误,可以使用备份的数据进行恢复,这样就尽可能地降低了意外原因导致的损失.

数据备份是理员非常重要的工作之一,系统意外崩溃或者硬件的损坏都可能导致数据库的丢失,因此MariaDB管理员应该定期地备份数据库,使得在意外情况发生时,尽可能减少损失.

◆数据备份还原◆

全局锁定: 在备份数据库之前,我们应该先将数据库设置为只读模式,下面将设置全局为只读模式.

MariaDB [(none)]> show global variables like "%read_only%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

MariaDB [(none)]> set global read_only=1;                         #1是只读,0是读写
Query OK, 0 rows affected (0.08 sec)

MariaDB [(none)]> show global variables like "%read_only%";       #再次查询读写状态
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | ON    |
+---------------+-------+
1 row in set (0.00 sec)

表的锁定: 在备份数据库之前,我们应该先将数据库设置为只读模式,下面将设置指定表为只读模式.

MariaDB [(none)]> flush tables with read lock;                #锁定数据表
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> unlock tables;                              #解除表锁定
Query OK, 0 rows affected (0.00 sec)

MySQLDump备份数据: 通过内置备份命令,来备份数据库,以下是常用语法.

[root@localhost ~]# mysqldump -u root -p 数据库名 >备份文件.sql             #备份单个数据库
[root@localhost ~]# mysqldump -u root -p 数据库名 表名 >备份文件.sql         #备份数据库中单个表
[root@localhost ~]# mysqldump -u root -p --databases 数据库名 >备份文件.sql  #备份指定的数据库
[root@localhost ~]# mysqldump -u root -p --all-databases >备份文件.sql      #备份整个数据库

MySQL还原数据: 通过内置备份命令,来还原数据库,以下是常用语法.

[root@localhost ~]# mysql -u root -p 数据库名 < 备份文件.sql               #还原单个数据库
[root@localhost ~]# mysql -u root -p 还原到的数据库名 < 备份文件.sql        #还原数据库中单个表
[root@localhost ~]# mysql -u root -p < 备份文件.sql                        #还指定的数据库
[root@localhost ~]# mysql -u root -p < 备份文件.sql                        #还原整个数据库    

MySQLHotcopy热备份: 使用hotcopy完成一次热备份.

[root@localhost ~]# mysqlhotcopy --flushlog -u="root" -p="123" --regexp=^l /root/

#以上参数解释: -u 用户名	-p 密码 --regexp=^l 备份开头是l的数据库 /root 备份文件保存位置

[root@localhost ~]# ll
总用量 0
drwxr-x---. 2 mysql mysql 39 9月  15 04:31 lyshark

MySQLHotcopy冷恢复: 使用hotcopy完成一次冷恢复,冷恢复需要关闭数据库.

[root@localhost ~]# systemctl stop mariadb

[root@localhost ~]# cp -a /root/lyshrk /usr/local/mysql/data
[root@localhost ~]# chown -R mysq.mysql /usr/local/mysql/data

[root@localhost ~]# systemctl start mariadb

◆数据导入导出◆

select 语句导出:

MariaDB [none]> select * from 数据库名称.表名称 INTO OUTFILE "/root/xxx.txt"
[root@localhost ~]# cat /root/xxx.txt

mysql命令导出文件

[root@localhost ~]# mysql -uroot -p --execute="select * from 表名;" 数据库名 > filename.txt       
[root@localhost ~]# mysql -uroot -p --verical --execute="select * from person;" 数据库名称 > /root/lyshark.txt
[root@localhost ~]# mysql -uroot -p --html --execute="select * from person;" 数据库名称 > /root/lyshark.html
[root@localhost ~]# mysql -uroot -p --xml --execute="select * from person;" 数据库名称 > /root/lyshark.xml

load data 导入数据:

[root@localhost ~]# load data INFILE '/root/wang.txt' INTO TABLE 数据库.表名称;
[root@localhost ~]# mysqlmport -uroot -p 数据库名称 filename.txt

MariaDB 日志配置与管理

MariaDB 日志记录了MariaDB数据库日常操作和错误信息,MariaDB有不同类型的日志文件(各自存储了不同类型的日志),从日志当中可以查询到MaraiDB数据库的运行情况、用户操作、错误信息等,可以为MariaDB管理和优化提供必要的信息,对于MariaDB的管理工作而言,这些日志文件是不可缺少的.

MariaDB默认分为以下4类,使用这些日志可查看MariaDB内部发生的事情,4类分别是:

● 错误日志: 记录MaraiDB服务的启动,运行或停止MariaDB服务时出现的问题
● 查询日志: 记录建立的客户端连接和执行的语句
● 二进制日志: 记录所有更改数据的语句,可以用于数据复制
● 慢查询日志: 记录所有执行时间超过默认值的所有查询或不适用索引的查询

默认情况下,所有日志创建于MariaDB数据目录中,通过刷新日志,可以强制关闭和重新打开日志文件(或者在某些情况下切换到一个新的日志),当执行一个FLUSH LOGS语句或执行mysqlladmin flush-logs或mysqladmin refresh时,将刷新日志.
如果正使用MariaDB复制功能,在复制服务器上可以维护更多日志文件,这种日志称为接替日志.启动日志功能会降低数据库的性能,如果开启慢查询日志则会占用大量的磁盘空间.

◆二进制日志◆

二进制日志主要记录数据库的变化,二进制日志以一种有效的格式,并且是事务安全的方式包含更新日志中可用的所有信息,二进制日志包含了所有更新了数据或者己经潜在更新了数据,语句以”事件”的形式保存,描述数据更改.

二进制日志还包含关于每个更新数据库的语句的执行时间信息,它不包含没有修改任何数据的语句,如果想要记录所有语句,需要使用一般查询日志,使用二进制日志的主要目的是最大可能地恢复数据库,因为二进制日志包含备份后进行的所有更新.

启动二进制日志:

1.默认情况下二进制日志是关闭状态的,可以通过修改数据库配置文件来设置开启日志.

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

[mysqld]
log-bin="/tmp"              #设置开启日志,也可不指定日志保存位置
expire_logs_days = 10       #设置日志自动清理天数
max_binlog_size = 100M      #定义了单个文件的大小限制

2.添加完毕后,重启数据库进程,即可打开二进制日志啦.

[root@localhost ~]# systemctl restart mariadb
[root@localhost ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.60-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show variables like 'log_%';
+---------------------------------+-------------------------------------------------
| Variable_name                   | Value
+---------------------------------+-------------------------------------------------
| log_bin                         | ON
| log_bin_trust_function_creators | OFF
| log_error                       | /var/log/mariadb/mariadb.log
| log_output                      | FILE
| log_queries_not_using_indexes   | OFF
| log_slave_updates               | OFF
| log_slow_filter                 | admin,filesort,filesort_on_disk,full_join,
| log_slow_queries                | OFF
| log_slow_rate_limit             | 1
| log_slow_verbosity              |
| log_warnings                    | 1
+---------------------------------+-------------------------------------------------
11 rows in set (0.01 sec)

查看二进制日志:

1.可以使用show binary logs语句查看二进制日志文件个数及文件名,SQL语句如下:

MariaDB [(none)]> show binary logs;
+--------------------+-----------+
| Log_name           | File_size |
+--------------------+-----------+
| mariadb-bin.000001 |       245 |
+--------------------+-----------+
1 row in set (0.02 sec)

可以看到当前只有一个二进制日志,日志文件的个数与MariaDB服务启动的次数相同,每次启动一次数据库,将会产生一个新的日志文件.

2.也可以使用mysqlbinlog命令查看日志内容,SQL语句如下:

[root@localhost ~]# mysqlbinlog mariadb-bin.000001

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
mysqlbinlog: File 'mariadb-bin.000001' not found (Errcode: 2)
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

删除二进制日志:

1.我们可以手动删除二进制日志,通过使用reset master语句删除所有日志,SQL语句如下:

[root@localhost ~]# mysql

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 5.5.60-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> reset master;
Query OK, 0 rows affected (0.01 sec)

2.也可以单独删除指定的日志文件,如下删除第一个日志文件,SQL代码如下:

MariaDB [(none)]> show binary logs;
+--------------------+-----------+
| Log_name           | File_size |
+--------------------+-----------+
| mariadb-bin.000001 |       264 |
| mariadb-bin.000002 |       264 |
| mariadb-bin.000003 |       245 |
+--------------------+-----------+
3 rows in set (0.00 sec)

MariaDB [(none)]> purge master logs to "mariadb-bin.000001";
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show binary logs;
+--------------------+-----------+
| Log_name           | File_size |
+--------------------+-----------+
| mariadb-bin.000002 |       264 |
| mariadb-bin.000003 |       245 |
+--------------------+-----------+
3 rows in set (0.00 sec)

3.或者单独删除指定日期的日志文件,如下删除2018/01/01以前所有日志,SQL代码如下:

MariaDB [(none)]> purge master logs before "20180101";
Query OK, 0 rows affected (0.00 sec)

◆系统错误日志◆

错误日志包含了数据库启动和停止时,以及服务器在运行过程中发生任何严重错误时的相关信息,错误日志对于数据库异常排查,有很大的帮助.

开启错误日志:

1.默认情况下二进制日志是关闭状态的,可以通过修改数据库配置文件来设置开启日志.

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

[mysqld]
log-error="/var/log/mariadb/mariadb.log"

2.添加完毕后,重启数据库进程,即可打开二进制日志啦.

[root@localhost ~]# systemctl restart mariadb
[root@localhost ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.60-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show variables like 'log_error';
+---------------+------------------------------+
| Variable_name | Value                        |
+---------------+------------------------------+
| log_error     | /var/log/mariadb/mariadb.log |
+---------------+------------------------------+
1 row in set (0.01 sec)

[root@localhost ~]# cat /var/log/mariadb/mariadb.log |head -n 10
181224 20:28:49 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
181224 20:28:49 [Note] /usr/libexec/mysqld (mysqld 5.5.60-MariaDB) starting as process 1622 ...
181224 20:28:49 InnoDB: The InnoDB memory heap is disabled
181224 20:28:49 InnoDB: Mutexes and rw_locks use GCC atomic builtins
181224 20:28:49 InnoDB: Compressed tables use zlib 1.2.7
181224 20:28:49 InnoDB: Using Linux native AIO
181224 20:28:50 InnoDB: Initializing buffer pool, size = 128.0M
181224 20:28:50 InnoDB: Completed initialization of buffer pool
InnoDB: The first specified data file ./ibdata1 did not exist:
InnoDB: a new database to be created!

删除错误日志:

[root@localhost ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 5.5.60-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> flush logs;
Query OK, 0 rows affected (0.01 sec)

◆慢查询日志◆

慢查询日志是记录查询时长超过指定时间的日志,慢查询日志主要用来记录执行时间较长的查询语句,通过慢查询日志,可以找出执行时间较长、执行效率较低的语句,然后进行优化.

开启错误日志:

1.默认情况下二进制日志是关闭状态的,可以通过修改数据库配置文件来设置开启日志.

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

[mysqld]
log-slow-queries="/tmp"
long_query_time=n

2.添加完毕后,重启数据库进程,即可打开二进制日志啦.

[root@localhost ~]# systemctl restart mariadb
[root@localhost ~]# mysql

参考文献

《MySQL5.7从入门到精通》 - 刘增杰