MySQL multi-instance operation guide under CentOS

Source: Internet
Author: User
Tags log log mysql in percona

MySQL work everywhere, countless installation, optimization, has no time, the collation of the archive, recently in the MySQL Dass service, the recently encountered problems, to collate, carding a guide for everyone;


MySQL multi-instance is a server running multiple MySQL service processes, open different service ports, through different sockets to listen to different service ports to provide their own services.

MySQL Benefits of multiple cases:

1, the effective use of server resources: through multi-instance configuration, the server can fully utilize the remaining resources.
2, the resource preemption problem: The problem of resource preemption, when a service instance service is high concurrency or slow query, will consume more memory, CPU, disk IO resources, resulting in other instances of the server service quality degradation.
3, save resources.

Two ways to deploy multiple instances

    1. Using multiple profiles to launch different processes to implement multiple instances, the advantages of this method is simple logic, simple configuration, the disadvantage is not easy to manage;
    2. Through the official Mysqld_multi with a separate configuration file to achieve multi-instance, this way to customize each instance configuration is not too, the advantage is easy to manage, centralized management;

To install two databases under the same development environment, you must address the following issues

    • Configuration file installation path cannot be the same
    • The database directory cannot be the same
    • Startup script cannot have the same name
    • Ports cannot be the same
    • Socket file generation path cannot be the same MySQL multi-instance Operation guide

      Specific detailed reference website (

    • MySQL has a variety of installation methods, such as binary installation, source code compilation and installation, Yum installation;
    • Yum installation is the default path, not conducive to post-maintenance, installation is relatively simple;
    • Source installation and compilation process is relatively long, if the source code is not modified and requires the use of a higher version of MySQL;
    • It is recommended to use the binary installation. This article installs MySQL in binary and uses Mysqld_mutil for MySQL multi-instance management. Environment preparation

CENTOS7 x64 Reference Address:
MySQL 5.7.22 (compressed version)

    • Download MySQL
cd /usr/local  wget
    • Unzip the installation
tar -zxvf  mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz  mv mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz  /usr/local/mysql
Initializing user groups and users

Create a MySQL user group and user, and this user is not logged in
Create user group: Groupadd MySQL
Create a non-logged on User: useradd-g mysql-s/sbin/nologin-d/opt/mysql MySQL
View the user information after creation: ID MSYQL

 id mysqluid=501(mysql) gid=501(mysql) groups=501(mysql)
Create a related directory
cd /  mkdir /data  cd /data  mkdir mysql  cd mysql   mkdir {mysql_3306,mysql_3307}  cd /data/mysql/mysql_3306  mkdir {data,log,tmp}  cd /data/mysql/mysql_3307  mkdir {data,log,tmp}
Change directory Permissions
chown -R mysql:mysql /data/mysql/  chown -R mysql:mysql /usr/local/mysql/  
Adding environment variables
echo ‘export PATH=$PATH:/usr/local/mysql/bin‘ >>  /etc/profile  source /etc/profile   

Check whether the configuration
cat /etc/profile

Copy my.cnf files to etc directory (MySQL 5.722 no my-default.cnf, need to be created automatically manually or copy before)

    • Create your own
      Modify MY.CNF

      Replace all of the contents in the/ETC/MY.CNF (the following configuration, integrated MySQL master-slave configuration)

[Client]port=3306socket=/tmp/mysql.sock[mysqld_multi] mysqld =/usr/local/mysql/bin/mysqld_safe mysqladmin =/usr/ Local/mysql/bin/mysqladmin log =/data/mysql/mysqld_multi.log [mysqld] user=mysql basedir =/usr/local/mysql sql_mode= No_engine_substitution,strict_trans_tables [mysqld3306] mysqld=mysqld mysqladmin=mysqladmin datadir=/data/mysql/ Mysql_3306/data port=3306 server_id=3306 socket=/tmp/mysql_3306.sock log-output=file slow_query_log = 1 Long_query_ Time = 1 Slow_query_log_file =/data/mysql/mysql_3306/log/slow.log Log-error =/data/mysql/mysql_3306/log/error.log Log-bin =/data/mysql/mysql_3306/log/mysql3306_binbinlog-ignore-db = MySQL [mysqld3307] mysqld=mysqld mysqladmin= Mysqladmindatadir=/data/mysql/mysql_3307/data port=3307 server_id=3307 Socket=/tmp/mysql_3307.sock log-output=file Slow_query_log = 1 Long_query_time = 1 Slow_query_log_file =/data/mysql/mysql_3307/log/slow.log Log-error =/data/mysql/ Mysql_3307/log/error.log Log-bin =/data/mysql/mysql_3307/log/mysql3307_binrEplicate-ignore-db=mysqlrelay-log = Slave-relay-binrelay-log-index = Slave-relay-bin.indexread_only 
Initializing the database

Initialize each instance: the password will be generated in the log after initialization, remember to save, initialize the password to use

cd /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/mysql_3306/data/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/mysql_3307/data/##开启各实例的SSL连接bin/mysql_ssl_rsa_setup --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/mysql_3306/data/bin/mysql_ssl_rsa_setup --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/mysql_3307/data/

installation directory for--basedir:mysql
--datadir: Data file directory for database

To see if the database was initialized successfully
cd /data/mysql/mysql_3306/data

Setting the Startup file
cp /usr/local/mysql/support-files/mysqld_multi.server /etc/init.d/mysqld_multi
Give script permission to execute
chmod +x /etc/init.d/mysqld_multi
Join Service Services Management
chkconfig --add mysqld_multi  
Mysqld_multi for multi-instance management
    • Start all instances:
      /usr/local/mysql/bin/mysqld_multi start  
    • To view the full instance status:
      /usr/local/mysql/bin/mysqld_multi report  
    • To start a single instance:
      /usr/local/mysql/bin/mysqld_multi start 3306  
    • To stop a single instance:
      /usr/local/mysql/bin/mysqld_multi stop 3306  
    • Stop it
      mysqld_multi stop 3306 --password=root#or 最好用这个mysqladmin -S /tmp/mysql_3306.sock -uroot -p shutdown
    • To view a single instance status:
      /usr/local/mysql/bin/mysqld_multi report 3306  
    • To view the listening ports for an instance:
      ss -tulpn|grep mysqld

      Change Password

      mysql -S /tmp/mysql_3306.sock -p
    • Enter the password you previously recorded and enter the password setting
set password=password(‘123456‘);
    • To make the changes take effect, or restart the service
flush privileges;

应用路径:/data/web/wordpressnginx 配置:/usr/local/openresty/nginx/conf/site-enable/help.conf

3307 will not write, you follow the above method, to operate;

To set up a remote connection to MySQL:

Where Root is the user name,% means everyone can access, password is the password, try not to use root, security is very important
If you see the current position, you have succeeded, pay tribute to the small partners learning on the road, together with you knowledge summary;
If the feeling is still good, share out, follow-up to introduce, MySQL master and slave operation Construction;

MySQL's classic working instructions; Percona Toolkit


MySQL multi-instance operation guide under CentOS

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.