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

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

目录

21.1. 用户管理(User Account Management)
21.1.1. 创建用户
21.1.2. 删除用户
21.1.3. 修改用户名
21.1.4. 修改密码
21.2. Access Privilege System
21.2.1. SHOW GRANTS
21.2.2. show privileges
21.2.3. Grant privileges
21.2.4. Revoke privileges
21.2.5. Show Privileges
21.2.6. MAX_QUERIES_PER_HOUR/MAX_UPDATES_PER_HOUR
21.2.7. Table Privileges
21.2.8. Column Privileges
21.3. 字符集转换
21.3.1. 转换 latin1 到 UTF-8
21.4. 重新整理AUTO_INCREMENT字段
21.5. 数据库内容替换
21.6. Kill 脚本
21.7. MySQL 时区管理
21.8. SHOW COMMAND
21.8.1. 查看版本
21.8.2. status
21.8.2.1. show status
21.8.2.2. show master status
21.8.2.3. show slave status
21.8.2.4. show plugins
21.8.3. show processlist
21.8.4. 线程的使用情况
21.8.5. DATABASES
21.8.6. 排序统计信息
21.8.7. Key 状态
21.8.8. FUNCTION
21.8.9. PROCEDURE
21.8.10. TRIGGERS
21.8.11. EVENTS
21.8.12. 引擎(ENGINES)
21.8.13. 字符集(Collation)
21.8.14. SHOW GRANTS
21.8.15. validate_password
21.9. Maintenance 数据库维护
21.9.1. CHECK 检查表
21.9.2. ANALYZE 分析表
21.9.3. CHECKSUM
21.9.4. OPTIMIZE 优化表
21.9.5. REPAIR 修复
21.10. INFORMATION_SCHEMA
21.10.1. 查询表字段
21.10.2. 列出所有触发器
21.10.3. 查看表数据尺寸
21.11. Backup and Recovery
21.11.1. Import / Export
21.11.1.1. Export(Backup)
21.11.1.2. Import(Recovery)
21.11.1.3. xml
21.11.1.4. 备份表数据
21.11.1.5. source
21.11.1.6. 使用 mysqlhotcopy 备份 MyISAM 引擎的数据库
21.11.1.7. AutoMySQLBackup
21.11.1.8. xtrabackup - Open source backup tool for InnoDB and XtraDB.
21.11.1.8.1. Percona yum Repository
21.11.1.8.2. Creating an Incremental Backup
21.11.2. Snapshot Backup
21.11.2.1. LVM Snapshot
21.11.2.2. Btrfs Snapshot

21.1. 用户管理(User Account Management)

21.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)

			
			

21.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';			
			
			

21.1.3. 修改用户名


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

21.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)