| |
MySQL running multiple instances There are two ways, first is to use multiple configuration files to start different processes to achieve multi-instance; the second is to implement multi-instance use separate configuration files mysqld_multi
Preparing the environment:
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, the installation method of Reference before Bowen: MySQL installation After installing MySQL software to create multiple instances 3306,3307,3308,3309 port were below respectively, either way use to manage multi-instance, are needed to initialize multiple database
First, the use of multiple configuration files to manage multiple instances
1, create a profile 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_errors = 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
server-id = 883306
log-bin = mysql-bin
binlog_cache_size = 4M
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 = 1032,1053,1062
explicit_defaults_for_timestamp = true
#buffers & cache
table_open_cache = 2048
table_definition_cache = 2048
table_open_cache = 2048
max_heap_table_size = 96M
sort_buffer_size = 2M
join_buffer_size = 2M
thread_cache_size = 256
query_cache_size = 0
query_cache_type = 0
query_cache_limit = 256K
query_cache_min_res_unit = 512
thread_stack = 192K
tmp_table_size = 96M
key_buffer_size = 8M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 32M
#myisam
myisam_sort_buffer_size = 128M
#myisam_max_sort_file_size = 10G
myisam_max_sort_file_size = 100M
myisam_repair_threads = 1
#innodb
innodb_buffer_pool_size = 100M
innodb_buffer_pool_instances = 1
innodb_data_file_path = ibdata1: 1G: autoextend
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 64M
innodb_log_file_size = 500M
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
Port Profile MySQL instance 3307,3308,3309 and 3306 of similar profile, 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 &
Observation MySQL process
# Ps -ef | grep mysqld
root 15873 12043 0 09:55 pts / 0 00:00:00 / bin / sh / usr / local / mysql / bin / mysqld_safe --defaults-file = / data / mysql / conf / my_3306.cnf
mysql 16773 15873 0 09:55 pts / 0 00:00:00 / 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 09:56 pts / 0 00:00:00 / bin / sh / usr / local / mysql / bin / mysqld_safe --defaults-file = / data / mysql / conf / my_3307.cnf
mysql 17775 16875 2 09:56 pts / 0 00:00:00 / 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 09:56 pts / 0 00:00:00 / bin / sh / usr / local / mysql / bin / mysqld_safe --defaults-file = / data / mysql / conf / my_3308.cnf
mysql 18700 17800 7 09:56 pts / 0 00:00:01 / 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 09:56 pts / 0 00:00:00 / bin / sh / usr / local / mysql / bin / mysqld_safe --defaults-file = / data / mysql / conf / my_3309.cnf
mysql 19623 18723 7 09:56 pts / 0 00:00:00 / 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
Login MySQL data
# 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. Close 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
Second, the use mysqld_multi to manage the MySQL data
Create a unified profile
# Cat /etc/my.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_multi]
mysqld = / usr / local / mysql / bin / mysqld_safe
mysqladmin = / usr / local / mysql / bin / mysqladmin
log = /data/mysql/mysql_multi/mysqld_multi.log
[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_errors = 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
server-id = 883306
log-bin = mysql-bin
binlog_cache_size = 4M
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 = 1032,1053,1062
explicit_defaults_for_timestamp = true
#buffers & cache
table_open_cache = 2048
table_definition_cache = 2048
table_open_cache = 2048
max_heap_table_size = 96M
sort_buffer_size = 2M
join_buffer_size = 2M
thread_cache_size = 256
query_cache_size = 0
query_cache_type = 0
query_cache_limit = 256K
query_cache_min_res_unit = 512
thread_stack = 192K
tmp_table_size = 96M
key_buffer_size = 8M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 32M
#myisam
myisam_sort_buffer_size = 128M
#myisam_max_sort_file_size = 10G
myisam_max_sort_file_size = 100M
myisam_repair_threads = 1
#innodb
innodb_buffer_pool_size = 100M
innodb_buffer_pool_instances = 1
innodb_data_file_path = ibdata1: 1G: autoextend
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 64M
innodb_log_file_size = 500M
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
# Port number for the instance special configuration 3306
[Mysqld3306]
port = 3306
server-id = 883306
# Specify the appropriate version of the present example basedir and datadir
basedir = / usr / local / mysql
datadir = / data / mysql / mysql_3306
socket = /tmp/mysql_3306.sock
# Reconfigure these options are not the same as the global configuration will override the global settings directly above
innodb_buffer_pool_size = 100m
#transaction_isolation = REPEATABLE-READ
# Port number for the instance special configuration 3306
[Mysqld3307]
port = 3307
server-id = 883307
# Specify the appropriate version of the present example basedir and datadir
basedir = / usr / local / mysql
datadir = / data / mysql / mysql_3307
socket = /tmp/mysql_3307.sock
# Reconfigure these options are not the same as the global configuration will override the global settings directly above
innodb_buffer_pool_size = 100m
#transaction_isolation = REPEATABLE-READ
[Mysqld3308]
port = 3308
server-id = 883308
# Binlog-do-db = database01
basedir = / usr / local / mysql
datadir = / data / mysql / mysql_3308
socket = /tmp/mysql_3308.sock
# Reconfigure these options are not the same as the global configuration will override the global settings directly above
innodb_buffer_pool_size = 100m
innodb_flush_log_at_trx_commit = 2
sync_binlog = 0
[Mysqld3309]
port = 3309
server-id = 883309
# Binlog-do-db = database02
basedir = / usr / local / mysql
datadir = / data / mysql / mysql_3309
socket = /tmp/mysql_3309.sock
# Reconfigure these options are not the same as the global configuration will override the global settings directly above
innodb_buffer_pool_size = 100m
innodb_flush_log_at_trx_commit = 2
sync_binlog = 0
# Mkdir / data / mysql / mysql_multi / # to store logs generated mysql_multi
# Chown mysql.mysql / data / mysql / mysql_multi / -R
2, initialize the database
As has been initialized, you do not have to re-initialize
# Cd / usr / local / mysql
# ./scripts/mysql_install_db --user = Mysql --datadir = / data / mysql / mysql_3306 /
# ./scripts/mysql_install_db --user = Mysql --datadir = / data / mysql / mysql_3307 /
# ./scripts/mysql_install_db --user = Mysql --datadir = / data / mysql / mysql_3308 /
# ./scripts/mysql_install_db --user = Mysql --datadir = / data / mysql / mysql_3309 /
3, start the MySQL data
# Mysqld_multi start 3306,3307,3308,3309 # can also start a separate
# Mysqld_multi report 3306,3307,3308,3309
Reporting MySQL servers
MySQL server from group: mysqld3306 is running
MySQL server from group: mysqld3307 is running
MySQL server from group: mysqld3308 is running
MySQL server from group: mysqld3309 is running
# Tailf /data/mysql/mysql_multi/mysqld_multi.log
151213 23:01:06 mysqld_safe Starting mysqld daemon with databases from / data / mysql / mysql_3306
151213 23:01:06 mysqld_safe Starting mysqld daemon with databases from / data / mysql / mysql_3307
151213 23:01:06 mysqld_safe Starting mysqld daemon with databases from / data / mysql / mysql_3308
151213 23:01:06 mysqld_safe Starting mysqld daemon with databases from / data / mysql / mysql_3309
mysqld_multi log file version 2.16; run: Sun Dec 13 23:01:27 2015
Reporting MySQL servers
MySQL server from group: mysqld3306 is running
MySQL server from group: mysqld3307 is running
MySQL server from group: mysqld3308 is running
MySQL server from group: mysqld3309 is running
Login MySQL database:
# Mysql -S /tmp/mysql_3306.sock
# Mysql -S /tmp/mysql_3307.sock
# Mysql -S /tmp/mysql_3308.sock
# Mysql -S /tmp/mysql_3307.sock
4, close the MySQL database
# Mysqld_multi stop 3306
# Mysqld_multi stop 3307,3308,3309
# Ps -ef | grep mysqld
root 30822 12043 0 23:04 pts / 0 00:00:00 grep mysqld
In addition, the configuration file can also be used to start a separate instance of MySQL 3306
# Cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
# Chmod + x /etc/init.d/mysqld
# /etc/init.d/mysqld Start
Starting MySQL .. [OK]
# Mysql -S /tmp/mysql_3306.sock
# /etc/init.d/mysqld Stop
Shutting down MySQL .. [OK] |
|
|
|