mysql实践-单机多实例

最近在学习mycat源码,有单台云主机上要跑多个实例的需求，参考了以下的文章
https://blog.csdn.net/clevercode/article/details/47610619
http://blog.51cto.com/lizhenliang/1636016

实现步骤如下:
1.安装cmake
2.下载mysql-5.5.27
3.编译
cd mysql-5.5.27
cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DSYSCONFDIR=/usr/local/mysql/etc \
-DMYSQL_DATADIR=/usr/local/mysql/data
make && make install
mkdir /usr/local/mysql/etc
cp support-files/my-medium.cnf /usr/local/mysql/etc/my.cnf
chown -R mysql.mysql /usr/local/mysql
echo 'PATH=$PATH:/usr/local/mysql/bin' >> /etc/profile
source /etc/profile
mkdir /usr/local/mysql/data3306
mkdir /usr/local/mysql/data3307
mkdir /usr/local/mysql/data3308
/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


4.修改my.cnf
vim /usr/local/mysql/etc/my.cnf
注释以下内容

[client]
password = your_password
port = 3306
socket = /tmp/mysql.sock

添加以下
[mysqld_multi]
mysqld=/usr/local/mysql/bin/mysqld_safe
mysqladmin=/usr/local/mysql/bin/mysqladmin
user=root
password=Wxm123456

修改
[mysqld1]
port = 3306
socket = /tmp/mysql3306.sock
pid-file = /usr/local/mysql/data3306/mysql.pid
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data3306
log_error = /usr/local/mysql/3306_error.log
[mysqld2]
port = 3307
socket = /tmp/mysql3307.sock
pid-file = /usr/local/mysql/data3307/mysql.pid
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data3307
log_error = /usr/local/mysql/3307_error.log
[mysqld3]
port = 3308
socket = /tmp/mysql3308.sock
pid-file = /usr/local/mysql/data3308/mysql.pid
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data3308
log_error = /usr/local/mysql/3308_error.log


5.不需要密码直接登录后,配置mysql访问权限
mysql -S /tmp/mysql3306.sock -P 3306 -uroot -p
mysql -S /tmp/mysql3307.sock -P 3307 -uroot -p
mysql -S /tmp/mysql3308.sock -P 3308-uroot -p

mysql> set password=password('Wxm123456');
mysql> grant all on *.* to'root'@'%' identified by 'Wxm123456';
mysql> flush privileges;


6.启动所有mysql实例
mysqld_multi --defaults-file=/usr/local/mysql/etc/my.cnf start 1,2,3


7.启动单个mysql实例
mysqld_multi --defaults-file=/usr/local/mysql/etc/my.cnf start 1
mysqld_multi --defaults-file=/usr/local/mysql/etc/my.cnf start 2
mysqld_multi --defaults-file=/usr/local/mysql/etc/my.cnf start 3


8.查看所有实例状态
#mysqld_multi --defaults-file=/usr/local/mysql/etc/my.cnf report 1,2,3
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is running
MySQL server from group: mysqld3 is running


9.停止所有实例

ps -ef | grep mysql | awk '{print $2}' | xargs kill -9


10.停止单个实例

ps -ef | grep data3306 | awk '{print $2}' | xargs kill -9
ps -ef | grep data3307 | awk '{print $2}' | xargs kill -9
ps -ef | grep data3308 | awk '{print $2}' | xargs kill -9