第 3 章 Database Administration-阿里云开发者社区

第 3 章 Database Administration

2018-01-05 841

简介：

+关注继续查看

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

原文出处：Netkiller 系列手札
本文作者：陈景峯
转载请与作者联系，同时请务必标明文章原始出处和作者信息及本声明。