多实例
# ls /var/lib/mysql
... ... mysql.sock 数据库运行时，才会产生mysql.sock这个文件，它是提供数据库传输数据的服务的文件。
# systemctl stop mysqld 关掉数据库服务
# mysql -uroot -p123456 试着连接数据库
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
提示找不到/var/lib/mysql/mysql.sock，所以无法登陆数据库
# ls /var/lib/mysql
... ... 这里也找不到mysql.sock这个文件了。
# systemctl start mysqld 启动数据库服务
# ls /var/lib/mysql
... ... mysql.sock 再次数据库运行后，mysql.sock这个文件又出现了
# ss -tunlp | grep :3306 查看3306端口时，能看到数据库进程的pid号是6143
tcp LISTEN 0 80 :::3306 :::* users:(("mysqld",pid=6143,fd=17))
# cat /var/run/mysqld/mysqld.pid 这个文件存的就是数据库进程的pid号6143
6143
-------------------------------------------------------------------------------------------------------------------
# systemctl stop mysqld 停掉mysql5.7版本，因为不支持多实例
# systemctl disable mysqld 开机不自启用disable
Removed symlink /etc/systemd/system/multi-user.target.wants/mysqld.service.
-------------------------------------------------------------------------------------------------------------------
真机拷贝给50主机这个包，一共612兆。mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz
# du -sh mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz 通过du -sh查看包的大小共612兆
612M mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz
# rpm -q gcc gcc-c++ 查看是否已安装它的2个依赖包
未安装软件包 gcc
未安装软件包 gcc-c++
# yum -y install gcc gcc-c++ 安装依赖包gcc,gcc-c++
# tar -zxvf mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz 解压
# ls
mysql-5.7.20-linux-glibc2.12-x86_64 解压后有这个同名的目录
# ls /usr/local/ 这个没有mysql这个目录
bin etc games include lib lib64 libexec sbin share src
# mv mysql-5.7.20-linux-glibc2.12-x86_64 /usr/local/mysql 源码包习惯放在这个目录下
# cd /usr/local/
# ls
bin etc games include lib lib64 libexec mysql sbin share src
# cd mysql/
# ls
bin COPYING docs include lib man README share support-files
# ls /usr/local/mysql/bin/mysqld_multi
-----------------------------------------------------------------------------------------------------------------------------
# ls /etc/my.cnf
/etc/my.cnf
# mv /etc/my.cnf /root/
# vim /etc/my.cnf 全部纯手打！
[mysqld_multi] 一定要有d！有d才是多实例！
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
user = root
[mysqld1] 一定要有d！第一个数据库
datadir=/data3307 指定目录
port=3307 指定端口,要和系统默认的端口3306区分开来,自己定义
log-error=/data3307/mysql3307.log
pid-file=/data3307/mysql3307.pid
socket=/data3307/mysql3307.socket
[mysqld2] 可以复制上面的mysqld1,然后改成第二个数据库
datadir=/data3308
port=3308
log-error=/data3308/mysql3308.log
pid-file=/data3308/mysql3308.pid
socket=/data3308/mysql3308.socket
-------------------------------------------------------------------------------------------------------------------------
# mkdir /data3307
# mkdir /data3308
--------------------------------------------------------------------------------------------------------------------------
如果目录下是空的，直接启动多实例，会自己创建
# ls /data3307
# ls /data3308
# /usr/local/mysql/bin/mysqld_multi start 1
显示信息如下:
Installing new database in /data3307
2018-11-24T09:34:33.270983Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-11-24T09:34:35.351345Z 0 [Warning] InnoDB: New log files created, LSN=45790
2018-11-24T09:34:35.787663Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2018-11-24T09:34:35.970962Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 28162e98-efcc-11e8-8dcf-525400a51722.
2018-11-24T09:34:36.006621Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2018-11-24T09:34:36.008037Z 1 [Note] A temporary password is generated for root@localhost: ,qQyLidmg96d
---------------------------------------------------------------------------------------------------------------------------
初始化密码在最后一行',qQyLidmg96d'就是这个，不要包含空格！
如果忘了密码，把数据库删掉，然后重新初始化一遍！
一定要用单引号把密码引起来,防止有特殊符号。
# /usr/local/mysql/bin/mysql -uroot -p',qQyLidmg96d' -S /data3307/mysql3307.sock 是大S
登陆数据库后一定要强制要求改密码,否则连查询权限都没有。
mysql> show databases; 没改密码，查询时报错
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> alter user root@"localhost" identified by "123456"; 没有密码策略要求
# /usr/local/mysql/bin/mysql -uroot -p123456 -S /data3307/mysql3307.sock 用新密码重新登陆
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
mysql> create database db1;create table db1.a(id int);
mysql> insert into db1.a values(666);
mysql> select * from db1.a;
+------+
| id |
+------+
| 666 |
+------+
---------------------------------------------------------------------------------------------------------------------
# ss -tunlp | grep :3307
tcp LISTEN 0 80 :::3307 :::* users:(("mysqld",pid=7317,fd=16))
# ss -tunlp | grep :3308
# /usr/local/mysql/bin/mysqld_multi --user=root --password=123456 stop 1 停止，关闭3307端口服务
------------------------------------------------------------------------------------------------------------------
如果3307配置错了，可以
# rm -rf /data3307/* 先清空3307默认的目录下所有内容
# ls /data3307/
# vim /etc/my.cnf
改成正确的配置
# /usr/local/mysql/bin/mysqld_multi start 1
页面如下:
Installing new database in /data3307
2018-11-24T11:01:01.367322Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-11-24T11:01:03.297874Z 0 [Warning] InnoDB: New log files created, LSN=45790
2018-11-24T11:01:03.792466Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2018-11-24T11:01:03.961451Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 3c5eb565-efd8-11e8-b0ee-525400a51722.
2018-11-24T11:01:03.998030Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2018-11-24T11:01:03.998900Z 1 [Note] A temporary password is generated for root@localhost: jz0_FXd*kQO0
# /usr/local/mysql/bin/mysql -uroot -p'jz0_FXd*kQO0' -S /data3307/mysql3307.socket
mysql> alter user root@"localhost" identified by "123456";
mysql> create database db1;
mysql> insert into db1.a values(666);
mysql> select * from db1.a;
+------+
| id |
+------+
| 666 |
+------+
mysql> quit
Bye
# ls /data3307/db1/ 在对应目录里生成了刚才我们创建的文件
a.frm a.ibd db.opt
# ss -tunlp | grep :3307
------------------------------------------------------------------------------------------------------------------------------------------
初始化3308
# /usr/local/mysql/bin/mysqld_multi start 2
页面显示如下：
Installing new database in /data3308
2018-11-24T11:17:20.100495Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-11-24T11:17:21.920147Z 0 [Warning] InnoDB: New log files created, LSN=45790
2018-11-24T11:17:22.437348Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2018-11-24T11:17:22.606674Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 83b01e12-efda-11e8-a4cc-525400a51722.
2018-11-24T11:17:22.642760Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2018-11-24T11:17:22.644395Z 1 [Note] A temporary password is generated for root@localhost: r-o-sS+LF7fF
# /usr/local/mysql/bin/mysql -uroot -p'r-o-sS+LF7fF' -S /data3308/mysql3308.socket
mysql> alter user root@"localhost" identified by "123456";
mysql> create database db1;
mysql> insert into db1.a values(666);
mysql> select * from db1.a;
+------+
| id |
+------+
| 666 |
+------+
# ls /data3308/db1/
a.frm a.ibd db.opt
# ss -tunlp | grep :3308
# /usr/local/mysql/bin/mysqld_multi -uroot -p123465 stop 1 如果我们选项写错了，它会报错
报错显示如下：
Unknown option: uroot 未知选项: uroot，这里就提示我们哪个选项错了
Unknown option: p123465
Error with an option, see mysqld_multi --help for more info.
正确关闭3308服务
# /usr/local/mysql/bin/mysqld_multi --user=root --password=123456 stop 2
# ss -tunlp | grep :3308 关闭了就查不到3308端口了
# /usr/local/mysql/bin/mysql -uroot -p123456 -S /data3308/mysql3308.socket 关闭服务后是无法连接3308的
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/data3308/mysql3308.socket' (2)
它提示找不到/data3308/mysql3308.socket
只要启服务，再连接数据库就可以了。
# /usr/local/mysql/bin/mysqld_multi --user=root --password=123456 start 2 启动服务
# /usr/local/mysql/bin/mysql -uroot -p123456 -S /data3308/mysql3308.socket 成功登陆
