CleverCode recently studied multiple mysql instances and found that there are two ways: The first is to use multiple configuration files to start different processes to implement multiple instances. The advantage of this method is simple in logic and configuration, but the disadvantage is that it is not easy to manage. CleverCode has recently studied multiple mysql instances and found two ways:
The first is to use multiple configuration files to start different processes to implement multiple instances. The advantage of this method is simple in logic and configuration, but the disadvantage is that it is not easy to manage.
The second is through mysqld_multi. You can use a separate configuration file to implement multiple instances. in this way, the configuration of each instance is not very good. The advantage is that it is easy to manage and can be centrally managed.
We recommend that you use multiple configuration files. This kind of practical application is good, the coupling is not strong, and the configuration is convenient, especially for master-slave replication.
Previous article mysql multi-instance (multiple configuration files): http://blog.csdn.net/clevercode/article/details/47642619. This section describes how to configure multiple configuration files. This article introduces the mysqld_multi method.
1. environment introduction:
1) Introduction
Mysql version: mysql-5.5.27
Cmake: cmake-2.8.8
Operating system: CentOS6.5
Mysql instances: 3
The ports used by the instance are 3306, 3307, and 3308 respectively.
2) install all software packages this time.
Http://download.csdn.net/detail/clevercode/8662323
2. configure the firewall
1) add 3306, 3307, and 3308 to the firewall configuration file (allow ports 3306, 3307, and 3308 to pass through the firewall)
# Vi/etc/sysconfig/iptables # Edit the firewall configuration file
-A input-m state -- state NEW-m tcp-p tcp -- dport 3306-j ACCEPT
-A input-m state -- state NEW-m tcp-p tcp -- dport 3307-j ACCEPT
-A input-m state -- state NEW-m tcp-p tcp -- dport 3308-j ACCEPT
2) restart the firewall
#/Etc/init. d/iptables restart # restart the firewall to make the configuration take effect.
3. install cmake
Extract
# Cd/usr/local/src/mysql
# Tar zxvf cmake-2.8.8.tar.gz
# Cd cmake-2.8.8
Configuration
#./Configure
Compile
# Make
Install
# Make install
View version
# Cmake-version
4. install Mysql
1) create a user
# Groupadd mysql # Add a mysql Group
# Useradd-g mysql-s/bin/false # Create a user mysql and add it to the mysql group. mysql users are not allowed to log on to the system directly.
2) decompress
# Cd/usr/local/src/mysql
# Tar zxvf mysql-5.5.27.tar.gz
# Cd mysql-5.5.27
4) configuration
# Cmake.-DCMAKE_INSTALL_PREFIX =/usr/local/mysql
PS: When cmake is used, you don't need to use so many parameters. you only need one-DCMAKE_INSTALL_PREFIX =/usr/local/mysql. we can configure it in my. cnf. [Mysqld] to see if my. cnf after your copy has these settings.
5) compile
# Make
6) installation
# Make install
5. initialize the database
# Mkdir-p/data0/dbdata/mysql/3306.
# Mkdir-p/data0/dbdata/mysql/3307.
# Mkdir-p/data0/dbdata/mysql/3308.
# Chown-R mysql: mysql/data0/dbdata/mysql/3306
# Chown-R mysql: mysql/data0/dbdata/mysql/3307
# Chown-R mysql: mysql/data0/dbdata/mysql/3308
#/Usr/local/mysql/scripts/mysql_install_db -- basedir =/usr/local/mysql -- datadir =/data0/dbdata/mysql/3306 -- user = mysql
#/Usr/local/mysql/scripts/mysql_install_db -- basedir =/usr/local/mysql -- datadir =/data0/dbdata/mysql/3307 -- user = mysql
#/Usr/local/mysql/scripts/mysql_install_db -- basedir =/usr/local/mysql -- datadir =/data0/dbdata/mysql/3308 -- user = mysql
6. modify the configuration file (only use the/etc/my. cnf file)
# Cp/usr/local/mysql/support-files/my-huge.cnf/etc/my. cnf
# Vi/etc/my. cnf
[Mysqld_multi] mysqld =/usr/local/mysql/bin/mysqld_safe mysqladmin =/usr/local/mysql/bin/mysqladmin # used to log on to and disable this service user = rootpassword = CleverCode123 [mysqld3306] # Data Directory datadir =/data0/dbdata/mysql/3306 # connection port = 3306 socket =/tmp/mysql3306.sock # binlog log-bin =/data0/dbdata/mysql/3306 /mysql-bin pid-file =/data0/dbdata/mysql/3306/mysql_3306.pid [mysqld3307] datadir =/data0/dbdata/mysql/3307 port = 3307 socket =/tmp/mysql3307.sock log-bin =/data0/dbdata/mysql/3307/mysql-bin pid-file =/data0/dbdata/mysql/3307/mysql_3307.pid [mysqld3308] datadir =/data0/dbdata/ mysql/3308 port = 3308 socket =/tmp/mysql3308.socklog-bin =/data0/dbdata/mysql/3308/mysql-binpid-file =/data0/dbdata/mysql/3308/mysql_3308.pid
7. start mysql 3306, 3307, and 3308
#/Usr/local/mysql/bin/mysqld_multi start 3306
#/Usr/local/mysql/bin/mysqld_multi start 3307
#/Usr/local/mysql/bin/mysqld_multi start 3308
8. check whether the port is listening. if 3306, 3307, and 3308 are displayed, the system starts normally.
# Netstat-anp | grep 3308
Tcp 0 0 0.0.0.0: 3308 0.0.0.0: * LISTEN 2348/mysqld
Unix 2 [ACC] stream listening 10780 2348/mysqld/tmp/mysql3308.sock
9. initialize the password and authorize remote logon. the mysqladmin user name and password must be consistent with/ect/my. cnf; otherwise, the service cannot be stopped.
#/Usr/local/mysql/bin/mysqladmin-u root password "CleverCode123"-S/tmp/mysql3306.sock
#/Usr/local/mysql/bin/mysql-uroot-pCleverCode123-S/tmp/mysql3306.sock
Mysql> grant all privileges on *. * to 'root' @ '%' identified by 'pwd3306 'with grant option;
Mysql> flush privileges;
#/Usr/local/mysql/bin/mysqladmin-u root password "CleverCode123"-S/tmp/mysql3307.sock
#/Usr/local/mysql/bin/mysql-uroot-pCleverCode123-S/tmp/mysql3307.sock
Mysql> grant all privileges on *. * to 'root' @ '%' identified by 'pwd3307 'with grant option;
Mysql> flush privileges;
#/Usr/local/mysql/bin/mysqladmin-u root password "CleverCode123"-S/tmp/mysql3308.sock
#/Usr/local/mysql/bin/mysql-uroot-pCleverCode123-S/tmp/mysql3308.sock
Mysql> grant all privileges on *. * to 'root' @ '%' identified by 'pwd3308 'with grant option;
Mysql> flush privileges;
10 stop (the password must be initialized first ). You can use netstat-anp | grep 3308 to check whether the listening process still exists.
#/Usr/local/mysql/bin/mysqld_multi stop 3306
#/Usr/local/mysql/bin/mysqld_multi stop 3307
#/Usr/local/mysql/bin/mysqld_multi stop 3308
11. Display process status
#/Usr/local/mysql/bin/mysqld_multi report 3306
#/Usr/local/mysql/bin/mysqld_multi report 3307
#/Usr/local/mysql/bin/mysqld_multi report 3308
