02-MySQL基础管理

2019-12-17 本文已影响0人 UncleZ_strive

1. 用户管理

1.作用

2.长成什么样?

1.用户名@'白名单'

用户名:
不要太长 , 和业务有关
白名单?
允许链接的网段
常用:
user@'10.0.0.%'
user@'10.0.0.0/255.255.254.0'
user@'10.0.0.5%' 50-59
user@'localhost' 本地

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. 权限管理

1.MySQL权限列表

查看权限列表 mysql> show privileges;

2.授权和回收权限

授权
GRANT 权限 ON 权限作用范围 TO 用户 IDENTIFIED BY '123' with grant option;

权限作用范围 :
. ====> 一般是管理员会设置的方法
wordpress.* ====> 一般是业务用户会设置的方法
wordpress.t1 ====> 一般是业务用户户设置的方法

3.企业授权案例

授权一个管理员用户wordpress,可以从10网段任意地址登录管理数据库
GRANT ALL ON *.* TO wordpress@'10.0.0.%' IDENTIFIED BY '123' with grant option;
授权一个业务用户app,可以从10网段地址访问app库的所有表
grant select,update,insert,delete ON app.* TO app@'10.0.0.%' IDENTIFIED BY '123' ;

4.root管理员密码忘记或被篡改如何处理?

关闭数据库,启动到"单用户"模式

[root@db01 data_3306]# systemctl stop mysqld
[root@db01 data_3306]# mysqld_safe  --skip-grant-tables  --skip-networking  &

无密码登录MySQL

[root@db01 data_3306]# mysql
直接修改密码会报错
mysql> alter user root@'localhost' identified by '123456';
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

刷新MySQL的系统权限相关表
mysql> flush privileges;
mysql> alter user root@'localhost' identified by '123456';

重启数据库到正常模式

[root@db01 data_3306]# systemctl restart mysqld

5.查询用户权限

mysql> show grants for app@'10.0.0.%';

6.回收权限

mysql> revoke delete,drop on app.* from 'app'@'10.0.0.%';

3. MySQL的连接管理

1.自带客户端工具 mysql

-u  用户名 
-p  密码
-h  IP
-P  端口
-S  socket位置
-e  免交互
<   导入SQL脚本

例子:
(1) TCP连接串远程登录

mysql> grant all on *.* to oldguo@'10.0.0.%' identified by '123';
[root@db01 data_3306]# mysql -uroot -p -h 10.0.0.51 -P 3306

需要提前创建好远程用户

(2) Socket连接方式

mysql> grant all on *.* to oldguo@'localhost' identified by '123';
[root@db01 data_3306]# mysql -uoldguo -p -S /tmp/mysql.sock 
Enter password:

需要提前创建好localhost用户

(3)如何验证一个用户是通过本地还是远程和登录的.

show processlist;

(4) 免交互执行命令

[root@db01 ~]# mysql -uroot -p -e "show processlist"

(5) 导入SQL脚本
在linux上执行
mysql -uroot -p123456 < t100w.sql
进入MySQL执行
source /root/world.sql

2.自带客户端工具 mysqladmin

(1) 修改密码
mysqladmin -uroot -p123456 password 123

(2) 关闭数据库
mysqladmin -uroot -p123 shutdown

3.第三方开发工具

sqlyog 
navicat
workbench

4.应用程序连接

php-mysql 
pip3 install mysql 
jar 
go

4.MySQL的启动关闭

systemctl start mysqld
mysql.server start 
mysqld_safe
mysqld

5. MySQL的初始化配置

1.初始化配置方法

源码安装定制 < 初始化配置文件 < 命令行启动时定制

2.初始化配置文件

mysqld --help --verbose |grep my.cnf
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf

建议一个mysql实例一个配置文件

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

4.自定制初识化配置文件位置

mysqld --defaults-file=/opt/a.cnf &

6.多实例的规划和配置

分布式架构中应用广泛
1.创建存放数据的目录，并授权
mkdir -p /data/mysql/{data_3307,data_3308,data_3309}
chown -R mysql.mysql /data
2.编写my.cnf配置文件

[root@db01 ~]# cat /data/mysql/my3307.cnf 
[mysqld]
user=mysql
port=3307-------------------------------------------分布式修改不同端口
basedir=/usr/local/mysql57
datadir=/data/mysql/data_3307-----------------------分布式修改不同数据存放目录
server_id=7-----------------------------------------分布式id不能相同
socket=/tmp/mysql3307.sock--------------------------分布式生成不同的socket文件

3.初始化数据

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

4.启动多实例

mysqld --defaults-file=/data/mysql/my3307.cnf &
mysqld --defaults-file=/data/mysql/my3308.cnf &
mysqld --defaults-file=/data/mysql/my3309.cnf &

5.使用systemctl管理多实例

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

使用systemctl启动

systemctl start mysqld3307
systemctl start mysqld3308
systemctl start mysqld3309