3638MySQL数据库应用答案_Mysql数据库多实例配置

二进制安装：

[[email protected] ~]# mkdir /home/lufeng/tools -p

[[email protected] ~]# cd /home/lufeng/tools

[[email protected] tools]# rz

[[email protected] tools]# tar xf mysql-5.5.49-linux2.6-x86_64.tar.gz

[[email protected] tools]# mkdir -p /application/mysql-5.5.49

[[email protected] tools]# mv mysql-5.5.49-linux2.6-x86_64 /application/mysql-5.5.49

创建mysql用户|组

[[email protected] tools]# groupadd mysql

[[email protected] tools]# useradd mysql -g mysql -s /sbin/nologin -M

[[email protected] tools]# ln -s /application/mysql-5.5.49/ /application/mysql

[email protected] mysql]# sed -i ‘s#/usr/local/mysql#/application/mysql#g‘ /application/mysql/bin/mysqld_safe

创建多实例数据文件目录

[[email protected]_2 ~]# mkdir -p /data/{3306,3307}/data

配置相应的配置文件：

[[email protected]_2 ~]# cat /data/3306/my.cnf

####################################################

[client]

#password       = your_password

port            = 3306

socket          = /data/3306/mysql.sock

[mysqld]

user            = mysql

port            = 3306

socket          = /data/3306/mysql.sock

basedir         = /application/mysql

datadir         = /data/3306/data

pid-file        = /data/3306/mysql.pid

relay-log       = /data/3306/relay-bin

relay-log-info-file = /data/3306/relay-log.info

skip-external-locking

key_buffer_size = 16K

max_allowed_packet = 1M

table_open_cache = 4

sort_buffer_size = 64K

read_buffer_size = 256K

read_rnd_buffer_size = 256K

net_buffer_length = 2K

thread_stack = 128K

server-id       = 1

[mysqldump]

quick

max_allowed_packet = 2M

[mysql]

no-auto-rehash

[mysql_safe]

log-error=/data/3306/mysql_lufeng3306.err

pid-file=/data/3306/mysqlf.pid

##############################################

[[email protected]_2 ~]# cat /data/3307/my.cnf

#############################################

[client]

#password       = your_password

port            = 3307

socket          = /data/3307/mysql.sock

[mysqld]

user            = mysql

port            = 3307

socket          = /data/3307/mysql.sock

basedir         = /application/mysql

datadir         = /data/3307/data

pid-file        = /data/3307/mysql.pid

relay-log       = /data/3307/relay-bin

relay-log-info-file = /data/3307/relay-log.info

skip-external-locking

key_buffer_size = 16K

max_allowed_packet = 1M

table_open_cache = 4

sort_buffer_size = 64K

read_buffer_size = 256K

read_rnd_buffer_size = 256K

net_buffer_length = 2K

thread_stack = 128K

server-id       = 2

[mysqldump]

quick

max_allowed_packet = 2M

[mysql]

no-auto-rehash

[mysql_safe]

log-error=/data/3307/mysql_lufeng3307.err

pid-file=/data/3307/mysqlf.pid

##############################################

创建启动文件

[[email protected]_2 ~]# cat /data/3306/mysql

#!/bin/sh

################################

#mysql 3306 by lufeng @2017.4.3

################################

#int

port=3306

mysql_user="root"

mysql_pwd="199429"

CmdPath="/application/mysql/bin"

mysql_sock="/data/${port}/mysql.sock"

#startup function

function_start_mysql(){

if [ ! -e "$mysql_sock" ];then

printf "Starting MySQL...\n"

/bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf 2>&1 >/dev/null  &

else

printf "MySQL is running...\n"

exit

fi

}

#stop function

function_stop_mysql(){

if [ ! -e "$mysql_sock" ];then

printf "MySQL is stopped...\n"

exit

else

printf "Stoping MySQL...\n"

${CmdPath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S /data/${port}/mysql.sock shutdown

fi

}

#restart function

function_restart_mysql(){

printf "Restarting MySQL...\n"

function_stop_mysql

sleep 2

function_start_mysql

}

case $1 in

start)

