Mysql数据库安全和备份

一、用户权限认证

　　Mysql通过用户认证机制来和业务系统建立连接通道，当认证成功后，还需要通过访问控制模块来进行相关的权限验证，只有两次验证都通过后，才能继续执行SQL语句操作，这个过程涉及用户管理和访问控制两个模块。

　　用户管理模块用于管理登录用户信息、设置相关权限，Mysql系统表mysql.user存储了登录数据库的用户信息，当业务系统通过连接字符串参数尝试登录mysql时，首先会校验业务系统提供的用户身份，只有校验通过后才将控制权移交给访问控制模块。运行如下命令查看user信息，得到结果如图所示：

SELECT * FROM mysql.user;

我们可以看到， user表包含了40多个字段，记录了用户的相关权限信息，这些字段的解释如下：

　　除了以上的用户基本权限信息外，还有其他权限信息，存储在db、tables_priv、columns_priv、procs_priv表中：

• user 表：存放用户账户信息以及全局权限。
• db 表：存放数据库级权限，决定来自哪些主机的哪些用户可以访问此数据库。
• tables_priv 表：存放表级权限，决定来自哪些主机的哪些用户可以访问数据库的这个表。
• columns_priv 表：存放列级权限，决定来自哪些主机的哪些用户可以访问数据库表的这个字段。
• procs_priv 表：存放存储过程和函数级别的权限。

访问控制模块会根据以上表格的相关权限字段值来对于客户端发出的每条SQL语句进行权限检查，当有一条件不满足时则终止执行。若要为用户CURD权限，则使用如下命令：

# 创建用户
create user 'username'@'%' identified by '123456';

# 查看用户的权限
show grants for 'username'@'%';

# 设置全局权限
GRANT super,select on *.* to 'username'@'%';

# 设置库的权限
GRANT select,insert,update,delete,create,alter,execute on `db`.* to 'username'@'%';

# 设置表的权限
GRANT select,insert on `db`.tbname to 'username'@'%';

# 设置列的权限
GRANT select (col1), insert (col1, col2) ON `db`.tbname to 'username'@'%';

# 刷新权限，使其立马生效
flush privileges;

# 回收权限
revoke delete on `db`.* from 'username'@'%';

# GRANT语法格式解析：
super,select 表示具体要授予的权限。
ON 用来指定权限针对哪些库和表。
*.* 中前面的*号用来指定数据库名，后面的*号用来指定表名。
TO 表示将权限赋予某个用户。
'username'@'%' 表示username用户名，@后面接限制的主机，可以是IP、IP段、域名以及%，%表示任何地方。

基于数据的安全，我们应该在满足业务需求的前提下尽可能的给予用户最低权限，以下是几条建议：

• 只授予能满足需要的最小权限。
• 限制用户的登录主机。
• 为单独的库创建单独的登录用户，除root用户外，不允许其他用户访问本业务之外的库。
• 外部系统访问库时，权限精确到具体表或视图。
• 定期检查删除不需要的用户和权限。

二、数据的备份和还原

　　数据的备份是业务系统生产环境中必不可少的一环，常用的备份手段有文件方式定期备份，主从复制互为备份。根据数据备份的对象又分为数据库备份、表备份；根据操作模式分为热备、温备和冷备。在进行数据的备份前，我们应该先要明确要备份的数据对象，选择合适的手段，找到合适的备份工具，定制备份策略。

• 明确备份对象：通常有数据记录、代码(存储过程，自定义函数等)、数据库配置文件、数据库日志等。
• 选择备份工具：常用的有mysqldump、cp\ tar物理备份、lvm2 snapshot、xtrabackup等。
• 制定备份策略：针对不同的场景定制不同的策略，选择冷备、温备、热备模式，选择备份手段。

以下是目前几种生产环境中常用的备份实现思路：

　　1、基于日志点的主从复制。基于日志点的主从复制原理图：

首先主数据库master的insert、update、delete命令会被记录到二进制log日志文件中，从库slave将这些log文件读取到自己的中继日志中，然后在根据这些日志内容来执行同样的与主库相同insert、update、delete语句，实现数据的同步操作。根据原理图可知，要实现主从复制需要完成以下几件事：

