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

1. What is MySQL multi-instance?

MySQL multi-instance simply means to install a set of MySQL programs on a server and provide external access through different ports. Multiple instances not only save the cost of physical hosts, but also effectively improve the efficiency of CPU and disk HA of a single physical host, and you can also deploy database HA solutions among multiple instances.

2. How to configure multiple instances of MySQL?

There are two ways to configure multiple instances of mysql

1. According to the official provision, multiple instances are implemented using separate configuration files through mysqld_multi. Customizing the configuration of each instance in this way is not quite good. The advantage is that it is easy to manage and centrally manage.

2, using multiple configuration files and startup files, the differences between configuration files: server-id, socket file location, configuration path and data storage location are different. When initializing, only different configuration files are used to initialize the database, and different startup files are used to start when starting. This method is simple in logic and configuration, but it is not easy to manage.

Let's configure it with the second multi-instance method.

III. Multi-instance configuration

MySQL is installed with mysql5.5.52 version. For installation method, please see MySQL5.5.52 compilation and installation.

1. Stop single instance mysql database

[root@db01] # / etc/init.d/mysqld stopShutting down MySQL. SUCCESS!

2. Self-startup is prohibited.

[root@db01 ~] # chkconfig mysqld off [root@db01 ~] # chkconfig-- list mysqldmysqld 0: close 1: close 2: close 3: close 4: close 5: close 6: close

3. Create a multi-instance root directory / data/ directory

[root@db01] # mkdir-p / data/ {3306 3307} / data

It should be noted that in the multi-instance startup file, the commands that need to be executed to start MySQL services of different forces are essentially different. For example, the command to start 3306 instances is as follows

Mysql_safe-- defaults-file=/data/3306/mysql & > / dev/null

The command to start 3307 instance is as follows:

Mysql_safe-- defaults-file=/data/3307/mysql & > / dev/null

Let's take a look at the actual command to stop different MySQL instance services in the multi-instance startup file.

The command to stop 3306 instances is as follows:

Mysqladmin-uroot-p123456-S / data/3306/mysql.sock shutdown

The command to stop 3307 instances is as follows:

Mysqladmin-uroot-p123456-S / data/3307/mysql.sock shutdown

4. Create configuration files and startup files for multiple instances of MySQL

1) 3306mysql instance configuration file

[root@db01 ~] # vim / data/3306/ my.cnf [client] port = 3306socket = / data/3306/ MySQL. Socks [MySQL] no-auto- rehash [mysqld] user = mysqlport = 3306socket = / data/3306/mysql.sockbasedir = / application/mysqldatadir = / data/3306/dataopen_files_limit = 1024back_log = 600max_connections = 800max_connect_errors = 3000table_cache = 614external-locking = FALSEmax_allowed_packet = 8Msort_buffer_size = 1Mjoin_ Buffer_size = 1Mthread_cache_size = 100thread_concurrency = 2query_cache_size = 2Mquery_cache_limit = 1Mquery_cache_min_res_unit = 2k#default_table_type = InnoDBthread_stack = 192K#transaction_isolation = READ-COMMITTEDtmp_table_size = 2Mmax_heap_table_size = 2Mlong_query_time = 1#log_long_format#log-error = / data/3306/error.log#log-slow-queries = / data/3306/slow.logpid-file = / data/3306/mysql .pidlog-bin = / data/3306/mysql-binrelay-log = / data/3306/relay-binrelay-log-info-file = / data/3306/relay-log.infobinlog_cache_size = 1Mmax_binlog_cache_size = 1Mmax_binlog_size = 2Mexpire_logs_days = 7key_buffer_size = 16Mread_buffer_size = 1Mread_rnd_buffer_size = 1Mbulk_insert_buffer_size = 1M#myisam_sort_buffer_size = 1M#myisam_max_sort_file_size = 10Gram myisam _ Max_extra_sort_file_size = 10G#myisam_repair_threads = 1#myisam_recoverlower_case_table_names = 1skip-name-resolveslave-skip-errors = 1032 Magazine 1062 replicateLay 2innodb_log_buffer_size dbql server = 1innodb_additional_mem_pool_size = 4Minnodb_buffer_pool_size = 32Minnodb_data_file_path = ibdata1:128M:autoextendinnodb_file_io_threads = 4innodb_thread_concurrency = 8innodb_flush_log_at_trx_commit = 2innodb_log_buffer_size = 2Minnodb_log_file_size = 4Minnodb_log_files_in_group = 3innodb_max_dirty_pages_pct = 90innodb_lock_wait_timeout = 120innodb_file_per_table = 0 [mysqldump] quickmax_allowed_packet = 2m [mysqld _ safe] log-error=/data/3306/mysql_3306.errpid-file=/data/3306/mysqld.pid

2) 3307mysql instance configuration file

