Shulou(Shulou.com)06/01 Report--

Mysql work is everywhere, countless installation, optimization, there has been no time to organize and file, recently doing mysql Dass service, the recent problems encountered, sort out an operation guide; for your reference

Preface

Mysql multi-instance means that multiple Mysql service processes are run on a server, different service ports are opened, and different socket listens on different service ports to provide their own services.

Benefits of multiple cases of Mysql:

1. Effective use of server resources: through multi-instance configuration, the remaining resources of the server can be fully utilized.

2. Resource preemption problem: resource preemption problem. When the concurrency of a service instance service is very high or slow query is enabled, more memory, CPU and disk IO resources will be consumed, resulting in a decline in the service quality provided by other instances on the server.

3. Save resources.

Two ways to deploy multiple instances

Using multiple configuration files to start different processes to achieve multiple instances, this method has the advantages of simple logic and simple configuration, but the disadvantage is that it is not convenient to manage; through the official mysqld_multi to achieve multiple instances using separate configuration files, this way to customize the configuration of each instance is not quite aspects, the advantage is that it is very convenient to manage, centralized management

To install two databases in the same development environment, the following issues must be addressed

Configuration file installation path cannot be the same database directory cannot be the same startup script cannot be the same port cannot be the same socket file generation path cannot be the same Mysql multi-instance operation guide

For more information, please see the official website (https://dev.mysql.com/doc/refman/5.7/en/installing.html).

There are many ways to install mysql, such as binary installation, source code compilation installation, yum installation; yum installation is the default path, which is not conducive to later maintenance, and installation is relatively simple; the process of source code installation and compilation is relatively long, if there are no changes to the source code and a higher version of mysql is required; binary installation is recommended. This article installs mysql in binary and uses mysqld_mutil for mysql multi-instance management. Environmental preparation

Centos7 x64 reference address: https://dev.mysql.com/downloads/mysql/

Mysql 5.7.22 (compressed version)

Download mysqlcd / usr/local wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz extract install 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 initialize user groups and users

Create a mysql user group and user who is not logged in

Create a user group: groupadd mysql

Create an unlogged-in user: useradd-g mysql-s / sbin/nologin-d / opt/mysql mysql

View the created user information: id msyql

Id mysqluid=501 (mysql) gid=501 (mysql) groups=501 (mysql) create related directories 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/ add environment variable echo 'export PATH=$PATH:/usr/local/mysql/bin' > > / etc/profile source / etc/profile

Check if cat / etc/profile is configured

Copy the my.cnf file to the etc directory (mysql 5.722 does not have my-default.cnf and needs to be automatically created manually or copy in the previous place)

Create your own vim / etc/my.cnf and modify my.cnf

Replace all the contents in / etc/my.cnf with the following (the following configuration, mysql master-slave configuration has been integrated)

[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=mysqladmindatadir= / 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 initialize the database

Initialize each instance: after initialization, a password will be generated in the log. Remember to save it. Initialize the password using

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/## enable the SSL connection of each instance 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:

-- basedir:mysql installation directory

-- datadir: the data file directory of the database

Check whether the database is initialized successfully cd / data/mysql/mysql_3306/data

Set the startup file cp / usr/local/mysql/support-files/mysqld_multi.server / etc/init.d/mysqld_multi to give the script executable permissions chmod + x / etc/init.d/mysqld_multi to join the service service management chkconfig-- add mysqld_multi mysqld_multi for multi-instance management to start all instances: / usr/local/mysql/bin/mysqld_multi start to view all instance status: / usr/local/mysql/bin/mysqld_multi Report starts a single instance: / usr/local/mysql/bin/mysqld_multi start 3306 stops a single instance: / usr/local/mysql/bin/mysqld_multi stop 3306 stops mysqld_multi stop 3306-- password=root#or had better use this mysqladmin-S / tmp/mysql_3306.sock-uroot-p shutdown to view the status of a single instance: / usr/local/mysql/bin/mysqld_multi report 3306 to view the listening port of the instance: ss-tulpn | grep mysqld

Change the password mysql-S / tmp/mysql_3306.sock-p enter the previously recorded password, enter the password setting set password=password ('123456'), make the change effective, or restart the service flush privileges

Application path: / data/web/wordpressnginx configuration: / usr/local/openresty/nginx/conf/site-enable/help.conf

3307 does not write, you follow the above method, carry on the operation

Set remote connection mysql:GRANT ALL PRIVILEGES ON *. * TO 'root'@'%' IDENTIFIED BY' password' WITH GRANT OPTION;FLUSH PRIVILEGES

Where root is the user name,% means that everyone can access, password is the password, try not to use root, security is very important

If you see your current position, you have succeeded. Pay tribute to your friends who are studying on the road and sum up your knowledge with you.

If it feels good, share it and introduce it to you later. Mysql master and slave operations are built.

Mysql's classic work guide; 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#

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.