MySQL常用命令和操作

创建和删除数据库

# 创建数据库
CREATE DATABASE db_name DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
# 删除数据库
DROP DATABASE db_name;
# 更改数据库的字符编码
ALTER DATABASE db_name DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci

修改root用户密码

mysqladmin -u root password 'new-password'

用户管理和权限设置

刷新系统权限表

Tip: 用户和权限操作后,都要刷新系统权限表才能生效。

flush privileges;

用户管理

# 创建用户
insert into mysql.user(Host,User,Password) values("%","username",password("password"));
# 修改指定用户密码
update mysql.user set password=password('新密码') where User="username" and Host="%";
# 删除用户
DELETE FROM user WHERE User="username" and Host="%";

授权和撤销授权

# 管理员授权
GRANT all privileges ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;
# 用户授权
GRANT all privileges ON DBName.* TO username@"%" IDENTIFIED BY 'password';
# 取消授权
REVOKE all privileges ON DBName.* TO username@"%" IDENTIFIED BY 'password';

数据库备份与恢复

# 备份后直接压缩写入磁盘
mysqldump -u root -p --routines --events DBName | gzip > DBName.tar.gz
# 恢复
gunzip < DBName.tar.gz | mysql -u root -p

发表评论