Home | 简体中文 | 繁体中文 | 杂文 | 打赏(Donations) | ITEYE 博客 | OSChina 博客 | Facebook | Linkedin | 知乎专栏 | Search | Email

第 23 章 Database Administration

目录

23.1. User Account Management
23.1.1. Create User
23.1.2. Drop User
23.1.3. Rename User
23.1.4. SET PASSWORD
23.2. Access Privilege System
23.2.1. SHOW GRANTS
23.2.2. show privileges
23.2.3. Grant privileges
23.2.4. Revoke privileges
23.2.5. Show Privileges
23.2.6. MAX_QUERIES_PER_HOUR/MAX_UPDATES_PER_HOUR
23.2.7. Table Privileges
23.2.8. Column Privileges
23.3. Maintenance 数据库维护
23.3.1. CHECK 检查表
23.3.2. ANALYZE 分析表
23.3.3. CHECKSUM
23.3.4. OPTIMIZE 优化表
23.3.5. REPAIR 修复
23.4. Backup and Recovery
23.4.1. Import / Export
23.4.1.1. Export(Backup)
23.4.1.2. Import(Recovery)
23.4.1.3. xml
23.4.1.4. 备份表数据
23.4.1.5. source
23.4.1.6. 使用 mysqlhotcopy 备份 MyISAM 引擎的数据库
23.4.1.7. AutoMySQLBackup
23.4.1.8. xtrabackup - Open source backup tool for InnoDB and XtraDB.
23.4.2. Snapshot Backup
23.4.2.1. LVM Snapshot
23.4.2.2. Btrfs Snapshot

23.1. User Account Management

23.1.1. Create User

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;
			
			

23.1.2. Drop User

DROP USER user [, user] ...
			
			
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)
			
			

23.1.3. Rename User

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

23.1.4. SET PASSWORD

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