MySQL OCP5.7之配置多实例数据库

2021-06-20

MySQL OCP5.7之配置多实例数据库

配置mysql多实例
一、配置单独一个实例：
1.创建目录：
#mkdir /mysql/data/ -p
#chown mysql:mysql /mysql

2.初始化数据目录：
[root@node232 ~]# mysqld --no-defaults --initialize --user=mysql --datadir=/mysql/data
2021-06-20T00:37:05.282207Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-06-20T00:37:05.679036Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-06-20T00:37:05.745532Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-06-20T00:37:05.808946Z 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: a3613d32-d15f-11eb-9a4f-000c29cb49a4.
2021-06-20T00:37:05.810277Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-06-20T00:37:05.811624Z 1 [Note] A temporary password is generated for root@localhost: F2%%dn*1+vhW
2.1 查看初始化目录后的文件：
[root@node232 mysql]# pwd
/mysql
[root@node232 mysql]# tree -L 1 ./
./
├── data
├── socket
└── socket.lock

1 directory, 2 files
[root@node232 mysql]# tree -L 1 ./data
./data
├── auto.cnf
├── ib_buffer_pool
├── ibdata1
├── ib_logfile0
├── ib_logfile1
├── ibtmp1
├── mysql
├── node232.pid
├── performance_schema
└── sys

3.启动数据库实例：
[root@node232 data]# mysqld --no-defaults --user=mysql --datadir=/mysql/data/ --port=3310 --socket=/mysql/socket
2021-06-20T00:41:24.385860Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-06-20T00:41:24.385986Z 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
2021-06-20T00:41:24.386027Z 0 [Note] mysqld (mysqld 5.7.18) starting as process 4644 ...
2021-06-20T00:41:24.392251Z 0 [Note] InnoDB: PUNCH HOLE support not available
2021-06-20T00:41:24.392309Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2021-06-20T00:41:24.392321Z 0 [Note] InnoDB: Uses event mutexes
2021-06-20T00:41:24.392332Z 0 [Note] InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
2021-06-20T00:41:24.392344Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.3
2021-06-20T00:41:24.392355Z 0 [Note] InnoDB: Using Linux native AIO
2021-06-20T00:41:24.392930Z 0 [Note] InnoDB: Number of pools: 1
2021-06-20T00:41:24.393149Z 0 [Note] InnoDB: Using CPU crc32 instructions
2021-06-20T00:41:24.395484Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2021-06-20T00:41:24.407739Z 0 [Note] InnoDB: Completed initialization of buffer pool
2021-06-20T00:41:24.411584Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2021-06-20T00:41:24.424216Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2021-06-20T00:41:24.442520Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2021-06-20T00:41:24.442602Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2021-06-20T00:41:24.481528Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2021-06-20T00:41:24.482623Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2021-06-20T00:41:24.482642Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2021-06-20T00:41:24.488645Z 0 [Note] InnoDB: 5.7.18 started; log sequence number 2535558
2021-06-20T00:41:24.527370Z 0 [Note] InnoDB: Loading buffer pool(s) from /mysql/data/ib_buffer_pool
2021-06-20T00:41:24.528810Z 0 [Note] Plugin 'FEDERATED' is disabled.
2021-06-20T00:41:24.530225Z 0 [Note] InnoDB: Buffer pool(s) load completed at 210620 8:41:24
2021-06-20T00:41:24.535115Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key
2021-06-20T00:41:24.535146Z 0 [Note] Server hostname (bind-address): '*'; port: 3310
2021-06-20T00:41:24.535195Z 0 [Note] IPv6 is available.
2021-06-20T00:41:24.535209Z 0 [Note] - '::' resolves to '::';
2021-06-20T00:41:24.535230Z 0 [Note] Server socket created on IP: '::'.
2021-06-20T00:41:24.549084Z 0 [Note] Event Scheduler: Loaded 0 events
2021-06-20T00:41:24.549456Z 0 [Note] mysqld: ready for connections.
Version: '5.7.18' socket: '/mysql/socket' port: 3310 MySQL Community Server (GPL)
2021-06-20T00:41:24.549536Z 0 [Note] Executing 'SELECT * FROM INFORMATION_SCHEMA.TABLES;' to get a list of tables using the deprecated partition engine. You may use the startup option '--disable-partition-engine-check' to skip this check.
2021-06-20T00:41:24.549551Z 0 [Note] Beginning of list of non-natively partitioned tables
2021-06-20T00:41:24.572260Z 0 [Note] End of list of non-natively partitioned tables

