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


视频讲解:https://mp.weixin.qq.com/s/IuFb1mS2f3G-dKYnZ9JQ3g

分割线
打赏
YWSOS.COM 平台代运维解决方案
 评论
 发表评论
姓   名:

Powered by AKCMS