一、基本概述
查看最新文章更新：请点击这里
多实例：
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 = 3307 server_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 for more 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 for more 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 for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, 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 id is 2 Server version: 5.6.40-log MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is 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 123456 Enter password: Warning: Using a password on the command line interface 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 id is 7 Server version: 5.6.40-log MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is 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.00 sec) 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 line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 2 Server version: 5.6.40-log MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is 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。就可以了。