MySQL Multi-instance installation

MySQL Multi-instance installation
Install multiple MySQL instances on a single server
Take full advantage of hardware resources
Implemented through Mysqld_multi

(1) Stop the previous single instance database
Mysqladmin-uroot-p shutdown
The first step is no control.

(2) Building multi-instance related data Catalog

Mkdir-p/data1
Mkdir-p/data2
Mkdir-p/data3
Mkdir-p/DATA4

Chown-r mysql.mysql/data1
Chown-r Mysql.mysql/data2
Chown-r mysql.mysql/data3
Chown-r MYSQL.MYSQL/DATA4

(3) Initializing each instance (mysql5.6x version)

scripts/mysql_install_db--user=mysql--datadir=/data1
scripts/mysql_install_db--user=mysql--datadir=/data2
scripts/mysql_install_db--user=mysql--datadir=/data3
scripts/mysql_install_db--user=mysql--DATADIR=/DATA4

Cp/usr/local/mysql/support-files/mysqld_multi.server/etc/init.d/mysqld_multid
Chkconfig Mysqld_multid on

(4) Viewing and starting with Mysqld_multi related commands

[[email protected] mysql]# mysqld_multi  reportReporting MySQL serversMySQL server from group: mysqld1 is not runningMySQL server from group: mysqld2 is not runningMySQL server from group: mysqld3 is not runningMySQL server from group: mysqld3 is not running

Start MySQL instance 1234 separately
[[email protected] mysql]# Mysqld_multi start 1
[[email protected] mysql]# Mysqld_multi start 2
[[email protected] mysql]# Mysqld_multi start 3
[[email protected] mysql]# Mysqld_multi start 4

or [[email protected] mysql]# Mysqld_multi start

[[email protected] mysql]# mysqld_multi reportReporting MySQL serversMySQL server from group: mysqld1 is runningMySQL server from group: mysqld2 is runningMySQL server from group: mysqld3 is runningMySQL server from group: mysqld4 is running

  [[email protected] mysql]# netstat-anlp | grep mysqltcp 0 0::: 3307:::*                        LISTEN 29467/mysqld TCP 0 0::: 3308:::* LISTEN 29469/mysqld TCP 0 0::: 3309:::* LIST EN 29474/mysqld TCP 0 0:: 3310:::* LISTEN 25447/ MYSQLD UNIX 2 [ACC] STREAM LISTENING 35931 25447/mysqld/tmp/mysql.sock4unix 2 [ ACC] Stream LISTENING 38712 29467/mysqld/tmp/mysql.sock1unix 2 [ACC] Stream Listenin        G 38703 29469/mysqld/tmp/mysql.sock2unix 2 [ACC] STREAM LISTENING 38724 29474/mysqld /tmp/mysql.sock3  

Enter instances separately
Mysql-uroot-p-s/tmp/mysql.sock1
Mysql-uroot-p-s/tmp/mysql.sock2
Mysql-uroot-p-s/tmp/mysql.sock3
Mysql-uroot-p-s/tmp/mysql.sock4

(5) Federated Test

3307 preparing the relevant data

Mysql> Show variables like '%port% '; +---------------------+-------+| variable_name | Value |+---------------------+-------+| Innodb_support_xa | On | | Large_files_support | On | | Port | 3307 | |       Report_host | ||       Report_password | || Report_port | 3307 | |       Report_user | |+---------------------+-------+7 rows in Set (0.00 sec) mysql> Create database burn; Query OK, 1 row affected (5.01 sec) mysql> use burn;database changedmysql> mysql> CREATE TABLE book (ID int. not NUL L auto_increment,name varchar () not null,primary key (ID)); Query OK, 0 rows affected (0.04 sec) mysql> insert into book values (1, "中文版"); Query OK, 1 row affected (0.06 sec) mysql> select * from book;+----+---------+| ID |  Name |+----+---------+| 1 | 中文版 |+----+---------+1 row in Set (0.00 sec) mysql> create user ' burn ' @ ' 127.0.0.1 ' identified by ' 123 '; Query OK, 0 rows affected (0.11 sec) mysql> Grant Select on burn.* to' Burn ' @ ' 127.0.0.1 '; Query OK, 0 rows affected (0.03 sec) mysql> Show grants for ' Burn ' @ ' 127.0.0.1 '; +-------------------------------------- -----------------------------------------------------------------------+| Grants for [email protected] |+---- ---------------------------------------------------------------------------------------------------------+| GRANT USAGE on *. Burn ' @ ' 127.0.0.1 ' identified by PASSWORD ' *23ae809ddacaf96af0fd78ed04b6a265e05aa257 ' | | GRANT SELECT on ' burn '. * to ' burn ' @ ' 127.0.0.1 ' |+------------ -------------------------------------------------------------------------------------------------+2 rows in Set ( 0.00 sec)

