mysql数据库管理

用户管理

1）用户与授权
新建用户
CREATE USER 'y1905'@'%' identified by '123456';
select host,user,password from mysql.user;
删除用户
drop user 'y1905'@'%';
重命名用户
RENAME USER 'myslave'@'192.168.9.%' TO 'y1905'@'%';
给用户设置密码
当前用户修改密码
set password=password("123456");
其他用户修改密码
set password for 'y1905'@'%'=password('pwd123');
忘记密码
a、停止服务
systemctl stop mysqld
b、跳过密码直接登录
mysqld_safe --skip-grant-tables&
c、登录
[root@www ~]# mysql
mysql> update mysql.user set password=password("111") where user='root';
mysql> flush privileges；
d、kill进程myslqd
e、重启
systemctl start mysqld
f、验证
[root@www ~]# mysql -uroot -p111

授权

设置权限
mysql> grant select,update on mmm.* to 'testa'@'192.168.9.%' identified by '123';
查看授权
show grants for testa@'192.168.9.%';
撤销权限
revoke update on mmm.* from 'testa'@'192.168.9.%';

权限列表

权限	说明
All	设置所有权限
ALTER	允许使用ALTER TABLE
CREATE	允许使用CREATE TABLE
CREATE USER	允许使用CREATE USER
DELETE	允许使用DELETE
INDEX	允许使用INDEX
INSERT	允许使用INSERT
SELECT	允许使用SELECT
UPDATE	允许使用UPDATE
DROP	允许使用DROP
REPLICATION SLAVE	允许从主服务器中读取二进制文件
SHOW DATABASES	允许显示所有数据库

日志管理

vi /etc/my.cnf
log-error=/usr/local/mysql/data/my_error.log   错误日志存放位置
general_log = ON    #打开通用日志,默认为OFF
general_log_file = /usr/local/mysql/data/mysql_general.log
log_bin = y2002-bin   # 如果没有写=右面的内容，自动生成主机名***-bin.00000*的日志
注意：mysql必须对指定的文件夹有读写权限，否则无法操作文件。

1）错误日志
主要记录当mysql启动和停止时，以及在运行过程中发生任何错误时的相关信息
默认保存在mysql安装路径data文件夹下，后缀名是.err
2）通用日志
通用日志用来记录mysql的所有连接和语句，默认是关闭的
3）二进制日志
二进制日志用来记录所有更新了数据或已经潜在更新了数据的语句，记录了数据的更改
默认是关闭的
二进制文件使用mysqlbinlong命令查看，格式为 msyqlbinlog mysql-bin.000001
4）慢查询日志
慢查询日志记录所有执行时间超过 long_query_time 秒的SQL语句，用于找到哪些查询语句执行时间长，以便对其进行优化。
默认是关闭的
通过配置文件更改：
vi  /etc/my.cnf
slow_query_log=ON     开启慢查询日志
slow_query_log_file=mysql_slow_query.log  指定存放慢查询日志路径，
如果没有指定绝对路径，默认保存在data目录下
long_query_time=1   设置超时时间

mysql> show variables like "%slow%";   查看慢查询功能是否开启
+---------------------------+-----------------------------------+
| Variable_name             | Value                             |
+---------------------------+-----------------------------------+
| log_slow_admin_statements | OFF                               |
| log_slow_slave_statements | OFF                               |
| slow_launch_time          | 2                                 |
| slow_query_log            | OFF         状态为OFF                      |
| slow_query_log_file       | /usr/local/mysql/data/my-slow.log |
+---------------------------+-----------------------------------+
5 rows in set (0.00 sec)

mysql> set global slow_query_log=ON;    开启慢查询功能，临时
Query OK, 0 rows affected (0.04 sec)

mysql> show variables like "%slow%";
+---------------------------+-----------------------------------+
| Variable_name             | Value                             |
+---------------------------+-----------------------------------+
| log_slow_admin_statements | OFF                               |
| log_slow_slave_statements | OFF                               |
| slow_launch_time          | 2                                 |
| slow_query_log            | ON                                |
| slow_query_log_file       | /usr/local/mysql/data/my-slow.log |
+---------------------------+-----------------------------------+
5 rows in set (0.00 sec)


mysql> show variables like "long_query_time";   查看超时时间
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |   #默认10s
+-----------------+-----------+
1 row in set (0.00 sec)

mysql> set global long_query_time=5;  #设置慢查询的时间为5s
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like "long_query_time";
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |   #没有变
+-----------------+-----------+
1 row in set (0.00 sec)

mysql> show variables like "long_query_time";
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 5.000000 |   #重新登录
+-----------------+----------+
1 row in set (0.00 sec)



验证

mysql> select sleep(6);

vi my-slow.log 
/usr/local/mysql/bin/mysqld, Version: 5.6.36-log (Source distribution). started with:
Tcp port: 3306  Unix socket: /tmp/mysql.sock
Time                 Id Command    Argument
# Time: 191012 19:42:59
# User@Host: root[root] @ localhost []  Id:     2
# Query_time: 6.000773  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1570880579;
select sleep(6);

数据乱码解决

乱码产生的原因

在计算机中任何数据都是以二进制存储的，要存储一个字符就要对他进行编码，用一个二进制数与其对应，这种对应的规则的集合叫作‘字符集’。
Mysql服务器使用中经常会有乱码产生，主要有以下几种原因：

• 服务器系统字符设置问题
• 数据表语系设置的问题
• 客户端连接语系的问题
  实际对乱码问题原因的简单解释就是：在使用、存储数据的时候，各个环节设置的字符集如果不同，就会产生乱码

乱码解决方法

修改字符集

• 创建数据库时指定
  CREATE DATABASE test CHARSET utf8;
• 创建数据表时指定
  create table idtest(id int(10)) default charset=gb2312
show table status 可查看字符集
• 修改MySQL配置文件
  通常都是使用修改MySQL配置文件的方法，使数据库中所有存储内容都是utf8的格式。
  修改MySQL配置文件/etc/my.cnf
  添加[client]部分，并加入：
  default-character-set = utf8
  重启后生效，只修改这一处，之后创建数据库和数据表都不需要再单独指定字符集。