手把手教你--mysql多实例部署

mysql多实例部署

本地下载mysql
在浏览器中访问改地址，页面如下图所示：
https://dev.mysql.com/downloads/mysql/

点击 Archives

如图所示下载：

软件下载

下载二进制格式的mysql软件包，建议提前下好传入

[root@lzh ~]# cd /usr/src/
[root@lzh src]# wget https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.25-linux-glibc2.12-x86_64.tar.gz
此处下载过程省略......
[root@lzh src]# ls                    
debug  kernels  mysql-5.7.25-linux-glibc2.12-x86_64.tar.gz

创建用户和组

[root@lzh src]# groupadd -r mysql
[root@lzh src]# useradd -M -s /sbin/nologin -g mysql mysql

解压软件至/usr/local

[root@lzh src]# tar xf  mysql-5.7.25-linux-glibc2.12-x86_64.tar.gz  -C /usr/local/
[root@lzh src]# cd /usr/local/
[root@lzh local]# ls
bin  etc  games  include  lib  lib64  libexec  mysql-5.7.25-linux-glibc2.12-x86_64  sbin  share  src

创建软连接

[root@lzh local]# ln -sv mysql-5.7.25-linux-glibc2.12-x86_64/ mysql
"mysql" -> "mysql-5.7.25-linux-glibc2.12-x86_64/"
[root@lzh local]# ls
bin  games    lib    libexec  mysql-5.7.25-linux-glibc2.12-x86_64  shareetc  include  lib64  mysql    sbin                                 src

修改目录/usr/local/mysql的属主属组

[root@lzh local]# chown -R  mysql.mysql mysql /usr/local/mysql
[root@lzh local]# ll -d /usr/local/mysql
lrwxrwxrwx. 1 mysql mysql 36 8月   8 16:22 /usr/local/mysql -> mysql-5.7.25-linux-glibc2.12-x86_64/

配置环境变量
不配置环境变量则这样启动 /usr/local/mysql/bin/mysql start

[root@lzh local]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@lzh local]# source /etc/profile.d/mysql.sh 
[root@lzh local]# echo $PATH
/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
    /usr/local/mysql/bin/mysql start 不配置环境变量则这样启动

创建各实例数据存放目录

[root@lzh local]# mkdir -p /opt/data/{3306,3307,3308}
[root@lzh local]# chown -R mysql.mysql /opt/data/
[root@lzh local]# ll -d /opt/data/
drwxr-xr-x. 5 mysql mysql 42 8月   8 16:30 /opt/data/

初始化各实例
初始化3306实例

[root@lzh local]# mysqld --initialize --datadir=/opt/data/3306 --user=mysql
2020-08-08T08:53:36.895391Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-08-08T08:53:37.216803Z 0 [Warning] InnoDB: New log files created, LSN=45790
2020-08-08T08:53:37.293414Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2020-08-08T08:53:37.381809Z 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: a6018084-d954-11ea-be29-000c29c77a86.
2020-08-08T08:53:37.413287Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2020-08-08T08:53:37.414030Z 1 [Note] A temporary password is generated for root@localhost: uqZFpv84iS=k
[root@lzh local]# echo 'uqZFpv84iS=k' > 3306_pass

初始化3307实例

[root@lzh local]# mysqld --initialize --datadir=/opt/data/3307 --user=mysql
2020-08-08T08:55:29.957163Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-08-08T08:55:30.502217Z 0 [Warning] InnoDB: New log files created, LSN=45790
2020-08-08T08:55:30.545251Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2020-08-08T08:55:30.600298Z 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: e97d45e2-d954-11ea-8308-000c29c77a86.
2020-08-08T08:55:30.601578Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2020-08-08T08:55:30.602017Z 1 [Note] A temporary password is generated for root@localhost: oQqQwqHiI5!N
[root@lzh local]# echo 'oQqQwqHiI5!N' > 3307_pass

初始化3308实例

[root@lzh local]# mysqld --initialize --datadir=/opt/data/3308 --user=mysql
2020-08-08T08:55:51.838954Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-08-08T08:55:52.072076Z 0 [Warning] InnoDB: New log files created, LSN=45790
2020-08-08T08:55:52.118554Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2020-08-08T08:55:52.173987Z 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: f6592812-d954-11ea-84e6-000c29c77a86.
2020-08-08T08:55:52.174680Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2020-08-08T08:55:52.175108Z 1 [Note] A temporary password is generated for root@localhost: ge4UvlVD(b+/
[root@lzh local]# echo 'ge4UvlVD(b+/' > 3308_pass

安装perl

[root@lzh local]# yum -y install perl

配置配置文件/etc/my.cnf

[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin

[mysqld3306]
datadir = /opt/data/3306
port = 3306
socket = /tmp/mysql3306.sock
pid-file = /opt/data/3306/mysql_3306.pid
log-error = /var/log/3306.log

[mysqld3307]
datadir = /opt/data/3307
port = 3307
socket = /tmp/mysql3307.sock
pid-file = /opt/data/3306/mysql_3307.pid
log-error = /var/log/3307.log

[mysqld3308]
datadir = /opt/data/3308
port = 3308
socket = /tmp/mysql3308.sock
pid-file = /opt/data/3306/mysql_3308.pid
log-error = /var/log/3308.log

启动各实例

[root@lzh local]# mysqld_multi start 3306
[root@lzh local]# mysqld_multi start 3307
[root@lzh local]# mysqld_multi start 3308
[root@lzh local]# ss -antl
State       Recv-Q Send-Q        Local Address:Port                       Peer Address:Port              
LISTEN      0      128                       *:80                                    *:*                  
LISTEN      0      128                       *:22                                    *:*                  
LISTEN      0      100               127.0.0.1:25                                    *:*                  
LISTEN      0      80                       :::3307                                 :::*                  
LISTEN      0      80                       :::3308                                 :::*                  
LISTEN      0      128                      :::80                                   :::*                  
LISTEN      0      128                      :::22                                   :::*                  
LISTEN      0      100                     ::1:25                                   :::*                  
LISTEN      0      80                       :::3306                                 :::*    

初始化密码

[root@lzh local]# cat 3306_pass
uqZFpv84iS=k
[root@lzh local]# mysql -uroot -p'uqZFpv84iS=k' -S /tmp/mysql3306.sock
mysql: [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.7.25

Copyright (c) 2000, 2019, 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> set password = password('123.com');
Query OK, 0 rows affected, 1 warning (0.00 sec)

[root@lzh ~]# cat 3307_pass
oQqQwqHiI5!N
[root@lzh ~]# mysql -uroot -p'bpG+yqeGN8G:' -S /tmp/mysql3307.sock -e 'set password = password("123.lizihan!");' --connect-expired-password
mysql: [Warning] Using a password on the command line interface can be insecure.

[root@lzh ~]# cat 3308_pass
ge4UvlVD(b+/
[root@lzh ~]# mysql -uroot -p'bpG+yqeGN8G:' -S /tmp/mysql3308.sock -e 'set password = password("123.lzh!");' --connect-expired-password
mysql: [Warning] Using a password on the command line interface can be insecure.