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
- [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
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' IDENTIFIED BY 'password';
- 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
来源: http://www.bubuko.com/infodetail-2708967.html