MySQL5.6 Multi-Instance deployment

Whether it is due to the budget, or the leadership requirements, multi-instance installation is also a DBA must master the technology, her start-stop and login mode and the single-instance installation database slightly different, this article records how to complete the MySQL5.6 multi-instance deployment.

First, let's look at the distinction between my.cnf and single-instance:

[Email protected] scripts]#
Cat/etc/my.cnf
[Client]
#port = 3306
#socket =/tmp/mysql.sock
#default-character-set = UTF8

[MySQL]
#default-character-set = UTF8

[mysqld3306]
Port = 3306
Basedir =/usr/local/mysql
DataDir =/data/mysql_3306
Socket =/tmp/mysql_3306.sock
Slow_query_log_file =/data/mysql_3306/slow.log
Log-error =/data/mysql_3306/error.log
Log-bin =/data/mysql_3306/mysql-bin
Sync_binlog = 1
Binlog_format = row
Transaction_isolation = Repeatable-read
Innodb_buffer_pool_size = 100m

[mysqld3308]
Port = 3308
Basedir =/usr/local/mysql
DataDir =/data/mysql_3308
Socket =/tmp/mysql_3308.sock
Slow_query_log = 1
Slow_query_log_file =/data/mysql_3308/slow.log
Log-error =/data/mysql_3308/error.log
Long_query_time = 1
Log-bin =/data/mysql_3308/mysql-bin
Sync_binlog = 1
Binlog_cache_size = 4M
Default-storage-engine = InnoDB
Binlog_format = row
Transaction_isolation = Repeatable-read
Innodb_buffer_pool_size = 100m

[Mysqld_multi]
Mysqld=/usr/local/mysql/bin/mysqld_safe
Mysqladmin=/usr/local/mysql/bin/mysqladmin


[Mysqldump]
Quick
Max_allowed_packet = 32M

As can be seen, multi-instance of the MY.CNF is actually as shown above, in order to demonstrate the experimental environment, Innodb_buffer_pool_size only opened 100m, the real production library of multi-instance deployment of the parameter to be larger, two instances of the value of the parameter reaches the memory 50%-80% All can.

The following begins to initialize our database
Start by creating our data Catalog
[[Email protected] ~] #mkdir-P/data/mysql_3306
[[Email protected] ~] #mkdir-P/data/mysql_3308
[[Email protected] ~] #echo "Export path= $PATH:/usr/local/mysql/bin:/usr/local/mysql/lib" >>/etc/profile

Enter the MySQL scripts folder to initialize the database, here we initialize the 3306 port database
[Email protected] scripts]#./mysql_install_db--basedir=/usr/local/mysql--datadir=/data/mysql_3306-- DEFAULTS-FILE=/ETC/MY.CNF--user=mysql

Here we initialize the 3308 port database
[Email protected] scripts]#/mysql_install_db--basedir=/usr/local/mysql--datadir=/data/mysql_3308-- DEFAULTS-FILE=/ETC/MY.CNF--user=mysql

After the initialization is complete, we can start and stop the database, and the single instance is different, multi-instance uses Mysqld_multi to start and stop the database
[Email protected] bin]#/mysqld_multi--defaults-file=/etc/my.cnf--user=root--password=manager start 3306,3308

You can use the Mysqld_multi Report command to detect the health of multiple instances
1234 [[Email protected] Bin]#./mysqld_multi report
Reporting MySQL Servers
MySQL server from group:mysqld3306 is running
MySQL server from group:mysqld3308 is running

The login method and the single instance are roughly the same, but due to the existence of multiple instances, we need to specify a different port number
[Email protected] bin]# mysql-uroot-p-p3306-h 192.168.1.48
Enter Password:
Welcome to the MySQL Monitor. Commands End With; or \g.
Your MySQL Connection ID is 6 server version:5.6.16-log mysql Community server (GPL)

Copyright (c) 2000,
Oracle and/or its affiliates. All rights reserved.

Oracle is a
Registered trademark of Oracle Corporation and/or its affiliates. Other names trademarks of their respective owners.

Type ' help; ' or ' \h '
For help. Type ' \c ' to clear the current input statement.


mysql> show databases;
+--------------------+
| Database |
+--------------------+
| Information_schema
|
| 3306db |
| MySQL |
| Performance_schema
|
| Test |
+--------------------+
5 rows in Set (0.00 sec)

Of course, using the socket file login is also possible
[[email protected] bin] #mysql-uroot-p-s/data/mysql_3306/mysql_3306.sock
Enter Password:
Welcome to the MySQL Monitor. Commands End With; or \g.
Your MySQL Connection ID is 7 server version:5.6.16-log mysql Community server (GPL)

Copyright (c) 2000,
Oracle and/or its affiliates. All rights reserved.

Oracle is a
Registered trademark of Oracle Corporation and/or its affiliates. Other names trademarks of their respective owners.

Type ' help; ' or ' \h '
For help. Type ' \c ' to clear the current input statement.


mysql> show databases;
+--------------------+
| Database |
+--------------------+
| Information_schema
|
| 3306db |
| MySQL |
| Performance_schema
|
| Test |
+--------------------+
5 rows in Set (0.00 sec)

Here is the login 3308 port database
[[email protected] bin] #mysql-uroot-p-p3308-h 192.168.1.48
Enter Password:
Welcome to the MySQL Monitor. Commands End With; or \g.
Your MySQL Connection ID is 8 server version:5.6.16-log mysql Community server (GPL)

Copyright (c) 2000,
Oracle and/or its affiliates. All rights reserved.

Oracle is a
Registered trademark of Oracle Corporation and/or its affiliates. Other names trademarks of their respective owners.

Type ' help; ' or ' \h '
For help. Type ' \c ' to clear the current input statement.


Type ' help; ' or ' \h '
For help. Type ' \c ' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| Information_schema
|
| 3308db |
| MySQL |
| Performance_schema
|
| Test |
+--------------------+
5 rows in Set (0.00SEC)

Mysql> quit
Bye

Log in to the database using a 3308 port socket file
[[email protected] bin] #mysql-uroot-p-s/data/mysql_3308/mysql_3308.sock
Enter Password:
Welcome to the MySQL Monitor. Commands End With; or \g.
Your MySQL Connection ID is 9 server version:5.6.16-log mysql Community server (GPL)

Copyright (c) 2000,
Oracle and/or its affiliates. All rights reserved.

Oracle is a
Registered trademark of Oracle Corporation and/or its affiliates. Other names trademarks of their respective owners.

Type ' help; ' or ' \h '
For help. Type ' \c ' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| Information_schema
|
| 3308db |
| MySQL |
| Performance_schema
|
| Test |
+--------------------+
5 rows in Set (0.00SEC)

At this point, MySQL5.6 multi-instance deployment is complete.

MySQL5.6 Multi-Instance deployment