mysql 单服务器创建多实例

mysql单服务器启动多个实例实际有两种方式

第一种方式就是使用mysql  mysqld_multi  

第二种方式就是分别使用不同的目录端口启动不同的实例

第一种方式：

1、数据库实例目录
  data3306
  data3307
  data3308


2、创建实例
/usr/local/mysql//scripts/mysql_install_db –basedir=/usr/local/mysql/ –datadir=/usr/local/mysql/data3306 –user=mysql
/usr/local/mysql//scripts/mysql_install_db –basedir=/usr/local/mysql/ –datadir=/usr/local/mysql/data3307 –user=mysql
/usr/local/mysql//scripts/mysql_install_db –basedir=/usr/local/mysql/ –datadir=/usr/local/mysql/data3308 –user=mysql


3、修改my.cnf文件修改部分


[mysqld_multi]
mysqld = /usr/local/mysql//bin/mysqld
mysqladmin = /usr/local/mysql//bin/mysqladmin
user = root
password = 123456




[mysqld3306]
port = 3306
socket = /usr/local/mysql/mysqld3306.sock
pid-file = /usr/local/mysql/mysqld3306.pid
basedir = /usr/local/mysql/
datadir = /usr/local/mysql/data3306


[mysqld3307]
port = 3307
socket = /usr/local/mysql/mysqld3307.sock
pid-file = /usr/local/mysql/mysqld3307.pid
basedir = /usr/local/mysql/
datadir = /usr/local/mysql/data3307


[mysqld3307]
port = 3307
socket = /usr/local/mysql/mysqld3308.sock
pid-file = /usr/local/mysql/mysqld3308.pid
basedir = /usr/local/mysql/
datadir = /usr/local/mysql/data3308




4、启动数据库


mysqld_multi –defaults-file=/etc/mysql/my.cnf start 3306
mysqld_multi –defaults-file=/etc/mysql/my.cnf start 3307
mysqld_multi –defaults-file=/etc/mysql/my.cnf start 3308


5、登录数据库修改root密码
mysql -S /usr/local/mysql/mysqld3306.sock
mysql -S /usr/local/mysql/mysqld3307.sock
mysql -S /usr/local/mysql/mysqld3308.sock


use mysql
UPDATE user SET password=PASSWORD('123456') WHERE user=’root’;
FLUSH PRIVILEGES;




6、关闭数据库
mysqld_multi –defaults-file=/etc/mysql/my.cnf stop 3306
mysqld_multi –defaults-file=/etc/mysql/my.cnf stop 3307
mysqld_multi –defaults-file=/etc/mysql/my.cnf stop 3308


7、全部启动/关闭
mysqld_multi –defaults-file=/etc/mysql/my.cnf start 3306-3308
mysqld_multi –defaults-file=/etc/mysql/my.cnf stop 3306-3308

第二种方式：

1，创建目录

data3307 

data3308 

2,分别添加配置未见my.cnf

并修改里面相关的参数

3.进行系统表初始化也可以从现有的复制过来一会

/usr/local/mysql//scripts/mysql_install_db –basedir=/usr/local/mysql/ –datadir=/usr/local/mysql/data3307 –user=mysql
/usr/local/mysql//scripts/mysql_install_db –basedir=/usr/local/mysql/ –datadir=/usr/local/mysql/data3308 –user=mysql

4，启动脚本修改

cp /etc/init.d/mysql.server /etc/ini.d/mysql3307.server   cp /etc/init.d/mysql.server /etc/init.d/mysql3308.server

然后修改里面的配置路径

5，分别启动数据库，启动之前相关目录权限修改为mysql:mysql

/etc/init.d/mysql.server start  /etc/init.d/mysql3307.server start  /etc/init.d/mysql3308.server start