SQL 语句分类(常用3类)
DML---数据操纵语言(select,insert,delete,update)
DDL---数据定义语言(create,alter,drop)
DCL---数据控制语言(grant,revoke,commit, rollback)
TPL---事物处理语言(begintransaction,commit,rollback)
CCL---指针控制语言(declare,cursor,fetch into,update where current)
1.查看,当前用户表select user,host from mysql.user;
2.删除用户delete from mysql.user where host='xxxx';
drop user "xxxx"@"localhost"
3.显示当前数据库show databases;
show databases like '%数据库名%';
use ; 连接数据库
select database(); 查看当前数据库
4.删除数据库drop database ;
5.创建库create database ;
6.给用户设置密码(非改密码)/application/mysql/bin/mysqladmin -u root password '密码'
7.添加额外管理员delete from mysql.user;
grant all privileges on *.* to system@'localhost' identified by '密码' with grant option;
8.数据库启动方法1:/etc/init.d/mysqld start
方法2:mysqld_safe --user=mysql &
9.数据库关闭方法1:/etc/init.d/mysqld stop
方法2:kill -9 mysqld
方法3:pkill mysqld
方法4:killall mysqld
方法5:mysqladmin -u root -p 123456 shutdown (优雅关闭)
10.修改登陆提示符,防止误操作prompt \u@test \r:\m:\s->
永久生效:
[mysql]
prompt=\\u@test \\r:\\m:\\s->
11.修改mysql root密码方法1:mysqladmin -u root -p 123456 password '654321'
方法2:updata mysql.user set password=password("xxxx") where user='root' and host='localhost';
方法3:set password=password('xxxx')
12.查看表结构desc mysql.user;
13.修改表数据updata mysql.user set password=password("xxx") where user='root' and host='localhost';
14.刷新到文件flush privileges;
15.找回丢失的mysql root 密码/etc/init.d/mysqld stop
mysqld_safe --skip-grant-tables --user=mysql &
mysql 登陆
set password=password("xxxx")
flush privileges;
重启数据库即可
多实例增加 --defaults-file=/data/3306/my.cnf如
mysqld_safe --defaults-file=/data/3306/my.cnf --skip-grant-tables --user=mysql &
mysql -uroot -p -S /data/3306/mysql.sock
set password=password("xxxx")
flush privileges;
重启数据库即可
16.查询并排序select user,host,password from mysql.user order by user asc;
17.查看库字符集show create database xxxx\G
18.分别创建一个gbk\utf8字符集create database xxxxx DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;
create database xxxxx DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
19.查看版本、用户、时间select version();
select user();
select now();
20.查看当前库的表show tables;
show tables like 'user';
show tables from ; //查看制定库中的表
21.mysql创建用户与授权方法1:
create user 'qinzc'@'localhost' identified by '123456';
grant all on d3306.* to 'qinzc'@'localhost';
方法2:
grant all on d3306.* to 'test'@'192.168.1.%' identified by '123456';
22.查看用户当前权限show grants for 'qinzc'@'localhost';
SELECT
INSERT
UPDATE
DELETE
CREATE
DROP
REFERENCES
INDEX
ALTER
CREATE TEMPORARY TABLES
LOCK TABLE
EXECUT
CREATE VIE
SHOW VIEW
CREATE ROUTINE
ALTER ROUTINE
EVENT
TRIGGER
23.收回权限revoke insert on d3306.* from 'qinzc'@'localhost';
24.SQL数据导入mysql -uroot -p123456 d3306
25.防止人为误操作mysql数据库alias mysql='mysql -U'
[oldboy_c64 ~]# echo "alias mysql='mysql -U'" >>/etc/profile
[oldboy_c64 ~]# . /etc/profile
[oldboy_c64 ~]# tail -1 /etc/profilealias mysql='mysql -U'
26.查看 mysql 进程状态 (重要!查看正在执行的SQL语句)show processlist;
show full processlist; //完整语句内容
例:mysql线程中,“大海捞针”
平时登陆数据库show processlist;,发现结果经常超长,找自己要看的的比较困难,而且,
SQL显示不全。如果直接执行show full processlist那更是瞬间滚了N屏。找到有问题的
SQL语句非常困难。
现在好了,老男孩给你们推荐如下语句。
mysql -u root -p'oldboy' -S /data1/3307/mysql.sock -e "show full processlist;"|grep -v Sleep
过滤当前执行的SQL语句完整内容,这条命令很有用。不知道你能否体会到。后面还可以加iconv等对中文转码,
根据需求过滤想要的内容,此命令屡试不爽啊。
主库线程同步状态:
show processlist\G
从库查看状态:
show slave status\G
27.查看进程所有参数变量show variables;
mysql -uroot -p123456 -e "show variables;"
28.查看 mysql 运行状态 (重要!,要分析并做好监控)show status;
mysql -uroot -p123456 -e "show status;"
#全局状态
show global status;
mysql -uroot -p123456 -e "show global status;"
例:
[root@A-host ~]# mysql -u root -p123456 -S /data/3306/mysql.sock -e "show global status;"|grep select
Com_insert_select 0
Com_replace_select 0
Com_select 85
29.在线设置参数(一般 开/关{ON/OFF} 参数不能更改)例子:
mysql> show variables like 'key_buffer%';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| key_buffer_size | 16777216 |
+-----------------+----------+
1 row in set (0.00 sec)
mysql> set global key_buffer_size=1024*1024*32;
Query OK, 0 rows affected (0.08 sec)
mysql> show variables like 'key_buffer%';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| key_buffer_size | 33554432 |
+-----------------+----------+
1 row in set (0.00 sec)
30.解析mysqlbinlog日志binlog日志用来记录mysql内部增删改查等对mysql有更新的内容的记录。
mysqlbinlog --hlep
[root@A-host ~]# mysqlbinlog /data/3306/mysql-bin.000001 //查看
[root@A-host ~]# mysqlbinlog -d d3306 /data/3306/mysql-bin.000001 >1.txt //查看d3306库日志(拆库)
#指定开始位置和结束位置恢复
mysqlbinlog /data/3306/mysql-bin.000001 --start-position=510 --stop-position=1312 -r pos.sql //-r 与> 一致
#指定开始和结束时间恢复
mysqlbinlog /data/3306/mysql-bin.000001 --start-datatime='2018-8-20 14:42:54' --stop-datatime='2018-8-20 14:43:54' >time.sql
31.mysqlbinlog日志轮询[mysqld]
expire_logs_days = 7 //保留7天日志
类似:
find /data/3306/ -type f -name "mysql-bin.000*" -mtime +7|xargs rm -f
32.刷新MySQLbinlog数据mysqladmin -uroot -p123456 fulsh-logs
不登陆数据库执行mysql命令小结
赞0
踩0
本文标签:MySQL
版权声明:若无特殊注明,本文为《橙子柠檬》原创,转载请保留文章出处。
本文链接:https://qinzc.me/post-111.html
正文到此结束