MySQL multi-instance operation guide under CentOS

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;

Preface

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 (https://dev.mysql.com/doc/refman/5.7/en/installing.html)

• 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: https://dev.mysql.com/downloads/mysql/
MySQL 5.7.22 (compressed version)

• Download MySQL

cd /usr/local  wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz

• 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

    vim/etc/my.cnf  

  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/

Ps:
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

https://www.percona.com/downloads/percona-toolkit/LATEST/

Percona-xtrabackup

https://www.percona.com/doc/percona-xtrabackup/2.1/xtrabackup_bin/incremental_backups.html#

MySQL multi-instance operation guide under CentOS