MySQL Compilation and Installation and Construction of Multiple MySQL Instances on Single Host

I. Initial information:

• MySQL program version: mysql-5.6.37
• Server system: CentOS 6.8
• Service name assignment for two instances: mysqld1 and mysql2
• Port allocation for two instances: 33061 and 33062

2. Creating mysql groups and users

groupadd mysql
useradd mysql -g mysql

Create directories and grant permissions

1. Program directory (basedir)

mkdir -pv /usr/local/mysql/mysql_instance_1
mkdir -pv /usr/local/mysql/mysql_instance_2
chown -R mysql.mysql /usr/local/mysql/mysql_instance_1
chown -R mysql.mysql /usr/local/mysql/mysql_instance_2

2. Data directory

mkdir -pv /data/datadir_1_mysql
mkdir -pv /data/datadir_2_mysql
chown -R mysql.mysql /data/datadir_1_mysql
chown -R mysql.mysql /data/datadir_2_mysql

4. Preparing dependency procedures:

yum install cmake make gcc gcc-c++ Perl bison ncurses ncurses-devel -y

5. Create the first MySQL instance

1. Decompression package

tar -xvf mysql-5.6.37.tar.gz
cd mysql-5.6.37

• [Note:] Before each compilation, the information generated by the previous compilation, whether successful or unsuccessful, should be cleaned up as follows:

make clean
rm -f  CMakeCache.txt

2. Compile and Install

cd mysql-5.6.37
cmake . 
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql/mysql_instance_1 \
-DSYSCONFDIR=/usr/local/mysql/mysql_instance_1 \
-DMYSQL_DATADIR=/data/datadir_1_mysql \
-DMYSQL_TCP_PORT=33061 \
-DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql_instance_1/mysqld.sock \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DEXTRA_CHARSETS=all \
-DWITH_SSL=system \
-DWITH_EMBEDDED_SERVER=1 \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_SSL=bundled \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_PERFSCHEMA_STORAGE_ENGINE=1

Execute until cmake is successfully executed without error

make -j 4 && make install

3. Copy configuration files

cp /usr/local/mysql/mysql_instance_1/support-files/my-default.cnf /usr/local/mysql/mysql_instance_1/my.cnf

4. Modify configuration files

[mysqld]
 skip_name_resolve = 1
 innodb_file_per_table = 1

 basedir = /usr/local/mysql/mysql_instance_1
 datadir = /data/datadir_1_mysql
 port = 33061
 server_id = 10
 socket = /usr/local/mysql/mysql_instance_1/mysqld.sock

 sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[mysqld_safe]
 log-error = /data/datadir_1_mysql/mysql_1.log
 pid-file =  /data/datadir_1_mysql/node3.pid

[client]
 socket = /usr/local/mysql/mysql_instance_1/mysqld.sock

5. Copy the startup service file and add boot-up (CentOS 6.x system)

cp /usr/local/mysql/mysql_instance_1/support-files/mysql.server /etc/rc.d/init.d/mysqld1
chkconfig --add mysqld1
chkconfig --list mysqld1

6. Initialization of the database

/usr/local/mysql/mysql_instance_1/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql/mysql_instance_1 --datadir=/data/datadir_1_mysql

7. Start the first MySQL instance

service mysqld1 start

8. [Select Execution] Load MySQL Security Script

/usr/local/mysql/mysql_instance_1/bin/mysql_secure_installation

9. View Startup Services

ps aux | grep mysql

Create a second MySQL instance

1. Decompression program

rm -rf mysql-5.6.37
tar -xvf mysql-5.6.37.tar.gz
cd mysql-5.6.37

• [Note:] Before each compilation, the information generated by the previous compilation, whether successful or unsuccessful, should be cleaned up as follows:

make clean
rm -f  CMakeCache.txt

2. Compile and Install

cd mysql-5.6.37
cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql/mysql_instance_2 -DSYSCONFDIR=/usr/local/mysql/mysql_instance_2 -DMYSQL_DATADIR=/data/datadir_2_mysql -DMYSQL_TCP_PORT=33062 -DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql_instance_2/mysqld.sock -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DEXTRA_CHARSETS=all -DWITH_SSL=system -DWITH_EMBEDDED_SERVER=1 -DENABLED_LOCAL_INFILE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_SSL=bundled -DWITH_ARCHIVE_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITH_PERFSCHEMA_STORAGE_ENGINE=1

After the cmake command is executed successfully, execute:

make -j 4 && make install

3. Copy configuration files

cp /usr/local/mysql/mysql_instance_2/support-files/my-default.cnf /usr/local/mysql/mysql_instance_2/my.cnf

4. Modify configuration files

[mysqld]
 skip_name_resolve = 1
 innodb_file_per_table = 1

 basedir = /usr/local/mysql/mysql_instance_2
 datadir = /data/datadir_2_mysql
 port = 33062
 server_id = 20
 socket = /usr/local/mysql/mysql_instance_2/mysqld.sock

 sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[mysqld_safe]
 log-error = /data/datadir_2_mysql/mysql_2.log
 pid-file =  /data/datadir_2_mysql/node3.pid

[client]
 socket = /usr/local/mysql/mysql_instance_2/mysqld.sock

5. Copy startup service file

cp /usr/local/mysql/mysql_instance_2/support-files/mysql.server /etc/rc.d/init.d/mysqld2

6. Modify the service file and modify the following statements.

[Note:] Make sure that the lock_file of each instance is different, otherwise the error will be reported.

lock_file_path="$lockdir/mysql_sec"

7. Add boot-up (CentOS 6.x system)

chkconfig --add mysqld2
chkconfig --list mysqld2

8. Initialization of the database

/usr/local/mysql/mysql_instance_2/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql/mysql_instance_2 --datadir=/data/datadir_2_mysql

9. Start the second MySQL instance

service mysqld2 start

10. [Optional execution] Load MySQL security scripts

/usr/local/mysql/mysql_instance_2/bin/mysql_secure_installation

11. View the Started Services

ps aux | grep mysql

7. Log on to MySQL Service with Mysql Client

1. Log on to MySQL instance 1:

/usr/local/mysql/mysql_instance_1/bin/mysql -uroot -p

2. Log on to MySQL instance 2:

/usr/local/mysql/mysql_instance_2/bin/mysql -uroot -p

8. Reference Documents

• MySQL Compilation Parameters Official Document

https://dev.mysql.com/doc/refman/5.7/en/source-configuration-options.html#option_cmake_with_systemd

• Blog: MySQL 5.6.10 Best Practices for Source Coding and Installation

http://blog.csdn.net/luoduyu/article/details/21475107