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

Due to the use of source code to install MySQL5.7.28 multi-instance, there will be problems when importing the database, so re-study the method of using mysql_multi to manage multi-instance. After the verification of the test environment, in all aspects of use, especially in backup and restore, did not report the problem of MySQL5.7.28 multi-instance, stepped on a lot of holes, here I will share my deployment process, if anything goes wrong Please also give a lot of correction and guidance, thank you!

Reference article: Centos7.5 installation MySQL 5.7.24 binary package deployment

Https://www.jb51.net/article/151867.htm

This document installs multiple instances of mysql directly from Chapter 2.7.The specific deployment process is as follows:

2.7 install multiple instances of mysql

2.7.1. Create a software installation directory (please modify the deployment path according to the actual)

[root@~] # mkdir-pv / data/mysql/ {3306 3307} [root@~] # mkdir-v / data/mysql/3306/ {logs,data,binlog} [root@~] # mkdir-v / data/mysql/3307/ {logs,data,binlog}

2.7.2. Download the MySQL installation package

[root@~] # cd / opt [root@~] # wget-c https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz[root@~]# tar zxvf mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz [root@~] # mv mysql-5.7.28-linux-glibc2.12-x86_64 / usr/local/mysql [root @ ~] # chown-R mysql:mysql / usr/local/mysql [root@~] # chown-R mysql:mysql / data

2.7.3. MySQL parameter configuration

 configuration my.cnf parameter file

(1) the values of server_id=3306 and server_id=3307 should be configured according to the actual configuration. Note that the id value configured cannot conflict with the values configured by other instances in the LAN.

(2) max_connections=1000 configures the maximum number of connections to the MySQL database, which is configured according to the actual needs, and other parameters are modified or added according to the actual needs.

Add

(3) the full contents of the configuration file are as follows