function_start_mysql

;;

stop)

function_stop_mysql

;;

restart)

function_restart_mysql

;;

*)

printf  "Usage: /data/${port}/mysql {start|stop|restart}\n"

esac

##########################################################

[[email protected]_2 ~]# cat /data/3307/mysql

#!/bin/sh

################################

#mysql 3307 by lufeng @2017.4.3

################################

#int

port=3307

mysql_user="root"

mysql_pwd="199429"

CmdPath="/application/mysql/bin"

mysql_sock="/data/${port}/mysql.sock"

#startup function

function_start_mysql()

{

if [ ! -e "$mysql_sock" ];then

printf "Starting MySQL...\n"

/bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf 2>&1 >/dev/null  &

else

printf "MySQL is running...\n"

exit

fi

}

#stop function

function_stop_mysql()

{

if [ ! -e "$mysql_sock" ];then

printf "MySQL is stopped...\n"

exit

else

printf "Stoping MySQL...\n"

${CmdPath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S /data/${port}/mysql.sock shutdown

fi

}

#restart function

function_restart_mysql()

{

printf "Restarting MySQL...\n"

function_stop_mysql

sleep 2

function_start_mysql

}

case $1 in

start)

function_start_mysql

;;

stop)

function_stop_mysql

;;

restart)

function_restart_mysql

;;

*)

printf "Usage: /data/${port}/mysql {start|stop|restart}\n"

esac

#######################################################

配置多实例文件权限

[[email protected]_2 ~]# chown -R mysql.mysql /data

[[email protected]_2 ~]# find /data -name mysql|xargs chmod 700

[[email protected]_2 ~]# find /data -name mysql -exec ls -l {} \;

-rwx------ 1 mysql mysql 999 4月   3 13:33 /data/3306/mysql

-rwx------ 1 mysql mysql 1210 4月   3 13:35 /data/3307/mysql

全局变量：

[[email protected]_2 ~]# ls /application/mysql/bin/mysql

/application/mysql/bin/mysql

[[email protected]_2 ~]# echo ‘export PATH=/application/mysql/bin:$PATH‘ >>/etc/profile

[[email protected]_2 ~]# tail -1 /etc/profile

export PATH=/application/mysql/bin:$PATH

[[email protected]_2 ~]# source /etc/profile

[[email protected]_2 ~]# echo $PATH

/application/mysql/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin

初始化数据库文件

[[email protected]_2 scripts]# cd /application/mysql/scripts/

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

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

Installing MySQL system tables...

170403 13:47:58 [Note] /application/mysql/bin/mysqld (mysqld 5.5.49) starting as process 2971 ...

OK

Filling help tables...

170403 13:47:59 [Note] /application/mysql/bin/mysqld (mysqld 5.5.49) starting as process 2980 ...

OK

============成功标志

启动命令：

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

Starting MySQL...

[[email protected]_2 ~]# /data/3307/mysql start

Starting MySQL...

[[email protected]_2 ~]# netstat -lntup|grep mysql

tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      3638/mysqld

tcp        0      0 0.0.0.0:3307                0.0.0.0:*                   LISTEN      3962/mysqld

配置开机自启动：

[[email protected]_2 ~]# echo "mysql multi instances" >>/etc/rc.local

[[email protected]_2 ~]# echo "/data/3306/mysql start" >>/etc/rc.local

[[email protected]_2 ~]# echo "/data/3307/mysql start" >>/etc/rc.local

[[email protected]_2 ~]# tail -3 /etc/rc.local

mysql multi instances

/data/3306/mysql start

/data/3307/mysql start

登录：[[email protected] ~]# mysql -S /data/3306/mysql.sock

安全配置：

[[email protected] ~]# mysqladmin -u root -S /data/3307/mysql.sock password ‘199429‘

[[email protected] ~]# mysql -S /data/3306/mysql.sock -uroot -p

Enter password:

原文：http://bestlufeng.blog.51cto.com/11790256/1912675