• 准备两台够互通的数据库服务器master和slave。
• 运行如下命令在主库上创建一个专用于复制数据的用户。

CREATE user 'copyuser'@'x.x.x.x’ IDENTIFIED WITH mysql_native_password BY 'password';
GRANT REPLICATION SLAVE ON*.*TO 'copyuser'@'%';

• 开启主库的binlog，通过在my.cnf配置文件的[mysqlId]区增加如下代码实现。

[mysqld]
#表示该数据库在集群中的ID,不能与其他集群的id相同
server-id = 1
#二进制日志文件的路径
log_bin = /var/log/mysql/mysql-bin.log  
#指定产生binlog的schema
binlog_do_db = masterdb
#指定需要忽略的schema
binlog_ignore_db = mysql,information_schema,moon

• 修改从库slave的my.cnf配置文件。 

[mysqld]
#表示该数据库在集群中的ID,不能与其他集群的id相同
server-id = 2
#设置中继日志路径
relay_log = /var/log/mysql-relay-bin.log

• 在主数据库master中运行如下命令，将数据库信息导出到all.sql文件。

mysqldump --master-data=2 --single-transaction --triggers --routines --all-databases -u root -p >>all.sql

•  复制all.sql到从数据库slave，通过如下命令导入数据到从数据库slave（同步初始数据库信息）。

#运行该命令后需要输入密码，回车后完成数据导入
mysql -u root -p <all.sql

• 在从数据库slave中运行如下命令配置主从链接信息。(MASTER_LOG_FILE、MASTER_LOG_POS参数可以在all.sql中找到，也可以通过运行 ' SHOW MASTER STATUS  '获得。)

CHANGE MASTER TO  
MASTER_HOST =‘master_host_ip’, 
MASTER_USER ='copyuser',  
MASTER_PASSWORD = 'password',
MASTER_LOG_FILE = 'mysql_log_filename',
MASTER_LOG_POS= 值; 

•  使用' START SLAVE '命令启动主从复制服务。 到此，在Master数据库的增删改操作都会被同步到Slave数据库中。与此命令配套的还有SHOW SLAVE STATUS \G(查看状态信息)，STOP SLAVE(停止)。

 补充说明：CHANGE MASTER TO 用于配置到主数据库Master的连接，其参数说明如下

　　2、基于GTID的主从复制。

 　　在基于日志点的主从复制中，如果日志的文件和偏移量指定错误，会导致复制遗漏或重复，这里我们介绍另一种基于GTID的主从复制。GTID是全局事务ID，保证为每一个在主机上提交的事务在数据库集群中可以生成一个唯一的ID。基于GTID的复制执行步骤如下(与基于日志点的步骤差不多，只不过是配置不同而已)：

• 准备两台够互通的数据库服务器master和slave。(同上)
• 运行如下命令在主库上创建一个专用于复制数据的用户。(同上)
• 在master主数据库配置文件my.cnf下配置如下代码。

[mysqld]
#表示该数据库在集群中的ID,不能与其他集群的id相同
server-id = 1
#二进制日志文件的路径
log_bin = /var/log/mysql/mysql-bin.log  
#指定产生binlog的schema
binlog_do_db = masterdb
#指定需要忽略的schema
binlog_ignore_db = mysql,information_schema,moon
#开启GTID模式
gtid_model=on
#强制GTID一致性(安全)
enforce-gtid-consistency = on
#在从父服务器中记录传递过来的主服务器修改日志
log_slave_updates = on

• 在slave从服务器my.cnf配置文件中配置如下代码。

[mysqld]
#表示该数据库在集群中的ID,不能与其他集群的id相同
server-id = 1
#二进制日志文件的路径
log_bin = /var/log/mysql/mysql-bin.log  
#指定产生binlog的schema
binlog_do_db = masterdb
#指定需要忽略的schema
binlog_ignore_db = mysql,information_schema,moon
#开启GTID模式
gtid_model=on
#强制GTID一致性(安全)
enforce-gtid-consistency = on
#在从父服务器中记录传递过来的主服务器修改日志
log_slave_updates = on
#中继日志路径(如果指定了TABLE存储，则此项无用)
relay_log=/var/log/mysql/relay_log.log  
#设置数据库为只能读取，不能更新和删除
relay_only=on
#建议使用，指的是将master的信息存入表(能进行事务回滚)
master_info_respository=TABLE 
#建议使用，指的是将中继日志的信息存入表(能进行事务回滚)
relay_log_info_repository=TABLE

