mysql数据库运维常见问题_RDS数据库运维常用工具及常见问题

常用工具

1、日志管理、慢日志

2、CloudDBA功能

3、数据管理DMS平台

4、查看表空间大小select table_name, round(DATA_LENGTH/1024/1024/1024, 2) as data, round(INDEX_LENGTH/1024/1024/1024, 2) as idx_data, table_rows from tables where table_schema = 'exe_sf_p' order by data desc;

常见问题

1、Table 'exe_sf_t/#sql-ib27027' already exists

2、Lock wait timeout exceeded; try restarting transactionSELECT * FROM information_schema.innodb_trx order by trx_started asc;

kill threadid删除Lock住最久的那个线程

4、语法效率问题宣导

参数化

查询字段索引

JOIN、IN

归档、分表：

条件中尽量能够过滤一些行将驱动表变得小一点，用小表去驱动大表

右表的条件列一定要加上索引(主键、唯一索引、前缀索引等)，最好能够使type达到range及以上(ref,eq_ref,const,system)

一般不要用left join

explain

5、MySql Host is blocked because of many connection errors

6、主从同步延迟

show processlist

show slave status

重启RDS从库，避免RDS从库同步假死

7、The table '/home/mysql/data3003/tmp/#sql_25c8_2' is full

修改RDS的tmp_table_size参数

10、too many connections

show variables like '%max_connections%';

set global max_connections=1400;

11、The MySQL server is running with the --read-only option