Home | 簡體中文 | 繁體中文 | 雜文 | 打賞(Donations) | ITEYE 博客 | OSChina 博客 | Facebook | Linkedin | 知乎專欄 | Search | Email

第 10 章 Database Administration

目錄

10.1. User Account Management
10.1.1. Create User
10.1.2. Drop User
10.1.3. Rename User
10.1.4. SET PASSWORD
10.2. Access Privilege System
10.2.1. SHOW GRANTS
10.2.2. show privileges
10.2.3. Grant privileges
10.2.4. Revoke privileges
10.2.5. Show Privileges
10.2.6. MAX_QUERIES_PER_HOUR/MAX_UPDATES_PER_HOUR
10.2.7. Table Privileges
10.2.8. Column Privileges
10.3. Maintenance 資料庫維護
10.3.1. CHECK 檢查表
10.3.2. ANALYZE 分析表
10.3.3. CHECKSUM
10.3.4. OPTIMIZE 優化表
10.3.5. REPAIR 修復
10.4. Backup and Recovery
10.4.1. Import / Export
10.4.1.1. Export(Backup)
10.4.1.2. Import(Recovery)
10.4.1.3. xml
10.4.1.4. 備份表數據
10.4.1.5. source
10.4.1.6. 使用 mysqlhotcopy 備份 MyISAM 引擎的資料庫
10.4.1.7. AutoMySQLBackup
10.4.1.8. xtrabackup - Open source backup tool for InnoDB and XtraDB.
10.4.2. Snapshot Backup
10.4.2.1. LVM Snapshot
10.4.2.2. Btrfs Snapshot

10.1. User Account Management

10.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;
			
			

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

10.1.3. Rename User

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

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