mysql创建多实例,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