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

第 22 章 Database Administration

目錄

22.1. User Account Management
22.1.1. Create User
22.1.2. Drop User
22.1.3. Rename User
22.1.4. SET PASSWORD
22.2. Access Privilege System
22.2.1. SHOW GRANTS
22.2.2. show privileges
22.2.3. Grant privileges
22.2.4. Revoke privileges
22.2.5. Show Privileges
22.2.6. MAX_QUERIES_PER_HOUR/MAX_UPDATES_PER_HOUR
22.2.7. Table Privileges
22.2.8. Column Privileges
22.3. Maintenance 資料庫維護
22.3.1. CHECK 檢查表
22.3.2. ANALYZE 分析表
22.3.3. CHECKSUM
22.3.4. OPTIMIZE 優化表
22.3.5. REPAIR 修復
22.4. Backup and Recovery
22.4.1. Import / Export
22.4.1.1. Export(Backup)
22.4.1.2. Import(Recovery)
22.4.1.3. xml
22.4.1.4. 備份表數據
22.4.1.5. source
22.4.1.6. 使用 mysqlhotcopy 備份 MyISAM 引擎的資料庫
22.4.1.7. AutoMySQLBackup
22.4.1.8. xtrabackup - Open source backup tool for InnoDB and XtraDB.
22.4.2. Snapshot Backup
22.4.2.1. LVM Snapshot
22.4.2.2. Btrfs Snapshot

22.1. User Account Management

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

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

22.1.3. Rename User

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

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