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

第 3 章 Database Administration

目錄

3.1. User Account Management
3.1.1. Create User
3.1.2. Drop User
3.1.3. Rename User
3.1.4. SET PASSWORD
3.2. Access Privilege System
3.2.1. SHOW GRANTS
3.2.2. show privileges
3.2.3. Grant privileges
3.2.4. Revoke privileges
3.2.5. Show Privileges
3.2.6. MAX_QUERIES_PER_HOUR/MAX_UPDATES_PER_HOUR
3.2.7. Table Privileges
3.2.8. Column Privileges
3.3. Maintenance 資料庫維護
3.3.1. CHECK 檢查表
3.3.2. ANALYZE 分析表
3.3.3. CHECKSUM
3.3.4. OPTIMIZE 優化表
3.3.5. REPAIR 修復
3.4. Backup and Recovery
3.4.1. Import / Export
3.4.1.1. Export(Backup)
3.4.1.2. Import(Recovery)
3.4.1.3. xml
3.4.1.4. 備份表數據
3.4.1.5. source
3.4.1.6. 使用 mysqlhotcopy 備份 MyISAM 引擎的資料庫
3.4.1.7. AutoMySQLBackup
3.4.1.8. xtrabackup - Open source backup tool for InnoDB and XtraDB.
3.4.2. Snapshot Backup
3.4.2.1. LVM Snapshot
3.4.2.2. Btrfs Snapshot

3.1. User Account Management

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

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

3.1.3. Rename User

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

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