How does MySQL manage multiple instances?

How does MySQL manage multiple instances?

MySQL can run multiple instances in two ways. The first is to start different processes to implement multiple instances. The second is to use a separate configuration file through mysqld_multi to implement multiple instances.

Environment preparation:

Operating System: Red Hat Enterprise Linux Server release 6.5 (Santiago)

MySQL version: mysql-5.6.22-linux-glibc2.5-x86_64

To run multiple instances, first install the MySQL software. For the installation method, see the previous blog post: Install MySQL. after installing the MySQL software, create multiple instances with ports 3306, 3307, 3308, and 3309 respectively, no matter which method is used to manage multiple instances, multiple databases need to be initialized.

1. Use multiple configuration files to manage multiple instances

1. Create configuration files for each instance

# Mkdir/data/mysql/conf/-p

# Cd/data/mysql/conf/

# Vim my_3306.cnf

[Client]
Port = 3306
Socket =/tmp/mysql_3306.sock

[Mysql]
Prompt = "\ u @ \ h: \ p \ R: \ m: \ s [\ d]>"
# Tee =/data/mysql/mysql_3306/query. log
No-auto-rehash


[Mysqld]
# Misc
User = mysql
Basedir =/usr/local/mysql
Datadir =/data/mysql/mysql_3306
Port = 3306
Socket =/tmp/mysql_3306.sock
Event_scheduler = 0

# Timeout
Interactive_timeout = 300
Wait_timeout = 300

# Character set
Character-set-server = utf8

Open_files_limit = 65535
Max_connections = 100
Max_connect_errorrs = 100000

Skip-name-resolve = 1
# Logs
Log-output = file
Slow_query_log = 1
Slow_query_log_file = slow. log
Log-error = error. log
Log_warnings = 2
Pid-file = mysql. pid
Long_query_time = 1
# Log-slow-admin-statements = 1
# Log-queries-not-using-indexes = 1
Log-slow-slave-statements = 1


# Binlog
Binlog_format = row
Servers-id = 883306
Log-bin = mysql-bin
Binlog_cache_size = 4 M
Max_binlog_size = 1G
Max_binlog_cache_size = 2G
Sync_binlog = 0
Expire_logs_days = 10

# Relay log
Skip_slave_start = 1
Max_relay_log_size = 1G
Relay_log_purge = 1
Relay_log_recovery = 1
Log_slave_updates
# Slave-skip-errors = 1062

Explicit_defaults_for_timestamp = true
# Buffers & cache
Table_open_cache = 2048
Table_definition_cached = 2048
Table_open_cache = 2048
Max_heap_table_size = 96 M
Sort_buffer_size = 2 M
Join_buffer_size = 2 M
Thread _ cache_size = 256
Query_cache_size = 0
Query_cache_type = 0
Query_cache_limit = 256 K
Query_cache_min_res_unit = 512
Thread_stack = 192 K
Tmp_table_size = 96 M
Key_buffer_size = 8 M
Read_buffer_size = 2 M
Read_rnd_buffer_size = 16 M
Bulk_insert_buffer_size = 32 M

# Myisam
Myisam_sort_buffer_size = 128 M
# Myisam_max_sort_file_size = 10G
Myisam_max_sort_file_size = 100 M

Myisam_repair_threads = 1

# Innodb
Innodb_buffer_pool_size = 100 M
Innodb_buffer_pool_instances = 1
Innodb_data_file_path = ibdata1: 1G: autoextend
Innodb_flush_log_at_trx_commit = 2
Innodb_log_buffer_size = 64 M
Innodb_log_file_size = 500 M
Innodb_log_files_in_group = 3
Innodb_max_dirty_pages_pct = 50
Innodb_file_per_table = 1
Innodb_rollback_on_timeout
Innodb_status_file = 1
Innodb_io_capacity = 2000
Transaction_isolation = READ-COMMITTED
Innodb_flush_method = O_DIRECT

The configuration files of MySQL instances on ports 3307, 3308, and 3309 are similar to those on port 3306. You only need to replace the port.

# Cp my_3306.cnf my_3307.cnf

# Cp my_3306.cnf my_3308.cnf

# Cp my_3306.cnf my_3309.cnf

# Sed-I's/3306/3307/G' my_3307.cnf

# Sed-I's/3306/3308/G' my_3308.cnf

# Sed-I's/3306/3309/G' my_3309.cnf

# Chown mysq. mysql/data/mysql/conf-R

2. initialize the database

# Cd/usr/local/mysql

#./Scripts/mysql_install_db -- user = mysql -- defaults-file =/data/mysql/conf/my_3306.cn -- datadir =/data/mysql/mysql_3306/

