mysql用户及授权，密码操作

创建授权用户：
CREATE USER ‘jeffrey’@’localhost’ IDENTIFIED BY ‘mypass’;
GRANT ALL ON db1.* TO ‘jeffrey’@’localhost’;
GRANT SELECT ON db2.invoice TO ‘jeffrey’@’localhost’;
GRANT USAGE ON *.* TO ‘jeffrey’@’localhost’ WITH MAX_QUERIES_PER_HOUR 90;

删除root重新创建root：
GRANT ALL PRIVILEGES ON *.* TO ‘system’@’localhost’ IDENTIFIED BY ‘pass’ WITH GRANT OPTION; 创建root
GRANT ALL PRIVILEGES ON *.* TO ‘system’@’127.0.0.1’ IDENTIFIED BY ‘pass’ WITH GRANT OPTION;
FLUSH PRIVILEGES;

查看用户权限：
SHOW GRANTS FOR ‘system’@’localhost’ \G

建议先建后删除

mysql> delete user from user;
Query OK, 8 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO ‘system’@’localhost’ IDENTIFIED BY ‘pass’ WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO ‘system’@’127.0.0.1’ IDENTIFIED BY ‘pass’ WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

mysql> DROP database test;
Query OK, 0 rows affected (0.00 sec)

查看表字段：
mysql> desc mysql.user;

修改用户密码：
方法一：
mysql> update mysql.user set password=password(‘123′) where user=’zmj’;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

方法二：
mysql> SET PASSWORD FOR ‘zmj’@’localhost’ = PASSWORD(‘1234’);

mysql> flush privileges;

方法三：
# mysqladmin -u zmj password ‘12345’ -S /mysql/3307/mysql.sock -p1234

注意：
1、必须使用函数 password
2、必须加where限定
恢复数据库密码：
# 杀掉老进程
# kill `cat /mysql/3307/mysqld.pid`

# 跳过授权表形式启动mysql
# /usr/local/mysql/bin/mysqld_safe –defaults-file=/mysql/3307/my.cnf –skip-grant-table &

# 空密码进入数据库，修改密码
# mysql -usystem -p -S /mysql/3307/mysql.sock ##密码为空
mysql> update mysql.user set password=password(“pass”) where user=’system’ and host=’localhost’;
mysql> flush privileges;

# 杀掉进程，重启启动数据库
#mysqladmin关闭数据库
# kill `# ss -putln | grep 3307 | awk -F ‘ ‘ ‘{print $NF}’ | awk -F ‘,’ ‘{print $2}’ | awk -F ‘=’ ‘{print $NF}’`
# /mysql/3307/mysql start

DQL(查询):Data Query Language SELECT WHERE,ORDER BY *** ASC | DESC ,GROUP BY ,HAVING
DML(操作):Data Manipulation Language INSERT , UPDATE , DELETE
TPL(事务处理): BEGIN,TRANSACTION,COMMIT,ROLLBACK
DCL(数据控制语言):Data Control Language GRANT , REVOKE
DDL(数据定义语言)：Data Definition Laguage CREATE , DROP , ALTER
CCL(指针控制语言)：CURSOR Control Laguage DECLARE CURSOR , FETCH INTO , UPDATE WHERE CURRENT