一、MySQL多实例配置

1.1.1 MySQL多实例配置

1. 创建多实例目录

[ /]# mkdir -p /data/330{7,8,9}/data
[ /]# ll /data/330{7,8,9}/data
/data/3307/data:
total 0

/data/3308/data:
total 0

/data/3309/data:
total 0

1. 创建多实例配置文件

#3307
[ /]# cat > /data/3307/my.cnf <<EOF
> [mysqld]
> basedir=/application/mysql
> datadir=/data/3307/data
> socket=/data/3307/mysql.sock
> log_error=/data/3307/mysql.log
> port=3307
> server_id=7
> log_bin=/data/3307/mysql-bin
> EOF

#3308
[ /]# cat >> /data/3308/my.cnf <<EOF
> [mysqld]
> basedir=/application/mysql
> datadir=/data/3308/data
> socket=/data/3308/mysql.sock
> log_error=/data/3308/mysql.err
> port=3308
> server_id=8
> log_bin=/data/3308/mysql-bin
> EOF


#3309
[ /]# cat > /data/3309/my.cnf <<EOF
> [mysqld]
> basedir=/application/mysql
> datadir=/data/3309/data
> socket=/data/3309/mysql.sock
> log_error=/data/3309/mysql.log
> port=3309
> server_id=9
> log_bin=/data/3309/mysql-bin
> EOF

1. 初始化多实例数据库

#初始化3307
[ /]# mysqld --initialize-insecure  --user=mysql --basedir=/application/mysql --datadir=/data/3307/data
2020-06-03T10:40:28.642248Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-06-03T10:40:31.730097Z 0 [Warning] InnoDB: New log files created, LSN=45790
2020-06-03T10:40:32.101976Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2020-06-03T10:40:32.189902Z 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: a64409a6-a586-11ea-95ee-000c290e8d03.
2020-06-03T10:40:32.190639Z 0 [Warning] Gtid table is not ready to be used. Table ‘mysql.gtid_executed‘ cannot be opened.
2020-06-03T10:40:32.191541Z 1 [Warning]  is created with an empty password ! Please consider switching off the --initialize-insecure option.

[ /]# ll /data/3307/data/
total 110628
-rw-r----- 1 mysql mysql       56 Jun  3 18:40 auto.cnf
-rw-r----- 1 mysql mysql      419 Jun  3 18:40 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 Jun  3 18:40 ibdata1
-rw-r----- 1 mysql mysql 50331648 Jun  3 18:40 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Jun  3 18:40 ib_logfile1
drwxr-x--- 2 mysql mysql     4096 Jun  3 18:40 mysql
drwxr-x--- 2 mysql mysql     8192 Jun  3 18:40 performance_schema
drwxr-x--- 2 mysql mysql     8192 Jun  3 18:40 sys

#3308
[ /]# mysqld --initialize-insecure  --user=mysql --basedir=/application/mysql --datadir=/data/3308/data
2020-06-03T10:42:01.320591Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-06-03T10:42:02.961300Z 0 [Warning] InnoDB: New log files created, LSN=45790
2020-06-03T10:42:03.241067Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2020-06-03T10:42:03.351967Z 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: dc9a43e0-a586-11ea-9850-000c290e8d03.
2020-06-03T10:42:03.352854Z 0 [Warning] Gtid table is not ready to be used. Table ‘mysql.gtid_executed‘ cannot be opened.
2020-06-03T10:42:03.353708Z 1 [Warning]  is created with an empty password ! Please consider switching off the --initialize-insecure option.

[ /]# ll /data/3308/data/
total 110628
-rw-r----- 1 mysql mysql       56 Jun  3 18:42 auto.cnf
-rw-r----- 1 mysql mysql      419 Jun  3 18:42 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 Jun  3 18:42 ibdata1
-rw-r----- 1 mysql mysql 50331648 Jun  3 18:42 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Jun  3 18:42 ib_logfile1
drwxr-x--- 2 mysql mysql     4096 Jun  3 18:42 mysql
drwxr-x--- 2 mysql mysql     8192 Jun  3 18:42 performance_schema
drwxr-x--- 2 mysql mysql     8192 Jun  3 18:42 sys

#3309
[ /]# mysqld --initialize-insecure  --user=mysql --basedir=/application/mysql --datadir=/data/3309/data
2020-06-03T10:42:49.888571Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-06-03T10:42:53.436573Z 0 [Warning] InnoDB: New log files created, LSN=45790
2020-06-03T10:42:53.870404Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2020-06-03T10:42:53.910121Z 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: fabcd3bf-a586-11ea-9a67-000c290e8d03.
2020-06-03T10:42:53.931817Z 0 [Warning] Gtid table is not ready to be used. Table ‘mysql.gtid_executed‘ cannot be opened.
2020-06-03T10:42:53.932754Z 1 [Warning]  is created with an empty password ! Please consider switching off the --initialize-insecure option.

