Mysql一些常用的语句记录
gathin发表于:2023-01-01 22:37:55浏览:411次
数据库账号创建
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'; #将某时间以前的二进制日志清除