[root@~] # vim / etc/ my.cnf [mysqld _ multi] mysqld = / usr/local/mysql/bin/mysqldmysqladmin = / usr/local/mysql/bin/mysqladminlog = / data/mysql/mysqld_multi.log # user=root#pass= [MySQL] prompt= "\ u@jsshapp\ R:\ m:\ s [\ d] >" no-auto- Rehash [mysqld3306] user= mysqlport = 3306symbolic-links = 0#basedir = / usr/datadir = / data/mysql/3306/datasocket = / data/mysql/3306/ Mysql3306.sockpid-file = / data/mysql/3306/mysqld3306.pidserver_id = 3306character_set_server = utf8max_connections = 1000skip_name_resolve = 1open_files_limit = 65536thread_cache_size = 64table_open_cache = 4096table_definition_cache = 1024table_open_cache_instances = 64max_prepared_stmt_count = 1048576explicit_defaults_for_timestamp = truelog_timestamps = systembinlog_format = rowlog_bin = / data/mysql/3306/binlog/mysql-binbinlog_rows_query_log_events = onexpire_logs_ Days = 7binlog_cache_size = 4Mmax_binlog_cache_size = 2Gmax_binlog_size = 1Gsync_binlog = 1log_bin_trust_function_creators = 1slow_query_log = onslow_query_log_file = / data/mysql/3306/data/slow.loglog-error = / data/mysql/3306/logs/error.loglog_queries_not_using_indexes = onlong_query_time = 1.000000gtid_mode = onenforce_gtid_consistency = ondefault_storage_engine = innodbdefault_tmp_storage_engine = innodbinnodb_ Data_file_path = ibdata1:12M:autoextend:max:2000Minnodb_temp_data_file_path = ibtmp1:12M:autoextend:max:2000Minnodb_buffer_pool_filename = ib_buffer_poolinnodb_log_files_in_group = 3innodb_log_file_size = 512Minnodb_online_alter_log_max_size = 1024Minnodb_open_files = 4096innodb_page_size = 16kinnodb_thread_concurrency = 0innodb_read_io_threads = 4innodb_write_io_threads = 4innodb_purge_threads = 4innodb_page_cleaners = 4innodb_print_all_deadlocks = oninnodb_lock_wait_timeout = 20innodb_spin_wait_delay = 128innodb_autoinc_lock_mode = 2innodb_io_capacity = 200innodb_io_capacity_max = 2000#innodb_flush_neighbors = innodb_log_buffer_size = 8Minnodb_flush_log_at_timeout = 1innodb_flush_log_at_trx_commit = 2innodb_buffer_pool_size = 1024Minnodb_buffer_pool_instances = 4autocommit = 1innodb_buffer_pool_dump_pct = 25innodbbufferbread pooldump _ At_shutdown = ONinnodb_buffer_pool_load_at_startup = on [mysqld3307] user = mysqlport = 3307symbolic-links = 0lower_case_table_names = 1#basedir = / usr/datadir = / data/mysql/3307/datasocket = / data/mysql/3307/mysql3307.sockpid-file = / data/mysql/3307/mysqld3307.pidserver_id = 3307character_set_server = utf8max_connections = 1000skip_name_resolve = 1open_files_limit = 65536thread_cache_size = 64table_open_cache = 4096table_definition_cache = 1024table_open_cache_instances = 64max_prepared_stmt_count = 1048576explicit_defaults_for_timestamp = truelog_timestamps = systembinlog_format = rowlog_bin = / data/mysql/3307/binlog/mysql-binbinlog_rows_query_log_events = onexpire_logs_days = 7binlog_cache_size = 4Mmax_binlog_cache_size = 2Gmax_binlog_size = 1Gsync_binlog = 1slow_query_log = onslow_query_log_file = / data/mysql/3307/data/slow.loglog-error = / data/mysql / 3307/logs/error.loglog_queries_not_using_indexes = onlong_query_time = 1.000000gtid_mode = onenforce_gtid_consistency = ondefault_storage_engine = innodbdefault_tmp_storage_engine = innodbinnodb_data_file_path = ibdata1:12M:autoextend:max:2000Minnodb_temp_data_file_path = ibtmp1:12M:autoextend:max:2000Minnodb_buffer_pool_filename = ib_buffer_poolinnodb_log_files_in_group = 3innodb_log_file_size = 512Minnodb_online_alter_log _ max_size = 1024Minnodb_open_files = 4096innodb_page_size = 16kinnodb_thread_concurrency = 0innodb_read_io_threads = 4innodb_write_io_threads = 4innodb_purge_threads = 4innodb_page_cleaners = 4innodb_print_all_deadlocks = oninnodb_lock_wait_timeout = 20innodb_spin_wait_delay = 128innodb_autoinc_lock_mode = 2innodb_io_capacity = 200innodb_io_capacity_max = 2000#innodb_flush_neighbors = innodb_log_buffer_size = 8Minnodb_flush_log_at _ timeout = 1innodb_flush_log_at_trx_commit = 2innodb_buffer_pool_size = 1024Minnodb_buffer_pool_instances = 4autocommit = 1innodb_buffer_pool_dump_pct = 25innodb_buffer_pool_dump_at_shutdown = ONinnodb_buffer_pool_load_at_startup = on [mysqldump] quickmax_allowed_packet = 32m

2.7.4. Configure MySQL environment variables

[root@~] # echo 'export PATH=/usr/local/mysql/bin:$PATH' > / etc/profile [root@~] # tail-1 / etc/profile [root@~] # source / etc/profile

2.7.5. Testone instance initialization

Initialization of  operation instance

[root@~] # mysqld-- defaults-file=/etc/my.cnf-- initialize-- basedir=/usr/local/mysql/-- user=mysql-- datadir=/data/mysql/3306/data/ > / tmp/3306.log 2 > & 1 [root@~] # tail-100f / tmp/3306.log--- use the tail command to view the initialization log, and the following indicates that the initialization is complete (where # 5 initialization xYW+ flush privileges;mysql > initialization)

 verifies the unified password of testone root users (the normal output of the interface indicates that the information_schema content is normal)

[root@~] # mysql-uroot-p-S / data/mysql/3306/mysql3306.sock-e "show databases;" | grep information_schemaEnter password:

 modifies the my.cnf configuration file and adds the modified unified password to the configuration file

[root@~] # sed-I "s @ ^ # user=root@user=root@g" / etc/my.cnf [root@~] # sed-I "s @ ^ # pass=@pass= password @ g" / etc/my.cnf [root@~] # cat / etc/my.cnf | grep pass=-use the cat command to check whether the output of the pass field of the configuration file is consistent

 stops testone instance

[root@~] # mysqld_multi stop 3306 [root@~] # netstat-tnlp | grep 3306-an empty input result indicates that the service stops normally

2.7.6. Testtwo instance initialization

Initialization of  operation instance