3308 Test federated Test

Mysql> Show variables like '%port% ';
+---------------------+-------+
| variable_name | Value |
+---------------------+-------+
| Innodb_support_xa | On |
| Large_files_support | On |
| Port | 3308 |
| Report_host | |
| Report_password | |
| Report_port | 3308 |
| Report_user | |
+---------------------+-------+
7 Rows in Set (0.00 sec)

Mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+-------------- +------+------------+
| Engine | Support | Comment | Transactions | XA | savepoints |
+--------------------+---------+----------------------------------------------------------------+-------------- +------+------------+
| InnoDB | DEFAULT | Supports transactions, Row-level locking, and foreign keys | YES | YES | YES |
| CSV | YES | CSV Storage Engine | NO | NO | NO |
| MyISAM | YES | MyISAM Storage Engine | NO | NO | NO |
| Blackhole | YES | /dev/null Storage Engine (anything you write to it disappears) | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| Federated | NO | Federated MySQL Storage Engine | NULL | NULL | NULL |
| ARCHIVE | YES | Archive Storage Engine | NO | NO | NO |
| Mrg_myisam | YES | Collection of identical MyISAM tables | NO | NO | NO |
| Performance_schema | YES | Performance Schema | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------+-------------- +------+------------+
9 Rows in Set (0.00 sec)

The federated feature is not turned on
Add in/ETC/MY.CNF
[Mysqld2]
Federated

Mysqld_multi Stop 2
Mysqld_multi Start 2

Mysql> Show engines;+--------------------+---------+--------------------------------------------------------- -------+--------------+------+------------+| Engine | Support | Comment | Transactions | XA | savepoints |+--------------------+---------+----------------------------------------------------------------+--- -----------+------+------------+| InnoDB | DEFAULT | Supports transactions, Row-level locking, and foreign keys | YES | YES | YES | | CSV | YES | CSV Storage Engine | NO | NO | NO | | MyISAM | YES | MyISAM Storage Engine | NO | NO | NO | | Blackhole | YES | /dev/null Storage Engine (anything you write to it disappears) | NO | NO | NO | | MEMORY | YES | Hash based, stored In memory, useful for temporary tables | NO | NO | NO | | Federated | YES | Federated MySQL Storage Engine | NO | NO | NO | | ARCHIVE | YES | Archive Storage Engine | NO | NO | NO | | Mrg_myisam | YES | Collection of identical MyISAM tables | NO | NO | NO | | Performance_schema | YES |   Performance Schema

Indicates that it has been opened

Final Test

mysql> create database federated_test;Query OK, 1 row affected (5.01 sec)mysql> use federated_test;Database changedmysql> create table feterated_book(    -> id int not null auto_increment,    -> name varchar(10) not null,primary key(id)    -> )engine=federated    -> connection=‘mysql://burn:[email protected]:3307/burn/book‘;Query OK, 0 rows affected (0.11 sec)mysql> select * from feterated_book;+----+---------+| id | name    |+----+---------+|  1 | english |+----+---------+1 row in set (0.00 sec)

Get!!!!!!

Attachment multi-instance configuration file

