linux运维、架构之路-MySQL多实例
一、MySQL多实例介绍
一台服务器上开启多个不同的服务端口(3306,3307,3308),运行多个MySQL服务进程,共用一套MySQL安装程序,多实例MySQL在逻辑上看是各自独立的
1、多实例主从复制原理图
2、优点
①有效利用服务器资源
②节约服务器资源
③资源互相抢占问题,其中一个实例并发很高或者慢查询,其它实例也受影响
3、应用场景
①公司资金紧缺
②并发访问不是特别大的业务
③门户网站应用MySQL多实例场景,一般是从库
4、MySQL多实例常见配置方案
①多配置文件、多启动程序的方案(常用)
②单一配置文件、单一启动程序多实例方案(一个配置文件,不好管理,耦合度太高,)
二、MySQL多实例部署
1、环境
[root@MySQL ~]# cat /etc/redhat-release
CentOS release 6.9 (Final)
[root@MySQL ~]# uname -r
2.6.32-696.el6.x86_64
[root@MySQL ~]# hostname -I
172.19.5.54 172.16.1.54
2、安装MySQL
useradd -s /sbin/nologin -M mysql
cd /server/tools
tar xf mysql-5.6.36-linux-glibc2.5-x86_64.tar.gz
mv mysql-5.6.36-linux-glibc2.5-x86_64 /application/mysql-5.6.36
ln -s /application/mysql-5.6.36/ /application/mysql
chown -R mysql.mysql /application/mysql
3、配置多实例启动命令和配置文件目录
mkdir -p /data/{3306,3307}/data
4、unzip data.zip 在/下直接解压覆盖/data目录(注:data.zip是打包好的多实例配置文件、数据、启动命令)
1
2
3
4
5
6
7
8
9
10
|
[root@MySQL data]# tree . ├── 3306 │ ├── data #3306实例数据文件目录 │ ├── my.cnf #3306实例配置文件 │ └── mysql #3306实例启动命令 └── 3307 ├── data #3307实例数据文件目录 ├── my.cnf #3307实例配置文件 └── mysql #3307实例启动命令 |
5、授权mysql用户管理/data目录
chown -R mysql.mysql /data
6、授权mysql命令执行权限
find /data -type f -name "mysql"|xargs chmod +x
7、配置mysql命令启动环境变量
方法一:
ln -s /application/mysql/bin/* /usr/local/sbin/
方法二:
echo 'export PATH=/application/mysql/bin:$PATH' >>/etc/profile
source /etc/profile
8、初始化多实例数据库
cd /application/mysql/scripts/
./mysql_install_db --basedir=/application/mysql/ --datadir=/data/3306/data/ --user=mysql #初始化数据库3306
./mysql_install_db --basedir=/application/mysql/ --datadir=/data/3307/data/ --user=mysql #初始化数据库3307
9、MySQL多实例服务启动
#启动服务
/data/3306/mysql start
/data/3307/mysql start
[root@MySQL data]# netstat -lntup|grep 330
tcp 0 0 :::3306 :::* LISTEN 3848/mysqld
tcp 0 0 :::3307 :::* LISTEN 4885/mysqld
10、MySQL多实例设置密码及登录
#设置密码:
mysqladmin -uroot password 123456 -S /data/3306/mysql.sock
mysqladmin -uroot password 123456 -S /data/3307/mysql.sock
#登录数据库,指定sock
mysql -uroot -p123456 -S /data/3306/mysql.sock
mysql -uroot -p123456 -S /data/3307/mysql.sock
三、MySQL多实例配置文件
/data/3306/my.cnf [client] port = 3306 socket = /data/3306/mysql.sock [mysqld] port = 3306 socket = /data/3306/mysql.sock datadir = /data/3306/data open_files_limit = 1024 back_log = 600 max_connections = 800 max_connect_errors = 3000 table_open_cache = 512 external-locking = FALSE max_allowed_packet =8M sort_buffer_size = 1M join_buffer_size = 1M thread_cache_size = 100 thread_concurrency = 2 query_cache_size = 2M query_cache_limit = 1M query_cache_min_res_unit = 2k thread_stack = 192K tmp_table_size = 2M max_heap_table_size = 2M long_query_time = 1 pid-file = /data/3306/mysql.pid log-bin = /data/3306/mysql-bin relay-log = /data/3306/relay-bin relay-log-info-file = /data/3306/relay-log.info binlog_cache_size = 1M max_binlog_cache_size = 1M max_binlog_size = 2M expire_logs_days = 7 key_buffer_size = 16M read_buffer_size = 1M read_rnd_buffer_size = 1M bulk_insert_buffer_size = 1M lower_case_table_names = 1 skip-name-resolve slave-skip-errors = 1032,1062 replicate-ignore-db=mysql server-id = 4 |
/data/3307/my.cnf [client] port = 3307 socket = /data/3307/mysql.sock [mysqld] port = 3307 socket = /data/3307/mysql.sock datadir = /data/3307/data open_files_limit = 1024 back_log = 600 max_connections = 800 max_connect_errors = 3000 table_open_cache = 512 external-locking = FALSE max_allowed_packet =8M sort_buffer_size = 1M join_buffer_size = 1M thread_cache_size = 100 thread_concurrency = 2 query_cache_size = 2M query_cache_limit = 1M query_cache_min_res_unit = 2k thread_stack = 192K tmp_table_size = 2M max_heap_table_size = 2M long_query_time = 1 pid-file = /data/3307/mysql.pid relay-log = /data/3307/relay-bin relay-log-info-file = /data/3307/relay-log.info binlog_cache_size = 1M max_binlog_cache_size = 1M max_binlog_size = 2M expire_logs_days = 7 key_buffer_size = 16M read_buffer_size = 1M read_rnd_buffer_size = 1M bulk_insert_buffer_size = 1M lower_case_table_names = 1 skip-name-resolve slave-skip-errors = 1032,1062 replicate-ignore-db=mysql server-id = 5 |
说明:对比两个配置文件之后,可以发现除了端口、server-id不一样,3306开启了log-bin功能,这样可以做MySQL主复制
四、MySQL多实例启动命令脚本
#!/bin/sh
#init
port=3306
mysql_user="root"
mysql_pwd="123456"
CmdPath="/application/mysql/bin"
mysql_sock="/data/${port}/mysql.sock"
#startup function
function_start_mysql()
{
if [ ! -e "$mysql_sock" ];then
printf "Starting MySQL...\n"
/bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf 2>&1 > /dev/null &
else
printf "MySQL is running...\n"
exit
fi
} #stop function
function_stop_mysql()
{
if [ ! -e "$mysql_sock" ];then
printf "MySQL is stopped...\n"
exit
else
printf "Stoping MySQL...\n"
${CmdPath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S /data/${port}/mysql.sock shutdown
fi
} #restart function
function_restart_mysql()
{
printf "Restarting MySQL...\n"
function_stop_mysql
sleep 2
function_start_mysql
} case $1 in
start)
function_start_mysql
;;
stop)
function_stop_mysql
;;
restart)
function_restart_mysql
;;
*)
printf "Usage: /data/${port}/mysql {start|stop|restart}\n"
esac
五、遇到的错误
1、[ERROR] Plugin 'InnoDB' init function returned error
删除MySQL目录下的ib_logfile0和ib_logfile1两个文件,就可以解决问题了
2、5.5.32——>5.6.36配置参数不对导致多实例无法启动
table_cache——>table_open_cache = 512(5.6改名了)
六、生产环境多实例配置环境
1、一般在1-4个实例之间居多,1-2个最多,大业务占用的机器比较多,机器R510居多,CPU是E5210,48G内存,硬盘12*300G SAS,RAID10
2、内存32G,双cpu8核,硬盘6X600G,跑2-3个实例
最新文章
- C# *= 运算顺序
- ubuntu16.04部署RED5流媒体服务器
- Newtonsoft.Json之JArray, JObject, JPropertyJValue
- Zabbix探索:资产信息的妙用
- SAP-Basic:使用SCC1时提示 TA263“集团被集团副本锁定用于输入”
- [Mugeda HTML5技术教程之7]添加动画
- python核心编程-第三章-习题
- MySQL主从同步报错故障处理集锦
- easyExcel导出excel的简单使用
- C#获得指定目录床架时间、更新时间和最后访问时间等信息的代码
- hadoop集群的规划和搭建
- unity中多个门的开关动画保持独立性
- sklearn-数据预处理scale
- java多线程面试题小结
- linux下设置mysql5.7数据库远程访问
- iOS 开发笔记-Objective-C之KVC、KVO
- CUDA C Programming Guide 在线教程学习笔记 Part 13
- 关于主键的设计、primary key
- hibernate_SessionFactory_getCurrentSession_JTA简介
- JAVA学习笔记--数组初始化