Home | 简体中文 | 繁体中文 | 杂文 | 知乎专栏 | 视频教程 | bilibili | Github | OSChina 博客 | 云社区 | 云栖社区 | Facebook | Linkedin | 打赏(Donations) | About
知乎专栏多维度架构 | 微信号 netkiller-ebook | 51CTO:视频教程

第 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. Maintenance 数据库维护
21.3.1. CHECK 检查表
21.3.2. ANALYZE 分析表
21.3.3. CHECKSUM
21.3.4. OPTIMIZE 优化表
21.3.5. REPAIR 修复
21.4. INFORMATION_SCHEMA
21.4.1. 查询表字段
21.4.2. 列出所有触发器
21.5. Backup and Recovery
21.5.1. Import / Export
21.5.1.1. Export(Backup)
21.5.1.2. Import(Recovery)
21.5.1.3. xml
21.5.1.4. 备份表数据
21.5.1.5. source
21.5.1.6. 使用 mysqlhotcopy 备份 MyISAM 引擎的数据库
21.5.1.7. AutoMySQLBackup
21.5.1.8. xtrabackup - Open source backup tool for InnoDB and XtraDB.
21.5.1.8.1. Percona yum Repository
21.5.1.8.2. Creating an Incremental Backup
21.5.2. Snapshot Backup
21.5.2.1. LVM Snapshot
21.5.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)
			
			

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)			
			
			
网站:http://www.netkiller.cn/ | 知乎:netkiller | 51CTO:视频教程 | Bilibili:netkiller | Github:netkiller