mysql 可以多少个用户实例_MySQL运维---多实例

一、基本概述

多实例：

1)多套后台进程+线程+内存结构

2)多个配置文件

a.多个端口

b.多个socket文件

c.多个日志文件

d.多个server_id

3)多套数据

二、多实例实战

1、二进制安装得位置

2、创建另外三个实例的数据存放目录

3、创建配置文件

[root@controller 3307]# cat my.cnf

[mysqld]

basedir= /application/mysql

datadir= /data/3307/data

socket= /data/3307/mysql.sock

log_error= /data/3307/mysql.log

log_bin= /data/3307/mysql-bin

port= 3307server_id= 7[client]

socket= /data/3307/mysql.sock

剩下两个配置文件复制+替换

4、查看配置文件目录结构

5、授权

chown -R mysql.mysql /data

6、分别初始化三个实例

[root@controller scripts]# ./mysql_install_db --defaults-file=/data/3308/my.cnf --user=mysql --basedir=/application/mysql --datadir=/data/3308/data

Installing MySQL system tables...2020-09-11 16:30:10 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated.

Please use --explicit_defaults_for_timestamp server option (see documentation formore details).2020-09-11 16:30:10 0 [Note] Ignoring --secure-file-priv value as server is running with --bootstrap.2020-09-11 16:30:10 0 [Note] /application/mysql/bin/mysqld (mysqld 5.6.40-log) starting as process 1633...

OK

Filling help tables...2020-09-11 16:30:13 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated.

Please use --explicit_defaults_for_timestamp server option (see documentation formore details).2020-09-11 16:30:13 0 [Note] Ignoring --secure-file-priv value as server is running with --bootstrap.2020-09-11 16:30:13 0 [Note] /application/mysql/bin/mysqld (mysqld 5.6.40-log) starting as process 1655...

OK

To start mysqld at boot time you have to copy

support-files/mysql.server to the right place foryour system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER!Todoso, start the server, then issue the following commands:/application/mysql/bin/mysqladmin -u root password 'new-password'

/application/mysql/bin/mysqladmin -u root -h controller password 'new-password'Alternatively you can run:/application/mysql/bin/mysql_secure_installation

7、分别启动三个实例

[root@controller scripts]# mysqld_safe --defaults-file=/data/3308/my.cnf &[2] 1678[root@controller scripts]#200911 16:31:20 mysqld_safe Logging to '/data/3308/mysql.log'.200911 16:31:20 mysqld_safe Starting mysqld daemon with databases from /data/3308/data

8、查看端口验证

二、登录多实例

1、登录

[root@controller scripts]# mysql -uroot -p -S /data/3308/mysql.sock

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection idis 2Server version:5.6.40-log MySQL Community Server (GPL)

Copyright (c)2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracleis a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type'help;' or '\h' for help. Type '\c'to clear the current input statement.

mysql>

2、设置密码

[root@controller scripts]# mysqladmin -uroot -p -S /data/3307/mysql.sock password 123456Enter password:

Warning: Using a password on the command lineinterface can be insecure.

3、验证

[root@controller scripts]# mysql -uroot -p -S /data/3308/mysql.sock

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection idis 7Server version:5.6.40-log MySQL Community Server (GPL)

Copyright (c)2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracleis a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type'help;' or '\h' for help. Type '\c'to clear the current input statement.

mysql> show variables like 'server_id';+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| server_id | 8 |

+---------------+-------+

1 row in set (0.00sec)

mysql>

4、脚本

[root@controller scripts]# vim /usr/sbin/mysql3307

mysql-uroot -p3307 -S /data/3307/mysql.sock

5、授权

[root@controller scripts]# chmod +x /usr/sbin/mysql*

[root@controller scripts]# chmod 700 /usr/sbin/mysql* 推荐用这个，只能root执行

6、登录

[root@controller scripts]# mysql3307

Warning: Using a password on the command lineinterfacecan be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection idis 2Server version:5.6.40-log MySQL Community Server (GPL)

Copyright (c)2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracleis a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type'help;' or '\h' for help. Type '\c'to clear the current input statement.

mysql>

三、误删除mysql里面所有用户

1、误删所有用户

mysql> truncate mysql.user; 删除user表的所有用户

2、登录

[root@controller tmp]# mysqld_safe --skip-grant-tables & 这种方式启动mysql，跳过权限验证，远程，本地，都可以免密登录，生产环境不建议使用

3、登录

[root@controller tmp]# mysqld_safe --skip-grant-tables --skip-networking & 这种方式启动mysql，允许本地，免密登录

4、创建用户

方法一：

方法二：

insert into mysql.user values ('localhost','root',PASSWORD('123456'),'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');

5、授权

查看用户，没有任何权限

6、赋予超级权限

7、查看用户权限

8、退出mysql，再以正常方式启动mysql。就可以了。