MySQL5.6 multi-instance deployment

Whether forced by the budget or required by the leader, multi-instance installation is also a technology that DBA must master. Her start, stop and login method is slightly different from that of single-instance installation database. This document records how to complete MySQL5.6 multi-instance deployment.

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

[root@HE1 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

You can see that the multi-instance my.cnf is actually shown above. In order to demonstrate the experimental environment, the innodb_buffer_pool_size is only 100m open, and the parameter should be turned on for multi-instance deployment in the real production library. For both instances, the value of this parameter can reach 50% of memory. 80% of the memory.

Let's initialize our database.

First create our data directory

[root@HE1] # mkdir-p / data/mysql_3306

[root@HE1] # mkdir-p / data/mysql_3308

[root@HE1 ~] # echo "export PATH=$PATH:/usr/local/mysql/bin:/usr/local/mysql/lib" > > / etc/profile

Go to the scripts folder of mysql to initialize the database. Here we initialize the database on port 3306.

[root@HE1 scripts] # / mysql_install_db-- basedir=/usr/local/mysql-- datadir=/data/mysql_3306-- defaults-file=/etc/my.cnf-- user=mysql

Here we initialize the port 3308 database

[root@HE1 scripts] # / mysql_install_db-- basedir=/usr/local/mysql-- datadir=/data/mysql_3308-- defaults-file=/etc/my.cnf-- user=mysql

After initialization, we can start and stop the database. Unlike a single instance, multiple instances use mysqld_multi to start and stop the database.

[root@HE1 bin] #. / mysqld_multi-- defaults-file=/etc/my.cnf-- user=root-- password=MANAGER start 3306

You can use the report command of mysqld_multi to detect the health of multiple instances

1234 [root@HE1 bin] #. / mysqld_multi report

Reporting MySQL servers

MySQL server from group: mysqld3306 is running

MySQL server from group: mysqld3308 is running

The login method is basically the same as that of a single instance, but due to the existence of multiple instances, we need to specify different port numbers.

[root@HE1 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

2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a

Registered trademark of Oracle Corporation and/or its affiliates. Other names may be 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, it is also possible to log in using the socket file

[root@HE1 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

2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a

Registered trademark of Oracle Corporation and/or its affiliates. Other names may be 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)

This is the login to port 3308 database

[root@HE1 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

2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a

Registered trademark of Oracle Corporation and/or its affiliates. Other names may be 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 the socket file on port 3308

[root@HE1 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

2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a

Registered trademark of Oracle Corporation and/or its affiliates. Other names may be 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, the MySQL5.6 multi-instance deployment is complete.