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

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

目录

59.1. 用户管理(User Account Management)
59.1.1. 创建用户
59.1.2. 删除用户
59.1.3. 修改用户名
59.1.4. 修改密码
59.2. Access Privilege System
59.2.1. SHOW GRANTS
59.2.2. show privileges
59.2.3. Grant privileges
59.2.4. Revoke privileges
59.2.5. Show Privileges
59.2.6. MAX_QUERIES_PER_HOUR/MAX_UPDATES_PER_HOUR
59.2.7. Table Privileges
59.2.8. Column Privileges
59.3. Maintenance 数据库维护
59.3.1. CHECK 检查表
59.3.2. ANALYZE 分析表
59.3.3. CHECKSUM
59.3.4. OPTIMIZE 优化表
59.3.5. REPAIR 修复
59.4. INFORMATION_SCHEMA
59.4.1. 查询表字段
59.4.2. 列出所有触发器
59.5. Backup and Recovery
59.5.1. Import / Export
59.5.1.1. Export(Backup)
59.5.1.2. Import(Recovery)
59.5.1.3. xml
59.5.1.4. 备份表数据
59.5.1.5. source
59.5.1.6. 使用 mysqlhotcopy 备份 MyISAM 引擎的数据库
59.5.1.7. AutoMySQLBackup
59.5.1.8. xtrabackup - Open source backup tool for InnoDB and XtraDB.
59.5.1.8.1. Percona yum Repository
59.5.1.8.2. Creating an Incremental Backup
59.5.2. Snapshot Backup
59.5.2.1. LVM Snapshot
59.5.2.2. Btrfs Snapshot

59.1. 用户管理(User Account Management)

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

			
			

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

59.1.3. 修改用户名


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

59.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