MySQL Single-host multi-instance solution deployment

The single-host multi-instance MySQL solution is to run multiple MySQL database instances on one physical server.

Advantages of Single-host and multi-instance MySQL:
1. save costs and reduce the number of servers, IP addresses, and cabinets. The overall operation cost is lower than the single-host single-instance solution.
2. Improve the utilization and use the NUMA feature to bind the CPU to the MySQL instance.
3. Improve user experience, greatly improve IO response time, and help improve user experience.

Implementation of MySQL multi-instance deployment

1. MySQL multi-instance directory planning (data directory configuration file directory)

Instance 1
Data path:
/Home/mysql/data
Configuration File Path:
/Usr/local/mysql5/etc/lyq314.cnf
Instance 2
Data path:
/Home/mysql/data_log
Configuration File Path:
/Usr/local/mysql5/etc/lyq314_log.cnf
 
2. Modify the MySQL multi-instance configuration file

Instance 1 configuration file modification (no need to modify)
 
Vim/usr/local/mysql5/etc/lyq314.cnf
 
18 [client]
19 # password = your_password
20 port = 3306
21 socket =/tmp/mysql. sock
22
23 # Here follows entries for some specific programs
24
25 # The MySQL server
26 [mysqld]
27port = 3306
28 socket =/tmp/mysql. sock
29 pid-file = mysql. pid
30 skip-external-locking
31. skip-name-resolve
 
Modify instance 2 configuration file (modify port and socket)

Vim/usr/local/mysql5/etc/lyq314_log.cnf
18 [client]
19 # password = your_password
20 port = 3307
21 socket =/tmp/mysql_log.sock
22
23 # Here follows entries for some specific programs
24
25 # The MySQL server
26 [mysqld]
27port = 3307
28 socket =/tmp/mysql_log.sock
29 pid-file = mysql. pid
30 skip-external-locking
31. skip-name-resolve

3. MySQL multi-instance startup

Start instance 1

[Root @ lyq314 ~] #/Usr/local/mysql5/bin/mysqld_safe -- defaults-file =/usr/local/mysql5/etc/lyq314.cnf -- datadir =/home/mysql/data -- user = mysql &
[Root @ lyq314 ~] #130715 01:03:11 mysqld_safe Logging to '/home/mysql/data/lyq314.err '.
130715 01:03:11 mysqld_safe Starting mysqld daemon with databases from/home/mysql/data
[Root @ lyq314 ~] # Netstat-ntlp | grep mysql
Tcp 00 0.0.0.0: 3306 0.0.0.0: * LISTEN8353/mysqld
 
Start instance 2

[Root @ lyq314 ~] #/Usr/local/mysql5/bin/mysqld_safe -- defaults-file =/usr/local/mysql5/etc/lyq314_log.cnf -- datadir =/home/mysql/data_log -- user = mysql & amp &
[Root @ lyq314 ~] #130715 01:08:53 mysqld_safe Logging to '/home/mysql/data_log/lyq314.err '.
130715 01:08:54 mysqld_safe Starting mysqld daemon with databases from/home/mysql/data_log
[Root @ lyq314 ~] # Netstat-nltp | grep mysql
Tcp 00 0.0.0.0: 3306 0.0.0.0: * LISTEN8962/mysqld
Tcp 00 0.0.0.0: 3307 0.0.0.0: * LISTEN9085/mysqld
 
4. MySQL multi-instance access

Instance 1 (via port and socket)

[Root @ lyq314 ~] #/Usr/local/mysql5/bin/mysql-uroot-p-h127.0.0.1-P3306
[Root @ lyq314 ~] #/Usr/local/mysql5/bin/mysql-uroot-p-h127.0.0.1 -- port = 3306
[Root @ lyq314 ~] #/Usr/local/mysql5/bin/mysql-uroot-p-S/tmp/mysql. sock
[Root @ lyq314 ~] #/Usr/local/mysql5/bin/mysql-uroot-p -- socket =/tmp/mysql. sock
 
Instance 2 entry (through port and socket)

[Root @ lyq314 ~] #/Usr/local/mysql5/bin/mysql-uroot-p-h127.0.0.1-P3307
[Root @ lyq314 ~] #/Usr/local/mysql5/bin/mysql-uroot-p-h127.0.0.1 -- port = 3307
[Root @ lyq314 ~] #/Usr/local/mysql5/bin/mysql-uroot-p-S/tmp/mysql_log.sock
[Root @ lyq314 ~] #/Usr/local/mysql5/bin/mysql-uroot-p -- socket =/tmp/mysql_log.sock
 
5. MySQL multi-instance Shutdown

Disable instance 1
/Usr/local/mysql5/bin/mysqladmin-uroot-p-h127.0.0.1-P3306 shutdown
/Usr/local/mysql5/bin/mysqladmin-uroot-p-h127.0.0.1-P3306 shutdown

/Usr/local/mysql5/bin/mysqladmin-uroot-p-S/tmp/mysql. sockshutdown
/Usr/local/mysql5/bin/mysqladmin-uroot-p-socket =/tmp/mysql. sockshutdown
 
Disable instance 2

/Usr/local/mysql5/bin/mysqladmin-uroot-p-h127.0.0.1-P3307 shutdown
/Usr/local/mysql5/bin/mysqladmin-uroot-p-h127.0.0.1-P3307 shutdown

/Usr/local/mysql5/bin/mysqladmin-uroot-p-S/tmp/mysql_log.sockshutdown
/Usr/local/mysql5/bin/mysqladmin-uroot-p-socket =/tmp/mysql_log.sockshutdown

Production Environment MySQL 5.5.x single-host multi-instance configuration practices