• 在主数据库master中运行如下命令，将数据库信息导出到all.sql文件，并按照前面同样的方式将数据初始化到从数据库。(同上)
• 在从数据库slave中运行如下命令配置主从链接信息。(同上，只不过是配置MASTER_AUTO_POSITION= 1)

CHANGE MASTER TO
MASTER_HOST = 'master_host_ip',
MASTER_USER ='copyuser',  
MASTER_PASSWORD = 'password', 
MASTER_AUTO_POSITION= 1

•  使用' START SLAVE '命令启动主从复制服务。(同上)

 　　3、使用mysqldump、mysqlbinlog 工具备份和还原数据库。

　　在前面主从复制的步骤中，我们已经使用了mysqldump工具进行过了导出导入(备份和还原)。除此之外，还可以使用该工具从二进制日志中备份和还原，需要开启二进制日志记录，开启方式是在my.cnf配置文件中指定log_bin路径。例如：

[mysqld]
#表示该数据库在集群中的ID,不能与其他集群的id相同
server-id = 1
#开启并指定二进制日志文件的路径
log_bin = /var/log/mysql/mysql-bin 
#二进制日志自动删除的天数，默认值为 0，表示“没有自动删除”。
expire_logs_days = 10
#指定单个二进制文件的大小
max_binlog_size = 1​00M

 以上的配置在数据库运行后，会在/var/log/mysql目录下产生mysql-bin.index以及mysql-bin.000001....等一系列二进制格式的日志文件，用默认的文本工具打开显示的是乱码，我们可以使用mysqlbinlog工具来查看。

#通过该命令可以将二进制格式的日志文件的内容转换为我们能识别的sql并且保存到my.sql文件中
mysqlbinlog --no-defaults mysql_bin.000001 >>my.sql

 　　二进制日志中记录了用户对数据库更改的所有操作，如 INSERT 语句、UPDATE 语句、CREATE 语句等。如果数据库因为操作不当或其它原因丢失了数据，可以通过二进制日志来查看在一定时间段内用户的操作，结合数据库备份来还原数据库。

　　通常来说，我们应该按时的备份数据库，数据在遭受意外时，可以从最新的还原点来还原数据，但是在该还原点之后也有可能进行了新的增删改，这部分操作的还原则从二进制记录文件中来还原。其方式是找到还原点之后的二进制操作记录日志，使用如下命令还原：

mysqlbinlog mysql-bin.000001 | mysql -u root -p
mysqlbinlog mysql-bin.000002 | mysql -u root -p
mysqlbinlog mysql-bin.000003 | mysql -u root -p
mysqlbinlog mysql-bin.000004 | mysql -u root -p

　　二进制记录日志虽然可以用来还原数据库，但是占用的空间也会比较大，生产环境中我们不可能一直保留二进制记录日志（同时也不建议关闭二进制日志记录），需要结合备份点来删除冗余的日志。为了正确的找到还原点，我们应该在数据库备份完成后，清除之前的二进制记录，后面还原的时候就可以按照日志文件的00000...编号依次还原。

　　附与二进制日志文件相关的命令：

#查看二进制日志文件列表
SHOW binary logs;

#查看当前正在写入的二进制日志文件
SHOW master status;

#查看二进制日志文件内容，二进制日志使用二进制格式存储，不能直接打开查看。如果需要查看二进制日志，必须使用 mysqlbinlog 命令
mysqlbinlog filename.number

# 使用 RESET MASTER 语句可以删除的所有二进制日志
RESET MASTER;

#每个二进制日志文件后面有一个 6 位数的编号，如 000001。使用 PURGE MASTER LOGS TO 语句，可以删除指定二进制日志的编号之前的日志
PURGE MASTER LOGS TO 'filename.number';

#使用 PURGE MASTER LOGS TO 语句，可以删除指定时间之前创建的二进制日志
PURGE MASTER LOGS TO 'yyyy-mm-dd hh:MM:ss';

#使用 SET 语句来暂停/开启二进制日志功能
SET SQL_LOG_BIN=0/1;