[root@~] # mysqld-- defaults-file=/etc/my.cnf-- initialize-- basedir=/usr/local/mysql/-- user=mysql-- datadir=/data/mysql/3307/data/ > / tmp/3307.log 2 > & 1 [root@~] # tail-100f / tmp/3307.log--- use the tail command to view the initialization log, and the following indicates that the initialization is complete (where-initialization > t Ye) Ay6=I is the temporary password of the root user) A temporary password is generated for root@localhost:-pn > tten Ye) Ay6=I  generates ssl file [root@~] # mysql_ssl_rsa_setup-- user=mysql-- basedir=/usr/local/mysql-- datadir=/data/mysql/3307/data

 starts the testwo instance

[root@~] # mysqld_multi start 3307

-use the tail command to view the startup log. If it appears, it means the startup is successful.

[root@~] # tail-100f / data/mysql/3307/logs/error.logVersion: '5.7.28 socket: / data/mysql/3307/mysql3307.sock' port: 3307 MySQL Community Server (GPL)

 modifies root password

[root@~] # less / tmp/3307.log | grep'a temporary password' [root@~] # mysql-uroot-p-S / data/mysql/3307/mysql3307.sockEnter password:mysql > alter user 'root'@'localhost' identified by' Unified password; mysql > flush privileges;mysql > exit

 verifies the unified password of testtwo root users (the normal output of the interface indicates that the information_schema content is normal)

[root@~] # mysql-uroot-p-S / data/mysql/3307/mysql3307.sock-e "show databases;" | grep information_schema

Enter password:

 stops testtwo instance

[root@~] # mysqld_multi stop 3307 [root@~] # netstat-tnlp | grep 3307-an empty input result indicates that the service stops normally

2.7.7. Mysqld_multi multi-instance management command

 starts all instances

[root@~] # mysqld_multi start

 stops a single instance

[root@~] # mysqld_multi stop 3306 [root@~] # mysqld_multi stop 3307

 starts a single instance

[root@~] # mysqld_multi start 3306 [root@~] # mysqld_multi start 3307

 to view the status of all instances (is running)

[root@~] # mysqld_multi reportReporting MySQL serversMySQL server from group: mysqld3306 is runningMySQL server from group: mysqld3307 is running

 views the status of a single instance

[root@~] # mysqld_multi report 3306Reporting MySQL serversMySQL server from group: mysqld3306 is running [root@~] # mysqld_multi report 3307Reporting MySQL serversMySQL server from group: mysqld3307 is running

 stops all instances

[root@~] # mysqld_multi stop

2.7.8. Data import

(1) upload the database script (test.sql, testtwo.sql) files (whose names are operated according to the actual situation) to the / data/ path

(2) use the command to confirm that the two instances are already running

[root@~] # mysqld_multi reportReporting MySQL serversMySQL server from group: mysqld3306 is runningMySQL server from group: mysqld3307 is running

(3) go to testone's mysql database to create testone database instance, user name and password, and import data

[root@~] # cd / data/ [root@~] # mysql-uroot-p-S / data/mysql/3306/mysql3306.sockEnter password: mysql > create database testone default character set utf8 collate utf8_bin;mysql > grant select,insert,update,delete,create,alter,execute on testone.* to 'testone'@'%' identified by' password; mysql > flush privileges;mysql > exit [root@~] # mysql-uroot-p-S / data/mysql/3306/mysql3306.sock testone

Create database testtwo default character set utf8 collate utf8_bin;mysql > grant select,insert,update,delete,create,alter,execute on testtwo.* to 'testtwo'@'%' identified by' password; mysql > flush privileges;mysql > exit [root@~] # mysql-uroot-p-S / data/mysql/3307/mysql3307.sock testtwo < / data/testtwo.sql

2.7.9. Firewall configuration

 adds open ports according to actual requirements

[root@~] # firewall-cmd-zone=public-add-port=3306/tcp-permanent [root@~] # firewall-cmd-zone=public-add-port=3307/tcp-permanent

 reload

[root@~] # firewall-cmd-- reload

Summary

The above is introduced to you by the editor that CentOS7.5 uses mysql_multi to install multiple instances of MySQL5.7.28. I hope it will be helpful to you. If you have any questions, please leave me a message and the editor will reply to you in time. Thank you very much for your support to the website! If you think this article is helpful to you, you are welcome to reprint it, please indicate the source, thank you!

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.