[ /]# ll /data/3309/data/
total 110628
-rw-r----- 1 mysql mysql       56 Jun  3 18:42 auto.cnf
-rw-r----- 1 mysql mysql      419 Jun  3 18:42 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 Jun  3 18:42 ibdata1
-rw-r----- 1 mysql mysql 50331648 Jun  3 18:42 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Jun  3 18:42 ib_logfile1
drwxr-x--- 2 mysql mysql     4096 Jun  3 18:42 mysql
drwxr-x--- 2 mysql mysql     8192 Jun  3 18:42 performance_schema
drwxr-x--- 2 mysql mysql     8192 Jun  3 18:42 sys

1. 授权mysql用户管理data目录

[ /]# ll /data/
total 0
drwxr-xr-x 3 root root 32 Jun  3 18:27 3307
drwxr-xr-x 3 root root 32 Jun  3 18:32 3308
drwxr-xr-x 3 root root 32 Jun  3 18:32 3309
[ /]# chown -R mysql.mysql /data/
[ /]# ll /data/
total 0
drwxr-xr-x 3 mysql mysql 32 Jun  3 18:27 3307
drwxr-xr-x 3 mysql mysql 32 Jun  3 18:32 3308
drwxr-xr-x 3 mysql mysql 32 Jun  3 18:32 3309

1. 配置systemd多实例管理启动

[ /]# cd /usr/lib/systemd/system/
[ /usr/lib/systemd/system]# cp mysqld.service mysqld3307.service 
[ /usr/lib/systemd/system]# cp mysqld.service mysqld3308.service 
[ /usr/lib/systemd/system]# cp mysqld.service mysqld3309.service 

#3307 
[ /usr/lib/systemd/system]# cat mysqld3307.service
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://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
EnvironmentFile=/data/3307/my.cnf #改为多实例的配置文件
ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf #改为多实例的配置文件
LimitNOFILE = 5000
[Install]
WantedBy=multi-user.target

#3308 
[ /usr/lib/systemd/system]# cat mysqld3308.service
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://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
EnvironmentFile=/data/3308/my.cnf
ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf
LimitNOFILE = 5000
[Install]
WantedBy=multi-user.target

#3309
[ /usr/lib/systemd/system]# cat mysqld3309.service
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://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
EnvironmentFile=/data/3309/my.cnf
ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf
LimitNOFILE = 5000
[Install]
WantedBy=multi-user.target

1. 启动验证多实例

[ /usr/lib/systemd/system]# systemctl daemon-reload
[ /usr/lib/systemd/system]# systemctl start mysqld3307.service 
[ /usr/lib/systemd/system]# systemctl start mysqld3308.service 
[ /usr/lib/systemd/system]# systemctl start mysqld3309.service 
[ /usr/lib/systemd/system]# netstat -luntp|grep 330
tcp6       0      0 :::3307                 :::*                    LISTEN      2914/mysqld         
tcp6       0      0 :::3308                 :::*                    LISTEN      2948/mysqld         
tcp6       0      0 :::3309                 :::*                    LISTEN      2982/mysqld

1. 登录多实例数据库

[ /usr/lib/systemd/system]# mysql -S /data/3307/mysql.sock -e "select @@server_id"
+-------------+
| @@server_id |
+-------------+
|           7 |
+-------------+
[ /usr/lib/systemd/system]# mysql -S /data/3308/mysql.sock -e "select @@server_id"
+-------------+
| @@server_id |
+-------------+
|           8 |
+-------------+
[ /usr/lib/systemd/system]# mysql -S /data/3309/mysql.sock -e "select @@server_id"
+-------------+
| @@server_id |
+-------------+
|           9 |
+-------------+

1. 设置多实例密码

#3307
[ /usr/lib/systemd/system]# mysqladmin -uroot password ‘123456‘ -S /data/3307/mysql.sock 
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.

#3308
[ /usr/lib/systemd/system]# mysqladmin -uroot password ‘123456‘ -S /data/3308/mysql.sock 
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.

#3309
[ /usr/lib/systemd/system]# mysqladmin -uroot password ‘123456‘ -S /data/3309/mysql.sock 
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.

#修改之后登录成功
[ /usr/lib/systemd/system]# mysql -uroot -p -S /data/3307/mysql.sock 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.26-log MySQL Community Server (GPL)

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> 


#修改密码
mysql> alter user ‘localhost‘ identified by ‘456789‘;
Query OK, 0 rows affected (0.00 sec)