mysql常用操作

1 连接数据库：

1.1 连接

如下表示 账号root 密码123456 连接到mysql服务器 host-02 

mysql -uroot -p123456  -h host-02

如果不指定-h, 则默认连接当前服务器（localhost)

注： 在localhost权限情况下。当前mysql服务器连接时，指定 -h必须指定localhost，如果指定host-02，则默认root@host-02连接到host-02,   可能没有权限。

1.2 连接权限 查看

在很多情况下，创建用户时，就指定了用户连接数据库的权限。

创建用户dumper具备在任意节点（%）连接mysql服务器的权限

create user dumper@'%' identified by '123456'，

select * from mysql.user where user='root'  查看root 账号连接权限。

上图localhost即表明用户root只拥有在 mysql服务器本地登录连接的权限。

注： 在localhost权限情况下。当前mysql服务器连接时，指定 -h必须指定localhost，如果指定host-02，则默认root@host-02连接到host-02,   可能没有权限。

如果为%则意味着任意节点登录服务器。

1.3 更改用户连接权限。

1）更改表字段，并且刷新权限。

   update user set host='%'  

  flush privileges;

2）授权

授予用户myDbUser01在192.168.10.1节点连接服务器。 并授予dk库任意功能权限

 GRANT ALL PRIVILEGES ON dk.* TO 'myDbUser01'@'192.168.10.1' IDENTIFIED BY 'myPassword' WITH GRANT OPTION;

  flush privileges;

2 重启数据库

重启mysql进程。

systemctl restart mysqld.service

3  查看mysql最大连接数。 

连接mysql，输入

show variables like '%max_con%';

4  查看mysql连接进程（即当前连接信息）。

 登录MySQL时提示 "too many connections"，第一时间考虑服务链接数量过多导致无法登录，先停掉部分链接服务，后重新登录MySQL成功。

查看连接信息：

how processlist;

kill 2142停止连接。 

实际工作中要分析下 突然连接数急剧增加原因。  

本人碰到的常用情况是airflow定时任务历史任务并发同时运行，导致hive metastore大量并发连接mysql导致。

。

5 查看mysql日志信息。

5.1.错误日志(error log)：记录mysql服务的启停时正确和错误的信息，还记录启动、停止、运行过程中的错误信息。

     #>    show variables like 'log_error';

    /etc/my.cnf配置的路径： /var/log/mysqld.log

5.2.查询日志(general log)：记录建立的客户端连接和执行的语句。

         启动mysqld时，可以设定如下参数。

        --general_log={0|1} "来决定是否启用一般查询日志，使用" --general_log_file=file_name "来指定查询日志的路径。

         long_query_time = 10 # 指定慢查询超时时长，超出此时长的属于慢查询，会记录到慢查询日志中

                show variables like '%long_query_time%';  //默认值10

          默认路径：general_log_file                   数据路径+主机名.log:        /var/lib/mysql/'hostname'.log

         也可以通过参数设置是否启用查询日志：

        general_log=off # 是否启用一般查询日志，为全局变量，必须在global上修改。（重启失效）。 该参数默认OFF即关闭。
        sql_log_off=off # 在session级别控制是否启用一般查询日志，默认为off，即启用  （退出当前会话失效），但是使用前提是general_log是开启状态。

5.3.慢查询日志(slow log)：记录所有执行时间超过long_query_time的所有查询或不使用索引的查询。

      性能调优使用

5.4.二进制日志(bin log)：记录所有更改数据的语句，可用于数据复制。

5.5.中继日志(relay log)：主从复制时使用的日志。 

6 查看数据目录：

show variables like '%datadir%';  

默认路径：/var/lib/mysql/

7 修改 mysql参数

7.1 配置文件/etc/my.cnf里修改

        Mysql的配置参数可以在/etc/my.cnf里直接修改，这是永久的修改。mysql重启后以该配置文件为准。

7.2 临时修改参数

   1） 当前会话修改：set wait_timeout=90;

    退出当前会话后，重新连接参数恢复设置前。

   2） 全局修改：   set  global wait_timeout=100;

    这个修改对所有用户均有效。 但是数据库重启后就失效了。重启后依然以my.cnf的配置为准。

  7.3  数据库重启时，配置文件路径f

     一般默认： /etc/my.cnf  

  --defaults-extra-file=....  也可以通过此参数指定配置文件路径

8 权限管理：

1)grant 普通数据用户，查询、插入、更新、删除 数据库中所有表数据的权利。

grant select, insert, update, delete on testdb.* to test_user@’%’
2)grant 数据库开发人员，创建表、索引、视图、存储过程、函数。。。等权限。
grant 创建、修改、删除 MySQL 数据表结构权限。
grant create on testdb.* to developer@’192.168.0.%’;
grant alter on testdb.* to developer@’192.168.0.%’;
grant drop on testdb.* to developer@’192.168.0.%’;
grant 操作 MySQL 外键权限。
grant references on testdb.* to developer@’192.168.0.%’;
grant 操作 MySQL 临时表权限。
grant create temporary tables on testdb.* to developer@’192.168.0.%’;
grant 操作 MySQL 索引权限。
grant index on testdb.* to developer@’192.168.0.%’;
grant 操作 MySQL 视图、查看视图源代码 权限。
grant create view on testdb.* to developer@’192.168.0.%’;
grant show view on testdb.* to developer@’192.168.0.%’;
grant 操作 MySQL 存储过程、函数 权限。
grant create routine on testdb.* to developer@’192.168.0.%’; — now, can show procedure status
grant alter routine on testdb.* to developer@’192.168.0.%’; — now, you can drop a procedure
grant execute on testdb.* to developer@’192.168.0.%’;

3)grant 普通 DBA 管理某个 MySQL 数据库的权限。
grant all privileges on testdb to dba@’localhost’
关键字 “privileges” 可以省略。

4) grant 高级 DBA 管理 MySQL 中所有数据库的权限。
grant all on *.* to dba@’localhost’

参考：https://www.cnblogs.com/flying1819/articles/9077059.html

参考：https://blog.csdn.net/eagle89/article/details/80969565