Chinaunix首页 | 论坛 | 博客
  • 博客访问: 71648
  • 博文数量: 22
  • 博客积分: 0
  • 博客等级: 民兵
  • 技术积分: 134
  • 用 户 组: 普通用户
  • 注册时间: 2015-04-27 22:31
个人简介

梦想是要有的,万一实现了呢!

文章分类

全部博文(22)

文章存档

2015年(22)

分类: Mysql/postgreSQL

2015-05-13 12:39:15

在实际的开发过程中,可能会需要在一台服务器上部署多个MYSQL实例,
那建议使用MYSQL官方的解决方案 mysqld_multi.


1、安装好MySQL
这个过程比较简单,就不赘述了。


2、编辑 my.cof:   
本例中比较简单,详细内容参考链接
------------------------------------------------------
[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
user = root
password = 123456
[mysqld3306]
datadir = /usr/local/mysql_3306
port = 3306
socket = /tmp/mysql_3306.sock
log-bin=/usr/local/mysql_3306/mysql-bin
binlog_format=mixed
binlog_cache_size = 32M
expire_logs_days = 30
[mysqld3307]
datadir = /usr/local/mysql_3307
port = 3307
socket = /tmp/mysql_3307.sock
log-bin=/usr/local/mysql_3307/mysql-bin
binlog_format=mixed
binlog_cache_size = 32M
expire_logs_days = 3
------------------------------------------------------


3、创建数据目录
mkdir /usr/local/mysql_3306
mkdir /usr/local/mysql_3307


4、初始化DB
/usr/local/mysql/bin/mysql_install_db --datadir=/usr/local/mysql_3306 --user=mysql
/usr/local/mysql/bin/mysql_install_db --datadir=/usr/local/mysql_3307 --user=mysql
chown mysql:mysql /usr/local/mysql_3306
chown mysql:mysql /usr/local/mysql_3307


5、开启MySQL
mysqld_multi start 3306
mysqld_multi start 3307


查看是否成功:
[root@Gypsy_111 mysql_3306]# netstat -lnpt
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address               Foreign Address             State       


PID/Program name
tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      
2575/mysqld
tcp        0      0 0.0.0.0:3307                0.0.0.0:*                   LISTEN      
2461/mysqld
tcp        0      0 0.0.0.0:21                  0.0.0.0:*                   LISTEN      
1063/vsftpd
tcp        0      0 0.0.0.0:22                  0.0.0.0:*                   LISTEN      1052/sshd
tcp        0      0 127.0.0.1:25                0.0.0.0:*                   LISTEN      
1290/master
tcp        0      0 :::80                       :::*                        LISTEN      
1310/httpd
tcp        0      0 :::22                       :::*                        LISTEN      1052/sshd
tcp        0      0 ::1:25                      :::*                        LISTEN      
1290/master
[root@Gypsy_111 mysql_3306]#


[root@Gypsy_111 ~]# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is running






6、设置root密码

mysqladmin -uroot password 'mysql3306' -S /tmp/mysql_3306.sock
mysqladmin -uroot password 'mysql3307' -S /tmp/mysql_3307.sock


进入MySQL:
mysql -uroot -pmysql3306 -S /tmp/mysql_3306.sock
mysql -uroot -pmysql3307 -S /tmp/mysql_3307.sock


7、至于如何关闭每个实例


第一种方法:
理论上用 mysqld_multi stop 3306 就可以
但是实验证明关闭不了,不知道为什么?




第二种方法:进入数据库,授权一个可以关闭本实例的用户
mysql -uroot -pmysql3306 -S /tmp/mysql_3306.sock
GRANT SHUTDOWN ON *.* TO 'admin'@'localhost' IDENTIFIED BY '3306init0';
flush privileges;


mysql -uroot -pmysql3307 -S /tmp/mysql_3307.sock
GRANT SHUTDOWN ON *.* TO 'admin'@'localhost' IDENTIFIED BY '3307init0';
flush privileges;


***************************************************************************************
mysql> select user,host from mysql.user;
+-------+------------+
| user  | host       |
+-------+------------+
| root  | 127.0.0.1  |
|       | Gypsy\_111 |
| root  | Gypsy\_111 |
|       | localhost  |
| admin | localhost  |
| root  | localhost  |
+-------+------------+
6 rows in set (0.00 sec)

mysql> show grants for admin@localhost;
+------------------------------------------------------------------------------------------------
-----------------+
| Grants for admin@localhost                                                                      
                |
+------------------------------------------------------------------------------------------------
-----------------+
| GRANT SHUTDOWN ON *.* TO 'admin'@'localhost' IDENTIFIED BY PASSWORD 

'*F070332A4C00A902B30F07178BDF0AAE4F813B42' |
+------------------------------------------------------------------------------------------------
-----------------+
1 row in set (0.00 sec)
***************************************************************************************

然后:
mysqladmin -uadmin -p3306init0 -S /tmp/mysql_3306.sock shutdown
mysqladmin -uadmin -p3307init0 -S /tmp/mysql_3307.sock shutdown

这样就成功关闭了。


针对 mysqld_multi stop 3306 这种方式为什么关闭不了,请读者多多指教!

参考:

http://blog.chinaunix.net/uid-25760152-id-3472603.html

阅读(1114) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~