MySql 安装及实用笔记
安装
更新 rpm 包
rpm -Uvh http://repo.mysql.com//mysql57-community-release-el7-7.noarch.rpm
安装MySql
yum -y install mysql-community-server
启动 MySql 并设置为开机自启动服务
systemctl enable mysqld
systemctl start mysqld
检查 MySql 服务状态
systemctl status mysqld
修改 MySql 初始密码
第一次启动mysql,会在日志文件中生成root用户的一个随机密码,使用下面命令查看该密码
grep 'temporary password' /var/log/mysqld.log
修改root用户密码
使用刚刚的临时密码登录后,修改密码
# mysql -u root -p -h localhost
Enter password:
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '1lin24QWE!@#';
实用笔记
连接mysql
- 命令行
- sqlalchemy
mysql+pymysql://user:pwd@url:port/db_name
操作数据库
- 查看数据库
SHOW DATABASES;
- 选择数据库
USE db_name;
- 创建数据库
CREATE DATABASE `db_name` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
- 删除数据库
DROP DATABASE db_name;
操作表
- 查看表
SHOW TABLES;
- 查看表结构
DESC table_name;
退出MySql
exit
用户与权限管理
笔记
- 查看用户信息
use mysql;
select user,host,password from user;
- 查看用户权限
// 查看 user_name 在 host 下的权限
// host_address取值 ip/localhost/%
show grants for user_name@host_address;
show grants for user_name; //没有指定host则表示%
- 创建用户
create user user_name@host_address identified by 'password'; // 不指定host为%
例子:
create user lxp@localhost identified by 'lxps_password';
create user lxp@106.15.188.215 identified by 'lxps_password';
- 修改用户密码
set password for user_name@'host_address'=password('pwd');
flush privileges;
-- -----------------或者------------------
update user set password=password('iamsuperman') where user='superboy';
flush privileges;
-
删除用户
delete from user where user='user_name' and host='host_address'
DROP USER username@localhost;
-
用户权限管理
grant all on db_name.* to user_name@host_address identified by 'pwd';
flush privileges; // 一定要刷新权限
-- 赋予部分权限,其中的shopping.*表示对以shopping所有文件操作。
grant select,delete,update,insert on simpleshop.* to superboy@'localhost' identified by 'superboy';
flush privileges;
-- 赋予所有权限
grant all privileges on simpleshop.* to superboy@localhost identified by 'iamsuperboy';
flush privileges;
-- 撤销update权限
revoke update on simpleshop.* from superboy@localhost;
-- 撤销所有权限
revoke all on simpleshop.* from superboy@localhost;