MySQL 5.7 Multi-Instance Installation Configuration

1. Background description

MySQL multi-instance concept

Simply put, it is to open multiple different service ports (e.g. 3306, 3307) on a single server and run multiple MySQL service processes, which provide their services through different socket s listening on different service ports.

b. MySQL multi-instance roles and problems

  • Effective utilization of server resources
    When there is a surplus of resources, we can make full use of the remaining resources to provide services.
  • Save server resources
    When the company is short of funds, but the servers need to provide services independently as much as possible; moreover, when master-slave synchronization technology is needed, multi-instance is better.
  • Mutual preemption of resources
    When a service has high concurrency or slow query, the whole instance will consume more memory, CPU and disk IO resources, which will lead to the decline of the quality of service provided by other instances on the server.

c. MySQL multi-instance application scenario

  • A company with tight funds
  • Concurrent access is not a particularly big business
  • MySQL multi-instance scenarios for portal applications

2. Environmental preparation

Operating System: CentOS Linux release 7.6.1810
b. MySQL: mysql-5.7.27.tar.gz,Click Download Prepare MySQL source code compilation package
c. boost: boost_1_59_0.tar.gz,Click Download When installing MySQL 5.7, you need to rely on the boost library. The corresponding boost version of MySQL 5.7 is 1.59
d. cmake: cmake-2.8.12.2.tar.gz,Click Download When compiling and installing MySQL, you use cmake to compile and install, so you need to install cmake first

3. Installation method

3.1 Compile and install cmake

# It is recommended to create a tools Directory and put all packages together

[root@192168066012_MySQL_5_7_27 ~]# mkdir -p /home/tools/
[root@192168066012_MySQL_5_7_27 ~]# cd /home/tools/
[root@192168066012_MySQL_5_7_27 tools]# ls -l
total 143932
-rw-r--r--  1 root root 83709983 Jul 31 18:34 boost_1_59_0.tar.gz
-rw-r--r--  1 root root  6068231 Jul 31 18:36 cmake-2.8.12.2.tar.gz
-rw-r--r--  1 root root 54398696 Jul 31 18:34 mysql-5.7.27.tar.gz

# Unzip the cmake installation package

[root@192168066012_MySQL_5_7_27 tools]# tar xf cmake-2.8.12.2.tar.gz
[root@192168066012_MySQL_5_7_27 tools]# cd cmake-2.8.12.2
[root@192168066012_MySQL_5_7_27 cmake-2.8.12.2]# ll
total 3056
-rwxr-xr-x   1  501 games   53974 Jan 17  2014 bootstrap
-rw-r--r--   1  501 games  254736 Jan 17  2014 ChangeLog.manual
-rw-r--r--   1  501 games 2603523 Jan 17  2014 ChangeLog.txt
-rw-r--r--   1  501 games    5465 Jan 17  2014 CMakeCPack.cmake
-rw-r--r--   1  501 games    3436 Jan 17  2014 CMakeCPackOptions.cmake.in
-rw-r--r--   1  501 games     153 Jan 17  2014 CMakeGraphVizOptions.cmake
-rw-r--r--   1  501 games   25439 Jan 17  2014 CMakeLists.txt
-rw-r--r--   1  501 games    4481 Jan 17  2014 CMakeLogo.gif
-rw-r--r--   1  501 games     796 Jan 17  2014 cmake_uninstall.cmake.in
-rw-r--r--   1  501 games    2515 Jan 17  2014 CompileFlags.cmake
-rwxr-xr-x   1  501 games      99 Jan 17  2014 configure
-rw-r--r--   1  501 games    2637 Jan 17  2014 Copyright.txt
-rw-r--r--   1  501 games    1050 Jan 17  2014 CTestConfig.cmake
-rw-r--r--   1  501 games    3338 Jan 17  2014 CTestCustom.cmake.in
-rw-r--r--   1  501 games     145 Jan 17  2014 CTestCustom.ctest.in
-rw-r--r--   1  501 games     809 Jan 17  2014 DartConfig.cmake
-rw-r--r--   1  501 games    4144 Jan 17  2014 DartLocal.conf.in
drwxr-xr-x   3 root root      142 Aug  1 14:32 Docs
-rw-r--r--   1  501 games   28046 Jan 17  2014 doxygen.config
drwxr-xr-x   4 root root       53 Aug  1 14:32 Example
drwxr-xr-x   9 root root    16384 Aug  1 14:32 Modules
-rw-r--r--   1  501 games    1946 Jan 17  2014 Readme.txt
drwxr-xr-x   7 root root    24576 Aug  1 14:32 Source
drwxr-xr-x   2 root root     4096 Aug  1 14:32 Templates
drwxr-xr-x 169 root root     8192 Aug  1 14:32 Tests
drwxr-xr-x  15 root root     4096 Aug  1 14:32 Utilities

