mysql单机多实例

安装mysql

http://download.csdn.net/detail/mchdba/7545037 下载mysql-5.6.12.tar.gz

yum install cmake -y

yum install -y ncurses-devel.x86_64

mkdir-p /usr/local/mysql

tar -xvf mysql-5.6.12.tar.gz

cd mysql-5.6.12

time cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/home/data/mysql/data -DWITH_INNOBASE_STORAGE_ENGINE=1 -DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock -DMYSQL_USER=root -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci

time make

time make install

准备文件夹

mkdir /usr/local/mysql3307

mkdir -p /home/data/mysql3307/data

mkdir -p /home/data/mysql3307/binlog

mkdir -p /home/data/mysql3307/relay_log/

mkdir /usr/local/mysql3308

mkdir -p /home/data/mysql3308/data

mkdir -p /home/data/mysql3308/binlog

mkdir -p /home/data/mysql3308/relay_log/

编辑配置

[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin =/usr/local/mysql/bin/mysqladmin
log =/home/mysql/mydata/log/mysqld_multi.log
 
[mysqld1]
socket = /usr/local/mysql3307/mysql.sock
port = 3307
pid-file = /usr/local/mysql3307/mysqld.pid
datadir = /home/data/mysql3307/data
 
log_bin=/home/data/mysql3307/binlog
server-id = 1230
relay_log =/home/data/mysql3307/relay_log/mysql-relay-bin
log_slave_updates = 1
read_only = 0
 
innodb_buffer_pool_size = 128M
innodb_flush_log_at_trx_commit = 0


[mysqld2]
socket = /usr/local/mysql3308/mysql.sock
port = 3308
pid-file = /usr/local/mysql3308/mysqld.pid
datadir = /home/data/mysql3308/data


log_bin=/home/data/mysql3308/binlog
server-id = 2230
relay_log =/home/data/mysql3308/relay_log/mysql-relay-bin
log_slave_updates = 1
read_only = 0


innodb_buffer_pool_size = 128M
innodb_flush_log_at_trx_commit = 0

初始化实例数据库

/usr/local/mysql/scripts/mysql_install_db --user=root --basedir=/usr/local/mysql --datadir=/home/data/mysql3307/data

/usr/local/mysql/scripts/mysql_install_db --user=root --basedir=/usr/local/mysql --datadir=/home/data/mysql3308/data

启动多实例

mysqld_multi --defaults-extra-file=/etc/my.cnf start 1,2

1. 在执行mysqld_multi时报以下错误：

/usr/local/mysql/bin/mysqld_multi --defaults-file=/root/multi.cnf report
WARNING: my_print_defaults command not found.
Please make sure you have this command available and
in your path. The command is available from the latest
MySQL distribution.
ABORT: Can't find command 'my_print_defaults'.
This command is available from the latest MySQL
distribution. Please make sure you have the command
in your PATH.

解决方法：

在/etc/profile中添加如下内容：

export PATH=$PATH:/usr/local/mysql/bin/

并使其生效 source /etc/profile

查看多实例

 mysqld_multi --defaults-extra-file=/etc/my.cnf report

停止多实例

mysqld_multi --defaults-extra-file=/etc/my.cnf stop 1,2

登陆各实例

mysql --socket=/usr/local/mysql3307/mysql.sock