Mysql 多实例详细配置教程
8.14 Mysql 多实例什么是多实例？
就是一台机器上开启多个不同的服务端口，运行多个mysql服务进程，这些服务进程通过不同的socket监听不同的服务端口来提供各自的服务
这些mysql实例共用一套mysql安装程序，使用不同（也可以相同）的my.cnf配置文，启动程序，数据库文件，在提供服务时，多实例mysql在逻辑上看来是各自独立，
优缺点：
节约服务器资源
并发高或慢查询时，整个实例消耗更多的系统资源，影响其它实例
8.14.1 配置mysql
[*] 建立数据目录
mkdir -p /usr/local/mysql/var/{3306,3007}
[*] 修改配置文件my.cnf
注意：port、server-id不能重复
vim /usr/local/mysql/var/3306/my.cnf
port = 3306
socket = /usr/local/mysql/var/3306/mysql.sock
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
8.14.2 初始化mysql
/usr/local/mysql/bin/mysql_install_db --basedir=/usr/local/mysql --datadir=/usr/local/mysql/var/3306 --user=mysql
8.14.3 启动mysql多实例
[*]脚本启动方式： /usr/local/mysql/var/3306/mysqld start
将下面命令的方式写成脚本，利用case做｛start|stop｝
命令启动方式：
mysqld_safe --defaults-file=/usr/local/mysql/3306/my.cnf
[*]创建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/mysqldsafe --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${mysqlpassword} -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_startmysql
;;
*)
echo "Usage: /data/dbdata${mysql_port}/mysqld {start|stop|restart|kill}"
;;
esac
给文件755执行权chmod 755 /usr/local/mysql/var/3306/mysqld
8.14.4 停止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
8.14.5 登录mysql多实例
/usr/local/mysql/bin/mysql -S /usr/local/mysql/3306/mysql.sock
mysql -uroot -p123456 -P 3306 -h 192.168.1.100
8.14.6 修改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'> flush privileges;
创建了admin帐号以后脚本的stop功能和restart功能就正常了！
8.14.7 添加开机启动
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
页:
[1]