# Start compiling and installing

[root@192168066012_MySQL_5_7_27 cmake-2.8.12.2]# ./configure
...ellipsis
CMake has bootstrapped.  Now run gmake.

# See the "Now run gmake" explanation ok, you can do gmake & gmake install

[root@192168066012_MySQL_5_7_27 cmake-2.8.12.2]# gmake
...ellipsis
[100%] Built target pseudonl_valgrind
[root@192168066012_MySQL_5_7_27 cmake-2.8.12.2]# gmake install
...ellipsis
-- Installing: /usr/local/share/cmake-2.8/completions/ctest

Tips:
At the end of each step, you can use echo $?, when the return value is 0, the compilation process is normal.

3.2 Unzip the boost library to / usr/local / directory

# Specify unzip to / usr/local / directory, using the - C parameter

[root@192168066012_MySQL_5_7_27 tools]# tar xf boost_1_59_0.tar.gz -C /usr/local/
[root@192168066012_MySQL_5_7_27 tools]# cd /usr/local/
[root@192168066012_MySQL_5_7_27 local]# mv boost_1_59_0/ boost
[root@192168066012_MySQL_5_7_27 local]# ls -l
total 0
drwxr-xr-x. 2 root root   45 Jul 30 19:49 bin
drwx------  8  501 games 302 Aug 12  2015 boost
drwxr-xr-x  3 root root   23 Jul 30 19:49 doc
drwxr-xr-x. 2 root root    6 Apr 11  2018 etc
drwxr-xr-x. 2 root root    6 Apr 11  2018 games
drwxr-xr-x. 2 root root    6 Apr 11  2018 include
drwxr-xr-x. 2 root root    6 Apr 11  2018 lib
drwxr-xr-x. 2 root root    6 Apr 11  2018 lib64
drwxr-xr-x. 2 root root    6 Apr 11  2018 libexec
drwxr-xr-x  3 root root   18 Jul 30 19:49 man
drwxr-xr-x. 2 root root    6 Apr 11  2018 sbin
drwxr-xr-x. 7 root root   81 Jul 30 19:49 share
drwxr-xr-x. 2 root root    6 Apr 11  2018 src

3.3 Unzip MySQL 5.7 and compile and install it

3.3.1 Install MySQL 5.7 dependency packages

[root@192168066012_MySQL_5_7_27 tools]# yum install ncurses-devel -y
...ellipsis
Installed:
    ncurses-devel.x86_64 0:5.9-14.20130511.el7_4

Complete!
[root@192168066012_MySQL_5_7_27 tools]# yum install libaio-devel -y
...ellipsis
Installed:
  libaio-devel.x86_64 0:0.3.109-13.el7

Dependency Installed:
  libaio.x86_64 0:0.3.109-13.el7

Complete!

Tips:
MySQL 5.7 dependent packages: gcc gcc-c++ ncurses ncurses-devel bison make, etc. for reference, you can use rpm-qa | grep ${package name} to check before installation.

3.3.2 Create MySQL File Storage Directory, Users and Groups

# Create MySQL multi-instance related file directories

