mysql用户授权、备份与恢复、优化配置

mysql常见配置，及数据库层面性能调优及问题排查说明

一 用户管理及授权

-- 创建用户
    CREATE USER 'username'@'localhost' IDENTIFIED BY 'passwrd';
-- 查看所有用户
    select * from mysql.user;
-- 删除用户
    DROP USER 'username'@'localhost'; 
-- 授予用户通过外网IP对该数据库的全部权限
    grant all privileges on imp_commondb.* to 'username'@'%' identified by 'passwrd';
-- 赋予用户所用权限
    GRANT ALL PRIVILEGES ON *.* TO username@"%" IDENTIFIED BY "passwrd"; 
-- 赋予用户所用权限
    GRANT ALL PRIVILEGES ON *.* TO username@localhost IDENTIFIED BY "passwrd"; 
-- 删除用户对数据库的操作权限
    REVOKE all privileges ON imp_commondb.* FROM 'username'@'%';
-- 赋权后，需要刷新才能生效，也可重启数据库
    FLUSH PRIVILEGES;

二 .conf或者 .ini相关配置

-- 慢sql日志配置 
    long_query_time=2 
    slow-query-log=On  
    slow_query_log_file="E:\Program Files\MySQL\MySQL Server 5.5\log\mysql_slow_query.log" 
-- 记录数据库启动关闭信息，以及运行过程中产生的错误信息
    log-error="E:\Program Files\MySQL\MySQL Server 5.5\log\myerror.log"
-- 记录除select语句之外的所有sql语句到日志中，可以用来恢复数据文件
    log-bin=E:/mysqllog/bin
-- 配置主键偶数自增：
    auto_increment_increment=2
    auto_increment_offset=2
-- 当忘记mysql用户名登录密码时，在my.ini加入下面命令，强行免密码连接，连接后在mysql.user表中手动添加已知用户后，重新启动mysql服务，用已知用户登录，进行赋权等操作
    skip-grant-tables

三 配置参数查询

-- 查看慢查询时间
    show variables like "long_query_time";
-- 查看慢查询配置情况
    show status like "%slow_queries%";
-- 查看慢查询日志路径
    show variables like "%slow%";

四 mysql问题排查及性能优化命令

-- 通过如下sql语句，来查看一共执行过几次慢查询
    show global status like '%slow%';
-- 查看等待锁的事务
    SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS; 
-- 查看正在锁的事务
    SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; 
-- 查询是否锁表
    show OPEN TABLES where In_use > 0;
-- 查询mysql进程
    show full processlist;(kill id 强制杀死进程)
-- mysql客户端查看最后一个死锁信息
    show engine innodb status ;
-- 命令窗口或者xshell查看mysql最后一个死锁信息
    show engine innodb status \G;
-- 查询某个sql的执行计划
    explain sql语句

五 mysql 数据库备份与还原

-- 备份整个数据库（包含数据）：
    mysqldump -uroot -p'root' dbname > /opt/dbname.sql 
-- 备份整个数据库（不含数据）：
    mysqldump -uroot -p'root' -d dbname > /opt/dbname.sql 
-- 备份多张表（包含数据）：
    mysqldump -uroot -p'root' dbname tablename1 tablename2 tablename3 > /opt/dbname.sql
-- 备份数据库,使用参数（--ignore-table）排除多个表：
    mysqldump -uroot -p'root' --ignore-table=dbname.table1 --ignore-table=dbname.table2 dbname > /opt/dbname.sql
-- 还原数据库(-f参数，忽略错误)：
    mysqldump -uroot -p'root' -f dbname <  /opt/dbname.sql