创建授权用户:
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