mysql疑难杂症记录

mysql主从延迟如何解决

mysql主从问题
#之前碰到过，主从延迟从根本上上是无法解决的，只能优化和业务上调整
1网络（内网一般不需要考虑）
2配置（从库配置比主库高）
3sql优化，减少大并发写操作
4业务层面，核心业务强制走主库（这个问题我还问过阿里的RDS，阿里云也无法保证延迟完全没有，只能保证到1秒一下）

mysqldump无法连接数据库

配置文件中添加mysqldump命令的mysql.sock路径
[mysqldump]
socket=/data/mysql/mysql.sock
sql_mode

select @@sql_mode;

#/etc/my.cnf中设置默认，并且删除ONLY_FULL_GROUP_BY
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

mysqldump 无法连接数据库

#配置文件中添加mysqldump命令的mysql.sock路径
[mysqldump]
socket=/data/mysql/mysql.sock

查看mysql连接数

#查看线程数，连接数
show status like 'Threads%';
show STATUS LIKE '%Connection%';  
#最大连接数
show VARIABLES LIKE '%max_connections%';  
#当前线程状态
show PROCESSLIST;  
show FULL PROCESSLIST;  

查看my.cnf目录

查看mysql默认读取my.cnf的目录
mysql --help|grep 'my.cnf'

锁解表

FLUSH TABLES WITH READ LOCK
unlock tables

查看引擎

show engines  

查看mysql数据库大小、表大小、数据大小、索引大小

select sum(DATA_LENGTH) from information_schema.TABLES where information_schema.TABLES.TABLE_SCHEMA='database' and information_schema.TABLES.TABLE_NAME='table'

表结构

select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,COLUMN_TYPE,COLUMN_COMMENT from information_schema.columns where TABLE_SCHEMA='table'

查看数据库、表大小

select concat(round(sum(data_length/1024/1024),2),'MB') as data_length_MB,concat(round(sum(index_length/1024/1024),2),'MB') as index_length_MB from tables where table_schema='库名' and table_name = '表名';

查看实例下所有库大小

select table_schema, sum(data_length+index_length)/1024/1024 as total_mb, sum(data_length)/1024/1024 as data_mb, sum(index_length)/1024/1024 as index_mb,count(*) as tables, curdate() as today from information_schema.tables group by table_schema order by 2 desc;  

查看某个库中的所有表占用并按表大小倒叙

select table_name, (data_length/1024/1024) as data_mb , (index_length/1024/1024)   as index_mb,((data_length+index_length)/1024/1024) as all_mb, table_rows from information_schema.tables where table_schema = 'bmdb' GROUP BY data_mb desc;  

删数据后磁盘未释放

#MySQL删除数据几种情况以及是否释放磁盘空间:
#缩表
optimize table  表名 
#立刻释放磁盘空间 ，不管是 Innodb和MyISAM ;
drop table table_name 
#立刻释放磁盘空间 ，不管是 Innodb和MyISAM 。truncate table其实有点类似于drop table 然后creat,只不过这个create table 的过程做了优化，比如表结构文件之前已经有了等等。所以速度上应该是接近drop table的速度;
truncate table table_name 
#删除表的全部数据,对于MyISAM 会立刻释放磁盘空间 （应该是做了特别处理，也比较合理）,InnoDB 不会释放磁盘空间;
#对于delete from table_name where xxx带条件的删除, 不管是innodb还是MyISAM都不会释放磁盘空间;
#delete操作以后使用optimize table table_name 会立刻释放磁盘空间。不管是innodb还是myisam 。所以要想达到释放磁盘空间的目的，delete以后执行optimize table 操作。
#delete from表以后虽然未释放磁盘空间，但是下次插入数据的时候，仍然可以使用这部分空间。 
delete from table_name