Deploying multiple instances of Mysql on a single machine

1
groupadd mysql
2
useradd -g mysql -M -s /sbin/nologin mysql

1
tar -xvf mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz
2
mv mysql-5.7.37-linux-glibc2.12-x86_64 /usr/local/mysql

1
mkdir -pv /data/{3006,3007,3008}/data
2
tree /data/
3
/data/
4
├── 3306
5
│   └── data
6
├── 3307
7
│   └── data
8
└── 3308
9
    └── data

1
[mysql]
2
default-character-set = utfe
3
port = 3306
4
socket = /data/3306/mysql.sock
5

6
[mysqld]
7
user = mysql
8
log-bin = mysql-bin
9
port = 3306
10

11
socket = /data/3306/mysql.sock
12
basedir = /usr/local/mysql
13
datadir = /data/3306/data
14

15
character-set-server = utf8
16

17
pid-file = /data/3306/mysql.pid
18
relay-log = /data/3306/relay-bin
19
relay-log-info-file = /data/3306/relay-log.info
20

21
# Server ID
22
server-id = 1
23

24
# gtid
25
gtid_mode = on
26
enforce-gtid-consistency = true
27

28
log-slave-updates = on
29

30
skip_slave_start = 1
31

32
# Record relay.info into the data table
33
relay_log_info_repository = TABLE
34

35
# Record master.info into the data table
36
master_info_repository = TABLE
37

38
[mysqld_safe]
39
log-error = /data/3306/mysql_3306.err
40
pid-file = /data/3306/mysqld.pid

1
cd /usr/local/mysql/bin
2
./mysqld --initialize  --basedir=/usr/local/mysql --datadir=/data/3306/data --user=mysql
3
./mysqld --initialize  --basedir=/usr/local/mysql --datadir=/data/3307/data --user=mysql
4
./mysqld --initialize  --basedir=/usr/local/mysql --datadir=/data/3308/data --user=mysql
5

6
# The last line of the initialization printout ends with the MySql login password, remember to save it!

1
echo "export PATH=/usr/local/mysql/bin:$PATH" > /etc/profile.d/mysql.sh
2
source /etc/profile

1
cat > /etc/systemd/system/mysql-3306.service <<EOF
2
[Unit]
3
Description=MySQL Server
4
Documentation=man:mysqld(8)
5
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
6
After=network.target
7
After=syslog.target
8

9
[Service]
10
User=mysql
11
Group=mysql
12
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/data/3306/my.cnf
13
LimitNOFILE = 5000
14

15
[Install]
16
WantedBy=multi-user.target
17
EOF
18

19
cat > /etc/systemd/system/mysql-3307.service <<EOF
20
[Unit]
21
Description=MySQL Server
22
Documentation=man:mysqld(8)
23
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
24
After=network.target
25
After=syslog.target
26

27
[Service]
28
User=mysql
29
Group=mysql
30
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf
31
LimitNOFILE = 5000
32

33
[Install]
34
WantedBy=multi-user.target
35
EOF
36

37
cat > /etc/systemd/system/mysql-3308.service <<EOF
38
[Unit]
39
Description=MySQL Server
40
Documentation=man:mysqld(8)
41
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
42
After=network.target
43
After=syslog.target
44

45
[Service]
46
User=mysql
47
Group=mysql
48
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf
49
LimitNOFILE = 5000
50

51
[Install]
52
WantedBy=multi-user.target
53
EOF

1
systemctl start mysql-3306.service
2
systemctl start mysql-3307.service
3
systemctl start mysql-3308.service
4
systemctl enable mysql-3306.service
5
systemctl enable mysql-3307.service
6
systemctl enable mysql-3308.service

1
[root@localhost ~]#m mysql -S /data/3306/mysql.sock -u root -P 3306 -p
2
Enter password:
3
Welcome to the MySQL monitor.  Commands end with ; or \g.
4
Your MySQL connection id is 3
5
Server version: 5.7.37-log MySQL Community Server (GPL)
6

7
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
8

9
Oracle is a registered trademark of Oracle Corporation and/or its
10
affiliates. Other names may be trademarks of their respective
11
owners.
12

13
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
14

15
mysql> show databases;
16
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement

1
mysql> set password for root@localhost = password( '123456' );
2
Query OK, 0 rows affected, 1 warning (0.00 sec)
3

4
mysql>quit
5
Bye
6

7
# Logging in again to perform database operations works fine
8
mysql> show databases;
9
+--------------------+
10
| Database           |
11
+--------------------+
12
| information_schema |
13
| mysql              |
14
| performance_schema |
15
| sys                |
16
+--------------------+
17
4 rows in set (0.00 sec)