Mysql 多实例
-
什么是多实例?
就是一台机器上开启多个不同的服务端口,运行多个mysql服务进程,这些服务进程通过不同的socket监听不同的服务端口来提供各自的服务
这些mysql实例共用一套mysql安装程序,使用不同(也可以相同)的my.cnf配置文,启动程序,数据库文件,在提供服务时,多实例mysql在逻辑上看来是各自独立,
优缺点:
节约服务器资源
并发高或慢查询时,整个实例消耗更多的系统资源,影响其它实例
-
配置mysql
2.1. 建立数据目录
mkdir -p /usr/local/mysql/var/{3306,3007}2.2 修改配置文件my.cnf
注意:port、server-id不能重复vim /usr/local/mysql/var/3306/my.cnf [client] port = 3306 socket = /usr/local/mysql/var/3306/mysql.sock [mysqld] datadir=/usr/local/mysql/var/3306/ port = 3306 socket =/usr/local/mysql/var/3306/mysql.sock server-id = 1 skip-name-resolve datadir=/data/mysql/3306/var/ relay-log=/data/mysql/3306/relay-bin relay-log-info-file =/data/mysql/3306/relay-log.info log-error=/data/mysql/3306/mysql.error pid-file=/data/mysql/3306/mysql.pid
-
初始化mysql
/usr/local/mysql/bin/mysql_install_db --basedir=/usr/local/mysql --datadir=/usr/local/mysql/var/3306 --user=mysql
-
启动mysql多实例
4.1. 脚本启动方式:
/usr/local/mysql/var/3306/mysqld start
将下面命令的方式写成脚本,利用case做{start|stop}命令启动方式: mysqld_safe --defaults-file=/usr/local/mysql/3306/my.cnf 4.2 创建mysql启动脚本 创建自动启动文件 vim /usr/local/mysql/var/3306/mysqld 3306的启动文件如下: #!/bin/bash mysql_port=3306 mysql_username="admin" mysql_password="password" start_mysql() { echo "Starting MySQL...\n" /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/dbdata_${mysql_port}/my.cnf 2>&1 > /dev/null & } stop_mysql() { echo "Stoping MySQL...\n" /usr/local/mysql/bin/mysqladmin -u ${mysql_username} -p${mysql_password} -S /data/dbdata_${mysql_port}/mysql.sock shutdown } restart_mysql() { echo "Restarting MySQL...\n" stop_mysql start_mysql } kill_mysql() { kill -9 $(ps -ef | grep 'bin/mysqld_safe' | grep ${mysql_port} | awk '{echo $2}') kill -9 $(ps -ef | grep 'libexec/mysqld' | grep ${mysql_port} | awk '{echo $2}') } case $1 in start) start_mysql ;; stop) stop_mysql ;; kill) kill_mysql ;; restart) function_stop_mysql function_start_mysql ;; *) echo "Usage: /data/dbdata_${mysql_port}/mysqld {start|stop|restart|kill}" ;; esac 给文件755执行权chmod 755 /usr/local/mysql/var/3306/mysqld
-
停止mysql多实例
root无密码 mysqladmin -S /usr/local/mysql/3306/mysql.sock shutdown root有密码 /usr/local/mysql/bin/mysqladmin -uroot -p123456 -s /usr/local/mysql/var/3306/mysql.sock shutdown
6.### 登录mysql多实例
/usr/local/mysql/bin/mysql -S /usr/local/mysql/3306/mysql.sock
mysql -uroot -p123456 -P 3306 -h 192.168.1.100
-
修改mysql 多实例root密码
/usr/local/mysql/bin/mysqladmin -uroot password 'password' -S /usr/local/mysql/var/3306/mysql.sock 登录测试并创建关闭mysql的帐号权限,mysqld脚本要用到! /usr/local/mysql/bin/mysql -uroot -ppassword -S /data/dbdata_3308/mysql.sock GRANT SHUTDOWN ON *.* TO 'admin'@'localhost' IDENTIFIED BY 'password'; flush privileges; 创建了admin帐号以后脚本的stop功能和restart功能就正常了!
-
添加开机启动
vi /etc/rc.local /usr/local/mysql/var/3306/mysqld start /usr/local/mysql/var/3307/mysqld start /usr/local/mysql/var/3308/mysqld start