Home | 簡體中文 | 繁體中文 | 雜文 | 打賞(Donations) | 雲棲社區 | OSChina 博客 | Facebook | Linkedin | 知乎專欄 | Github | Search | About

第 49 章 Database Administration

目錄

49.1. User Account Management
49.1.1. Create User
49.1.2. Drop User
49.1.3. Rename User
49.1.4. SET PASSWORD
49.2. Access Privilege System
49.2.1. SHOW GRANTS
49.2.2. show privileges
49.2.3. Grant privileges
49.2.4. Revoke privileges
49.2.5. Show Privileges
49.2.6. MAX_QUERIES_PER_HOUR/MAX_UPDATES_PER_HOUR
49.2.7. Table Privileges
49.2.8. Column Privileges
49.3. Maintenance 資料庫維護
49.3.1. CHECK 檢查表
49.3.2. ANALYZE 分析表
49.3.3. CHECKSUM
49.3.4. OPTIMIZE 優化表
49.3.5. REPAIR 修復
49.4. INFORMATION_SCHEMA
49.4.1. 查詢表欄位
49.4.2. 列出所有觸發器
49.5. Backup and Recovery
49.5.1. Import / Export
49.5.1.1. Export(Backup)
49.5.1.2. Import(Recovery)
49.5.1.3. xml
49.5.1.4. 備份表數據
49.5.1.5. source
49.5.1.6. 使用 mysqlhotcopy 備份 MyISAM 引擎的資料庫
49.5.1.7. AutoMySQLBackup
49.5.1.8. xtrabackup - Open source backup tool for InnoDB and XtraDB.
49.5.1.8.1. Percona yum Repository
49.5.1.8.2. Creating an Incremental Backup
49.5.2. Snapshot Backup
49.5.2.1. LVM Snapshot
49.5.2.2. Btrfs Snapshot

49.1. User Account Management

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

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

49.1.3. Rename User

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

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