Home | 简体中文 | 繁体中文 | 杂文 | 打赏(Donations) | OSChina 博客 | Facebook | Linkedin | 知乎专栏 | Search | About

第 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. INFORMATION_SCHEMA
3.4.1. 查询表字段
3.5. Backup and Recovery
3.5.1. Import / Export
3.5.1.1. Export(Backup)
3.5.1.2. Import(Recovery)
3.5.1.3. xml
3.5.1.4. 备份表数据
3.5.1.5. source
3.5.1.6. 使用 mysqlhotcopy 备份 MyISAM 引擎的数据库
3.5.1.7. AutoMySQLBackup
3.5.1.8. xtrabackup - Open source backup tool for InnoDB and XtraDB.
3.5.1.8.1. Percona yum Repository
3.5.1.8.2. Creating an Incremental Backup
3.5.2. Snapshot Backup
3.5.2.1. LVM Snapshot
3.5.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';