Shulou(Shulou.com)05/31 Report--

This article will explain in detail how the multi-instance introduction and configuration of MySQL is, and the content of the article is of high quality, so the editor will share it with you for reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

1. What is MySQL multi-instance

To put it simply, MySQL multi-instance is to open multiple different service ports on a server and run multiple MySQL service processes at the same time. These service processes provide services by listening to different server ports through different socket. These MySQL multiple instances share a set of MySQL installers, using different my.cnf (or the same) configuration files, launchers (or the same), and data files. When providing services, multi-instance MySQL seems to be logically independent, and it obtains the corresponding number of hardware resources of the server according to the corresponding settings of the configuration file.

The advantage of multi-instance is that it can make effective use of server resources and save server resources, but there will be the problem of mutual preemption of resources. When a database instance has high concurrency or has SQL slow query, the whole instance will consume a lot of resources such as system CPU, disk I and so on, resulting in a decline in the quality of service provided by other database instances on the server. The resources acquired by different instances are relatively independent and cannot be completely isolated like virtualization.

two。 Configure multiple instances of MySQL

The multi-instance configuration of MySQL can be a single-file, single-startup deployment scenario, but it is not recommended and the risk is too high. What is deployed here is a multi-profile, multi-startup deployment solution. Previously, we have built the MySQL service, that is, there is a default instance with port 3306. Here, we add one more instance, port 3307.

(1) create a multi-instance data file directory, which can be determined by itself.

Mkdir-p / data/3307/data

(2) create a MySQL multi-instance configuration file, vim / data/3307/my.cnf, as follows

[client] port = 3307socket = / data/3307/ mysql.sock [MySQL] no-auto- rehash [mysqld] user = mysqlport = 3307socket = / data/3307/mysql.sockbasedir = / usr/local/mysqldatadir = / data/3307/dataopen_files_limit = 1024read-onlyback_log = 600max_connections = 800max_connect_errors = 3000#table_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 pid-file = / data/3307/mysql.pidrelay-log = / data / 3307/relay-binrelay-log-info-file = / data/3307/relay-log.infobinlog_cache_size = 1Mmax_binlog_cache_size = 1Mmax_binlog_size = 2Mkey_buffer_size = 16Mread_rnd_buffer_size = 1Mbulk_insert_buffer_size = 1Mlower_case_table_names = 1skip-name-resolveslave-skip-errors = 1032 I set the server_id of port 3306 to 52. It cannot be 52#innodb_additional_mem_pool_size = 4Minnodb_buffer_pool_size = 32Minnodb_data_file_path = ibdata1:128M:autoextendinnodb_file_io_threads = 4#innodb_threads_concurrency = 8innodb_flush_log_at_trx_commit = 2innodb_log_buffer_size = 2Minnodb_log_file_size = 4Minnodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 90innodblocklockwait _ Timeout = 120innodb_file_per_table = 0 [mysqldump] quickmax_allowed_packet = 2m [MySQL _ safe] log-error=/data/3307/mysql_pcm3307.errpid-file=/data/3307/mysqld.pid

(3) create a startup file for multiple instances of MySQL, vim / data/3307/mysql, as follows

#! / bin/bash#initport=3307mysql_user= "root" mysql_pwd= "pcm123456" CmdPath= "/ usr/local/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 & # launch the command 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 # stop command fi} # restart functionfunction_restart_mysql () { Printf "Restart 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.sh {start | stop | restart}\ n" esac

(4) configure file permissions for multiple instances of MySQL

Chown-R mysql:mysql / data/find / data-name mysql.sh | xargs chmod 700 # modify permission is 700, because it contains the database password and requires high security

(5) initialize the database file of multiple instances of MySQL

/ usr/local/mysql/scripts/mysql_install_db-basedir=/usr/local/mysql/-datadir=/data/3307/data/-user=mysql

Looking at the data directory under directory 3307, you can see that there are a lot of extra data files.

(6) start the MySQL multi-instance database, and check to see that the port 3307 has been started.

/ data/3307/mysql.sh start

PS: my startup failed at first, and then I started normally after deleting the three files ibdata1,ib_logfile0,ib_logfile0 in the data directory. It should be because I initialized the MySQL service once before, and again the initialization generated files did not match the later configuration.

3. Log in to manage MySQL multi-instance database

(1) Log in to MySQL database

Mysql-S / data/3307/mysql.sock # needs to specify the sock file, otherwise the default 3306 is used.

(2) the password of MySQL's root is still empty and we need to modify it.

Mysqladmin-u root password 'pcm123456'-S / data/3307/mysql.sock

This is the end of the multi-instance introduction and configuration of MySQL. I hope the above content can be helpful to you and learn more knowledge. If you think the article is good, you can share it for more people to see.

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.