Mysql一些常用的语句记录

数据库账号创建

 mysql> CREATE USER 'you_name'@'localhost' IDENTIFIED BY 'password';

授权数据库

 mysql> GRANT ALL PRIVILEGES ON *.* TO 'you_name'@'localhost' #所有库的全部权限超管了
 mysql> GRANT ALL PRIVILEGES ON `database`.`table` TO 'you_name'@'localhost' #某个库的全部权限
 mysql>GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'repl_user'@'%'; #一般用来做slave 的账号

查看他的权限

mysql> SHOW GRANTS FOR 'you_name'@'localhost';

将数据库实例设置为锁表并全库只读

mysql> FLUSH TABLES WITH READ LOCK;
mysql> SET GLOBAL READ_ONLY = ON;

将数据库实例设置改回可写，并解锁

mysql> SET GLOBAL READ_ONLY = OFF;
mysql> UNLOCK TABLES;

查看库某个二进制日志点的执行情况

$ mysqlbinlog -v --start-position=803115397 --stop-position=803115399 /mysql/binlog/mysql-bin.025781 > /tmpbinlog.log

#下面的命令可以查看 详细SQL语句信息 并导出到文件
$ mysqlbinlog --no-defaults --base64-output=decode-rows -vv --start-datetime="2023-03-27 10:18:00" /mysql/3306/binlog/mysql-bin.000335 -r ~/bin.sql

测算本库的大小

  mysql> SELECT t.TABLE_SCHEMA "db name",
  ROUND(SUM(t.INDEX_LENGTH+t.DATA_LENGTH)/1024/1024,2) "database size MB" 
  FROM information_schema.TABLES t GROUP BY t.TABLE_SCHEMA

查询 数据库 wowtalk_db 中 引擎为非Innodb 的表

 mysql> SELECT table_catalog,table_schema,table_name,ENGINE 
 FROM information_schema.tables 
 WHERE table_schema='you_database' AND ENGINE!='Innodb';

查询数据库实例中所有表名是 “message_”开头的表及 目前自增ID值位置

mysql> SELECT table_name,AUTO_INCREMENT 
FROM INFORMATION_SCHEMA.TABLES 
WHERE table_name LIKE 'message_%' AND table_name NOT LIKE 'message_at%' 
ORDER BY AUTO_INCREMENT DESC;

经典分页子查询拉取数据方法，适合大数据的分页显示不会越拉越慢

mysql> SELECT t.customer_id,t.title,t.content 
FROM (
SELECT `customer_id` FROM product_coment 
WHERE product_id=1999727 AND audit_status=1 LIMIT 0,15
) a JOIN product_comment t ON a.comment_id = t.comment_id;

查询MYSQL当前正在执行那些语句

mysql> show processlist;

清除binLog日志

mysql> purge binary logs befor '2023-03-28 00:00:00';   #将某时间以前的二进制日志清除