[root@db01 ~] # cp / data/3306/my.cnf / data/3307/my.cnf [root@db01 ~] # sed-I's 3306 / 3307 / data/3307/my.cnf [root@db01 ~] # sed-n / server-id/p / data/3307/my.cnfserver-id = 1 [root@db01 ~] # sed-I 's#server-id = 1#server-id = 2roomg' / data/3307/my.cnf [root@db01 ~] # cat / data/3307/ my.cnf [client] port = 3307socket = / data/3307/ MySQL. Socks [MySQL] no-auto- rehash [mysqld] user = mysqlport = 3307socket = / data/3307/mysql.sockbasedir = / application/mysqldatadir = / data/3307/dataopen_files_limit = 1024back_log = 600max_connections = 800max_connect_errors = 3000table_cache = 614external-locking = FALSEmax_allowed_packet = 8Msort _ buffer_size = 1Mjoin_buffer_size = 1Mthread_cache_size = 100thread_concurrency = 2query_cache_size = 2Mquery_cache_limit = 1Mquery_cache_min_res_unit = 2k#default_table_type = InnoDBthread_stack = 192K#transaction_isolation = READ-COMMITTEDtmp_table_size = 2Mmax_heap_table_size = 2Mlong_query_time = 1#log_long_format#log-error = / data/3307/error.log#log-slow-queries = / data/3307/slow.logpid-file = / data/3307/mysql.pidlog-bin = / data/3307/mysql-binrelay-log = / data/3307/relay-binrelay-log-info-file = / data/3307/relay-log.infobinlog_cache_size = 1Mmax_binlog_cache_size = 1Mmax_binlog_size = 2Mexpire_logs_days = 7key_buffer_size = 16Mread_buffer_size = 1Mread_rnd_buffer_size = 1Mbulk_insert_buffer_size = 1M#myisam_sort_buffer_size = 1M#myisam_max_sort_file _ size = 10G#myisam_max_extra_sort_file_size = 10G#myisam_repair_threads = 1#myisam_recoverlower_case_table_names = 1skip-name-resolveslave-skip-errors = 1032, 1062 replicates, houses, houses, dbstores, mysqlserverAfter id = 2innodb_additional_mem_pool_size = 4Minnodb_buffer_pool_size = 32Minnodb_data_file_path = ibdata1:128M:autoextendinnodb_file_io_threads = 4innodb_thread_concurrency = 8innodb_flush_log_at_trx_commit = 2innodb_log_buffer_size = 2Minnodb _ log_file_size = 4Minnodb_log_files_in_group = 3innodb_max_dirty_pages_pct = 90innodb_lock_wait_timeout = 120innodb_file_per_table = 0 [mysqldump] quickmax_allowed_packet = 2m [mysqld _ safe] log-error=/data/3307/mysql_3307.errpid-file=/data/3307/mysqld.pid

5. The creation of MySQL multi-instance startup file is almost the same as that of configuration file. You can also add the following via vim command:

1) 3306mysql instance launch file

[root@db01 ~] # vim / data Filename:mysql# Description:Start MySQL multi instance script# Version:1.0# Date:2016/12/10# Author:xuanwiei# Email:1756112532@qq.com# 3306 Filename:mysql# Description:Start MySQL multi instance script# Version:1.0# Date:2016/12/10# Author:xuanwiei# Email:1756112532@qq.com# # # initport=3306mysql_user= "root" mysql_pwd= "123456" # here is to be modified to match the database password with CmdPath= "/ application/mysql/bin" mysql_sock= "/ data/$ {port} / mysql.sock" # startup functionfunction_start_mysql () {if [!-e "$mysql_sock"] Then printf "Starting MySQL...\ n" / bin/sh ${CmdPath} / mysqld_safe-- defaults-file=/data/$ {port} / my.cnf 2 > & 1 > / dev/null & else printf "MySQL is running...\ n" exit fi} # stop functionfunction_stop_mysql () {if [!-e "$mysql_sock"] Then printf "MySQL is stopped...\ n" exit else printf "Stoping MySQL...\ n" ${CmdPath} / mysqladmin-u ${mysql_user}-p$ {mysql_pwd}-S / data/$ {port} / mysql.sock shutdown fi} # restart functionfunction_restart_mysql () {printf "Restarting MySQL...\ n" function_stop_mysql sleep 2 function_start_mysql} case $1 instart) function_start_mysql Stop) function_stop_mysql;;restart) function_restart_mysql;;*) printf "Usage: / data/$ {port} / mysql {start | stop | restart}\ n" esac

2) 3307mysql instance startup file

