MySQL 多实例


:Mr.zhou  阅读: 1,959 次

  一台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: $0 {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: $0 {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

转载请注明原文链接:http://www.z-dig.com/mysql-multiple-instances.html



正文部分到此结束