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

第 61 章 Database Administration

目录

61.1. User Account Management
61.1.1. Create User
61.1.2. Drop User
61.1.3. Rename User
61.1.4. SET PASSWORD
61.2. Access Privilege System
61.2.1. SHOW GRANTS
61.2.2. show privileges
61.2.3. Grant privileges
61.2.4. Revoke privileges
61.2.5. Show Privileges
61.2.6. MAX_QUERIES_PER_HOUR/MAX_UPDATES_PER_HOUR
61.2.7. Table Privileges
61.2.8. Column Privileges
61.3. Maintenance 数据库维护
61.3.1. CHECK 检查表
61.3.2. ANALYZE 分析表
61.3.3. CHECKSUM
61.3.4. OPTIMIZE 优化表
61.3.5. REPAIR 修复
61.4. INFORMATION_SCHEMA
61.4.1. 查询表字段
61.4.2. 列出所有触发器
61.5. Backup and Recovery
61.5.1. Import / Export
61.5.1.1. Export(Backup)
61.5.1.2. Import(Recovery)
61.5.1.3. xml
61.5.1.4. 备份表数据
61.5.1.5. source
61.5.1.6. 使用 mysqlhotcopy 备份 MyISAM 引擎的数据库
61.5.1.7. AutoMySQLBackup
61.5.1.8. xtrabackup - Open source backup tool for InnoDB and XtraDB.
61.5.1.8.1. Percona yum Repository
61.5.1.8.2. Creating an Incremental Backup
61.5.2. Snapshot Backup
61.5.2.1. LVM Snapshot
61.5.2.2. Btrfs Snapshot

61.1. User Account Management

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

			
			

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

61.1.3. Rename User

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

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