Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

MySQL5.6 multi-instance deployment

2025-04-02 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

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

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.

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.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report