Mysql数据库安全和备份
一、用户权限认证
Mysql通过用户认证机制来和业务系统建立连接通道，当认证成功后，还需要通过访问控制模块来进行相关的权限验证，只有两次验证都通过后，才能继续执行SQL语句操作，这个过程涉及用户管理和访问控制两个模块。
用户管理模块用于管理登录用户信息、设置相关权限，Mysql系统表mysql.user存储了登录数据库的用户信息，当业务系统通过连接字符串参数尝试登录mysql时，首先会校验业务系统提供的用户身份，只有校验通过后才将控制权移交给访问控制模块。运行如下命令查看user信息，得到结果如图所示：
SELECT * FROM mysql.user;
我们可以看到， user表包含了40多个字段，记录了用户的相关权限信息，这些字段的解释如下：
| Select_priv | 用户是否可以通过SELECT命令选择数据。 |
| Insert_priv | 用户是否可以通过INSERT命令插入数据。 |
| Update_priv | 用户是否可以通过UPDATE命令修改现有数据。 |
| Delete_priv | 用户是否可以通过DELETE命令删除现有数据。 |
| Create_priv | 用户是否可以创建新的数据库和表。 |
| Drop_priv | 用户是否可以删除现有数据库和表。 |
| Reload_priv | 用户是否可以执行刷新和重新加载MySQL所用各种内部缓存的特定命令，包括日志、权限、主机、查询和表。 |
| Shutdown_priv | 用户是否可以关闭MySQL服务器。在将此权限提供给root账户之外的任何用户时，都应当非常谨慎。 |
| Process_priv | 用户是否可以通过SHOW PROCESSLIST命令查看其他用户的进程。 |
| File_priv | 用户是否可以执行SELECT INTO OUTFILE和LOAD DATA INFILE命令。 |
| Grant_priv | 用户是否可以将已经授予给该用户自己的权限再授予其他用户。 |
| References_priv | 某些未来功能的占位符；现在没有作用。 |
| Index_priv | 用户是否可以创建和删除表索引。 |
| Alter_priv | 用户是否可以重命名和修改表结构。 |
| Show_db_priv | 用户是否可以查看服务器上所有数据库。 |
| Super_priv | 用户是否可以执行某些强大的管理功能。 |
| Create_tmp_table_priv | 用户是否可以创建临时表。 |
| Lock_tables_priv | 用户是否可以使用LOCK TABLES命令阻止对表的访问/修改。 |
| Execute_priv | 用户是否可以执行存储过程。 |
| Repl_slave_priv | 用户是否可以读取用于维护复制数据库环境的二进制日志文件。 |
| Repl_client_priv | 用户是否可以确定复制从服务器和主服务器的位置。 |
| Create_view_priv | 用户是否可以创建视图。 |
| Show_view_priv | 用户是否可以查看视图或了解视图如何执行。 |
| Create_routine_priv | 用户是否可以更改或放弃存储过程和函数。 |
| Alter_routine_priv | 用户是否可以修改或删除存储函数及函数。 |
| Create_user_priv | 用户是否可以执行CREATE USER命令。 |
| Event_priv | 用户能否创建、修改和删除事件。 |
| Trigger_priv | 用户能否创建和删除触发器。 |
| ssl_type | 支持ssl标准加密安全字段 |
| ssl_cipher | 支持ssl标准加密安全字段 |
| x509_issuer | 支持x509标准字段 |
| x509_subject | 支持x509标准字段 |
| plugin | 进行用户连接时的密码验证插件，plugin创建外部/代理用户，默认值为mysql_native_password。 |
| password_expired | 密码是否过期 (N 未过期，y 已过期)。 |
| password_last_changed | 记录密码最近修改的时间。 |
| password_lifetime | 设置密码的有效时间，单位为天数。 |
| account_locked | 用户是否被锁定（Y 锁定，N 未锁定）。 |
| max_questions | 规定每小时允许执行查询的操作次数。 |
| max_updates | 规定每小时允许执行更新的操作次数。 |
| max_connections | 规定每小时允许执行的连接操作次数。 |
| max_user_connections | 规定允许同时建立的连接次数。 |
除了以上的用户基本权限信息外，还有其他权限信息，存储在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的连接，其参数说明如下
| MASTER_HOST | master 服务器的主机名（或IP地址） |
| MASTER_PORT | 设置 master 服务器中的MySQL实例端口号 |
| MASTER_USER | 连接到 master 主机复制账户所对应的用户名 |
| MASTER_PASSWORD | 连接到 master 主机复制账户所对应的用户名的密码 |
| MASTER_LOG_FILE |
MASTER_LOG_FILE 与 MASTER_LOG_POS 两个选项确定 slave 的 IO 线程下次开始执行时从 master 开始读取的位置坐标。 如果指定了 MASTER_LOG_FILE 或 MASTER_LOG_POS 中的任意一项，就不能再指定RELAY_LOG_FILE 或 RELAY_LOG_POS，也不能指定MASTER_AUTO_POSITION = 1。 |
| MASTER_LOG_POS | |
| RELAY_LOG_FILE | RELAY_LOG_FILE 与 RELAY_LOG_POS 两个选项确定 slave 的 SQL 线程下次开始执行时从relay_log 开始读取的位置坐标 |
| RELAY_LOG_POS | |
| MASTER_AUTO_POSITION | 使用 MASTER_AUTO_POSITION = 1，slave 服务器连接 master 服务器时将使用基于 GTID 的复制协议，此时需要开启gtid-mode= ON(在 my.cnf 中设置)。 |
| MASTER_CONNECT_RETRY | 重新连接到 master 时的超时等待时间，默认为60秒 |
| MASTER_RETRY_COUNT | 限制重新连接的次数,默认值是24 * 3600 = 86400 |
| MASTER_DELAY | 表示收到master的事件后多少s才开始执行数据同步操作，默认为0 |
| MASTER_BIND | 从服务器有多个IP的情况下使用，以确定使用哪一个 IP 连接到 master |
| IGNORE_SERVER_IDS | server_id_list，主要用于多主复制或环形复制的情况，处于复制链条中间的服务器异常时，可以跳过出问题的MySQL实例。 |
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 = 100M
以上的配置在数据库运行后，会在/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;
