Home | 简体中文 | 繁体中文 | 杂文 | 打赏(Donations) | Github | OSChina 博客 | 云社区 | 云栖社区 | Facebook | Linkedin | 知乎专栏 | 视频教程 | About

第 60 章 Database Administration

目录

60.1. User Account Management
60.1.1. Create User
60.1.2. Drop User
60.1.3. Rename User
60.1.4. SET PASSWORD
60.2. Access Privilege System
60.2.1. SHOW GRANTS
60.2.2. show privileges
60.2.3. Grant privileges
60.2.4. Revoke privileges
60.2.5. Show Privileges
60.2.6. MAX_QUERIES_PER_HOUR/MAX_UPDATES_PER_HOUR
60.2.7. Table Privileges
60.2.8. Column Privileges
60.3. Maintenance 数据库维护
60.3.1. CHECK 检查表
60.3.2. ANALYZE 分析表
60.3.3. CHECKSUM
60.3.4. OPTIMIZE 优化表
60.3.5. REPAIR 修复
60.4. INFORMATION_SCHEMA
60.4.1. 查询表字段
60.4.2. 列出所有触发器
60.5. Backup and Recovery
60.5.1. Import / Export
60.5.1.1. Export(Backup)
60.5.1.2. Import(Recovery)
60.5.1.3. xml
60.5.1.4. 备份表数据
60.5.1.5. source
60.5.1.6. 使用 mysqlhotcopy 备份 MyISAM 引擎的数据库
60.5.1.7. AutoMySQLBackup
60.5.1.8. xtrabackup - Open source backup tool for InnoDB and XtraDB.
60.5.1.8.1. Percona yum Repository
60.5.1.8.2. Creating an Incremental Backup
60.5.2. Snapshot Backup
60.5.2.1. LVM Snapshot
60.5.2.2. Btrfs Snapshot

60.1. User Account Management

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

MySQL 8.0

			
mysql> CREATE USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'pMQiEge1ikst7S_6tlXzBOmt_4b';
Query OK, 0 rows affected (0.06 sec)

mysql> grant all on *.* to 'root'@'%';
Query OK, 0 rows affected (0.11 sec)

			
			

60.1.2. Drop User

DROP USER user [, user] ...
			
			
mysql> drop user 'root'@'%';
Query OK, 0 rows affected (0.02 sec)
			
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)
			
			

60.1.3. Rename User

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

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