mysql单机多实例multi启停_mysqld_multi部署mysql单机多实例

1.安装gcc-c++、ncurses依赖包

# yum install gcc-c++ ncurses-devel

2.安装cmake，用来编译mysql

# tar -xvf cmake-3.2.0-.tar.gz

# mv cmake-3.2.0 cmake

# mv cmake /tmp/

# cd /tmp/cmake/

# ./bootstrap

# make

# make install

# cmake --version

3.安装bison

# tar -xvf bison-3.0.tar.gz

# mv bison-3.0 bison

# mv bison /tmp

# cd /tmp/bison/

# ./cofigure

# make

# make install

# bison --version

4.创建mysql用户

# /usr/sbin/groupadd mysql

# useradd -s /sbin/nologin -g mysql -M mysql

5.编译安装

配置

# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \

-DMYSQL_UNIX_ADDR=/tmp/mysqld.sock \

-DDEFAULT_CHARSET=utf8 \

-DDEFAULT_COLLATION=utf8_general_ci \

-DEXTRA_CHARSETS=all \

-DWITH_EXTRA_CHARSETS:STRING=utf8,gbk \

-DWITH_MYISAM_STORAGE_ENGINE=1 \

-DWITH_INNOBASE_STORAGE_ENGINE=1 \

-DENABLED_LOCAL_INFILE=1 \

-DMYSQL_TCP_PORT=3306 \

-DMYSQL_USER=mysql \

生成可执行文件

# make

安装

# make install

6.创建多实例的数据文件目录

# mkdir -p /data/{3306,3307}/data/

7.初始化数据库,创建基础的数据库文件

# cd /usr/local/mysql/scripts/

#./mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/data/3306/data/

#./mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/data/3307/data/

8.配置多实例的文件权限

授权mysql用户和组管理多实例目录/data

# chown -R mysql.mysql /data

9.配置多实例的配置文件

# vim /etc/my.cnf

[mysqld_multi]

mysqld = /usr/local/mysql/bin/mysqld_safe

mysqladmin = /usr/local/mysql/bin/mysqladmin

user = multi_admin

password = multipass

[mysqld3306]

socket = /data/3306/data/mysql.sock

port = 3306

log-error = /data/3306/data/mysql.err

datadir = /data/3306/data/

pid-file = /data/3306/data/mysql.pid

log-bin = /data/3306/data/mysql-bin

relay_log = /data/3306/data/relay-bin

slow_query_log_file = /data/3306/data/slowquery.log

general_log_file = /data/3306/data/general.log

user = mysql

[mysqld3307]

socket = /data/3307/data/mysql.sock

port = 3307

log-error = /data/3307/data/mysql.err

datadir = /data/3307/data/

pid-file = /data/3307/data/mysql.pid

log-bin = /data/3307/data/mysql-bin

relay_log = /data/3307/data/relay-bin

slow_query_log_file = /data/3307/data/slowquery.log

general_log_file = /data/3307/data/general.log

user = mysql

10.配置mysql命令的环境变量

# echo 'export PATH=/usr/local/mysql/bin:$PATH' >> /etc/profile

# source /etc/profile

11.启动

查看状态

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

Reporting MySQL servers

MySQL server from group: mysqld3306 is not running

MySQL server from group: mysqld3307 is not running

启动实例

# mysqld_multi start

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

Reporting MySQL servers

MySQL server from group: mysqld3306 is running

MySQL server from group: mysqld3307 is running

12.修改的root用户的密码并且限制只能本机登陆

# mysqladmin -S /data/3306/data/mysql.sock -u root -h 'localhost' password 'XXXXXXXX'

# mysqladmin -S /data/3307/data/mysql.sock -u root -h 'localhost' password 'XXXXXXXX'

13.使用root用户登陆

# mysql -S /data/3306/data/mysql.sock -u root -p

# mysql -S /data/3307/data/mysql.sock -u root -p

14.创建关闭实例的用户

mysql> CREATE USER 'multi_admin'@'localhost' IDENTIFIED BY 'multipass';

mysql> GRANT SHUTDOWN ON *.* TO 'multi_admin'@'localhost';

14.停止实例

停止所有实例

# mysqld_multi stop

停止实例1

# mysqld_mulit stop 3306

管理多实例的话可以使用mysqlmanager实例管理器，管理器来会比较方面