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</opt/qinzc_bak.sql

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命令小结

http://blog.51cto.com/oldboy/632608