
支持阿里云数据库,希望能为更多云用户服务;之前支持阿里巴巴集团数据库,承担了业务的数据库设计,架构优化,异地多活,库存热点优化,稳定性保障等工作,经历数次双11,有丰富的实战和优化经验和对业务的敏感度。关注数据库、数据仓库、大数据等领域。
事前预防 事前预防大于事后处理,对数据库的管理来说,更是要防患于未然。您可以通过以下方式进行数据安全的事前预防: 权限隔离 环境隔离 内核回收站 设置实例备份规则 使用数据库管理DMS 使用备份服务DBS 权限隔离 您可以通过设置高权限账号和普通账号进行数据权限隔离。包括读写权限,DML权限,DDL权限,创建表,修改表,等,除了只读权限之外都需要进行管理和关注。权限说明见下图: 可在数据库管理界面配置不同的权限。 更多信息请参见创建数据库账号和授权服务账号帮助文档。 环境隔离 对环境的隔离也是同样重要,测试环境、开发环境、生产环境要分开,不要共用一套数据库。 内核回收站 第三个预防的重点是内核上面的回收站,当执行drop时,与表无关的对象会做保留,防止误删。MySQL 8.0的回收机制执行DROP TABLE/DATABASE语句时,只保留相关的表对象,并移动到专门的recycle bin目录中。其它对象的删除策略如下: 如果是与表无关的对象,根据操作语句决定是否保留,不做回收。 如果是表的附属对象,可能会修改表数据的,做删除处理,例如Trigger和Foreign key。 但Column statistics不做清理,随表进入回收站。 MySQL 8.0的清理机制回收站会启动一个后台线程,来异步清理超过recycle_bin_retention时间的表对象。在清理回收站表的时候,如果遇到大表,会再启动一个后台线程异步删除大表。具体请参考帮助文档。 设置实例备份规则 还可以对数据进行备份来预防潜在的风险。实例备份规则为默认按照整实例进行备份和可以按照表进行备份。如下图所示:具体操作,参见帮助文档。 使用数据库管理DMS进行安全预防 在事前预防中,阿里云提供强大的数据库管理DMS服务和备份恢复DBS服务。DMS是一款强大的数据库管理和设计工具,直观的GUI开发环境让用户简单管理多达25种数据库,包括表结构设计、数据操作、数据开发及性能诊断优化等操作。DMS通过研发规范、研发流程、权限控制(可控制到列级别、行级别)、操作拦截、数据脱敏、流程审批、安全审计及变更回滚等功能有效保障数据安全。 DMS是一种新的工作模式: 多种角色参与整个研发流程,包括管理员、DBA、安全管理员及开发、测试、产品、运营、财务等。 可消除集中管控的人员瓶颈、减少沟通成本、规范化管理减少故障误操作、变更周 期可控、业务迭代速度快、提升研发效率。 对接企业账号系统,数据安全解决方案-从“审计”到“拦截”。可控的SQL操作包含:全局权限管控、风险识别、数据脱敏、操作审计、安全规则引擎。可进行灵活的安全规则设置:变更稳定-覆盖数据变更、结构变更、代码发布的全流程把控。尤其是对于大批量的数据变更,大表结构变更等,会有针对性的流程把控和精细化的管理,在不影响业务的情况下,提升整体的安全性。 使用阿里云备份服务DBS 备份服务DBS为数据库提供连续数据保护、低成本的备份服务。它可以为多种环境的数据提供强有力的保护,包括企业数据中心、其他云厂商、混合云及公共云。RDS备份的数据是可见的,如果有数据分析的需求还可以用云原生数据湖进行支持,整个产品生态是打通和健全的。产品功能: 可恢复任1秒数据实例、单库、多表、单表,备份及恢复粒度自由选择,RTO大幅降低,备份有效性实时验证。 安全加密无论传输,还是存储使用最严苛的加密技术,保护用户数据安全。 低成本,免运维低成本异地备份,让企业快速满足三级等保要求,无需为灾备提供前期成本,配置到运行只需几分钟。 多环境支持支持自建IDC、云上自建数据库、其他云环境的数据库备份。 备份服务DBS与RDS自带备份的区别: 针对RDS数据库,DBS提供转储备份和逻辑备份,满足RDS客户的异地备份和灵活备份诉求。 针对RDS数据库,RDS提供物理备份,满足RDS客户的本地备份和快速恢复诉求。 DBS控制台入口:详细内容请参见帮助文档。 事中谨慎 即使非常谨慎,数据库在现场运行时是不可能不对数据进行查询、修改和删除的,我们不能因噎废食,仍然要进行这些看似有风险的操作,但是我们可以采取以下措施,让整个数据库的使用过程更加安全。 使用内核回收站 操作隔离 操作备份 使用内核回收站 如果要使用DROP语句,可以在操作之前可以先打开回收站,回收站是可以进行清理和查询的。回收站会有id与原表相对应,并会对过程进行记录。以下示例是操作以MySQL 8.0为例进行说明: SET SESSION RECYCLE_BIN=ON;--------------------打开会话级回收站 CALL DBMS_RECYCLE.SHOW_TABLES();--------------看回收站里的表 DROP TABLE tablename;--------------------------------------------------删除表 CALL DBMS_RECYCLE.PURGE_TABLE (‘<TABLE>’);-----清理回收站里的表 表示例: mysql> call dbms_recycle.show_tables(); +-----------------+---------------+---------------+--------------+---------------------+---------------------+ | SCHEMA | TABLE | ORIGIN_SCHEMA | ORIGIN_TABLE | RECYCLED_TIME | PURGE_TIME | +-----------------+---------------+---------------+--------------+---------------------+---------------------+ | __recycle_bin__ | __innodb_1063 | product_db | t1 | 2019-08-08 11:01:46 | 2019-08-15 11:01:46 | | __recycle_bin__ | __innodb_1064 | product_db | t2 | 2019-08-08 11:01:46 | 2019-08-15 11:01:46 | | __recycle_bin__ | __innodb_1065 | product_db | parent | 2019-08-08 11:01:46 | 2019-08-15 11:01:46 | | __recycle_bin__ | __innodb_1066 | product_db | child | 2019-08-08 11:01:46 | 2019-08-15 11:01:46 | +-----------------+---------------+---------------+--------------+---------------------+---------------------+ 4 rows in set (0.00 sec) 更多信息请参见帮助文档。 操作隔离 如果我们要执行DML类的操作,在执行变更的时候最好走一下变更的流程。如果缺少这样流程,大批量的操作语句最好有多人review。注意:1、业务相关的操作:一定带WHERE条件语句,把执行变更的范围变到最小。2、运维/后台相关操作 注意读写分离 针对数据统计分析类/定时任务类/大批量操作类 尽量避免业务高峰 多人Review保证正确性 操作备份 最好在每次操作执行之前,都把数据备份一遍,这样即使真正出现问题,也可以执行回滚。备份方法每次执行DML/DDL之前,导出执行前的SQL,作为回滚脚本。运维/后台相关操作 立即执行默认开启为“是”,提交即刻执行;可按需指定在业务特定时间执行。 事务控制默认不开启为“否”,逐条提交,遇到失败则终止但不回滚;开启该功能遇到失败则全部回滚(仅限DML,DDL不在此范围内)。 备份数据默认开启为“是”,针对update、delete操作进行将要影响数据的全记录行,insert脚本生成附件。 帮助文档 事后恢复 及时我们非常谨慎,也不能百分百避免问题的发生,如果真的发生问题了,我们可以通过以下操作来进行事后恢复。 DMS工单操作历史。 DMS数据追踪。 内核回收站。 RDS克隆实例。 RDS库/表恢复。 DBS恢复。 DMS工单操作历史 如果您使用了阿里云数据管理DMS平台,可以使用DMS查看工单操作历史。 使用场景 MySQL 5.5/5.6/5.7/8.0DELETE/UPDATE/INSERT需要管控的数据库实例开通了DMS安全协同管控模式 恢复方法 对于变更执行后出现异常不符合诉求需要回滚的,可以直接在工单页面内,选择右侧>工单操作历史>查看备份,找到备份文件。 下载备份脚本下载,做相应处理。 确认数据满足需求后,重新提交变更工单。 更多内容请参见帮助文档。 DMS数据追踪 如果您不是通过DMS进行的变更,也可以通过DMS进行数据的追踪,逆向解析Binlog,本地Binlog和本地清理后到OSS上的Binlog都可以追踪。(前提在binlog仍然存在,一般云数据库Binlog保留周期为7天,如需更长时间可在RDS控制台按需调整) 使用场景 MySQL 5.5/5.6/5.7/8.0 DELETE/UPDATE/INSERT 少量 功能 在线搜索日志内容,无需手工下载Binlog。 支持数据的插入/更新/删除日志搜索,无需手工解析Binlog。 支持逐条数据恢复,无需手工生成回滚语句。 支持的Binlog OSS Binlog(RDS会定时将Binlog备份到OSS上)。 本地热Binlog(数据库服务器上Binlog)。 操作示例如下图所示: 更多内容请参见帮助文档。示例—少量数据追踪操作示例如下图所示:更多内容请参见帮助文档。 从回收站恢复 内核支持从回收站进行恢复,我们只需要确认回收站中有我们想要的表。 使用场景 MySQL 8.0 DROP TABLE/SCHEMA 其它对象的删除策略是: 与表无关的对象,比如 procedure,根据操作语句决定是否保留,不做回收。 表的附属对象,比如 trigger,Foreign key,column statistics等,只要存在可能修改表数据的,做删除,比如 trigger,Foreign key。 但columns statistics不做清理,随表进入回收站。 找回方法 查看回收站表 CALL DBMS_RECYCLE.SHOW_TABLES();--------------看回收站里的表。 找回方法 CREATE TABLE SRC_SCHEMA.SRC_TABLE LIKE SCHEMA.TABLE;-------------------- 加粗部分是要替换的内容。 INSERT INTO SRC_SCHEMA.SRC_TABLE SELECT * FROM SCHEMA.TABLE; ----- 加粗部分是要替换的内容。 示例—从回收站恢复T1的数据 mysql> call dbms_recycle.show_tables(); +-----------------+---------------+---------------+--------------+---------------------+---------------------+ | SCHEMA | TABLE | ORIGIN_SCHEMA | ORIGIN_TABLE | RECYCLED_TIME | PURGE_TIME | +-----------------+---------------+---------------+--------------+---------------------+---------------------+ | __recycle_bin__ | __innodb_1063 | product_db | t1 | 2019-08-08 11:01:46 | 2019-08-15 11:01:46 | | __recycle_bin__ | __innodb_1064 | product_db | t2 | 2019-08-08 11:01:46 | 2019-08-15 11:01:46 | | __recycle_bin__ | __innodb_1065 | product_db | parent | 2019-08-08 11:01:46 | 2019-08-15 11:01:46 | | __recycle_bin__ | __innodb_1066 | product_db | child | 2019-08-08 11:01:46 | 2019-08-15 11:01:46 | +-----------------+---------------+---------------+--------------+---------------------+---------------------+ 4 rows in set (0.00 sec) mysql>create table product_db.t1 like __recycle_bin__. __innodb_1063; mysql>insert into product_db.t1 select * from __recycle_bin__. __innodb_1063; 克隆实例 如果您还没有使用DMS,也可以用控制台上实例的备份来恢复,克隆示例支持按照时间点或备份集恢复,最安全的方式建议恢复到一个新实例,不在原实例原地恢复。 使用场景 通过备份克隆出整个实例。 所有误操作(DDL/DML)。 还原方式 指定时间点。 指定备份集。 操作步骤 恢复到一个新实例。 验证数据准确性。 将数据迁回原实例。 操作示例如下图所示:更多内容请参见帮助文档。 库/表级别恢复 如果您之前设置了库/表级别备份的话,您的误操作如果只是一个库或者表,那么您可以进行库/表级别恢复,但是库/表级别恢复有很多限制。 使用场景 通过备份指定恢复误删的数据库或表。 所有误操作(DDL/DML)。操作示例如下图所示: 限制事项 必须打开库/表级别备份。 每次最多选择50个库/表。 运行中且没有被锁定。 如果要按时间点进行恢复,需要确保日志备份已开启。 若要按备份集恢复,则原实例必须至少有一个备份集。 更多内容请参见帮助文档。 从DBS恢复 如果您之前选择了DBS备份,您就可以通过DBS进行事后恢复。 使用场景 支持整个实例、多个数据库、单个数据库、多张表或一张表恢复。 支持秒级任意时间点恢复,并且用户可以灵活选择恢复对象。 操作示例如下图所示: 可以进行选择恢复操作的时间点等操作配置,操作示例如下图所示:
问题描述 有开发问我这样一个问题: mysql> select * from aaa; +----+---------------------+----------+---------------------+ | id | dt | name | dtt | +----+---------------------+----------+---------------------+ | 1 | 2019-01-14 18:15:39 | aaaaaaaa | 2019-01-30 17:14:08 | +----+---------------------+----------+---------------------+ 1 row in set (0.00 sec) mysql> select * from bbb; +----+---------------------+----------+ | id | dt | name | +----+---------------------+----------+ | 1 | 2019-01-14 18:19:19 | aaaaaaaa | | 2 | 2019-01-14 18:20:49 | aaaaaaaa | +----+---------------------+----------+ 2 rows in set (0.00 sec) mysql> select * from aaa where id in (select id from bbb where dtt<now()); +----+---------------------+----------+---------------------+ | id | dt | name | dtt | +----+---------------------+----------+---------------------+ | 1 | 2019-01-14 18:15:39 | aaaaaaaa | 2019-01-30 17:14:08 | +----+---------------------+----------+---------------------+ 1 row in set (0.00 sec) 上面内容里dtt这个字段在bbb表中并不存在,但是在外表中存在,但是为什么不报错反而查出来结果了呢? 原因 我请PostgreSQL的同事也做了同样的操作,也是不报错的;这个在SQLServer和Oracle里面同样成立,why?一般规则是,语句中的列名由FROM子句中引用的表隐式地限定在同一级别。如果子查询的FROM子句中引用的表中不存在列,则由外部查询的FROM子句中引用的表隐式地限定列。即:在块结构语言计算子查询时,它开始在本地查找以解析列名。如果失败,则转到外部范围,直到找到具有该名称的列或失败为止。 风险 如果有开发这样执行delete语句,可能会导致全表被误删! mysql> delete from aaa where id in (select id from bbb where dtt<now()); 正确又安全的写法 带上表名 mysql> select aaa.* from aaa where aaa.id in (select bbb.id from bbb where bbb.dtt<now()); mysql> ERROR 1054 (42S22): Unknown column 'bbb.dtt' in 'where clause' 参考 https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms178050(v=sql.105) https://social.msdn.microsoft.com/Forums/sqlserver/en-US/4cfebb2e-6fdf-4ebf-9a6f-ee1911615eba/query-returning-wrong-data-even-inner-subquery-has-syntax-error?forum=transactsql
背景 有同学问在RDS MySQL 5.6在timestamp 设置为 not null 并且SQL模式是严格模式时,仍然可以插入空值,理论上应该有报错,是不是RDS的bug? 环境 MySQL 5.6 5.7 8.0,在RDS和自建数据库上都会遇到 现象 从上图中可以看到,在timestamp设置为not null时会自动补齐: DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 在客户的自建环境中timestamp类型没有自动补齐,在严格模式下会报错;在RDS MySQL的环境中datetime类型没有自动补齐,,在严格模式下会报错: 溯源 If the explicit_defaults_for_timestamp system variable is disabled, TIMESTAMP columns by default are NOT NULL, cannot contain NULL values, and assigning NULL assigns the current timestamp. To permit a TIMESTAMP column to contain NULL, explicitly declare it with the NULL attribute. In this case, the default value also becomes NULL unless overridden with a DEFAULT clause that specifies a different default value. DEFAULT NULL can be used to explicitly specify NULL as the default value. (For a TIMESTAMP column not declared with the NULL attribute, DEFAULT NULL is invalid.) If a TIMESTAMP column permits NULL values, assigning NULL sets it to NULL, not to the current timestamp.在explicit_defaults_for_timestamp值为OFF时,会自动补齐上文中的内容,而客户的环境里面explicit_defaults_for_timestamp是ON的,所以跟RDS MySQL结果有差异,跟客户说过后,验证就是explicit_defaults_for_timestamp值差异导致。 结论 每一件不合理的事情背后,必然有一个自己还未了解的合理原因.
打开performance_schema 控制台可开 打开内存监控 update performance_schema.setup_instruments set enabled = 'yes' where name like 'memory%'; 查看监控 select * from sys.x$memory_by_host_by_current_bytes ; select * from sys.x$memory_by_thread_by_current_bytes ; select * from sys.x$memory_by_user_by_current_bytes ; select * from sys.x$memory_global_by_current_bytes ; select * from sys.x$memory_global_total ; event_name内存事件名称。 current_count事件发生的总次数。 current_alloc事件尚未释放的已分配字节的当前数目。 current_avg_alloc为事件的每个内存块分配的当前字节数。 high_count分配给事件的内存块数量的最高标记。 high_alloc为事件分配的字节数的高水位标记。 high_avg_alloc为事件分配的每个内存块的平均字节数的最高标记。 参考 http://www.innomysql.com/mysql-5-7-oom%E9%97%AE%E9%A2%98%E8%AF%8A%E6%96%AD-%E5%B0%B1%E6%98%AF%E8%BF%99%E4%B9%88%E7%AE%80%E5%8D%95/ https://dev.mysql.com/doc/refman/5.7/en/sys-memory-global-by-current-bytes.html https://www.percona.com/blog/2018/06/28/what-to-do-when-mysql-runs-out-of-memory-troubleshooting-guide/
前言 很多用户经常会问,我购买的连接是2000,怎么活跃了连接到了500就报警啊?就响应慢了啊?通过命令我们来看下关于连接数有多少种: mysql> show global variables like "max_connections"; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 1800 | +-----------------+-------+ mysql> show global status like "%Threads%"; +------------------------------------------------+-------+ | Variable_name | Value | +------------------------------------------------+-------+ | Threads_cached | 3 | | Threads_connected | 0 | | Threads_created | 7 | | Threads_rejected | 0 | | Threads_running | 2 | +------------------------------------------------+-------+ 11 rows in set (0.00 sec) 总连接数 客户购买的DB连接数是这个。max_connections,允许同时连接DB的客户端的最大线程数。如果客户端的连接数超过了max_connections,应用就会收到“too many connections”的错误。 下面是线程的状态信息: 已经创建的连接数 Threads_created是为处理连接而创建的线程数。再明确一点来说是连接到DB的,客户端的线程数。它包含Threads_running。 如果Threads_created很大,可能需要调整thread_cache_size。线程cache命中率=Threads_created/Connections,cache命中率当然越大越好,如果命中率较低,可以考虑增加thread_cache_size。 已经连接的连接数 Thread_connected当前打开的连接数。 活跃连接数 Threads_running官方的说法是“没有sleep的线程数”。顾名思义是:在DB端正在执行的客户端线程总数。Server端保持这些连接同时客户端等待回复。有些线程可能消耗CPU或者IO,有些线程可能啥也没做单纯等表锁或行锁释放。当DB执行完这个线程,客户端收到回复,线程的状态就会从"running" 变成 "connected". 如果发现活跃链接数突然增高,通常是以下原因: 应用缓存失效 突发流量 参考 https://dev.mysql.com/doc/refman/5.6/en/server-status-variables.html#statvar_Threads_running https://dba.stackexchange.com/questions/176679/what-is-the-difference-between-threads-connected-and-threads-running