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

This article will explain in detail what the installation method of MySQL multi-instance is. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

Step 1. Preparatory work

Turn off the firewall; install MySQL depends on the libaio library; download, extract, rename MySQL execution files; new user groups and users and other preparatory work will not be discussed here.

The executable directory is / data/mysql57

Add instructions for users and groups

Groupadd mysqluseradd mysql-g mysql

Step 2. Add environment variabl

In the / etc/profile file, append PATH=$ {PATH}: / data/mysql57/bin/

After saving, if you need to take effect immediately, you can execute source / etc/profile.

Step 3. Create a directory and authorize

Execute under the data root directory

Mkdir-p mysql3306/data mysql3306/mysql_log mysql3306/tmp mysql3307/data mysql3307/mysql_log mysql3307/tmp mysqld_multi/log

This step is necessary to create a file that records the log.

Touch / data/mysql3306/mysql_log/mysql3306.errtouch / data/mysql3307/mysql_log/mysql3307.err

Give permissions to directories and files

Chown-R mysql.mysql mysql3* mysqld_multi

In this test case, mysql57 is copy from other services, not downloaded and decompressed directly, so the following two steps of authorization are added.

Chmod-R 755 / data/mysql57/binchmod-R 755 / data/mysql57/support-files

Step 4. Edit my.cnf

[client] host=localhostsocket = / tmp/mysql.sock default-character-set=utf8mb4#loose-local-infile= 0 [mysqld] user=mysqllog_bin_trust_function_creators=1secure_file_priv='/tmp'#server setting#sql_mode = "ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER NO_ENGINE_SUBSTITUTION "character-set-server=utf8mb4collation_server=utf8mb4_unicode_cilower_case_table_names = 0skip_name_resolve = 1#max_connect_errors = 1000max_connections = 2000thread cachesize256 thread read stack= 262144#back_log=80max_allowed_packet = 134217728event_scheduler = 1 local-infile=0#lower_case_table_names = 1explicit_defaults_for_timestamp = 1expire_logs_days = 7log_bin_trust_function_creators = 1####.frm/.ibd files qty relatedopen_files_limit=65535innodb_open _ files=65535table_open_cache=65535table_definition_cache=65535#### seesion buffer relatedread_buffer_size = 262144read_rnd_buffer_size = 524288sort_buffer_size = 8388608join_buffer_size = 8388608####memory table size tmp_table_size = 67108864max_heap_table_size=67108864####timeoutinteractive_timeout = 1800wait_timeout = 180 million connect_timeout=10#slow query # slow_query_log = 1log_slow_slave_statements = 1#log_queries_not_using _ indexes = 1log_throttle_queries_not_using_indexes = 10long_query_time = 1#min_examined_row_limit = 10000#innodb settings#innodb_buffer_pool_size = 10737418240innodb_buffer_pool_instances = 16 innodb_buffer_pool_dump_pct = 40innodb_lru_scan_depth = 2048innodb_page_cleaners = 16#innodb_purge_threads = 4innodb_sort_buffer_size = 67108864#innodb_file_per_table = 1#innodb _ flush_log_at_trx_commit = 1innodb_undo_log_truncate = 1innodb_undo_tablespaces = 3innodb _ max_undo_log_size = 2147483648innodb_purge_rseg_truncate_frequency = 128innodb_log_file_size = 1073741824innodb_log_files_in_group = 3innodb_log_buffer_size = 16777216innodb_flush_method = O_DIRECTinnodb_flush_neighbors = 0innodb_print_all_deadlocks = 1innodb_strict_mode = 1#innodb_lock_wait_timeout = 50innodb_io_capacity = 32768 innodbscientiio _ Capacity_max = 65536innodb_thread_concurrency = 32innodb_write_io_threads = 8innodb_read_io_threads = 8#replication settings#master_info_repository = TABLErelay_log_info_repository = TABLEgtid_mode = onenforce_gtid_consistency = 1binlog_gtid_simple_recovery=1relay_log_recovery = 1slave-parallel-type = LOGICAL_CLOCKslave-parallel-workers = 16slave_transaction_retries=128slave_preserve_commit_order=1log_slave_updates=1binlog_format = ROWlog_timestamps=systembinlog_rows_query _ log_events = 1 binlogarithmic rowboat imagefilled filled slave_skip_errors = ddl_exist_errors#semi sync replication settings#plugin_dir=/data/mysql/plugin/#plugin_load = "rpl_semi_sync_master=semisync_master.so Rpl_semi_sync_slave=semisync_slave.so "# rpl_semi_sync_master_enabled = 1#rpl_semi_sync_master_timeout = 5000#rpl_semi_sync_slave_enabled = 1 [mysqld_multi] mysqld = / data/mysql57/bin/mysqld_safemysqladmin = / data/mysql57/bin/mysqladminlog = / data/mysqld_multi/log/mysqld_ multi.log [mysqld3306] basedir = / data/mysql57mysqladmin=mysqladmindatadir=/data/mysql3306/dataport=3306 server_id=102473306socket= / tmp/mysql_3306.socktmpdir = / data/ Mysql3306/tmppid-file = / data/mysql3306/mysql_log/mysql3306.pidslow_query_log_file = / data/mysql3306/mysql_log/mysql3306_slow_new.log log-error = / data/mysql3306/mysql_log/mysql3306.errgeneral_log_file= / data/mysql3306/mysql_log/mysql3306.genloglog-bin = / data/mysql3306/mysql_log/mysql3306_binrelay_log = / data/mysql3306/mysql_log/relay3306.loginnodb_buffer_pool_size = 90Ginnodb_buffer_pool_instances = 8 [ Mysqld3307] basedir = / data/mysql57mysqladmin=mysqladmindatadir=/data/mysql3307/dataport=3307server_id=102473307socket= / tmp/mysql_3307.socktmpdir = / data/mysql3307/tmppid-file = / data/mysql3307/mysql_log/mysql3307.pidslow_query_log_file = / data/mysql3307/mysql_log/mysql3307_slow_new.loglog-error = / data/mysql3307/mysql_log/mysql3307.errgeneral_log_file= / data/mysql3307/mysql_log/mysql3307.genloglog-bin = / data/mysql3307/mysql_log/mysql3307_binrelay_log = / data / mysql3307/mysql_log/relay3307.loginnodb_buffer_pool_size = 90Ginnodb_buffer_pool_instances = 8 [mysqldump] quick

