批量sql
SELECT CONCAT('RENAME TABLE ',table_name,' TO ',UPPER(table_name),';') FROM information_schema.TABLES WHERE TABLE_SCHEMA='GTA_QDB' AND table_name<>'UTSUPLOG';
查看io
yum install dstat -y
dstat -M topio -d -M topbio
yum install sysstat -y
# %util 表示io饱和度
iostat -x nvme0n1 5
数据库日常巡检脚本
echo -e '\n\033[32m######非Innodb#####\033[0m'
/application/mysql/bin/mysql -ulambert -p -e "SELECT table_name,TABLE_SCHEMA, table_type, engine FROM information_schema.tables where engine <> 'InnoDB' and table_schema in ('echemi_db_v0_3','market_db_v1','buying_lead_db_v1');"
echo -e '\n\033[32m######没有主键#####\033[0m'
/application/mysql/bin/mysql -ulambert -p -e "select concat(table_schema,'.',table_name) from information_schema.tables where table_schema in ('echemi_db_v0_3','market_db_v1','buying_lead_db_v1');">/home/sa/lambert/xunjian/a.txt
t_tables=`cat /home/sa/lambert/xunjian/a.txt |tail -n +2`
for i in $t_tables
do
t_result=`/application/mysql/bin/mysql -ulambert -p -e "show index from $i where Key_name='PRIMARY';"`
[[ $t_result == '' ]]&&echo $i
done
echo -e '\n\033[32m######text类型#####\033[0m'
for i in $t_tables
do
t_result=`/application/mysql/bin/mysql -ulambert -p -e "show columns from $i where type='text';"`
[[ $t_result == '' ]]||echo $i
done
echo -e '\n\033[32m######列数大于50#####\033[0m'
for i in $t_tables
do
t_result=`/application/mysql/bin/mysql -ulambert -p -e "show columns from $i;"|wc -l`
[[ $t_result -gt 50 ]]&&echo $i
done
echo -e '\n\033[32m######大于1G的表#####\033[0m'
/application/mysql/bin/mysql -ulambert -p -e "select concat(table_schema,'.',table_name), (data_length/1024/1024) as data_mb from information_schema.tables where data_length >1000000000 and table_schema in ('echemi_db_v0_3','market_db_v1','buying_lead_db_v1');"
echo -e '\n\033[32m######索引占表四分之一的表#####\033[0m'
/application/mysql/bin/mysql -ulambert -p -e "select table_schema,table_name,data_length,index_length,(index_length/data_length) ratio from information_schema.tables where (index_length/data_length) >0.25 and table_schema in ('echemi_db_v0_3','market_db_v1','buying_lead_db_v1');"
系统优化
- numa关闭
1.bios方式进入时设置。改成节点交叉存取
2.在kernel行最后加上numa=off
[root@local host grub]#vim /etc/grub.conf
3.修改启动脚本
shell>vi $MYSQL_HOME/bin/mysqld_safe
...
cmd="/usr/bin/numactl --interleave all $cmd"
4.mysql 5.7里直接设置参数
(好像没啥卵用)
loose_innodb_numa_interleave=1
- swap使用率调小
sysctl vm.swappiness=10
cat /proc/sys/vm/swappiness
echo 'vm.swappiness=10'>> /etc/sysctl.conf
事务的隔离级别
- 1.查看
select @@tx_isolation;
- 2.修改
set global tx_isolation='read-committed';
注释：一般使用rc或者rr隔离级别，但是rc可以避免gap锁，默认是rr，日常中除特殊需求，可以调成rc
查看锁
show processlist; 查看现有的线程锁的信息
select * from information_schema.INNODB_TRX \G 查看有没有其他事务在工作
select * from information_schema.INNODB_LOCKS \G 查看现有锁的信息
select * from information_schema.innodb_lock_waits \G 看锁的id和被阻塞的id
show OPEN TABLES where In_use > 0; 看表级锁
select * from information_schema.INNODB_TRX;
select * from information_schema.innodb_lock_waits;
select * from information_schema.INNODB_LOCKS;
show OPEN TABLES where In_use > 0;
show full processlist;
show engine innodb status;
mysqlbinlog命令
mysqlbinlog --skip-gtids --database=crm_core --start-datetime="2017-05-31 10:00:00" --stop-datetime='2017-05-31 14:00:00' --start-position=27284 bin-log.00039[1-9]
主从延迟分析
1.dump线程（tps是否很高），网络传输，应用线程分析（从库io能力）
iostat -t 3
2.双一：没有特别需求，可以关闭
innodb_flush_log_at_trx_commit=1
sync_binlog=1
3.是否有备份（read lock）
show OPEN TABLES where In_use > 0;
4.ddl语句
跳过事务
(root@localhost) [(none)]>stop slave;
Query OK, 0 rows affected (3.27 sec)
(root@localhost) [(none)]>SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
Query OK, 0 rows affected (0.00 sec)
(root@localhost) [(none)]>start slave;
Query OK, 0 rows affected (0.01 sec)
5.大事务，长事务
- 大、长事务定义：
大事务：修改批量数据，要占用大量的undo页
长事务:开始一个事务一直不提交。这段时间操作过的表产生的undo数据都不能被删除，会一直被MVCC --长事务危害往往比大事务要大 - 判断事务
mysql> use information_schema;
mysql> select * from INNODB_TRX \G 看所有未提交事务
trx_started: 2016-08-21 13:43:54 跟现在的时间比，时间长的话就是长事务。
trx_rows_modified: 0 看事务修改的行数，多的话就是大事务
- 如何处理大事务？
不能删除，因为这样的话会回滚很长时间
[root@localhost ~]# ps -ef|grep mysqld
[root@localhost ~]# kill -9 17789 可以杀死mysql进程，但是风险非常的大
- 如何杀掉长事务？
通过查看事务ID，然后
mysql> select * from INNODB_TRX \G
trx_mysql_thread_id: 13
mysql> kill ID;
mysql> show processlist; 看所有会话
6.检查表主键
select DISTINCT TABLE_NAME as 没有主键
from information_schema.`COLUMNS` where TABLE_SCHEMA='test' and TABLE_NAME not in(SELECT DISTINCT TABLE_NAME
FROM information_schema.`COLUMNS` where COLUMN_KEY in ('MUL','PRI','uni') and TABLE_SCHEMA='test');
注意：没有主键产生的坑，挺多的。
