mysql疑难杂症记录

mysql主从延迟如何解决

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


mysqldump无法连接数据库

配置文件中添加mysqldump命令的mysql.sock路径
[mysqldump]
socket&＃61;/data/mysql/mysql.sock
sql_modeselect &＃64;&＃64;sql_mode;#/etc/my.cnf中设置默认&＃xff0c;并且删除ONLY_FULL_GROUP_BY
sql_mode &＃61; 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&＃61;/data/mysql/mysql.sock


查看mysql连接数

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


查看my.cnf目录

查看mysql默认读取my.cnf的目录
mysql --help|grep &＃39;my.cnf&＃39;


锁解表

FLUSH TABLES WITH READ LOCK
unlock tables


查看引擎

show engines


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

select sum(DATA_LENGTH) from information_schema.TABLES where information_schema.TABLES.TABLE_SCHEMA&＃61;&＃39;database&＃39; and information_schema.TABLES.TABLE_NAME&＃61;&＃39;table&＃39;


表结构

select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,COLUMN_TYPE,COLUMN_COMMENT from information_schema.columns where TABLE_SCHEMA&＃61;&＃39;table&＃39;


查看数据库、表大小

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


查看实例下所有库大小

select table_schema, sum(data_length&＃43;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&＃43;index_length)/1024/1024) as all_mb, table_rows from information_schema.tables where table_schema &＃61; &＃39;bmdb&＃39; GROUP BY data_mb desc;


删数据后磁盘未释放

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