[mysqld]federated####### #basic settings####### #server-id = one port = 3306user = Mysqlbind_address = 10.166.224.32# autocommit = 0character_set_server=utf8mb4skip_name_resolve = 1max_connections = 800max_connect_errors = 1000datadir =/ Data/mysql_data#datadir =/usr/local/mysql-5.6.27-linux-glibc2.5-x86_64/data Transaction_isolation = Read-committedexplicit_defaults_for_timestamp = 1join_buffer_size = 134217728tmp_table_size = 67108864tmpdir =/tmpmax _allowed_packet = 16777216sql_mode = "Strict_trans_tables,no_engine_substitution,no_zero_date,no_zero_in_date, Error_for_division_by_zero,no_auto_create_user "interactive_timeout = 1800wait_timeout = 1800read_buffer_size = 16777216read_rnd_buffer_size = 33554432sort_buffer_size = 33554432####### #log settings####### #log_error = Error.logslow_query_log = 1slow_query_log_file = Slow.loglog_queries_not_using_indexes = 1log_slow_admin_statements = 1log_slow_slave_statements = 1log_throttle_queries_not_using_indexes = 10expire_logs_days = 90long_queRy_time = 2min_examined_row_limit = 100####### #replication settings####### #master_info_repository = Tablerelay_log_ Info_repository = Table#log_bin = Bin.logsync_binlog = 1#gtid_mode = On#enforce_gtid_consistency = 1#log_slave_ Updatesbinlog_format = Row Relay_log = Relay.logrelay_log_recovery = 1binlog_gtid_simple_recovery = 1slave_skip_errors = ddl_exist_errors####### #innodb settings####### #innodb_page_size = 8192innodb_buffer_pool_size = 2ginnodb_buffer_ pool_instances = 8innodb_buffer_pool_load_at_startup = 1innodb_buffer_pool_dump_at_shutdown = 1innodb_lru_scan_depth = 2000innodb_lock_wait_timeout = 5innodb_io_capacity = 4000innodb_io_capacity_max = 8000innodb_flush_method = O_ Directinnodb_file_format = Barracudainnodb_file_format_max = Barracuda#innodb_log_group_home_dir =/redolog/#innodb_ Undo_directory =/undolog/innodb_undo_logs = 128innodb_undo_tablespaces = 3innodb_flush_neighbors = 1innodb_log_file_ Size = 128minnodb_log_buffer_size = 16777216innodb_purge_threads = 1innodb_largE_prefix = 1innodb_thread_concurrency = 64innodb_print_all_deadlocks = 1innodb_strict_mode = 1innodb_sort_buffer_size = 67108864 ####### #semi Sync replication settings####### #plugin_dir =/usr/local/mysql/lib/pluginplugin_load = "Rpl_semi _sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so "loose_rpl_semi_sync_master_enabled = 1loose_rpl_semi_sync_slave_enabled = 1loose_rpl_semi_sync_master_timeout = 5000[mysqld-5.7]innodb_buffer_pool_dump _pct = 40innodb_page_cleaners = 4innodb_purge_threads = 4innodb_undo_log_truncate = 1innodb_max_undo_log_size = 2Ginnodb _purge_rseg_truncate_frequency = 128binlog_gtid_simple_recovery=1log_timestamps=systemtransaction_write_set_ Extraction=murmur32#binlog_checksum=none # only for group Replication#group replication Setting#plugin_load=group_ Replication.so#loose_group_replication_group_name=e5720089-6b00-11e5-b248-fa163e30f9a2#loose_group_replication _start_on_boot=1#loose_group_replication_recovery_user= ' Rpl_user ' #loose_group_replication_recovery_password= ' rpl_pass ' show_compatibility_56=on[mysqld_multi]mysqld = Mysqld_safelog=/usr/local/mysql/mysql_ MULTI.LOG[MYSQLD1]SERVER-ID=111BASEDIR=/USR/LOCAL/MYSQLDATADIR=/DATA1PORT=3307SOCKET=/TMP/MYSQL.SOCK1[MYSQLD2] Server-id=222basedir=/usr/local/mysqldatadir=/data2port=3308socket=/tmp/mysql.sock2[mysqld3]server-id= 333basedir=/usr/local/mysql56datadir=/data3port=3309socket=/tmp/mysql.sock3

MySQL multi-instance installation