===============================mysql维护===============================
// 查看mysql全局变量(global)值
sql: show global variables like '%tmp_table_size%'
// 根据id结束慢查询sql语句
sql: show processlist;
sql: kill [id]
// 显示`show processlist显示不全`的sql语句
sql: SELECT `INFO` FROM information_schema.`PROCESSLIST`;
// 20条慢查询
/usr/local/mysql/bin/mysqldumpslow -s c -t 20 /tmp/slowquery.log
// 修复损坏的mysql表
cmd: myisamchk -f -o ./tabname.MYI
// 显示系统支持的存储引擎
sql：SHOW ENGINES;
// 修改mysql表引擎
sql：ALTER TABLE uchome_log TYPE = innodb ;
===============================mysql导入和导出===============================
// mysql 导入
cmd: /usr/local/mysql/bin/mysql -uroot -p --default-character-set=utf8 --force db_name < db_name.sql
或者
sql: source ./db_name.sql
// mysql 导出库
cmd: mysqldump -u root -p db_name > db_name.sql
// mysql 导出表
cmd: mysqldump -u root -p db_name tab_name > db_name.tab_name.sql
// mysqldump 导出并压缩(gzip)
cmd: mysqldump -hlocalhost -umyuser -pmypass --databases 'mydb' | gzip --fast > /home/jom_ch/mydb.gz
// 仅导出指定表
cmd: mysqldump -umyuser -pmypass --tables mydb tab1 tab2 > /home/jom_ch/anlytabs.sql
// 导出指定字段到文件
sql: select title,message into outfile '/home/chenghui/fields.txt' fields terminated by ',' optionally enclosed by '"' lines terminated by '/n' from tab_message;
// 清空mysql日志
sql: reset master;
===============================mysql语句===============================
// 添加索引
sql: ALTER TABLE `tabname` ADD INDEX (`username`);
// 删除索引
sql: ALTER TABLE `tabname` DROP INDEX `username`;
// 指定 AUTO_INCREMENT 起始值
sql: alter table mytab AUTO_INCREMENT=1000;
// 格式化unix时间戳
sql: select FROM_UNIXTIME( timer, '%Y%m%d' ) from `tabname` limit 1
// 字符串替换，把search替换成replace
sql: UPDATE table SET field_name=REPLACE(field_name, 'search', 'replace');
// 正则匹配字符串
sql: SELECT * FROM `uchome_space` WHERE username REGEXP "^[a-z]{4}[0-9]{4}$"
