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