[root@192168066012_MySQL_5_7_27 tools]# mkdir -p /application/mysql
[root@192168066012_MySQL_5_7_27 tools]# mkdir -p /application/mysql/logs
[root@192168066012_MySQL_5_7_27 tools]# mkdir -p /application/mysql/data/{3306,3307}/data
[root@192168066012_MySQL_5_7_27 tools]# tree -L 3 /application/mysql/data/
/application/mysql/data/
|-- 3306
|   `-- data
`-- 3307
    `-- data

4 directories, 0 files

# Create mysql groups and users

[root@192168066012_MySQL_5_7_27 tools]# groupadd mysql
[root@192168066012_MySQL_5_7_27 tools]# useradd mysql -s /sbin/nologin -M -g mysql
[root@192168066012_MySQL_5_7_27 tools]# id mysql
uid=1000(mysql) gid=1000(mysql) groups=1000(mysql)

Explain:
- s/sbin/nologin user cannot log in to the system
- M does not create a user's HOME directory
- g Specifies the GID or group name of the user login group

3.3.3 Compile and install MySQL 5.7

# Unzip source mysql-5.7.27.tar.gz installation package

[root@192168066012_MySQL_5_7_27 tools]# cd /home/tools/
[root@192168066012_MySQL_5_7_27 tools]# tar xf mysql-5.7.27.tar.gz

# cmake configuration compilation parameters

[root@192168066012_MySQL_5_7_27 tools]# cd mysql-5.7.27
[root@192168066012_MySQL_5_7_27 mysql-5.7.27]# cmake . -DCMAKE_INSTALL_PREFIX=/application/mysql \
 -DMYSQL_DATADIR=/application/mysql/data \
 -DMYSQL_UNIX_ADDR=/application/mysql/tmp/mysql.sock \
 -DDEFAULT_CHARSET=utf8 \
 -DDEFAULT_COLLATION=utf8_general_ci \
 -DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii \
 -DENABLED_LOCAL_INFILE=ON \
 -DWITH_INNOBASE_STORAGE_ENGINE=1 \
 -DWITH_FEDERATED_STORAGE_ENGINE=1 \
 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
 -DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \
 -DWITHOUT_PARTITION_STORAGE_ENGINE=1 \
 -DWITH_ZLIB=bundled \
 -DWITH_EMBEDDED_SERVER=1 \
 -DWITH_DEBUG=0 \
 -DWITH_BOOST=/usr/local/boost

# See the following information to show that the cmake compilation is complete, and continue to install by executing make

 -- Configuring done
 -- Generating done
 -- Build files have been written to: /home/tools/mysql-5.7.27

# Continue to make install and continue to install

...ellipsis
 Linking CXX executable my_safe_process
 [100%] Built target my_safe_process

# Installation completed

...ellipsis
 -- Installing: /application/mysql-5.7.27/share/aclocal/mysql.m4
 -- Installing: /application/mysql-5.7.27/support-files/mysql.server

Tips:
If there is no error in the above operation (echo $? Can be used at the end of each step to see if the return value is 0 or 0 is correct), check / application/mysql / directory, and if there is something below it, it means that MySQL 5.7.27 source code package has been successfully installed by cmake mode.

3.4 Configure MySQL System Environment Variables

[root@192168066012_MySQL_5_7_27 ~]# echo 'export PATH=/application/mysql/bin:$PATH' >>/etc/profile
[root@192168066012_MySQL_5_7_27 ~]# tail -1 /etc/profile
export PATH=/application/mysql/bin:$PATH

# Make the / etc/profile configuration file take effect immediately

[root@192168066012_MySQL_5_7_27 ~]# source /etc/profile
[root@192168066012_MySQL_5_7_27 ~]# echo $PATH
/application/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin

3.5 Initialization of database

3.5.1 Configure my.cnf file

# Modify the / etc/my.cnf file, unlike the previous version 5.6. There is No. cnf file in the support-files directory of version 5.7, so you need to edit it manually.

[root@192168066012_MySQL_5_7_27 ~]# cp /etc/my.cnf /etc/my.cnf.bak
[root@192168066012_MySQL_5_7_27 ~]# vim /etc/my.cnf
[mysqld_multi]
mysqld = /application/mysql/bin/mysqld
mysqladmin = /application/mysql/bin/mysqladmin
log = /application/mysql/logs/mysql_multi.log

[mysqld1]
port = 3306
user = mysql
character_set_server = utf8
socket = /application/mysql/data/3306/mysql.sock
datadir = /application/mysql/data/3306/data
pid-file = /application/mysql/data/3306/mysql.pid
log-bin = /application/mysql/data/3306/mysql-bin
server-id = 1
log-error = /application/mysql/data/3306/mysql_boyu3306.err

[mysqld2]
port = 3307
user = mysql
character_set_server = utf8
socket = /application/mysql/data/3307/mysql.sock
datadir = /application/mysql/data/3307/data
pid-file = /application/mysql/data/3307/mysql.pid
log-bin = /application/mysql/data/3307/mysql-bin
server-id = 3
log-error = /application/mysql/data/3307/mysql_boyu3307.err

Tips:
When adding new MySQL instances, pay attention to changing the port and server-id in my.cnf configuration file and startup script file, and do not conflict with the running instances.

3.5.2 Modify the ownership and subgroup of my.cnf file

[root@192168066012_MySQL_5_7_27 ~]# chown mysql:mysql /etc/my.cnf

3.5.3 Change ownership and subgroup of MySQL installation directory

[root@192168066012_MySQL_5_7_27 ~]# chown -R mysql:mysql /application/mysql/

3.5.4 Initialization of the database

[root@192168066012_MySQL_5_7_27 data]# cd /application/mysql/
[root@192168066012_MySQL_5_7_27 mysql]# ./bin/mysqld --initialize-insecure --user=mysql --basedir=/application/mysql --datadir=/application/mysql/data/3306/data/
2019-08-07T03:33:57.693586Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2019-08-07T03:33:57.973904Z 0 [Warning] InnoDB: New log files created, LSN=45790
2019-08-07T03:33:58.013774Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2019-08-07T03:33:58.070602Z 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: 30a49b8b-b8c4-11e9-b40d-000c290943d8.
2019-08-07T03:33:58.072487Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2019-08-07T03:33:58.074274Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
[root@192168066012_MySQL_5_7_27 mysql]# ./bin/mysqld --initialize-insecure --user=mysql --basedir=/application/mysql --datadir=/application/mysql/data/3307/data/
2019-08-07T03:34:06.558576Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2019-08-07T03:34:06.792397Z 0 [Warning] InnoDB: New log files created, LSN=45790
2019-08-07T03:34:06.835971Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2019-08-07T03:34:06.893802Z 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: 35e6eba6-b8c4-11e9-b7dc-000c290943d8.
2019-08-07T03:34:06.896053Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2019-08-07T03:34:06.897815Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.

Tips:
When initializing, the data directory must be empty. Otherwise, initialization fails and the following information will be missed

2019-08-07T06:58:34.828541Z 0 [ERROR] --initialize specified but the data directory has files in it. Aborting.
2019-08-07T06:58:34.828576Z 0 [ERROR] Aborting

Why initialize the database?
a. The main purpose of initialization is to create basic databases, such as MySQL databases, tables, etc.
b. Look at the data directory of the corresponding instance after initialization, and you can see the following information

[root@192168066012_MySQL_5_7_27 mysql]# tree -L 3 /application/mysql/data/
/application/mysql/data/
|-- 3306
|   `-- data
|       |-- auto.cnf
|       |-- ib_buffer_pool
|       |-- ibdata1
|       |-- ib_logfile0
|       |-- ib_logfile1
|       |-- mysql
|       |-- performance_schema
|       `-- sys
`-- 3307
    `-- data
        |-- auto.cnf
        |-- ib_buffer_pool
        |-- ibdata1
        |-- ib_logfile0
        |-- ib_logfile1
        |-- mysql
        |-- performance_schema
        `-- sys