#./Scripts/mysql_install_db -- user = mysql -- defaults-file =/data/mysql/conf/my_3307.cn -- datadir =/data/mysql/mysql_3307/

#./Scripts/mysql_install_db -- user = mysql -- defaults-file =/data/mysql/conf/my_3308.cn -- datadir =/data/mysql/mysql_3308/

#./Scripts/mysql_install_db -- user = mysql -- defaults-file =/data/mysql/conf/my_3309.cn -- datadir =/data/mysql/mysql_3309/

3. Start the database

# Mysqld_safe -- defaults-file =/data/mysql/conf/my_3306.cnf &

# Mysqld_safe -- defaults-file =/data/mysql/conf/my_3307.cnf &

# Mysqld_safe -- defaults-file =/data/mysql/conf/my_3308.cnf &

# Mysqld_safe -- defaults-file =/data/mysql/conf/my_3309.cnf &

Observe the MySQL process

# Ps-ef | grep mysqld

Root 15873 12043 0 00:00:00 pts/0/bin/sh/usr/local/mysql/bin/mysqld_safe -- defaults-file =/data/mysql/conf/my_3306.cnf

Mysql 16773 15873 0 00:00:00 pts/0/usr/local/mysql/bin/mysqld -- defaults-file =/data/mysql/conf/my_3306.cnf -- basedir =/usr/local/ mysql -- datadir =/data/mysql/mysql_3306 -- plugin-dir =/usr/local/mysql/lib/plugin -- user = mysql -- log-error =/data/mysql/mysql_3306 /error. log -- open-files-limit = 65535 -- pid-file =/data/mysql/mysql_3306/mysql. pid -- socket =/tmp/mysql_3306.sock -- port = 3306

Root 16875 12043 0 00:00:00 pts/0/bin/sh/usr/local/mysql/bin/mysqld_safe -- defaults-file =/data/mysql/conf/my_3307.cnf

Mysql 17775 16875 2 00:00:00 pts/0/usr/local/mysql/bin/mysqld -- defaults-file =/data/mysql/conf/my_3307.cnf -- basedir =/usr/local/ mysql -- datadir =/data/mysql/mysql_3307 -- plugin-dir =/usr/local/mysql/lib/plugin -- user = mysql -- log-error =/data/mysql/mysql_3307 /error. log -- open-files-limit = 65535 -- pid-file =/data/mysql/mysql_3307/mysql. pid -- socket =/tmp/mysql_3307.sock -- port = 3307

Root 17800 12043 0 00:00:00 pts/0/bin/sh/usr/local/mysql/bin/mysqld_safe -- defaults-file =/data/mysql/conf/my_3308.cnf

Mysql 18700 17800 7 00:00:01 pts/0/usr/local/mysql/bin/mysqld -- defaults-file =/data/mysql/conf/my_3308.cnf -- basedir =/usr/local/ mysql -- datadir =/data/mysql/mysql_3308 -- plugin-dir =/usr/local/mysql/lib/plugin -- user = mysql -- log-error =/data/mysql/mysql_3308 /error. log -- open-files-limit = 65535 -- pid-file =/data/mysql/mysql_3308/mysql. pid -- socket =/tmp/mysql_3308.sock -- port = 3308

Root 18723 12043 0 00:00:00 pts/0/bin/sh/usr/local/mysql/bin/mysqld_safe -- defaults-file =/data/mysql/conf/my_3309.cnf

Mysql 19623 18723 7 00:00:00 pts/0/usr/local/mysql/bin/mysqld -- defaults-file =/data/mysql/conf/my_3309.cnf -- basedir =/usr/local/ mysql -- datadir =/data/mysql/mysql_3309 -- plugin-dir =/usr/local/mysql/lib/plugin -- user = mysql -- log-error =/data/mysql/mysql_3309 /error. log -- open-files-limit = 65535 -- pid-file =/data/mysql/mysql_3309/mysql. pid -- socket =/tmp/mysql_3309.sock -- port = 3309

Log on to MySQL

# Mysql-S/tmp/mysql_3306.sock

# Mysql-S/tmp/mysql_3307.sock

# Mysql-S/tmp/mysql_3308.sock

# Mysql-S/tmp/mysql_3309.sock

4. Shut down the MySQL process

Mysqladmin shutdown-S/tmp/mysql_3306.sock

Mysqladmin shutdown-S/tmp/mysql_3307.sock

Mysqladmin shutdown-S/tmp/mysql_3308.sock

Mysqladmin shutdown-S/tmp/mysql_3309.sock

 

1 2 Next Page