MySQL多实例配置
一、MySQL多实例配置
1.1.1 MySQL多实例配置
- 创建多实例目录
[root@db01 /]# mkdir -p /data/330{7,8,9}/data
[root@db01 /]# ll /data/330{7,8,9}/data
/data/3307/data:
total 0
/data/3308/data:
total 0
/data/3309/data:
total 0
- 创建多实例配置文件
#3307
[root@db01 /]# 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
[root@db01 /]# 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
[root@db01 /]# 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
- 初始化多实例数据库
#初始化3307
[root@db01 /]# 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] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
[root@db01 /]# 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
[root@db01 /]# 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] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
[root@db01 /]# 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
[root@db01 /]# 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] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
[root@db01 /]# 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
- 授权mysql用户管理data目录
[root@db01 /]# 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
[root@db01 /]# chown -R mysql.mysql /data/
[root@db01 /]# 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
- 配置systemd多实例管理启动
[root@db01 /]# cd /usr/lib/systemd/system/
[root@db01 /usr/lib/systemd/system]# cp mysqld.service mysqld3307.service
[root@db01 /usr/lib/systemd/system]# cp mysqld.service mysqld3308.service
[root@db01 /usr/lib/systemd/system]# cp mysqld.service mysqld3309.service
#3307
[root@db01 /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
[root@db01 /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
[root@db01 /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
- 启动验证多实例
[root@db01 /usr/lib/systemd/system]# systemctl daemon-reload
[root@db01 /usr/lib/systemd/system]# systemctl start mysqld3307.service
[root@db01 /usr/lib/systemd/system]# systemctl start mysqld3308.service
[root@db01 /usr/lib/systemd/system]# systemctl start mysqld3309.service
[root@db01 /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
- 登录多实例数据库
[root@db01 /usr/lib/systemd/system]# mysql -S /data/3307/mysql.sock -e "select @@server_id"
+-------------+
| @@server_id |
+-------------+
| 7 |
+-------------+
[root@db01 /usr/lib/systemd/system]# mysql -S /data/3308/mysql.sock -e "select @@server_id"
+-------------+
| @@server_id |
+-------------+
| 8 |
+-------------+
[root@db01 /usr/lib/systemd/system]# mysql -S /data/3309/mysql.sock -e "select @@server_id"
+-------------+
| @@server_id |
+-------------+
| 9 |
+-------------+
- 设置多实例密码
#3307
[root@db01 /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
[root@db01 /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
[root@db01 /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.
#修改之后登录成功
[root@db01 /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 root@'localhost' identified by '456789';
Query OK, 0 rows affected (0.00 sec)