10 directories, 10 files

3.5.5 Instance opens an SSL connection

[root@192168066012_MySQL_5_7_27 mysql]# /application/mysql/bin/mysql_ssl_rsa_setup --user=mysql --basedir=/application/mysql --datadir=/application/mysql/data/3306/data/
Generating a 2048 bit RSA private key
.....................+++
......+++
writing new private key to 'ca-key.pem'
-----
Generating a 2048 bit RSA private key
............+++
.................+++
writing new private key to 'server-key.pem'
-----
Generating a 2048 bit RSA private key
........................................................+++
...............................+++
writing new private key to 'client-key.pem'
-----
[root@192168066012_MySQL_5_7_27 mysql]# /application/mysql/bin/mysql_ssl_rsa_setup --user=mysql --basedir=/application/mysql --datadir=/application/mysql/data/3307/data/
Generating a 2048 bit RSA private key
..................................................................+++
..............................+++
writing new private key to 'ca-key.pem'
-----
Generating a 2048 bit RSA private key
.....+++
...............................................................+++
writing new private key to 'server-key.pem'
-----
Generating a 2048 bit RSA private key
.........+++
......+++
writing new private key to 'client-key.pem'
-----

3.5.6 Modify server-key.pem file permissions under data directory

[root@192168066012_MySQL_5_7_27 ~]# cd /application/mysql/data/3306/data/
[root@192168066012_MySQL_5_7_27 data]# chmod +r server-key.pem
[root@192168066012_MySQL_5_7_27 data]# ls -lh server-key.pem
-rw-r--r-- 1 root root 1.7K Aug  7 15:07 server-key.pem
[root@192168066012_MySQL_5_7_27 data]# cd /application/mysql/data/3307/data/
[root@192168066012_MySQL_5_7_27 data]# chmod +r server-key.pem
[root@192168066012_MySQL_5_7_27 data]# ls -lh server-key.pem
-rw-r--r-- 1 root root 1.7K Aug  7 15:07 server-key.pem

