摘要:本文主要向大家介绍了MySQL数据库的MySQL多实例部署案例,通过具体的内容向大家展现,希望对大家学习MySQL数据库有所帮助。
本文主要向大家介绍了MySQL数据库的MySQL多实例部署案例,通过具体的内容向大家展现,希望对大家学习MySQL数据库有所帮助。
昨天晚上在群中和一些网友聊到了MySQL多实例的话题,最早接触MySQL多实例还是在1年前,那会我刚步入运维行业,做过MySQL多实例的相关实验,在后来的工作中也很少用到多实例,一直就淡漠了它,昨天再次提及,故此再次重新整理下以前的笔记,参考一些大牛的观点,也参考我的好友贺总(尊称)的意见,特此写下这篇文章!废话不说,切入正题....
在同一台物理服务器上部署多个实例,而多实例的部署方式简单,但是如何才能减少我们生产环境的维护成本,如何减少我们出错的机会,如何方便我们后续的迁移和清理等工作,以及如何借助多实例绑定的方式提高服务器的CPU资源利用率.
什么情况下我们会考虑一台物理服务器上部署多个实例,大致有以下几种情况:
u采用了数据伪分布式架构的原因,而项目启动初期又不一定有那多的用户量,为此先一组物理数据库服务器,但部署多个实例,方便后续迁移;
u为规避mysql对SMP架构不支持的缺陷,使用多实例绑定处理器的办法(NUMA处理器必须支持,不过现在大部分处理器都支持的!),把不同的数据库分配到不同的实例上提供数据服务;
u一台物理数据库服务器支撑多个数据库的数据服务,为提高mysql复制的从机的恢复效率,采用多实例部署;
u已经为双主复制的mysql数据库服务器架构,想部分重要业务的数据多一份异地机房的热备份,而mysql复制暂不支持多主的复制模式,且不给用户提供服务,为有效控制成本,会考虑异地机房部署一台性能超好的物理服务器,甚至外加磁盘柜的方式,为此也会部署多实例;
u传统游戏行业的MMO/MMORPG,以及Web Game,每一个服都对应一个数据库,而可能要做很多数据查询和数据订正的工作,为减少维护而出错的概率,也可能采用多实例部署的方式,按区的概念分配数据库;
下面是具体的搭建细节!
首先说明下MySQL的运行平台:
CentOS 5.8 x86_64
MySQL-5.5.25
准备的软件列表:
mysql-5.5.25.tar.gz
cmake-2.8.4.tar.gz
libunwind-1.0.1.tar.gz
gperftools-2.0.tar.gz
1. 安装Tcmalloc 优化加速mysql
还有几点需要说明的:
a. 配置文件中写了两个mysqld节点,也就是启用了两个实例!要是有多个实例,就可以在配置文件加相应的mysqld节点,也可以给定每个实例单个配置,但是个人推荐用一个配置文件。
b. 配置文件中参数比较多,可能有些版本不同导致有些参数不适用,在后面启动时可能会报错,请大家多看错误日志!基本上通过查看错误日志能解决90%以上的问题,这也是排除故障的最基础的方法,大家应该养成这样的习惯。
c. 错误日志不能完全理解为,错误日志记录的全是错误日志,它记录了错误信息、警告信息、服务启动状态信息等,这是大部分网友容易误解的!
d. 这里的配置文件内容仅供参考,自己可以参考安装目录support-files目录下相关配置文件,根据自己的生产需求来选择配置文件。
配置文件中对于多实例部分参数说明:
u[mysqld_multi] 节点下相关参数
user参数:该参数为mysqld_multi命令配置一个统一默认的管理帐号,能够统一管理旗下所有mysqld服务节点的运行、管理、检查等相关信息,若在此设置的话,命令方式执行时没制定就使用此参数的值;
password参数:该参数是对应user的密码,但是密码信息我们一般都不写到配置文件中,以避免泄漏,而是执行命令的时候再输入;
log:该参数用于记录mysqld_multi执行命令的日志信息,以及出错信息,以便于我们查找问题的根源;
u[mysqld] 节点下相关参数
该节点作为MySQL的服务节点,是整个配置文件的重点,也是难点,相关的参数调优等都集结在此节点之下。一台主机部署要多个实例,就涉及到各个实例各自的数据文件如何存放和隔离的问题,处于实例相关的目录和数据清理方便,以及维护成本更低,减少维护时的出错概率,我们采用上述样例配置文件中的目录结构,以及目录和数据文件命名方式,接下来我们主要阐述部分参数设置的意义:
user:该参数为mysqld服务启动后,mysqld使用何系统帐号运行mysqld服务的问题,不是指mysql授权表中创建的帐号,而是指操作系统级别中的帐号。我们安装mysql软件的时候,一般都会创建一个mysql帐号及为其制定用户编号,那么就可以把帐号名称mysql 或 mysql名称对应的用户编号,设置成参数user的值;
port:该参数是指定该服务节点下实例的监听端口。
其它大部分相关参数这里不再说明,请参考mysql5.5的官方手册!
4. 初使化MySQL两个实例
初使化mysqld3306实例:
/usr/local/webserver/mysql/scripts/mysql_install_db --basedir=/usr/local/webserver/mysql/ --datadir=/data/mysql/data3306/data/ --user=mysql
初使化mysqld3307实例:
/usr/local/webserver/mysql/scripts/mysql_install_db --basedir=/usr/local/webserver/mysql/ --datadir=/data/mysql/data3307/data/ --user=mysql
初使化实例分别指定不同的数据目录,要注意!
初使化出现如下字样,说明初使化成功:
when specifying MySQL privileges !
Installing MySQL system tables...
OK
Filling help tables...
OK
....
....
You can start the MySQL daemon with:
cd /usr/local/webserver/mysql/ ; /usr/local/webserver/mysql//bin/mysqld_safe &
You can test the MySQL daemon with mysql-test-run.pl
cd /usr/local/webserver/mysql//mysql-test ; perl mysql-test-run.pl
Please report any problems with the /usr/local/webserver/mysql//scripts/mysqlbug script!
很多网友在安装mysql时,启动mysql服务时,总是启动不成功,很有可能就是各网友朋友不够细心,MySQL初使化这步没成功导致!
5. 一切准备就绪,启动MySQL实例
启动命令mysqld_multi
命令执行语法:
mysqld_multi [OPTIONS] {start|stop|report} [GNR,GNR,GNR...]
或者
mysqld_multi [OPTIONS] {start|stop|report} [GNR-GNR,GNR,GNR-GNR,...]
示例:
mysqld_multi start 3306,3307
或者
mysqld_multi start 3306-3307
更多mysqld_multi命令的用法请参考官方文档和网络上其它的文档!同时也可以拷贝MySQL安装目录下support-files/mysqld_multi.server 脚本来启动多实例,这里不再说明!
mysqld_multi start 3306
mysqld_multi start 3307
启动完成后,查看各实例错误日志文件:
[root@MySQL5_10 ~]# cat /data/mysql/data3306/log/error.log 121019 20:33:37 mysqld_safe Starting mysqld daemon with databases from /data/mysql/data3306/data 121019 20:33:38 InnoDB: The InnoDB memory heap is disabled 121019 20:33:38 InnoDB: Mutexes and rw_locks use InnoDB's own implementation 121019 20:33:38 InnoDB: Compressed tables use zlib 1.2.3 121019 20:33:38 InnoDB: Initializing buffer pool, size = 100.0M 121019 20:33:38 InnoDB: Completed initialization of buffer pool InnoDB: The first specified data file /data/mysql/data3306/innodb_ts/ibdata1 did not exist: InnoDB: a new database to be created! 121019 20:33:38 InnoDB: Setting file /data/mysql/data3306/innodb_ts/ibdata1 size to 256 MB InnoDB: Database physically writes the file full: wait... InnoDB: Progress in MB: 100 200 121019 20:33:49 InnoDB: Log file /data/mysql/data3306/innodb_log/ib_logfile0 did not exist: new to be created InnoDB: Setting log file /data/mysql/data3306/innodb_log/ib_logfile0 size to 128 MB InnoDB: Database physically writes the file full: wait... InnoDB: Progress in MB: 100 121019 20:33:55 InnoDB: Log file /data/mysql/data3306/innodb_log/ib_logfile1 did not exist: new to be created InnoDB: Setting log file /data/mysql/data3306/innodb_log/ib_logfile1 size to 128 MB InnoDB: Database physically writes the file full: wait... InnoDB: Progress in MB: 100 InnoDB: Doublewrite buffer not found: creating new InnoDB: Doublewrite buffer created InnoDB: 127 rollback segment(s) active. InnoDB: Creating foreign key constraint system tables InnoDB: Foreign key constraint system tables created 121019 20:34:01 InnoDB: Waiting for the background threads to start 121019 20:34:02 InnoDB: 1.1.6 started; log sequence number 0 121019 20:34:03 [Note] Event Scheduler: Loaded 0 events 121019 20:34:03 [Note] /usr/local/webserver/mysql/bin/mysqld: ready for connections. Version: '5.5.12-log' socket: '/data/mysql/data3306/sock/mysql.sock' port: 3306 Source distribution [root@MySQL5_10 ~]# cat /data/mysql/data3307/log/error.log 121019 20:44:47 mysqld_safe mysqld from pid file /data/mysql/data3307/sock/mysql.pid ended 121019 20:47:22 mysqld_safe Starting mysqld daemon with databases from /data/mysql/data3307/data 121019 20:47:22 InnoDB: The InnoDB memory heap is disabled 121019 20:47:22 InnoDB: Mutexes and rw_locks use InnoDB's own implementation 121019 20:47:22 InnoDB: Compressed tables use zlib 1.2.3 121019 20:47:22 InnoDB: Initializing buffer pool, size = 100.0M 121019 20:47:22 InnoDB: Completed initialization of buffer pool InnoDB: The first specified data file /data/mysql/data3307/innodb_ts/ibdata1 did not exist: InnoDB: a new database to be created! 121019 20:47:22 InnoDB: Setting file /data/mysql/data3307/innodb_ts/ibdata1 size to 256 MB InnoDB: Database physically writes the file full: wait... InnoDB: Progress in MB: 100 200 121019 20:47:33 InnoDB: Log file /data/mysql/data3307/innodb_log/ib_logfile0 did not exist: new to be created InnoDB: Setting log file /data/mysql/data3307/innodb_log/ib_logfile0 size to 128 MB InnoDB: Database physically writes the file full: wait... InnoDB: Progress in MB: 100 121019 20:47:40 InnoDB: Log file /data/mysql/data3307/innodb_log/ib_logfile1 did not exist: new to be created InnoDB: Setting log file /data/mysql/data3307/innodb_log/ib_logfile1 size to 128 MB InnoDB: Database physically writes the file full: wait... InnoDB: Progress in MB: 100 InnoDB: Doublewrite buffer not found: creating new InnoDB: Doublewrite buffer created InnoDB: 127 rollback segment(s) active. InnoDB: Creating foreign key constraint system tables InnoDB: Foreign key constraint system tables created 121019 20:47:46 InnoDB: Waiting for the background threads to start 121019 20:47:47 InnoDB: 1.1.6 started; log sequence number 0 121019 20:47:47 [Note] Recovering after a crash using /data/mysql/data3307/binlog/mysql-bin3307 121019 20:47:47 [Note] Starting crash recovery... 121019 20:47:47 [Note] Crash recovery finished. 121019 20:47:47 [Note] Event Scheduler: Loaded 0 events 121019 20:47:47 [Note] /usr/local/webserver/mysql/bin/mysqld: ready for connections. Version: ' 5.5.12-log' socket: '/data/mysql/data3307/sock/mysql.sock' port: 3307 Source distribution 下面再看看mysqld_multi日志: [root@MySQL5_10 ~]# cat /data/mysql/multi.log Starting MySQL servers 121019 20:33:37 mysqld_safe Logging to '/data/mysql/data3306/log/error.log'. 121019 20:33:37 mysqld_safe Starting mysqld daemon with databases from /data/mysql/data3306/data mysqld_multi log file version 2.16; run: 五 10月 19 20:35:27 2012 Starting MySQL servers 121019 20:47:22 mysqld_safe Logging to '/data/mysql/data3307/log/error.log'. 121019 20:47:22 mysqld_safe Starting mysqld daemon with databases from /data/mysql/data3307/data 出现上面信息,说明多实例启动没有问题! 也可通过查看端口信息: [root@MySQL5_10 ~]# lsof -i:3306 COMMAND PID USER FD TYPE DEVICE SIZE NODE NAME mysqld 3463 mysql 13u IPv4 9377 TCP *:mysql (LISTEN) [root@MySQL5_10 ~]# lsof -i:3307 COMMAND PID USER FD TYPE DEVICE SIZE NODE NAME mysqld 9674 mysql 13u IPv4 20787 TCP *:opsession-prxy (LISTEN) [root@MySQL5_10 ~]# ps aux |grep mysqld |grep -v grep
显示结果略.........
................
由于51CTO博客不可以发超过8万字,所以把ps的结果省略了,不然可以看到启动的参数都是mysqld节点下面设置的相关参数,都能在这里显示出来!
我们还可以查看下各实例的监听端口:
[root@MySQL5_10 ~]# netstat -ntlp |grep mysql tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 3463/mysqld tcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 9674/mysqld 客户端登录各实例: [root@MySQL5_10 ~]# mysql -S /data/mysql/data3306/sock/mysql.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.5.25-log Source distribution Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. (root:MySQL5_10@qzhijun:10:30:46)[(none)]>show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.03 sec) (root:MySQL5_10@qzhijun:10:31:07)[(none)]> \q [root@MySQL5_10 ~]# mysql -S /data/mysql/data3307/sock/mysql.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.5.25-log Source distribution Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. (root:MySQL5_10@qzhijun:10:31:34)[(none)]>
一个简单和多实例到此OK........
以上就介绍了MySQL的相关知识,希望对MySQL有兴趣的朋友有所帮助。了解更多内容,请关注职坐标数据库MySQL频道!
您输入的评论内容中包含违禁敏感词
我知道了
请输入正确的手机号码
请输入正确的验证码
您今天的短信下发次数太多了,明天再试试吧!
我们会在第一时间安排职业规划师联系您!
您也可以联系我们的职业规划师咨询:
版权所有 职坐标-一站式IT培训就业服务领导者 沪ICP备13042190号-4
上海海同信息科技有限公司 Copyright ©2015 www.zhizuobiao.com,All Rights Reserved.
沪公网安备 31011502005948号