4.修改数据库ROOT密码：
[root@node232 mysql]# mysqladmin -uroot -S /mysql/socket -p password
Enter password:
New password:
Confirm new password:
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.

[root@node232 mysql]# lsof -i:3310
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 4644 mysql 20u IPv6 14400 0t0 TCP *:dyna-access (LISTEN)

5.进入数据库：
[root@node232 mysql]# mysql -u root -S /mysql/socket -p'RSCpass.'
root@localhost[(none)]>\s
--------------
mysql Ver 14.14 Distrib 5.7.18, for linux-glibc2.5 (x86_64) using EditLine wrapper

Connection id: 6
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.18 MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8mb4
Conn. characterset: utf8mb4
UNIX socket: /mysql/socket
Uptime: 19 min 9 sec

Threads: 1 Questions: 23 Slow queries: 0 Opens: 106 Flush tables: 1 Open tables: 99 Queries per second avg: 0.020
--------------

以上为配置一个单独的实例的步骤。

二、通过mysqld_multi命令批量管理多个实例
1.创建数据目录：
[root@node232 mysql]#mkdir -p /mysql/data1 /mysql/data2 /mysql/data3 /mysql/data4
[root@node232 mysql]# chown mysql.mysql /mysql/* -R

2.初始化mysql数据文件
[root@node232 mysql]# mysqld --no-defaults --initialize --user=mysql --datadir=/mysql/data1
2021-06-20T01:07:34.122671Z 1 [Note] A temporary password is generated for root@localhost: 1owuo%?j3vdT

[root@node232 mysql]# mysqld --no-defaults --initialize --user=mysql --datadir=/mysql/data2
2021-06-20T01:07:50.718066Z 1 [Note] A temporary password is generated for root@localhost: ;rjH6!l:f1Al

[root@node232 mysql]# mysqld --no-defaults --initialize --user=mysql --datadir=/mysql/data3
2021-06-20T01:08:12.713687Z 1 [Note] A temporary password is generated for root@localhost: tu8rl8qmls&R

[root@node232 mysql]# mysqld --no-defaults --initialize --user=mysql --datadir=/mysql/data4
2021-06-20T01:08:31.889246Z 1 [Note] A temporary password is generated for root@localhost: _ils%YpMr6_L

3.通过mysqld_multi命令启动所有的实例
编辑配置文件：
[root@node232 ~]# cat /etc/multi.cnf
[mysqld1]
user=mysql
socket=/mysql/socket1
port=3311
datadir=/mysql/data1
log-error=/mysql/data1

[mysqld2]
user=mysql
socket=/mysql/socket2
port=3312
datadir=/mysql/data2
log-error=/mysql/data2

[mysqld3]
user=mysql
socket=/mysql/socket3
port=3313
datadir=/mysql/data3
log-error=/mysql/data3

[mysqld4]
user=mysql
socket=/mysql/socket4
port=3314
datadir=/mysql/data4
log-error=/mysql/data4