Tips:
If the file server-key.pem is not given readable privileges for other users and groups, the following error will be reported when the database is started
2019-08-07T06:39:24.111526Z 0 [ERROR] SSL error: Unable to get private key from 'server-key.pem'
2019-08-07T06:39:24.111555Z 0 [Warning] Failed to set up SSL because of the following SSL library error: Unable to get private key

3.6 Start MySQL Database

3.6.1 Copy startup script to / etc/init.d / directory, rename mysqld_multi, and grant executable privileges

[root@192168066012_MySQL_5_7_27 ~]# cd /application/mysql
[root@192168066012_MySQL_5_7_27 mysql]# cp support-files/mysqld_multi.server /etc/init.d/mysqld_multi
[root@192168066012_MySQL_5_7_27 mysql]# chmod +x /etc/init.d/mysqld_multi
[root@192168066012_MySQL_5_7_27 mysql]# ll /etc/init.d/mysqld_multi
-rwxr-xr-x 1 root root 1061 8 July 11:38 /etc/init.d/mysqld_multi

3.6.2 Modify the absolute path of MySQL installation directory and bin directory in the script

[root@192168066012_MySQL_5_7_27 mysql]# diff /application/mysql/support-files/mysqld_multi.server /etc/init.d/mysqld_multi
17,18c17,18
< basedir=/usr/local/mysql
< bindir=/usr/local/mysql/bin
---
> basedir=/application/mysql
> bindir=/application/mysql/bin

Tips:
If MySQL is not installed in the / usr / local / directory, you need to modify the multi-instance mysqld_multi startup script. basedir=${MySQL installation directory}, bin dir=${bin directory under MySQL installation directory}, otherwise, executing the startup script will report "Can't execute/usr/local/mysql/bin/mysqld_multi-from dir/usr/local/mysql" error.

3.6.3 Reload system services, add MySQL database to boot-up and start-up

[root@192168066012_MySQL_5_7_27 mysql]# /bin/systemctl daemon-reload
[root@192168066012_MySQL_5_7_27 mysql]# /bin/systemctl enable mysqld_multi
mysqld_multi.service is not a native service, redirecting to /sbin/chkconfig.
Executing /sbin/chkconfig mysqld_multi on
[root@192168066012_MySQL_5_7_27 mysql]# chkconfig --list

Note: This output shows SysV services only and does not include native
      systemd services. SysV configuration data might be overridden by native
      systemd configuration.

      If you want to list systemd services use 'systemctl list-unit-files'.
      To see services enabled on particular target use
      'systemctl list-dependencies [target]'.

mysqld_multi    0:off   1:off   2:on    3:on    4:on    5:on    6:off
netconsole      0:off   1:off   2:off   3:off   4:off   5:off   6:off
network         0:off   1:off   2:on    3:on    4:on    5:on    6:off

3.6.4 Start MySQL database and check port listening status

