Home | 简体中文 | 繁体中文 | 杂文 | Github | 知乎专栏 | 51CTO学院 | CSDN程序员研修院 | OSChina 博客 | 腾讯云社区 | 阿里云栖社区 | Facebook | Linkedin | Youtube | 打赏(Donations) | About
知乎专栏多维度架构

第 3 章 数据库管理(Database Administration)

目录

3.1. 用户管理(User Account Management)
3.1.1. 创建用户
3.1.2. 删除用户
3.1.3. 修改用户名
3.1.4. 修改密码
3.2. Access Privilege System
3.2.1. SHOW GRANTS
3.2.2. show privileges
3.2.3. Grant privileges
3.2.4. Revoke privileges
3.2.5. Show Privileges
3.2.6. MAX_QUERIES_PER_HOUR/MAX_UPDATES_PER_HOUR
3.2.7. Table Privileges
3.2.8. Column Privileges
3.3. 字符集转换
3.3.1. 转换 latin1 到 UTF-8
3.4. 重新整理AUTO_INCREMENT字段
3.5. 数据库内容替换
3.6. Kill 脚本
3.7. MySQL 时区管理
3.8. SHOW COMMAND
3.8.1. 查看版本
3.8.2. status
3.8.3. show processlist
3.8.4. 线程的使用情况
3.8.5. DATABASES
3.8.6. 排序统计信息
3.8.7. Key 状态
3.8.8. FUNCTION
3.8.9. PROCEDURE
3.8.10. TRIGGERS
3.8.11. EVENTS
3.8.12. 引擎(ENGINES)
3.8.13. 字符集(Collation)
3.8.14. SHOW GRANTS
3.8.15. validate_password
3.9. Maintenance 数据库维护
3.9.1. CHECK 检查表
3.9.2. ANALYZE 分析表
3.9.3. CHECKSUM
3.9.4. OPTIMIZE 优化表
3.9.5. REPAIR 修复
3.10. INFORMATION_SCHEMA
3.10.1. 查询表字段
3.10.2. 列出所有触发器
3.10.3. 查看表数据尺寸
3.11. Backup and Recovery
3.11.1. Import / Export
3.11.2. Snapshot Backup

3.1. 用户管理(User Account Management)

3.1.1. 创建用户

			
CREATE USER user [IDENTIFIED BY [PASSWORD] 'password']
    [, user [IDENTIFIED BY [PASSWORD] 'password']] ...
			
			

CREATE USER 'test'@'xxx.xxx.xxx.xxx' IDENTIFIED BY  'your_password';
			

CREATE USER 'root'@'192.168.1.%' IDENTIFIED BY 'password';
			

add a new user by grant

			
GRANT ALL PRIVILEGES ON opencart.* TO 'neo'@'localhost' IDENTIFIED BY 'chen' WITH GRANT OPTION;

GRANT ALL PRIVILEGES ON *.* TO 'neo'@'localhost' IDENTIFIED BY 'chen' WITH GRANT OPTION;

FLUSH PRIVILEGES;
			
			

MySQL 8.0

			
mysql> CREATE USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'pMQiEge1ikst7S_6tlXzBOmt_4b';
Query OK, 0 rows affected (0.06 sec)

mysql> grant all on *.* to 'root'@'%';
Query OK, 0 rows affected (0.11 sec)

			
			

3.1.2. 删除用户


DROP USER user [, user] ...
			
			
mysql> drop user 'root'@'%';
Query OK, 0 rows affected (0.02 sec)
			
mysql> drop user admin@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> drop user admin@'127.0.0.1';
Query OK, 0 rows affected (0.00 sec)
			
			

判断用户是否存,存在再删除

			
DROP USER IF EXISTS 'nacos'@'localhost';			
			
			

3.1.3. 修改用户名


RENAME USER old_user TO new_user [, old_user TO new_user] ...
			

3.1.4. 修改密码

mysql 5.7 之前的版本


SET PASSWORD FOR 'bob'@'%.loc.gov' = PASSWORD('newpass');

SET PASSWORD FOR 'root'@'%' = PASSWORD('co2uqAMAho1aSOS62146Xoci6ogu4I');
			

MySQL 5.7 之后


ALTER USER 'root'@'localhost' IDENTIFIED BY 'your_password';		
			

			
mysql> ALTER user 'root'@'%' IDENTIFIED BY 'test';
Query OK, 0 rows affected (0.01 sec)

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