MySQL 备忘录

用户和权限

/*进入mysql*/
use mysql;
/*创建用于复制操作的用户*/
create user 'test'@'localhost' identified with mysql_native_password by 'test';
/*从节点分片授权*/
GRANT REPLICATION SLAVE ON *.* TO 'test'@'localhost';
/*授权*/
grant all privileges on *.* to 'test'@'localhost';
/*刷新授权表信息*/
FLUSH PRIVILEGES;

修改用户密码

ALTER USER 'root'@'localhost' identified with mysql_native_password BY '123456';

查看数据库用户

select user,host from mysql.user;

数据库操作

新建

CREATE DATABASE IF NOT EXISTS demodb DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

为数据库新建用户并赋权限

/*创建新的用户*/
create user 'demo'@'%' identified with mysql_native_password by 'demo001';
/*给用户赋数据库权限*/
GRANT ALL PRIVILEGES ON demodb.* TO 'demo'@'%';
flush privileges;

字符集

查看字符集

show variables like 'character_set%';

修改字符集

set character_set_client=gbk;

备份

# 备份MySQL里的全部数据库
mysqldump -u root -p 123456 --all-databases > c:\\all_database_fullbak.sql
# 备份MySQL数据库
mysqldump -u root -p 123456 kcgl> c:\\database_fullbak.sql 
# 备份MySQL数据库
mysqldump  -h hostname -u username -p password -database databasename > backupfile.sql
# 直接将MySQL数据库压缩备份
mysqldump -h hostname -u username -p password -database databasename | gzip > backupfile.sql.gz

还原

正常情况

# Bin目录下还原MySQL数据库的命令
mysql -u username -p password databasename < backupfile.sql
# 还原MySQL数据库的命令
mysql -h hostname -u username -p password databasename < backupfile.sql
# 还原压缩的MySQL数据库
gunzip < backupfile.sql.gz | mysql -u username -p password databasename

全备份还原单库

1. 从全备份文件中将需要的库的建表语句和INSERT数据拿出来，然后再导入

   sed -n '/^-- Current Database: `db_test001`/,/^-- Current Database: `/p' all_database_fullbak.sql > db_test001.sql

2. 导入库中

   mysql -uroot -p < db_test001.sql

   全备份还原单表

3. 先删除一个表： sys_user

4. 从全备份中提取出该表的建表语句

   sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `sys_user`/!d;q' all_database_fullbak.sql > sys_user.sql

5. 提取该表的insert into语句

   grep -i 'INSERT INTO `sys_user`'  all_database_fullbak.sql >> sys_user.sql

6. 导入到对应的库中

   mysql -uroot -p <sys_user.sql

7. 查看数据

   mysql> select count(*) from sys_user;

   锁表

   锁表后数据库所有用户都只能进行读操作

上锁

flush tables with read lock;

解锁

unlock tables;

只读模式

只读模式可以限定普通用户进行数据修改的操作，但不会限定具有super权限的用户的数据修改操作；

设置数据库只读

set global read_only=1;

取消数据库只读

set global read_only=0;

定位列名

select * from information_schema.COLUMNS where COLUMN_NAME='列名';

修改数据库名

MyISAM

直接修改数据库文件夹名称

Innodb

最保险的方法就是备份导出数据，新建库重新导入。

修改表名

修改单个表的

alter table tbl_name rename [to|as] new_tbl_name

可以修改多个表的

rename table tbl_name to new_tbl_name [,tbl_name2 TO new_tbl_name2...]

修改列

数据库大小写

参考

• MySQL大小写敏感的解决方案
• mysql字符串区分大小写的问题

数据库关键字和保留字

https://dev.mysql.com/doc/refman/8.0/en/keywords.html

Group By

ONLY_FULL_GROUP_BY

这个配置在MySQL 5.7以后是默认开启的。

mysql> select @@sql_mode;
+-----------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                            |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)

示例

执行下列的SQL语句

SELECT creator,modifier FROM `sys_user` GROUP BY creator;

执行该语句后，报错如下图
关闭连接重连后，再次执行相同的语句，显示正常的结果，不再有错误（如下图）

如何在不关闭ONLY_FULL_GROUP_BY的情况下，实现正常的查询呢？使用ANY_VALUE函数

SELECT creator,any_value(modifier) FROM `sys_user` GROUP BY creator;