[root@192168066012_MySQL_5_7_27 mysql]# /etc/init.d/mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld1 is not running
MySQL server from group: mysqld2 is not running
[root@192168066012_MySQL_5_7_27 mysql]# /etc/init.d/mysqld_multi start
[root@192168066012_MySQL_5_7_27 mysql]# netstat -lntup
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      6568/sshd
tcp        0      0 127.0.0.1:25            0.0.0.0:*               LISTEN      6814/master
tcp6       0      0 :::3306                 :::*                    LISTEN      7404/mysqld
tcp6       0      0 :::3307                 :::*                    LISTEN      7407/mysqld
tcp6       0      0 :::22                   :::*                    LISTEN      6568/sshd
tcp6       0      0 ::1:25                  :::*                    LISTEN      6814/master

3.7 Connect to MySQL Database

3.7.1 Specify socket login to 3306 database

[root@192168066012_MySQL_5_7_27 mysql]# mysql -S /application/mysql/data/3306/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.27-log Source distribution

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> \q
Bye

3.7.2 Specify socket login to 3307 database

[root@192168066012_MySQL_5_7_27 mysql]# mysql -S /application/mysql/data/3307/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.27-log Source distribution

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> \q
Bye

Tips:
a. Local login

  • Multi-instance local-end login specifies which instance to login to through socket file, the specific location of this file is specified when mysql is initialized, and the login program judges the database instance of login through socket file when login locally.
  • Mysql. sock <== file is a Unix socket file that servers communicate with local clients
  • For example: mysql-S/application/mysql/data/3306/mysql.sock landing 3306 database instance

b. Remote login

  • Remote login specifies an instance in a multi-instance host, TCP/IP port to specify the MySQL instance to be logged in, which is specified in MySQL configuration file my.cnf.
  • Port <== Port is a "logical connection location" in which client programs are assigned to special service programs on computers.
  • Remote landing case:
  • Grant all privileges on. to root @ "192.168.66.11" identified by "boyu123"; # grant all privileges to client 192.168.66.11 on MySQL server, including remote access rights
  • flush privileges; # reload mysql authorization table
  • select user,host from mysql,user; View the changed results
  • Mysql-uroot-p'boyu123'-h 192.168.66.12-P 3306 # above 192.168.66.11 to execute this command, you can login to the database remotely

3.8 Basic Operation of Database

3.8.1 Modification of multi-instance login password

[root@192168066012_MySQL_5_7_27 mysql]# mysqladmin -u root -p password "boyu123" -S /application/mysql/data/3306/mysql.sock
Enter password:
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@192168066012_MySQL_5_7_27 mysql]# mysqladmin -u root -p password "boyu123" -S /application/mysql/data/3307/mysql.sock
Enter password:
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.

3.8.2 Graceful Closure of Multi-instance Databases

# Use MySQL's own scripts

[root@192168066012_MySQL_5_7_27 mysql]# /etc/init.d/mysqld_multi stop
[root@192168066012_MySQL_5_7_27 mysql]# ps aux|grep mysqld
root      23962  0.0  0.0 112708   976 pts/0    S+   20:39   0:00 grep --color=auto mysqld

Tip: This method shuts down all databases on the server at the same time. If you want to shut down one by one, you can use mysqladmin method.

# Using mysqladmin's method

[root@192168066012_MySQL_5_7_27 mysql]# mysqladmin -uroot -p"boyu123" -S /application/mysql/data/3307/mysql.sock shutdown
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@192168066012_MySQL_5_7_27 mysql]# ps aux|grep mysqld
mysql     24058  0.1 10.1 1181012 188944 pts/0  Sl   20:39   0:01 /application/mysql/bin/mysqld --port=3306 --user=mysql --character_set_server=utf8 --socket=/application/mysql/data/3306/mysql.sock --datadir=/application/mysql/data/3306/data --pid-file=/application/mysql/data/3306/mysql.pid --log-bin=/application/mysql/data/3306/mysql-bin --server-id=1 --log-error=/application/mysql/data/3306/mysql_boyu3306.err
root      28235  0.0  0.0 112708   976 pts/0    S+   20:51   0:00 grep --color=auto mysqld

Tags: Linux MySQL cmake Database mysqladmin

Posted on Wed, 07 Aug 2019 01:44:24 -0700 by JunbinDuan