MySQL用户管理 MySQL用户管理

MySQL多实例 1.什么是实例？

一个进程+多个线程+预分配的内存结构

2.什么是多实例？

多个进程+多个线程+多个预分配的内存结构

【MySQL用户管理】多个配置文件：

- 端口号 - binlog - datadir - socket文件 - 错误日志 - pid文件

1.创建配置文件存放目录

[root@db01 ~]# mkdir /data/{3307,3308,3309} -p

2.准备不同的配置文件

[root@db01 ~]# vim /data/3307/my.cnf [mysqld] port = 3307 log-bin = mysql-bin server_id = 7 datadir = /data/3307/data basedir = /application/mysql socket = /data/3307/data/mysql.sock log_error = /data/3307/data/error.log pid_file = /data/3307/data/mysql.pid

[root@db01 ~]# vim /data/3308/my.cnf[mysqld] port = 3308 log-bin = mysql-bin server_id = 8 datadir = /data/3308/data basedir = /application/mysql socket = /data/3308/data/mysql.sock log_error = /data/3308/data/error.log pid_file = /data/3308/data/mysql.pid

[root@db01 ~]# vim /data/3309/my.cnf[mysqld] port = 3309 log-bin = mysql-bin server_id = 9 datadir = /data/3309/data basedir = /application/mysql socket = /data/3309/data/mysql.sock log_error = /data/3309/data/error.log pid_file = /data/3309/data/mysql.pid

3.初始化出不同的datadir

[root@db01 ~]# cd /application/mysql/scripts/ [root@db01 scripts]# ./mysql_install_db --defaults-file=/data/3307/my.cnf --user=mysql --basedir=/application/mysql --datadir=/data/3307/data[root@db01 scripts]# ./mysql_install_db --defaults-file=/data/3308/my.cnf --user=mysql --basedir=/application/mysql --datadir=/data/3308/data[root@db01 scripts]# ./mysql_install_db --defaults-file=/data/3309/my.cnf --user=mysql --basedir=/application/mysql --datadir=/data/3309/data

4.启动

[root@db01 scripts]# mysqld_safe --defaults-file=/data/3307/my.cnf & [root@db01 scripts]# mysqld_safe --defaults-file=/data/3308/my.cnf & [root@db01 scripts]# mysqld_safe --defaults-file=/data/3309/my.cnf &

停服务

[root@db01 scripts]# mysqladmin -S /data/3307/data/mysql.sock shutdown [root@db01 scripts]# mysqladmin -S /data/3308/data/mysql.sock shutdown [root@db01 scripts]# mysqladmin -S /data/3309/data/mysql.sock shutdown

5.检查端口

[root@db01 scripts]# netstat -lntup tcp600 :::3306:::*LISTEN50821/mysqld tcp600 :::3307:::*LISTEN51145/mysqld tcp600 :::3308:::*LISTEN51674/mysqld tcp600 :::3309:::*LISTEN51856/mysqld

6.连接

[root@db01 scripts]# mysql -S /data/3307/data/mysql.sock [root@db01 scripts]# mysql -S /data/3308/data/mysql.sock [root@db01 scripts]# mysql -S /data/3309/data/mysql.sock

7.测试

[root@db01 scripts]# mysql -S /data/3307/data/mysql.sock -e 'show variables like "server_id"; ' [root@db01 scripts]# mysql -S /data/3308/data/mysql.sock -e 'show variables like "server_id"; ' [root@db01 scripts]# mysql -S /data/3309/data/mysql.sock -e 'show variables like "server_id"; '

8.编写连接脚本

[root@db01 scripts]# vim /usr/local/bin/mysql3307 mysql -S /data/3307/data/mysql.sock[root@db01 scripts]# vim /usr/local/bin/mysql3308 mysql -S /data/3308/data/mysql.sock[root@db01 scripts]# vim /usr/local/bin/mysql3309 mysql -S /data/3309/data/mysql.sock

9.编写启动脚本

[root@db01 system]# vim mysql3309.service [Unit] Description=MySQL Server Documentation=man:mysqld(8) Documentation=https://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=/application/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf LimitNOFILE = 5000

[root@db01 system]# vim mysql3307.service [Unit] Description=MySQL Server Documentation=man:mysqld(8) Documentation=https://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=/application/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf LimitNOFILE = 5000

[root@db01 system]# vim mysql3308.service [Unit] Description=MySQL Server Documentation=man:mysqld(8) Documentation=https://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=/application/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf LimitNOFILE = 5000

MySQL的用户管理

Linux用户管理	MySQL用户管理
创建用户：useradd 或者adduser -p	create user root@'%'; create user root@'%' identified by '123';
设置密码：passwd username	grant all on . to root@'%' identified by '123';
删除用户：userdel	drop user root@'::1' delete from mysql.user where user='root' and host='::1';
修改用户：usermod	update,grant,alter

MySQL用户定义

用户名@'主机域' root@'localhost' root@'%' root@'10.0.0.%' root@'10.0.%.%' root@'10.%.%.%' root@'10.0.0.5%' 范围：50~59 '5' root@'10.0.0.0/255.255.255.0'

MySQL修改root密码的方式 1.grant

mysql> grant all on *.* to root@'localhost' identified by '123';

2.mysqladmin

[root@db03 ~]# mysqladmin-uroot -p123 password '456'

3.update

mysql> update mysql.user set password=PASSWORD('1') where user='root' and host='localhost'; mysql> flush privileges;

4.set

#修改当前登录的用户密码 mysql> set password=PASSWORD('123');

生产案例 1.误删除所有用户

## 方法一： # 1.停止数据库 [root@db02 ~]# systemctl stop mysqld # 2.跳过授权表，跳过网络，启动MySQL [root@db02 ~]# mysqld_safe --skip-grant-tables --skip-networking & # 3.往表里插数据 mysql> insert into mysql.user(user,host,password) values('root','localhost',PASSWORD('123')); ERROR 1364 (HY000): Field 'ssl_cipher' doesn't have a default value mysql>insert into mysql.user(user,host,password,ssl_cipher,x509_issuer,x509_subject) values('root','localhost',PASSWORD(('123'),'','',''); insert into mysql.user values ('localhost','root',PASSWORD('123'), 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', '', '', '', '',0,0,0,0,'mysql_native_password','','N'); # 4.重启数据库 [root@db02 ~]# /etc/init.d/mysqld restart # 5.连接数据库 [root@db02 ~]# mysql -uroot -p123## 方法二： # 1.停库 [root@db02 ~]# /etc/init.d/mysqld stop # 2.跳过授权表，跳过网络，启动MySQL [root@db02 ~]# mysqld_safe --skip-grant-tables --skip-networking & # 3.刷新授权表 mysql> flush privileges; # 4.直接创建用户并授权给密码 mysql> grant all on *.* to root@'localhost' identified by '123' with grant option; # 5.重启数据库 [root@db02 ~]# /etc/init.d/mysqld restart

2.忘记root密码

# 1.停止MySQL [root@db01 ~]# systemctl stop mysqld # 2.跳过授权表，跳过网络，启动MySQL [root@db01 ~]# mysqld_safe --skip-grant-tables --skip-networking & # 3.修改密码 mysql> update mysql.user set password=PASSWORD('1') where user='root' and host='localhost'; # 4.刷新系统授权表 mysql> flush privileges; # 5.重启数据库 [root@db01 ~]# /etc/init.d/mysqld restart