[root@db01 ~] # cp / data/3306/mysql / data/3307/mysql [root@db01 ~] # sed-I's license 3306 "3307" / data/3307/mysql [root@db01 ~] # cat / data # Description: Start MySQL multi instance script# Version: 1. Pound Date: 2016 12 CmdPath=: xuanwiei# Email: 1756112532 pound q.composure initport3307 mysql registered username = "root" mysql_pwd= "123456" CmdPath= "/ application/mysql/bin" mysql_sock= " / data/$ {port} / mysql.sock "# startup functionfunction_start_mysql () {if [!-e" $mysql_sock "] Then printf "Starting MySQL...\ n" / bin/sh ${CmdPath} / mysqld_safe-- defaults-file=/data/$ {port} / my.cnf 2 > & 1 > / dev/null & else printf "MySQL is running...\ n" exit fi} # stop functionfunction_stop_mysql () {if [!-e "$mysql_sock"] Then printf "MySQL is stopped...\ n" exit else printf "Stoping MySQL...\ n" ${CmdPath} / mysqladmin-u ${mysql_user}-p$ {mysql_pwd}-S / data/$ {port} / mysql.sock shutdown fi} # restart functionfunction_restart_mysql () {printf "Restarting MySQL...\ n" function_stop_mysql sleep 2 function_start_mysql} case $1 instart) function_start_mysql Stop) function_stop_mysql;;restart) function_restart_mysql;;*) printf "Usage: / data/$ {port} / mysql {start | stop | restart}\ n" esac

6. Configure file permissions for multiple instances of MySQL

(1) authorize mysql users and user groups to manage the root / data of the entire multi-instance through the following command

[root@db01] # chown-R mysql.mysql / data

(2) the mysql of all startup files can be executed through the following mysql multi-instance. It is best to set 700permissions, and be careful not to use 755permissions, because the file contains the database administrator password and will be read.

[root@db01 scripts] # find / data/-type f-name "mysql" / data/3306/mysql/data/3307/mysql [root@db01 scripts] # find / data/-type f-name "mysql" | xargs chmod 700 [root@db01 scripts] # find / data/-type f-name "mysql" | xargs ls-lmurrwx1 root root 1359 December 10 16:20 / data/3306/mysql-rwx- 1 root root 1359 December 10 16:22 / data/3307/mysql

7. Initialize the database file of multiple instances of MySQL

(1) initialize MySQL database

Cd / application/mysql/scripts/-basedir=/application/mysql\ >-datadir=/data/3306/data\ >-user=mysqlWARNING: The host 'db01' could not be looked up with resolveip.This probably means that your libc libraries are not 100% compatiblewith this binary MySQL version. The MySQL daemon, mysqld, should worknormally with the exception that hostname resolving will not work.This means that you should use IP addresses instead of hostnameswhen specifying MySQL privileges! Installing MySQL system tables...161117 14:14:14 [Note] / application/mysql/bin/mysqld (mysqld 5.5.52) starting as process 46676... OKFilling help tables...161117 14:14:15 [Note] / application/mysql/bin/mysqld (mysqld 5.5.52) starting as process 46683... OK

If there are two ok, initialization is successful

WARNING: The host 'db01' could not be looked up with resolveip.

The reason is that db01 is not parsed in the hosts file

Solution: echo "172.16.1.52 db01" > > / etc/hosts

[root@db01 scripts] # / application/mysql/scripts/mysql_install_db\ >-- basedir=/application/mysql\ >-- datadir=/data/3307/data\ >-- user=mysqlInstalling MySQL system tables...161117 14:18:20 [Note] / application/mysql/bin/mysqld (mysqld 5.5.52) starting as process 46733... OKFilling help tables...161117 14:18:21 [Note] / application/mysql/bin/mysqld (mysqld 5.5.52) starting as process 46740... OK

If there are two ok, initialization is successful

There is no WARNING: The host 'db01' could not be looked up with resolveip this time.

(2) the principle and result of initializing database.

[root@db01 scripts] # tree / data/data ├── 3306 │ ├── data │ │ ├── mysql │ ├── columns_priv.frm │ columns_priv.MYD │ ├── columns_priv.MYI │ ├── db.frm │ ├── db.MYD │ │ │ ├── db.MYI │ ├── event.frm │ ├── event.MYD │ ├── event.MYI │ ├── func.frm │ ├── func.MYD │ ├── func.MYI … Omit the part...

(3) initialization failure

Example 1: the warning message "WARNING: The host 'db01' could not be looked up with resolveip."

This warning message can be ignored. If you have to solve it, you need to modify the hostname resolution.

Echo "172.16.1.52 db01" > > / etc/hosts

8. Start MySQL multi-instance database

Start command for the first instance 3306

/ data/3306/mysql start

Start command for the second instance 3307

/ data/3307/mysql start

Now check to see if the MySQL multi-instance database started successfully

Netstat-lntup | grep 330

Operation procedure:

[root@db01 scripts] # / data/3306/mysql Usage: / data/3306/mysql {start | stop | restart} [root@db01 scripts] # / data/3306/mysql startStarting MySQL... [root@db01 scripts] # / data/3307/mysql startStarting MySQL...

View Port

[root@db01 scripts] # ss-nlutp | grep 330tcp LISTEN 0600 *: 3306 *: * users: (("mysqld", 48766, 12) tcp LISTEN 0600 *: 3307 *: * users: ("mysqld", 49510 12))

9. Configure and manage MySQL multi-instance database

(1) configure MySQL multi-instance database to start automatically

The startup of service is self-starting and critical, and the startup of MySQL multi-instance is no exception. Add the startup command of MySQL multi-instance to / etc/rc.local to achieve self-startup:

Cat > > / etc/rc.local

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.