二、安装配置MYSQL5.6多实例-共享配置文件方法

1、如果之前已经运行了单实例的数据库请先停库

[root@mysql ~]# mysqladmin shutdown -u root-p

Enter password:

 

2、创建各实例的数据目录

[root@mysql ~]# mkdir /home/mysql/data3307

[root@mysql ~]# mkdir /home/mysql/data3308

 

3、修改各实例数据目录的属主

[root@mysql ~]# chown -R mysql.mysql/home/mysql/data3307

[root@mysql ~]# chown -R mysql.mysql/home/mysql/data3308

 

4、校验数据目录

[root@mysql ~]# cd /home/mysql/

[root@mysql mysql]# ll

total 12

drwxr-xr-x 5 mysql mysql 4096 Nov  5 23:44 data

drwxr-xr-x 2 mysql mysql 4096 Nov  5 23:58 data3307

drwxr-xr-x 2 mysql mysql 4096 Nov  5 23:58 data3308

[root@mysql mysql]#

 

5.修改my.cnf主配置文件

[root@mysql mysql]#cp /etc/my.cnf/etc/my.cnf.bak

[root@mysql mysql]#vim /etc/my.cnf

[mysqld]

#basedir=/usr/local/mysql

#datadir=/home/mysql/data

#socket = /usr/local/mysql/mysqld.sock

#port =3306

#server_id =100

user = mysql

join_buffer_size = 128M

sort_buffer_size = 2M

read_rnd_buffer_size = 2M

default_storage_engine = InnoDB

join_buffer_size = 128M

max_allowed_packet= 1M

net_buffer_length= 8K

 

[mysqld_multi]

mysqld =/usr/local/mysql/bin/mysqld_safe

mysqladmin=/usr/local/mysql/bin/mysqladmin

 

[mysqld3306]

port  =3306

basedir  =/usr/local/mysql

datadir  =/home/mysql/data

socket =/home/mysql/data/mysqld.sock

server_id =1

 

[mysqld3307]

port  =3307

basedir  =/usr/local/mysql

datadir  =/home/mysql/data3307

socket   =/home/mysql/data3307/mysql.sock3307

server_id=2

 

[mysqld3308]

port  =3308

basedir  =/usr/local/mysql

datadir  =/home/mysql/data3308

socket   =/home/mysql/data3308/mysql.sock3308

server_id=3

 

6、初始化数据库实例

[root@mysql mysql]#/usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql--datadir=/home/mysql/data3307 --user=mysql

[root@mysql mysql]#/usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql--datadir=/home/mysql/data3308 --user=mysql

 

7、启动数据库

[root@mysql ~]# ps -ef|grep mysql|grep -vgrep

[root@mysql ~]# mysqld_multi start 3306

[root@mysql ~]# mysqld_multi start 3307

[root@mysql ~]# mysqld_multi start 3308

 

8、验证实例进程

[root@mysql ~]# ps -ef|grep mysql|grep -vgrep

root    60854     1  0 00:31 pts/0    00:00:00 /bin/sh/usr/local/mysql/bin/mysqld_safe --port=3306 --basedir=/usr/local/mysql--datadir=/home/mysql/data --socket=/home/mysql/data/mysqld.sock --server_id=1

mysql   61316 60854  0 00:31 pts/0    00:00:00 /usr/local/mysql/bin/mysqld--basedir=/usr/local/mysql --datadir=/home/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin--user=mysql --server-id=1 --log-error=/home/mysql/data/mysql-error.log--open-files-limit=65535 --pid-file=/home/mysql/data/mysql.pid--socket=/home/mysql/data/mysqld.sock --port=3306

root    61348     1  0 00:31 pts/0    00:00:00 /bin/sh/usr/local/mysql/bin/mysqld_safe --port=3307 --basedir=/usr/local/mysql--datadir=/home/mysql/data3307 --socket=/home/mysql/data3307/mysql.sock3307--server_id=2

mysql   61810 61348  0 00:31 pts/0    00:00:00 /usr/local/mysql/bin/mysqld--basedir=/usr/local/mysql --datadir=/home/mysql/data3307 --plugin-dir=/usr/local/mysql/lib/plugin--user=mysql --server-id=2 --log-error=/home/mysql/data/mysql-error.log--open-files-limit=65535 --pid-file=/home/mysql/data3307/mysql.pid--socket=/home/mysql/data3307/mysql.sock3307 --port=3307

root    61841     1  0 00:31 pts/0    00:00:00 /bin/sh/usr/local/mysql/bin/mysqld_safe --port=3308 --basedir=/usr/local/mysql--datadir=/home/mysql/data3308 --socket=/home/mysql/data3308/mysql.sock3308--server_id=3

mysql   62303 61841  0 00:31 pts/0    00:00:00 /usr/local/mysql/bin/mysqld--basedir=/usr/local/mysql --datadir=/home/mysql/data3308--plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --server-id=3--log-error=/home/mysql/data/mysql-error.log --open-files-limit=65535--pid-file=/home/mysql/data3308/mysql.pid --socket=/home/mysql/data3308/mysql.sock3308--port=3308

[root@mysql ~]#

 

9.验证端口

[root@mysql ~]# netstat -tunlp|grep 330

tcp       0      0 :::3307                     :::*                        LISTEN      61810/mysqld       

tcp       0      0 :::3308                     :::*                        LISTEN      62303/mysqld       

tcp       0      0 :::3306                     :::*                        LISTEN      61316/mysqld       

[root@mysql ~]# ss -tunlp|grep 330

tcp   LISTEN     0      128                   :::3307                 :::*      users:(("mysqld",61810,12))

tcp   LISTEN     0      128                   :::3308                 :::*      users:(("mysqld",62303,12))

tcp   LISTEN     0      128                   :::3306                 :::*      users:(("mysqld",61316,12))

[root@mysql ~]#

 

9、登录3306、3307、3308实例

mysql -uroot -p -S/home/mysql/data/mysqld.sock

mysql -S/home/mysql/data3307/mysql.sock3307

mysql -S /home/mysql/data3307/mysql.sock3308

mysql> status;

 

 

8.停止数据库

[root@mysql ~]# mysqld_multi stop 3306

[root@mysql ~]# mysqld_multi stop 3307

[root@mysql ~]# mysqld_multi stop 3308

[root@mysql ~]# ps -ef|grep mysql|grep -vgrep

root    57734     1  0 00:23 pts/0    00:00:00 /bin/sh/usr/local/mysql/bin/mysqld_safe --port=3306 --basedir=/usr/local/mysql --datadir=/home/mysql/data--socket=/usr/local/mysql/mysqld.sock --server_id=100

mysql   58196 57734  0 00:23 pts/0    00:00:00 /usr/local/mysql/bin/mysqld--basedir=/usr/local/mysql --datadir=/home/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin--user=mysql --server-id=100 --log-error=/home/mysql/data/mysql-error.log--open-files-limit=65535 --pid-file=/home/mysql/data/mysql.pid--socket=/usr/local/mysql/mysqld.sock --port=3306

[root@mysql ~]#

我们发现3306的实例还没有停止，这个是因为3306数据库配置了root@localhost的密码为123456，必须使用root用户及密码才能停

[root@mysql ~]# mysqld_multi stop 3306--user=root --password=123456

注意：3307，3308的实例没有配置密码登录，且空密码账户没有删除，不安全，要配置密码登录并删除掉空密码账户