Step 5. Initialize an instance

Initialize the instance of port 3306 and pay attention to the resulting temporary password.

/ data/mysql57/bin/mysqld-defaults-file=/etc/my.cnf-initialize-user=mysql-basedir=/data/mysql57-datadir=/data/mysql3306/data

Initialize an instance of port 3307 and pay attention to the resulting temporary password.

/ data/mysql57/bin/mysqld-defaults-file=/etc/my.cnf-initialize-user=mysql-basedir=/data/mysql57-datadir=/data/mysql3307/data

Step 6. Copy the generated mysqld_multi and add it to boot

Cp / data/mysql57/support-files/mysqld_multi.server / etc/init.d/mysqld_multichkconfig-- add mysqld_multi

Step 7. Enable multi-instance Mysqld

Open all instances

Mysqld_multi start

Check whether it is enabled (view the status of all instances)

Mysqld_multi report

Open the specified instance

Open [mysqld3306] in / etc/my.cnf, where the number after mysqld is the label, for example, the 3306 tag

Mysqld_multi start 3306

Open the 3307 tag instance as well

Mysqld_multi start 3307

(note: there is no mention here of shutting down the instance through the mysqld_multi stop command, why not, because the command is invalid. The operation of Step 9 will make it effective)

Step 8. Log in to the instance for the first time and change the password of the root account

You need to specify the socket parameter for multi-instance login

3306 instances of this test are logged in as follows:

Mysql-S / tmp/mysql_3306.sock-uroot-- port 3306-p

Step 9. Grant permission to shut down the instance through mysqld_multi stop.

To close the instance, you need to configure the root user and password, and modify the / etc/my.cnf file.

Add at [client] location

User=rootpassword= password

Because the account password needs to be kept in the file, there is a security risk. Whether it should be set up in this way or not in the actual environment depends on the specific situation and safety requirements.

After the operation of the above steps, this server successfully installed two MySQL instances, one with a Port of 3306 and the other with a Port of 3307

On the MySQL multi-instance installation method is shared here, I hope that the above content can be of some help to you, can 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.