设置SQL执行超时时间：
SET GLOBAL MAX_EXECUTION_TIME=1000;
SET SESSION MAX_EXECUTION_TIME=1000;
select /*+ max_execution_time(10)*/ count(*) FROM xxx.tablename;
锁的查看及处理：
select * from information_schema.innodb_locks;select * from information_schema.innodb_trx;
select * from information_schema.innodb_lock_waits;
SHOW PROCESSLIST;
KILL thread_id;
mysqldump导入导出：
单库备份导出导入：
mysqldump -u root -p db3 > /mysql_backup/db3.sql
mysql -u root -p db3 < db3.sql
多库备份导出导入：
mysqldump -u root -p -B db1 db2 > db1_db2.sql
mysql -u root -p < db1_db2.sql （数据库要提前存在）
全库备份导出导入：
mysqldump -u root -p --all-databases > all.sql
mysql -u root -p < all.sql
单表备份导出导入：
mysqldump -u root -p db3 shop > db3_shop.sql
mysql -u root -p db3 < db3_shop.sql
多表备份导出导入：
mysqldump -u root -p db3 shop dept > db3_shop_dept.sql
mysql -u root -p db3 < db3_shop_dept.sql
备份选项：
mysqldump --single-transaction --lock-all-tables --master-data
#!/bin/bash DataBakDir=/mysql/backup/backup-db LogOutFile=/mysql/backup/backup-db/bak-db.log LogErrOutFile=/mysql/backup/backup-db/bak-db-err.log BinLogBakDir=/mysql/backup/backup-binlog MyCNF=/mysql/data/3306/my.cnf mysql_host=192.168.1.20 #主机 mysql_port=3306 #端口 mysql_user=root #用户 mysql_pass=123456 #密码 Date=`date +%Y%m%d` Begin=`date +"%Y-%m-%d %H:%M:%S"` cd $DataBakDir DumpFile="dbbackup-alldb-$Date.sql" GZDumpFile=dbbackup-alldb-$Date.sql.gz /mysql/app/mysql/bin/mysqldump -u${mysql_user} -p${mysql_pass} --single-transaction --master-data=2 --routines --flush-logs --flush-privileges --all-databases --log-error=$LogErrOutFile > $DumpFile /mysql/app/mysql/bin/mysqldump -u${mysql_user} -p${mysql_pass} --skip-lock-tables --databases performance_schema information_schema sys | gzip > dbbackup-per-inf-sys-$Date.sql.gz tar -zcvf $GZDumpFile $DumpFile $MyCNF Last=`date +"%Y-%m-%d %H:%M:%S"` #Function export user privileges mysql_exp_grants() { mysql -B -u${mysql_user} -p${mysql_pass} -N -P${mysql_port} $@ -e "SELECT CONCAT( 'SHOW CREATE USER ''', user, '''@''', host, ''';' ) AS query FROM mysql.user" | \ mysql -u${mysql_user} -p${mysql_pass} -N -P${mysql_port} -f $@ | \ sed 's#$#;#g;s/^\(CREATE USER for .*\)/-- \1 /;/--/{x;p;x;}' mysql -B -u${mysql_user} -p${mysql_pass} -N -P${mysql_port} $@ -e "SELECT CONCAT( 'SHOW GRANTS FOR ''', user, '''@''', host, ''';' ) AS query FROM mysql.user" | \ mysql -u${mysql_user} -p${mysql_pass} -N -P${mysql_port} -f $@ | \ sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/-- \1 /;/--/{x;p;x;}' } mysql_exp_grants > ./mysql_exp_grants_out_$Date.sql echo "data-backup---Start:$Begin;Complete:$Last;$GZDumpFile Out Complete!" >> $LogOutFile #find $BinLogBakDir -mtime +7 -name "*bin*.*" -exec rm -rf {} \; find $DataBakDir -mtime +1 -name "*.sql" -exec rm -rf {} \; find $DataBakDir -mtime +15 -name "*.gz" -exec rm -rf {} \;
定时事件：
先开启 event_schedulerCREATE EVENT [IFNOT EXISTS] event_name
ON SCHEDULE schedule
[ONCOMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE]
[COMMENT 'comment']
DO sql_statement;
schedule:
AT TIMESTAMP [+ INTERVAL INTERVAL]
| EVERY INTERVAL [STARTS TIMESTAMP] [ENDS TIMESTAMP]
INTERVAL:
quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}
例：
create event if not exists modify_column on schedule at timestamp '2021-08-25 01:02:00' do ALTER TABLE `table_name1` MODIFY COLUMN `column1` VARCHAR(20);
CREATE DEFINER=`root`@`%` EVENT `modify_column_test` ON SCHEDULE AT '2021-08-26 12:10:00' ON COMPLETION NOT PRESERVE ENABLE COMMENT '修改列的数据类型及大小' DO ALTER TABLE `table_name1` MODIFY COLUMN `column1` VARCHAR(20)
CREATE EVENT `DB1`.`event_0907` ON SCHEDULE EVERY '1' DAY STARTS '2022-09-07 00:30:00' ON COMPLETION NOT PRESERVE ENABLE DO BEGIN DELETE FROM table_name2 WHERE time <= date_sub( str_to_date( date_format(curdate(), '%m/%d/%Y'), '%m/%d/%Y %h:%i:%s' ), INTERVAL 8 DAY ); OPTIMIZE TABLE table_name2; END;
日志刷新：
mysql> FLUSH LOGS;
shell> mysqladmin flush-logs
shell> mysqladmin refresh
各种日志查看：
错误日志：
mysql> show variables like 'log_error';
通用查询日志：
mysql> show variables like '%general%';
mysql> set global general_log=1;（开启，0是关闭）
慢查询日志：
mysql> show variables like '%query%';
mysql> show global status like "%slow%";
二进制日志：
mysql> show variables like "%log_bin%";
[root@mysql /var/lib/mysql]# mysqlbinlog -vv mysql-bin.000001 [ | less ]
DDL日志：
mysql> show variables like '%ddl%';
将周数换算为具体日期：
获取当前日期所在周是今年的第几周（每周的第一天是周日，每周的最后一天是周六）：select YEARWEEK(CURDATE());
当前所在周的第一天：
select subdate(
curdate( ),
IF
( date_format( curdate( ), ' %w' ) = 0, 7, date_format( curdate( ), ' %w' ) ) - 0
) ;
当前所在周的最后一天：
select subdate(
curdate( ),
IF
( date_format( curdate( ), ' %w' ) = 0, 7, date_format( curdate( ), ' %w' ) ) - 6
);
获取时间：
前天的时间（interval 2 day）：
select date_sub(str_to_date(date_format(curdate(),'%m/%d/%Y'),'%m/%d/%Y %h:%i:%s'),interval 2 day);
Excel表格拼接SQL语句：
=CONCATENATE("update table set column2 = ",B2," where column1 = ",A1,";")
当内容超过255字符使用多条拼接：
=AP2&AQ2&AR2&AS2
MySQL分表：
新建表直接分表：
create table employees( id int not null, fname varchar(30), lname varchar(30), hired date not null default '1970-01-01', separated date not null default '9999-12-31', job_code int not null default 0, store_id int not null default 0 )engine=myisam default charset=utf8 partition by range(store_id)( partition p0 values less than (6), partition p1 values less than (11), partition p2 values less than (16), partition p3 values less than (21) );
现有的表进行分表更改：
（会锁表，资源消耗大，不能直接执行）
alter table table_name partition by range columns(time)( partition p0 values less than ('2019-12-31 23:59:59'), partition p1 values less than ('2020-12-31 23:59:59'), partition p2 values less than ('2021-12-31 23:59:59'), partition p_dec values less than MAXVALUE);
表的存储及信息：
重建统计信息：ANALYZE TABLE tablename1[,tablename2, ...];
清理碎片空间：OPTIMIZE TABLE tablename1[,tablename2, ...];
查看表占用大小：select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from information_schema.TABLES where table_schema='dbname' and table_name='tablename';
改为innodb引擎：alter table tablename engine=innodb;
查看表信息：show table status from gf_dev where name='tablename';
xtrabackup简易全量备份脚本：
#!/bin/bash # xtrabackup备份MySQL数据库数据 # 备份均为全量备份 echo "`date` -- 备份开始..." >> /database_back/backlog.log innobackupex --user=root --password='123456' /database_back/xtrabackup/full/ &>> /database_back/backlog.log if [ $? -eq 0 ] then echo "备份成功！" >> /database_back/backlog.log echo "----------`date`" >> /database_back/backlog.log echo "" >> /database_back/backlog.log echo "" >> /database_back/backlog.log else echo "备份失败，请查看具体情况；" >> /database_back/backlog.log echo "----------`date`" >> /database_back/backlog.log echo "" >> /database_back/backlog.log echo "" >> /database_back/backlog.log exit 1 fi # 使用rsync推送至备库主机 echo "`date` -- 同步传输开始..." >> /database_back/rsynclog.log rsync -av /database_back/xtrabackup/full 192.168.1.20:/database_back/xtrabackup/ &>> /database_back/rsynclog.log if [ $? -eq 0 ] then echo "rsync传输完成！" >> /database_back/rsynclog.log echo "----------`date`" >> /database_back/rsynclog.log echo "" >> /database_back/rsynclog.log echo "" >> /database_back/rsynclog.log else echo "rsync传输失败，请查看具体情况；" >> /database_back/rsynclog.log echo "----------`date`" >> /database_back/rsynclog.log echo "" >> /database_back/rsynclog.log echo "" >> /database_back/rsynclog.log exit 2 fi
行拆分转列：
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(' 字符串 ',' 分隔符 ',help_topic_id+1),',',-1) AS 字段命名 FROM mysql.help_topic WHERE help_topic_id < LENGTH(' 字符串 ')-LENGTH(REPLACE(' 字符串 ',' 分隔符 ',''))+1
select id,replace(text,'uId":"','') as uId from ( select id, substring_index(substring_index(t.text,'","',b.help_topic_id +1),'","',-1) as text from (select id,text from table_name limit 100) t inner join mysql.help_topic b on b.help_topic_id < (length(t.text) - length(replace(t.text,'","','')) +1) where t.text<> '') t1 where t1.text like '%uId%'
wokerbench不能update和delete：
Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.
SET SQL_SAFE_UPDATES = 0
数据库数据本地导入（MySQL8.0.21版本），当需要本地导入已有数据时，会提示错误信息，如：
ERROR 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides
ERROR 2068 (HY000): LOAD DATA LOCAL INFILE file request rejected due to rest
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
在MySQL8.0版本中，不能直接使用本地数据导入，除了需要设置客户端的本地数据导入
show variables like 'local_infile';
set global local_infile=on;
还需要在配置文件/etc/my.cnf中添加 local-infile ，来启用服务端的本地数据导入；
之后在重启mysqld服务后，使用本地命令登录MySQL客户端：
mysql --local-infile -u username -p
数据库无法安装：
请查看系统内是否有其他MySQL实例的安装
请查看mariadb是否有冲突
MySQL8.0+版本创建用户及授权
MySQL8+有新的安全要求，不能像之前的版本那样一次性创建用户并授权需要先创建用户，再进行授权操作；
8.0+版本若直接授权创建新用户会提示语法错误，并不会提示其真正不能创建的原因：
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'identified by 'Abc123456#'' at line 1
需要先将用户创建，之后再进行授权：
create user u1@'172.16.1.190' identified by 'MySQL123456+';
grant all on *.* to u1@'172.16.1.190';
MySQL做HA高可用集群时安装软件报错：需要：libmysqlclient.so.18()(64bit)
解决：安装Percona-XtraDB-Cluster
# wget http://www.percona.com/redir/downloads/Percona-XtraDB-Cluster/5.5.37-25.10/RPM/rhel6/x86_64/Percona-XtraDB-Cluster-shared-55-5.5.37-25.10.756.el6.x86_64.rpm
# rpm -ivh Percona-XtraDB-Cluster-shared-55-5.5.37-25.10.756.el6.x86_64.rpm
MySQL启动时找不到sock文件
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/home/mysql/data/mysqldata1/sock/mysql.sock' (2)
找不到指定路径要注意my.cnf中sock文件的配置，若没有则重新生成：
mysqld_safe --user=mysql --basedir=/usr/local/mysql &
（有的时候也可能时延迟，刚开机会慢）
MySQL8.0以上连接遇到Authentication plugin ‘caching_sha2_password’ cannot be loaded错误
该错误由于MySQL的密码机制变换，而其他的应用程序依然使用旧版5.x版本的加密方式所引起的，需要更改加密方式，并重新更改一下密码：
root@localhost : mysql 11:30:20> alter user 'user5'@'192.168.3.%' identified by 'user5' password expire never;
Query OK, 0 rows affected (0.00 sec)
root@localhost : mysql 11:31:01> alter user 'user5'@'192.168.3.%' identified with mysql_native_password by 'user5';
Query OK, 0 rows affected (0.00 sec)
root@localhost : mysql 11:31:48> flush privileges;
查看错误日志发现innodb引擎损坏，可能是误删除了相关的文件导致
恢复innodb参考官方：https://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html
数据库备份 --> 停库
修改/etc/my.cnf配置文件
innodb_force_recovery = 6
4及以上可能会导致数据文件永久损坏，慎用；
从低等级1开始不断尝试启动，不能则逐级增加，将能导出的数据尽量导出；
0：正常启库而不强制恢复
1(SRV_FORCE_IGNORE_CORRUPT):忽略检查到的corrupt页。
2(SRV_FORCE_NO_BACKGROUND):阻止主线程的运行，如主线程需要执行full purge操作，会导致crash。
3(SRV_FORCE_NO_TRX_UNDO):不执行事务回滚操作。
4(SRV_FORCE_NO_IBUF_MERGE):不执行插入缓冲的合并操作。
5(SRV_FORCE_NO_UNDO_LOG_SCAN):不查看重做日志，InnoDB存储引擎会将未提交的事务视为已提交。
6(SRV_FORCE_NO_LOG_REDO):不执行前滚的操作。
插入数据报错：ERROR 1697 (HY000): VALUES value for partition 'p0' must have type INT
该问题由于分表的列指定数据类型不对，range需要int类型值，可以指定列及列名；
partition by range COLUMNS(time)
binlog恢复报错：ERROR 1781 (HY000) at line 13: @@SESSION.GTID_NEXT cannot be set to UUID:NUMBER when @@GLOBAL.GTID_MODE = OFF.
该问题由于数据库是GTID模式，恢复需要关闭GTID模式，但是关闭是不够得，还需要设置参数；
set @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;
Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.
MySQL安全等级高的原因；
set sql_safe_updates =0;
