1. 用户管理
- 1.1 作用 登录 管理对象
- 1.2 长成什么样?
用户名@‘白名单’
1.2.1 用户名: 不要太长 , 和业务有关
emp_user01
grant all on . to wordpress@’%’ identified ‘123’;
1.2.2 白名单?
user@‘10.0.0.56’
user@’%’
user@‘10.0.0.%’ 255.255.255.0 24位
user@‘10.0.0.0/255.255.254.0’ 23位
user@‘10.0.0.5%’ 50-59
user@‘localhost’ 本地
常用:
user@‘10.0.0.%’
user@‘10.0.0.0/255.255.254.0’
user@‘10.0.0.5%’ 50-59
user@‘localhost’ 本地 - 1.3 用户管理
创建用户:
mysql> CREATE USER oldguo@'10.0.0.%' IDENTIFIED BY '123';
查询用户:
mysql> select user,host from mysql.user;
mysql> select user,host,authentication_string from mysql.user;
修改用户:
mysql> alter user oldguo@'10.0.0.%' identified by '123456';
删除用户:
mysql> drop user oldguo@'10.0.0.%';
说明: 8.0+ 版本: 必须先创建用户再授权.
8.0以前: 可以grant 授权时自动创建用户.
2. 权限管理
- 2.1 MySQL权限列表
mysql> show privileges; - 2.2 授权和回收权限
GRANT 权限 ON 权限作用范围 TO 用户 IDENTIFIED BY ‘123’ with grant option;
权限作用范围 :
*.* 一般是管理员会设置的方法
oldguo.* 一般是业务用户会设置的方法
oldguo.t1 一般是业务用户户设置的方法
- 2.3 企业授权案例
(1)授权一个管理员用户oldguo,可以从10网段任意地址登录管理数据库
GRANT ALL ON *.* TO oldguo@'10.0.0.%' IDENTIFIED BY '123' with grant option;
(2)授权一个业务用户app,可以从10网段地址访问app库的所有表
grant select,update,insert,delete ON app.* TO app@'10.0.0.%' IDENTIFIED BY '123' ;
(3)授权一个开发用户dev,可以对dev库进行业务开发
- 2.4 root管理员密码忘记或被篡改如何处理?
(1) 关闭数据库,启动到"单用户"模式
systemctl stop mysqld
mysqld_safe --skip-grant-tables --skip-networking &
(2) 无密码登录MySQL
[root@db01 data_3306]# mysql
mysql> flush privileges;
mysql> alter user root@'localhost' identified by '123456';
(3) 重启数据库到正常模式
systemctl restart mysqld
- 2.5 查询用户权限
mysql> show grants for app@'10.0.0.%';
- 2.6 回收权限
mysql> revoke delete,drop on app.* from 'app'@'10.0.0.%';
3. MySQL的连接管理
- 3.1 自带客户端工具
mysql -u 用户名 -p 密码 -h IP -P 端口 -S socket位置 -e 免交互 < 导入SQL脚本
练习示例:
(1) TCP连接串远程登录 注:需要提前创建好远程用户
mysql> grant all on *.* to mdy@'10.0.0.%' identified by '123';
[root@db01 ~]# mysql -umdy -p -h10.0.0.51 -P 3306
Enter password:
(2) Socket连接方式 注:需要提前创建好localhost用户
mysql> grant all on *.* to mdy@'localhost' identified by '123';
[root@db01 data_3306]# mysql -umdy -p -S /tmp/mysql.sock
Enter password:
如何验证一个用户是通过本地还是远程和登录的
mysql> show processlist;
+----+------+------------+------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+------------+------+---------+------+----------+------------------+
| 5 | mdy | db01:51510 | NULL | Sleep | 38 | | NULL |
| 6 | mdy | localhost | NULL | Query | 0 | starting | show processlist |
+----+------+------------+------+---------+------+----------+------------------+
(3) 免交互执行命令
[root@db01 mysql]# mysql -uroot -p -e "show processlist"
Enter password:
+----+------+------------+------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+------------+------+---------+------+----------+------------------+
| 7 | mdy | db01:51512 | NULL | Sleep | 23 | | NULL |
| 8 | root | localhost | NULL | Query | 0 | starting | show processlist |
+----+------+------------+------+---------+------+----------+------------------+
(4) 导入SQL脚本
[root@db01 ~]# mysql -uroot -p < t100w.sql
Enter password:
mysql> source /root/world.sql
3.1.2
mysqladmin
(1) 修改密码
[root@db01 ~]# mysqladmin -uroot -p123456 password 123
[root@db01 ~]# mysql -uroot -p123
(2) 关闭数据库
[root@db01 ~]# mysqladmin -uroot -p123 shutdown
mysqldump(备份恢复) 略.
- 3.2 第三方开发工具
sqlyog
navicat
workbench
3.3 应用程序连接
php-mysql
pip3 install mysql
jar
go
4. MySQL的启动关闭
systemctl —> mysql.server start ----->
mysqld_safe ----> mysqld
5. MySQL的初始化配置
-
5.1 初始化配置方法
源码安装定制 < 初始化配置文件 < 命令行启动时定制 -
5.2 初始化配置文件
[root@db01 data_3306]# mysqld --help --verbose |grep my.cnf
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
建议一个mysql实例一个配置文件
- 5.3 配置文件书写格式
[root@db01 data_3306]# cat /etc/my.cnf
[mysqld]
user=mysql
port=3306
basedir=/usr/local/mysql57
datadir=/data/mysql/data_3306
server_id=6
socket=/tmp/mysql.sock
[mysql]
socket=/tmp/mysql.sock
标签项 ====> [mysqld]
服务器端 [server]: [mysqld],[mysqld_safe] ====> 影响到MySQL启动
客户端 [clinet] : [mysql] ,[mysqldump] ====> 影响本地客户端程序
配置项 ====> key=value
2. 5.4 自定制初识化配置文件位置
mysqld --defaults-file=/opt/a.cnf &
6. 多实例的规划和配置
分布式架构中应用广泛
- 6.1 端口和目录
mkdir -p /data/mysql/data*{3307,3308,3309}
7. 配置文件准备
cat > /data/mysql/my3307.cnf <<EOF
[mysqld]
user=mysql
port=3307
basedir=/usr/local/mysql57
datadir=/data/mysql/data_3307
server_id=7
socket=/tmp/mysql3307.sock
EOF
cat > /data/mysql/my3308.cnf <<EOF
[mysqld]
user=mysql
port=3308
basedir=/usr/local/mysql57
datadir=/data/mysql/data_3308
server_id=8
socket=/tmp/mysql3308.sock
EOF
cat > /data/mysql/my3309.cnf <<EOF
[mysqld]
user=mysql
port=3309
basedir=/usr/local/mysql57
datadir=/data/mysql/data_3309
server_id=9
socket=/tmp/mysql3309.sock
EOF
8. 授权
[root@db01 ~]# chown -R mysql.mysql /data/
9. 初始化数据
mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql57 --datadir=/data/mysql/data_3307
mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql57 --datadir=/data/mysql/data_3308
mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql57 --datadir=/data/mysql/data_3309
10. 启动多实例
[root@db01 mysql]# mysqld --defaults-file=/data/mysql/my3307.cnf &
[root@db01 mysql]# mysqld --defaults-file=/data/mysql/my3308.cnf &
[root@db01 mysql]# mysqld --defaults-file=/data/mysql/my3309.cnf &
[root@db01 mysql]# netstat -tulnp
11. 使用 systemd 管理多实例
cat >/etc/systemd/system/mysqld3307.service <<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=[http://dev.mysql.com/doc/refman/en/using-systemd.html](http://dev.mysql.com/doc/refman/en/using-systemd.html)
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql57/bin/mysqld --defaults-file=/data/mysql/my3307.cnf
LimitNOFILE = 5000
EOF
cat >/etc/systemd/system/mysqld3308.service <<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=[http://dev.mysql.com/doc/refman/en/using-systemd.html](http://dev.mysql.com/doc/refman/en/using-systemd.html)
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql57/bin/mysqld --defaults-file=/data/mysql/my3308.cnf
LimitNOFILE = 5000
EOF
cat >/etc/systemd/system/mysqld3309.service <<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=[http://dev.mysql.com/doc/refman/en/using-systemd.html](http://dev.mysql.com/doc/refman/en/using-systemd.html)
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql57/bin/mysqld --defaults-file=/data/mysql/my3309.cnf
LimitNOFILE = 5000
EOF
pkill mysqld
systemctl start mysqld3307
systemctl start mysqld3308
systemctl start mysqld3309