一台MySQL服务器，通过不同的配置文件和启动脚本。以不同的端口来区分不同的实例。
MySQL已安装完成。
Mysql安装路径为：/usr/local/mysql/
二进制安装：
http://www.z-dig.com/binary-packages-to-install-mysql-5-5.html 源码编译安装：
http://www.z-dig.com/to-compile-and-install-mysql-5-5.html 规划
实例1：端口 3306 ; 数据库存放目录：/mysql/3306/data/ ; 配置文件及脚本存放目录：/mysql/3306/
实例2：端口 3307 ; 数据库存放目录：/mysql/3307/data/ ; 配置文件及脚本存放目录：/mysql/3307/
创建目录并将目录的属主属组改为mysql
[root@mysql ~]# mkdir -p /mysql/{3306,3307}/data
[root@mysql ~]# tree /mysql
/mysql
├── 3306
│ └── data
└── 3307
└── data
4 directories, 0 files
[root@mysql ~]#
[root@mysql ~]# chown -R mysql.mysql /mysql/
初始化各实例的数据库
[root@mysql ~]# /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/mysql/3306/data/ --user=mysql
[root@mysql ~]# /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/mysql/3307/data/ --user=mysql
各实例的配置文件及脚本。（根据服务器的实际情况编辑相应的配置文件进行优化,本文省略。启动脚本待优化。）
实例1,配置文件。
[root@mysql ~]# cat /mysql/3306/my.cnf
[client]
port = 3306
socket = /mysql/3306/mysql.sock
[mysqld]
port = 3306
socket = /mysql/3306/mysql.sock
basedir = /usr/local/mysql
datadir = /mysql/3306/data
[mysqld_safe]
log_error = /mysql/3306/mysql.err
pid_file = /mysql/3306/mysql.pid
[root@mysql ~]#
实例1,启动脚本
[root@mysql ~]# cat /mysql/3306/mysql
#!/bin/sh
port=3306
mysql_user="root"
cmdpath="/usr/local/mysql/bin"
mysql_sock="/mysql/$port/mysql.sock"
start_mysql() {
if [[ ! -e "$mysql_sock" ]];then
printf "Starting MySQL ...\n"
/bin/sh $cmdpath/mysqld_safe --defaults-file=/mysql/$port/my.cnf 2>&1>/dev/null &
else
printf "MySQL is running...\n"
fi
}
stop_mysql() {
if [[ ! -e "$mysql_sock" ]];then
printf "MySQL is stopped ..\n"
else
printf "Stopping MySQL ..\n"
$cmdpath/mysqladmin -S $mysql_sock -uroot -p shutdown
fi
}
restart_mysql() {
if [[ ! -e "$mysql_sock" ]];then
printf "MySQL is stopped ..\n"
else
printf "Restarting MySQL ..\n"
$cmdpath/mysqladmin -S $mysql_sock -uroot -p shutdown&&
/bin/sh $cmdpath/mysqld_safe --defaults-file=/mysql/$port/my.cnf 2>&1>/dev/null &
fi
}
case $1 in
start)
start_mysql;;
stop)
stop_mysql;;
restart)
restart_mysql;;
*)
printf "Usage: mysql {start|stop|restart}\n"
esac
[root@mysql ~]#
实例2,配置文件
[root@mysql ~]# cat /mysql/3307/my.cnf
[client]
port = 3307
socket = /mysql/3307/mysql.sock
[mysqld]
port = 3307
socket = /mysql/3307/mysql.sock
basedir = /usr/local/mysql
datadir = /mysql/3307/data
[mysqld_safe]
log_error = /mysql/3307/mysql.err
pid_file = /mysql/3307/mysql.pid
[root@mysql ~]#
实例2,启动脚本
[root@mysql ~]# cat /mysql/3307/mysql
#!/bin/sh
port=3307
mysql_user="root"
cmdpath="/usr/local/mysql/bin"
mysql_sock="/mysql/$port/mysql.sock"
start_mysql() {
if [[ ! -e "$mysql_sock" ]];then
printf "Starting MySQL ...\n"
/bin/sh $cmdpath/mysqld_safe --defaults-file=/mysql/$port/my.cnf 2>&1>/dev/null &
else
printf "MySQL is running...\n"
fi
}
stop_mysql() {
if [[ ! -e "$mysql_sock" ]];then
printf "MySQL is stopped ..\n"
else
printf "Stopping MySQL ..\n"
$cmdpath/mysqladmin -S $mysql_sock -uroot -p shutdown
fi
}
restart_mysql() {
if [[ ! -e "$mysql_sock" ]];then
printf "MySQL is stopped ..\n"
else
printf "Restarting MySQL ..\n"
$cmdpath/mysqladmin -S $mysql_sock -uroot -p shutdown&&
/bin/sh $cmdpath/mysqld_safe --defaults-file=/mysql/$port/my.cnf 2>&1>/dev/null &
fi
}
case $1 in
start)
start_mysql;;
stop)
stop_mysql;;
restart)
restart_mysql;;
*)
printf "Usage: mysql {start|stop|restart}\n"
esac
[root@mysql ~]#
分别启动两个实例
[root@mysql ~]# /mysql/3306/mysql start
Starting MySQL ...
[root@mysql ~]# /mysql/3307/mysql start
Starting MySQL ...
[root@mysql ~]#
[root@mysql ~]# lsof -i :3306
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 32326 mysql 10u IPv4 36650 0t0 TCP *:mysql (LISTEN)
[root@mysql ~]# lsof -i :3307
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 32489 mysql 10u IPv4 36765 0t0 TCP *:opsession-prxy (LISTEN)
[root@mysql ~]#
修改各实例的root密码
MySQL服务器登陆MySQL时，使用各实例的Sock文件。远程主机登陆MySQL使用各实例的端口号。
[root@mysql ~]# mysqladmin -S /mysql/3306/mysql.sock -uroot password "123456"
[root@mysql ~]# mysqladmin -S /mysql/3307/mysql.sock -uroot password "654321"
[root@mysql ~]#
测试
本地登录测试
[root@mysql ~]# mysql -S /mysql/3306/mysql.sock -uroot -p123456 -e "show databases;"
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
[root@mysql ~]#
[root@mysql ~]# mysql -S /mysql/3307/mysql.sock -uroot -p654321 -e "show databases;"
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
[root@mysql ~]#
使用远程主机测试
[root@mysql ~]# mysql -S /mysql/3306/mysql.sock -uroot -p123456 -e "grant all on *.* to 'user1'@'172.16.1.%' identified by '123456';flush privileges;"
[root@mysql ~]#
[root@mysql ~]# mysql -S /mysql/3307/mysql.sock -uroot -p654321 -e "grant all on *.* to 'user2'@'172.16.1.%' identified by '654321';flush privileges;"
[root@mysql ~]#
[root@mysql ~]# ifconfig eth0|awk -F "[ :]+" 'NR==2{print $4}'
172.16.1.130
[root@mysql ~]#
[root@c-1 ~]# ifconfig eth0|awk -F "[ :]+" 'NR==2{print $4}'
172.16.1.210
[root@c-1 ~]#
[root@c-1 ~]# mysql -h172.16.1.130 -P3306 -uuser1 -p123456 -e "show databases;"
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
[root@c-1 ~]#
[root@c-1 ~]# mysql -h172.16.1.130 -P3307 -uuser2 -p654321 -e "show databases;"
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
[root@c-1 ~]#
关闭各实例
[root@mysql ~]# /mysql/3306/mysql stop
Stopping MySQL ..
Enter password:
[root@mysql ~]# /mysql/3307/mysql stop
Stopping MySQL ..
Enter password:
[root@mysql ~]#
[root@mysql ~]# lsof -i :3306
[root@mysql ~]# lsof -i :3307
