Mysql用户授权和多实例

mysql 表的物理存储结构

MyISAM（一种引擎）的表：
-rw-r----- 1 mysql mysql   10816 Apr 18 11:37 user.frm
-rw-r----- 1 mysql mysql     396 Apr 18 12:20  user.MYD
-rw-r----- 1 mysql mysql    4096 Apr 18 14:48 user.MYI
InnoDB(默认的存储引擎)的表：
-rw-r----- 1 mysql mysql    8636 Apr 18 11:37 time_zone.frm
-rw-r----- 1 mysql mysql   98304 Apr 18 11:37 time_zone.ibd
time_zone.frm：存储列相关信息
time_zone.ibd：数据行+索引

用户管理

用户名@'白名单'
白名单支持的方式？
zhangsan@'10.0.0.%'        表示zhangsan 可以在10.0.0.0/24 这个网段访问数据库 
zhangsan@'%'                表示zhangsan可以在任何地方访问数据库
zhangsan@'10.0.0.200'        表示zhangsan只能在10.0.0.200这台主机上访问数据库
zhangsan@'localhost'         表示zhangsan只能在数据库服务器上访问数据库
zhangsan@'10.0.0.5%'         表示zhangsan可以在10.0.0.50-59这几台主机上访问数据库
zhangsan@'10.0.0.0/255.255.254.0'  表示zhangsan可以zazai 10.0.0.0/23这个网段访问数据库

创建用户

mysql> create user zhangsan@'192.168.57.%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

查询用户

mysql> select user ,host from mysql.user;
+---------------+--------------+
 user  host 
+---------------+--------------+
 zhangsan  192.168.57.% 
 mysql.session  localhost 
 mysql.sys  localhost 
 root  localhost 
+---------------+--------------+
4 rows in set (0.00 sec)

修改用户

mysql> alter user zhangsan@'192.168.57.%' identified by '654321';
Query OK, 0 rows affected (0.00 sec)

删除用户

mysql> drop user zhangsan@'192.168.57.%';
Query OK, 0 rows affected (0.01 sec)

权限管理

创建权限

mysql> grant all on blog.\* to blog@'192.168.57.%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

常用权限列表

ALL:
SELECT,INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE
ALL : 以上所有权限，一般是普通管理员拥有的
with grant option：超级管理员才具备的，给别的用户授权的功能

查看权限

mysql> show grants for blog@'192.168.57.%';
+-----------------------------------------------------------+
 Grants for blog@192.168.57.% 
+-----------------------------------------------------------+
 GRANT USAGE ON *.* TO 'blog'@'192.168.57.%' 
 GRANT ALL PRIVILEGES ON `blog`.* TO 'blog'@'192.168.57.%' 
+-----------------------------------------------------------+
2 rows in set (0.00 sec)

回收权限

mysql> revoke delete on blog.* from blog@'192.168.57.%';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for blog@'192.168.57.%';
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 Grants for blog@192.168.57.% 
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 GRANT USAGE ON *.* TO 'blog'@'192.168.57.%' 
 GRANT SELECT, INSERT, UPDATE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `blog`.* TO 'blog'@'192.168.57.%' 
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Mysql root密码忘记处理方法

使用mysqld_safe启动 取消加载授权表和网络  把权限刷入内存 在alter修改   重启mysql

[root@db01 ~]# mysqld_safe --skip-grant-tables --skip-networking &
mysql> flush privileges;
mysql>  alter user root@'localhost' identified by '123456';
[root@db01 ~]# pkill mysqld
[root@db01 ~]# systemctl start  mysqld

Mysql 连接常用参数

-u 用户

-p 密码

-h IP

-P 端口

-S socket文件

-e 免交互执行命令

< 导入SQL脚本

创建mysql多实例

mkdir -p /data/330{7,8,9}/data

创建配置文件

[root@localhost ~]# cat > /data/3307/my.cnf <<EOF
> [mysqld]
> basedir=/usr/local/mysql
> datadir=/data/3307/data
> socket=/data/3307/mysql.sock
> log_error=/data/3307/mysql.log
> port=3307
> server_id=7
> log_bin=/data/3307/mysql-bin
> EOF
[root@localhost ~]# 
[root@localhost ~]# cat > /data/3308/my.cnf <<EOF
> [mysqld]
> basedir=/usr/local
> datadir=/data/3308/data
> socket=/data/3308/mysql.sock
> log_error=/data/3308/mysql.log
> port=3308
> server_id=8
> log_bin=/data/3308/mysql-bin
> EOF


[root@localhost ~]# cat > /data/3309/my.cnf <<EOF
> [mysqld]
> basedir=/usr/local
> datadir=/data/3309/data
> socket=/data/3309/mysql.sock
> log_error=/data/3309/mysql.log
> port=3309
> server_id=9
> log_bin=/data/3309/mysql-bin
> EOF

初始化三套数据

mv /etc/my.cnf /etc/my.cnf.bak
mysqld --initialize-insecure --user=mysql --datadir=/data/3307/data --basedir=/usr/local/mysql
mysqld --initialize-insecure --user=mysql --datadir=/data/3308/data --basedir=/usr/local/mysql
mysqld --initialize-insecure --user=mysql --datadir=/data/3309/data --basedir=/usr/local/mysql

systemd管理多实例

cd /etc/systemd/system
cp mysqld.service mysqld3307.service
cp mysqld.service mysqld3308.service
cp mysqld.service mysqld3309.service

vim mysqld3307.service
ExecStart=/usr/local/mysql/bin/mysqld  --defaults-file=/data/3307/my.cnf
vim mysqld3308.service
ExecStart=/usr/local/mysql/bin/mysqld  --defaults-file=/data/3308/my.cnf
vim mysqld3309.service
ExecStart=/usr/local/mysql/bin/mysqld  --defaults-file=/data/3309/my.cnf

授权

chown -R mysql.mysql /data/*

启动

systemctl start mysqld3307.service
systemctl start mysqld3308.service
systemctl start mysqld3309.service

验证

[root@localhost system]# netstat -lnpgrep 330
tcp6 0 0 :::3307 :::* LISTEN 24669/mysqld 
tcp6 0 0 :::3308 :::* LISTEN 24681/mysqld 
tcp6 0 0 :::3309 :::* LISTEN 24744/mysqld 
tcp6 0 0 :::3306 :::* LISTEN 5412/mysqld 
unix 2 [ ACC ] STREAM LISTENING 608826 24681/mysqld /data/3308/mysql.sock
unix 2 [ ACC ] STREAM LISTENING 608823 24669/mysqld /data/3307/mysql.sock
unix 2 [ ACC ] STREAM LISTENING 609060 24744/mysqld /data/3309/mysql.sock