判断是否包含某个字符串的方法

like

SELECT * FROM 表名 WHERE 字段名 like "%字符%";

find_in_set()

find_in_set(str1,str2) 函数是返回str2中str1所在的位置索引+1，str2必须以”,”分割开。

SELECT find_in_set('3','3,6,13,1,24,33,36')  -- 结果为 1

locate(字符,字段名)

使用locate(字符,字段名)函数，如果包含，返回>0的数，否则返回0

查询表索引碎片

表

show table status from kkk like 'frag_tab_myisam' \G;

SELECT CONCAT(table_schema, '.', table_name)                   AS  TABLE_NAME
      ,engine                                                  AS  TABLE_ENGINE 
      ,table_type                                              AS  TABLE_TYPE
      ,table_rows                                              AS  TABLE_ROWS
      ,CONCAT(ROUND(data_length  / ( 1024 * 1024), 2), 'M')    AS  TB_DATA_SIZE 
      ,CONCAT(ROUND(index_length / ( 1024 * 1024), 2), 'M')    AS  TB_IDX_SIZE 
      ,CONCAT(ROUND((data_length + index_length ) 
            / ( 1024 * 1024 ), 2), 'M')                        AS  TOTAL_SIZE
      ,CASE WHEN  data_length =0 THEN 0
            ELSE  ROUND(index_length / data_length, 2) END     AS  TB_INDX_RATE
    ,CONCAT(ROUND( data_free / 1024 / 1024,2), 'MB')           AS  TB_DATA_FREE 
    ,CASE WHEN (data_length + index_length) = 0 THEN 0
             ELSE ROUND(data_free/(data_length + index_length),2) 
     END                                                       AS  TB_FRAG_RATE
FROM information_schema.TABLES  
ORDER BY data_free DESC;
SELECT CONCAT(table_schema, '.', table_name)                    AS  TABLE_NAME
      ,engine                                                   AS  TABLE_ENGINE 
      ,table_type                                               AS  TABLE_TYPE
      ,table_rows                                               AS  TABLE_ROWS
      ,CONCAT(ROUND(data_length  / ( 1024 * 1024), 2), 'M')     AS  TB_DATA_SIZE 
      ,CONCAT(ROUND(index_length / ( 1024 * 1024), 2), 'M')     AS  TB_IDX_SIZE 
      ,CONCAT(ROUND((data_length + index_length ) 
            / ( 1024 * 1024 ), 2), 'M')                         AS  TOTAL_SIZE
      ,CASE WHEN  data_length =0 THEN 0
            ELSE  ROUND(index_length / data_length, 2) END      AS  TB_INDX_RATE
    ,CONCAT(ROUND( data_free / 1024 / 1024,2), 'MB')            AS  TB_DATA_FREE 
    ,CASE WHEN (data_length + index_length) = 0 THEN 0
             ELSE ROUND(data_free/(data_length + index_length),2) 
     END                                                        AS  TB_FRAG_RATE
FROM information_schema.TABLES  
WHERE ROUND(DATA_FREE/1024/1024,2) >=50
ORDER BY data_free DESC;
SELECT TABLE_SCHEMA
      ,TABLE_NAME 
      ,ENGINE
      ,ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024), 2) AS SIZE_MB
      ,ROUND(DATA_FREE/1024/1024,2) AS FREE_SIZ_MB
FROM information_schema.TABLES 
WHERE DATA_FREE >=10*1024*1024
ORDER BY FREE_SIZ_MB DESC;

清理索引

1. 我们可以通过一个nop操作(不产生对数据实质影响的操作), 来修改表.比如: 表的引擎为innodb , 可以 alter table xxx engine innodb(修改表的引擎类型为其默认类型会重新调整数据，但不会影响数据)
2. optimize table 表名 ,也可以修复

注意: 修复表的数据及索引碎片,就会把所有的数据文件重新整理一遍,使之对齐.
　　 这个过程,如果表的行数比较大,也是非常耗费资源的操作.
　 　所以,不能频繁的修复.

事务超时死锁解决

show full processlist;
select * from information_schema.innodb_trx;
select * from information_schema.innodb_locks;
SELECT * FROM information_schema.innodb_lock_waits;

相关资料

• MySQL事务锁等待超时 Lock wait timeout exceeded; try restarting transaction
• 关于MySQL的lock wait timeout exceeded解决方案