MySQL Multi-instance

First, installation environment

operating system: CentOS release 6.6 (Final) (minimized installation)

MySQL version: mysql-5.5.32.tar.gz

CMake version: cmake-2.8.7.tar.gz

Second, install the basic dependency package

first change the Yum source to the domestic Ali Yum source

[email protected] ~]# Yum install wget-y

[email protected] ~]# Wget-o/etc/yum.repos.d/centos-base.repo Http://mirrors.aliyun.com/repo/Centos-6.repo

[Email protected] ~]# CD/ETC/YUM.REPOS.D

[email protected] yum.repos.d]# Yum Makecache

Install the base package and the prerequisite dependency package

[email protected] ~]# yum install make gcc vim lrzsz tree ncurses-devel libaio-devel qt*-y

Third, source code compiled installation cmake and MySQL

First create a folder to store the source package, develop good operation and maintenance habits

[Email protected] ~]# mkdir/server/tools-p

[email protected] ~]# Cd/server/tools

I am on the real machine to download the source package, and then through the RZ upload to the server designated storage directory. (both SECURECRT and Xshell support both local and remote server uploads and downloads)

[Email protected] tools]# RZ

650) this.width=650; "Src=" Http://s2.51cto.com/wyfs02/M02/83/AA/wKiom1d6AtTBx_PKAABYtMgvEn4841.png-wh_500x0-wm_3 -wmp_4-s_391390911.png "title=" Rz.png "alt=" Wkiom1d6attbx_pkaabytmgven4841.png-wh_50 "/>

Decompression CMake

[Email protected] tools]# TAR-ZXVF cmake-2.8.7.tar.gz

[Email protected] tools]# CD cmake-2.8.7

Compiling CMake

[Email protected] cmake-2.8.7]#./configure

Installing CMake

[Email protected] cmake-2.8.7]# gmake && gmake Install

Unzip MySQL

[Email protected] tools]# TAR-ZXVF mysql-5.5.32.tar.gz

[Email protected] tools]# CD mysql-5.5.32

Create a MySQL user

[[email protected] mysql-5.5.32]# Groupadd MySQL

[Email protected] mysql-5.5.32]# useradd-g mysql-s/sbin/nologin-m MySQL

compiling MySQL

[Email protected] mysql-5.5.32]# mkdir/application-p

Cmake-dcmake_install_prefix=/application/mysql-5.5.32-dmysql_datadir=/application/mysql-5.5.32/data-dmysql_ Unix_addr=/application/mysql-5.5.32/tmp/mysql.sock-dextra_charsets=gbk.gb2312.utf8.ascii-denabled_local_infile =on-dwith_innobase_storage_engine=1-dwith_federated_storage_engine=1-dwith_blackhole_storage_engine=1-dwithout _example_storage_engine=1-dwithout_partition_storage_engine=1-dwith_fast_mutexes=1-dwith_zlib=bundled-denabled _local_infile=1-dwith_readline=1-dwith_embedded_server=1-dwith_debug=0

Install MySQL

[[email protected] mysql-5.5.32]# make && make install

Iv. Reproduction of MY.CNF

[Email protected] mysql-5.5.32]# CP/SERVER/TOOLS/MYSQL-5.5.32/SUPPORT-FILES/MY-SMALL.CNF/ETC/MY.CNF

Five, soft link and environment variable configuration

[Email protected] mysql-5.5.32]# ln-s/application/mysql-5.5.32//application/mysql

[Email protected] mysql-5.5.32]# echo ' path=/application/mysql/bin: $PATH ' >>/etc/profile

[Email protected] mysql-5.5.32]# Source/etc/profile

Vi. Delegation of authority

[Email protected] mysql-5.5.32]# chown-r mysql.mysql/application/mysql/data/

[Email protected] mysql-5.5.32]# chmod-r 1777/tmp/

Vii. creating a MySQL multi-instance data file directory

[Email protected] mysql-5.5.32]# mkdir-p/data/{3306,3307}/data

[Email protected] mysql-5.5.32]# Tree/data

650) this.width=650; "Src=" Http://s4.51cto.com/wyfs02/M01/83/AA/wKioL1d6BsqQYB6TAAAlbPOmZHA890.png-wh_500x0-wm_3 -wmp_4-s_2655483586.png "title=" Tree.png "alt=" Wkiol1d6bsqqyb6taaalbpomzha890.png-wh_50 "/>