4.启动4个独立的实例：
[root@node232 mysql]# mysqld_multi --defaults-file=/etc/multi.cnf start 1-4
[root@node232 mysql]# ps -ef |grep mysql
mysql 4887 1 61 09:16 pts/1 00:00:04 /usr/local/mysql/bin/mysqld --user=mysql --socket=/mysql/socket1 --port=3311 --datadir=/mysql/data1 --log-error=/mysql/data1
mysql 4890 1 68 09:16 pts/1 00:00:04 /usr/local/mysql/bin/mysqld --user=mysql --socket=/mysql/socket2 --port=3312 --datadir=/mysql/data2 --log-error=/mysql/data2
mysql 4893 1 66 09:16 pts/1 00:00:04 /usr/local/mysql/bin/mysqld --user=mysql --socket=/mysql/socket3 --port=3313 --datadir=/mysql/data3 --log-error=/mysql/data3
mysql 4896 1 69 09:16 pts/1 00:00:04 /usr/local/mysql/bin/mysqld --user=mysql --socket=/mysql/socket4 --port=3314 --datadir=/mysql/data4 --log-error=/mysql/data4
root 4946 4577 2 09:16 pts/1 00:00:00 grep mysql
[root@node232 mysql]# ps fp `pgrep mysqld`
PID TTY STAT TIME COMMAND
4896 pts/1 Sl 0:09 /usr/local/mysql/bin/mysqld --user=mysql --socket=/mysql/socket4 --port=3314 --datadir=/mysql
4893 pts/1 Sl 0:09 /usr/local/mysql/bin/mysqld --user=mysql --socket=/mysql/socket3 --port=3313 --datadir=/mysql
4890 pts/1 Sl 0:09 /usr/local/mysql/bin/mysqld --user=mysql --socket=/mysql/socket2 --port=3312 --datadir=/mysql
4887 pts/1 Sl 0:08 /usr/local/mysql/bin/mysqld --user=mysql --socket=/mysql/socket1 --port=3311 --datadir=/mysql

[root@node232 mysql]# netstat -lntup | grep mysql
tcp 0 0 :::3311 :::* LISTEN 4887/mysqld
tcp 0 0 :::3312 :::* LISTEN 4890/mysqld
tcp 0 0 :::3313 :::* LISTEN 4893/mysqld
tcp 0 0 :::3314 :::* LISTEN 4896/mysqld

查看生成的文件目录：
[root@node232 mysql]# pwd
/mysql
[root@node232 mysql]# tree -L 1 ./
./
├── data
├── data1
├── data1.err
├── data2
├── data2.err
├── data3
├── data3.err
├── data4
├── data4.err
├── socket1
├── socket1.lock
├── socket2
├── socket2.lock
├── socket3
├── socket3.lock
├── socket4
└── socket4.lock

5.修改4个独立的实例的root密码：
[root@node232 mysql]# mysqladmin -uroot -S /mysql/socket1 -p password
Enter password:
New password:
Confirm new password:
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
[root@node232 mysql]# mysqladmin -uroot -S /mysql/socket2 -p password
Enter password:
New password:
Confirm new password:
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
[root@node232 mysql]# mysqladmin -uroot -S /mysql/socket3 -p password
Enter password:
New password:
Confirm new password:
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
[root@node232 mysql]# mysqladmin -uroot -S /mysql/socket4 -p password
Enter password:
New password:
Confirm new password:
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.

6.登录每个独立实例：
[root@node232 mysql]# mysql -uroot -p -S /mysql/socket1
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.18-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, 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.

root@localhost[(none)]>\s
--------------
mysql Ver 14.14 Distrib 5.7.18, for linux-glibc2.5 (x86_64) using EditLine wrapper

Connection id: 4
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.18-log MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
UNIX socket: /mysql/socket1
Uptime: 7 min 32 sec

Threads: 1 Questions: 11 Slow queries: 0 Opens: 109 Flush tables: 1 Open tables: 102 Queries per second avg: 0.024
--------------

7.批量停止所有的实例：
[root@node232 mysql]# mysqld_multi --defaults-file=/etc/multi.cnf --user=root --password=RSCpass stop 1-4