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

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

目录

58.1. 用户管理(User Account Management)
58.1.1. 创建用户
58.1.2. 删除用户
58.1.3. 修改用户名
58.1.4. 修改密码
58.2. Access Privilege System
58.2.1. SHOW GRANTS
58.2.2. show privileges
58.2.3. Grant privileges
58.2.4. Revoke privileges
58.2.5. Show Privileges
58.2.6. MAX_QUERIES_PER_HOUR/MAX_UPDATES_PER_HOUR
58.2.7. Table Privileges
58.2.8. Column Privileges
58.3. 字符集转换
58.3.1. 转换 latin1 到 UTF-8
58.4. 重新整理AUTO_INCREMENT字段
58.5. 数据库内容替换
58.6. Kill 脚本
58.7. MySQL 时区管理
58.8. SHOW COMMAND
58.8.1. 查看版本
58.8.2. status
58.8.2.1. show status
58.8.2.2. show master status
58.8.2.3. show slave status
58.8.2.4. show plugins
58.8.3. show processlist
58.8.4. 线程的使用情况
58.8.5. DATABASES
58.8.6. 排序统计信息
58.8.7. Key 状态
58.8.8. FUNCTION
58.8.9. PROCEDURE
58.8.10. TRIGGERS
58.8.11. EVENTS
58.8.12. 引擎(ENGINES)
58.8.13. 字符集(Collation)
58.8.14. SHOW GRANTS
58.8.15. validate_password
58.9. Maintenance 数据库维护
58.9.1. CHECK 检查表
58.9.2. ANALYZE 分析表
58.9.3. CHECKSUM
58.9.4. OPTIMIZE 优化表
58.9.5. REPAIR 修复
58.10. INFORMATION_SCHEMA
58.10.1. 查询表字段
58.10.2. 列出所有触发器
58.10.3. 查看表数据尺寸
58.11. Backup and Recovery
58.11.1. Import / Export
58.11.1.1. Export(Backup)
58.11.1.2. Import(Recovery)
58.11.1.3. xml
58.11.1.4. 备份表数据
58.11.1.5. source
58.11.1.6. 使用 mysqlhotcopy 备份 MyISAM 引擎的数据库
58.11.1.7. AutoMySQLBackup
58.11.1.8. xtrabackup - Open source backup tool for InnoDB and XtraDB.
58.11.1.8.1. Percona yum Repository
58.11.1.8.2. Creating an Incremental Backup
58.11.2. Snapshot Backup
58.11.2.1. LVM Snapshot
58.11.2.2. Btrfs Snapshot

58.1. 用户管理(User Account Management)

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

			
			

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

58.1.3. 修改用户名


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

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