VIII. Create a configuration file for the MySQL instance my.cnf

[Email protected] 3306]# cp/server/tools/mysql-5.5.32/support-files/my-small.cnf my.cnf

[Email protected] 3307]# vim my.cnf

Configuration files can be set according to their needs (my.cnf in the attachment)

[email protected] 3306]# CP my.cnf/data/3307/

[Email protected] 3307]# sed-i ' s#3306#3307#g ' my.cnf

[Email protected] 3307]# vim my.cnf

change Server-id to 3

[Email protected] 3307]# chown-r mysql.mysql/data/

Ix. Initializing the database

[Email protected] 3306]# cd/application/mysql/scripts/

[Email protected] scripts]#/mysql_install_db--basedir=/application/mysql--datadir=/data/3306/data--user=mysql

[Email protected] scripts]#/mysql_install_db--basedir=/application/mysql--datadir=/data/3307/data--user=mysql

See two OK on success!

Ten, start MySQL multi-instance

[Email protected] scripts]# mysqld_safe--defaults-file=/data/3306/my.cnf 2>&1 >/dev/null &

[Email protected] scripts]# mysqld_safe--defaults-file=/data/3307/my.cnf 2>&1 >/dev/null &

You can write your own MySQL startup script (the attachment has MySQL startup script)

See if the MySQL multi-instance is in effect, that is, whether 3306 and 3307 are turned on

[Email protected] scripts]# Ss-ntulp|grep 330

650) this.width=650; "Src=" Http://s3.51cto.com/wyfs02/M00/83/AA/wKioL1d6C_XC1D_EAAAyvezZ5zk622.png-wh_500x0-wm_3 -wmp_4-s_81159091.png "title=" Netstat.png "alt=" Wkiol1d6c_xc1d_eaaayvezz5zk622.png-wh_50 "/>

Xi. MySQL startup script to start MySQL

[[email protected] 3306]# chown mysql:mysql MySQL

[[email protected] 3307]# chown mysql:mysql MySQL

[[email protected] 3306]# chmod u+x MySQL

[[email protected] 3307]# chmod u+x MySQL

[[email protected] 3306]#/data/3306/mysql start

[[email protected] 3306]#/data/3307/mysql start

[email protected] 3306]# Ss-ntulp|grep

TCP LISTEN 0 *:3306 *:* Users: (("mysqld", 68932,13))

TCP LISTEN 0 *:3307 *:* Users: (("mysqld", 69710,13))

12. Multiple instances into MySQL configuration

[Email protected] ~]# mysqladmin password oldboy123-s/data/3306/mysql.sock

[Email protected] ~]# mysqladmin password oldboy456-s/data/3307/mysql.sock

Go to MySQL

[Email protected] ~]# mysql-uroot-poldboy123-s/data/3306/mysql.sock

650) this.width=650; "Src=" Http://s5.51cto.com/wyfs02/M00/83/AA/wKioL1d6DiyQI5YeAAB-HknMbZw259.png-wh_500x0-wm_3 -wmp_4-s_3121491134.png "title=" Mysql3306.png "alt=" Wkiol1d6diyqi5yeaab-hknmbzw259.png-wh_50 "/>

[Email protected] ~]# mysql-uroot-poldboy456-s/data/3307/mysql.sock

650) this.width=650; "Src=" Http://s2.51cto.com/wyfs02/M00/83/AB/wKiom1d6DlOzBT_mAAB_pd5C2_Y534.png-wh_500x0-wm_3 -wmp_4-s_3713241573.png "title=" Mysql3307.png "alt=" Wkiom1d6dlozbt_maab_pd5c2_y534.png-wh_50 "/>

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| Information_schema |

| MySQL |

| Performance_schema |

| Test |

+--------------------+

4 rows in Set (0.00 sec)

Mysql> select User,host from Mysql.user;

+------+--------------------+

| user | Host |

+------+--------------------+

| Root | 127.0.0.1 |

| Root | :: 1 |

| Root | localhost |

| | oldboy.example.com |

| Root | oldboy.example.com |

+------+--------------------+

5 rows in Set (0.00 sec)

This article is from the "IT" blog, so be sure to keep this source http://wang119.blog.51cto.com/9428009/1795618

MySQL Multi-instance