MySQL多实例部署案例

上在群中和一些网友聊到了MySQL多实例的话题，最早接触MySQL多实例还是在1年前，那会我刚步入运维行业，做过MySQL多实例的相关实验，在后来的工作中也很少用到多实例，一直就淡漠了它，昨天再次提及，故此再次重新整理下以前的笔记，参考一些大牛的观点，也参考我的好友贺总（尊称）的意见，特此写下这篇文章！废话不说，切入正题....在同一台物理服务器上部署多个实例，而多实例的部署方式简单，但是如何才能减少我们生产环境的维护成本，如何减少我们出错的机会，如何方便我们后续的迁移和清理等工作，以及如何借助多实例绑定的方式提高服务器的CPU资源利用率.什么情况下我们会考虑一台物理服务器上部署多个实例，大致有以下几种情况：采用了数据伪分布式架构的原因，而项目启动初期又不一定有那多的用户量，为此先一组物理数据库服务器，但部署多个实例，方便后续迁移；为规避mysql对SMP架构不支持的缺陷，使用多实例绑定处理器的办法（NUMA处理器必须支持，不过现在大部分处理器都支持的！），把不同的数据库分配到不同的实例上提供数据服务；一台物理数据库服务器支撑多个数据库的数据服务，为提高mysql复制的从机的恢复效率，采用多实例部署；已经为双主复制的mysql数据库服务器架构，想部分重要业务的数据多一份异地机房的热备份，而mysql复制暂不支持多主的复制模式，且不给用户提供服务，为有效控制成本，会考虑异地机房部署一台性能超好的物理服务器，甚至外加磁盘柜的方式，为此也会部署多实例；传统游戏行业的MMO/MMORPG，以及Web Game，每一个服都对应一个数据库，而可能要做很多数据查询和数据订正的工作，为减少维护而出错的概率，也可能采用多实例部署的方式，按区的概念分配数据库；下面是具体的搭建细节！首先说明下MySQL的运行平台：CentOS 5.8 x86_64MySQL-5.5.25准备的软件列表：mysql-5.5.25.tar.gzcmake-2.8.4.tar.gzlibunwind-1.0.1.tar.gzgperftools-2.0.tar.gz1. 安装Tcmalloc 优化加速mysql64位操作系统要先安装libunwind库，32位操作系统可以不要安装:

cd /home/qiuzhijun/soft
tar zxf libunwind-1.0.1.tar.gz
cd libunwind-1.0.1
./configure
make;make install
cd ..
tar zxf gperftools-2.0.tar.gz
cd gperftools-2.0
./configure
make;make install
cd ..
echo "/usr/local/lib" > /etc/ld.so.conf.d/usr_local_lib.conf
/sbin/ldconfig
cd ..

利用TCMalloc提高mysql在高并发下的性能：

[root@MySQL5_10 ~]# ll /usr/local/lib/libtcmalloc.so
lrwxrwxrwx 1 root root 20 Jul 9 21:17 /usr/local/lib/libtcmalloc.so -> libtcmalloc.so.4.1.0
sed -i "/# executing mysqld_safe/aexport LD_PRELOAD=/usr/local/lib/libtcmalloc.so" /usr/local/webserver/mysql/bin/mysqld_safe

2.安装mysql

useradd -s /sbin/nologin -M mysql
mkdir -p /data/mysql/data330{6,7}/{sock,data,tmpdir,log,binlog, innodb_log, innodb_ts}
chown -R mysql:mysql /data/mysql
cd /home/qiuzhijun/soft
tar zxf cmake-2.8.4.tar.gz
cd cmake-2.8.4
./bootstrap
gmake
gmake install
cd ..
tar zxvf mysql-5.5.25.tar.gz
cd mysql-5.5.25/
rm -rf CMakeCache.txt
cmake -DCMAKE_INSTALL_PREFIX=/usr/local/webserver/mysql -DMYSQL_UNIX_ADDR=/tmp/mysql.sock -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DEXTRA_CHARSETS=all -DWITH_MYISAM_STORAGE_ENGINE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_READLINE=1 -DENABLED_LOCAL_INFILE=1 -DMYSQL_DATADIR=/data/mysql/data -DMYSQL_TCP_PORT=3306 -DWITH_DEBUG=0
make;make install
chown -R mysql:mysql /usr/local/webserver/mysql
ln -s /usr/local/webserver/mysql/lib/libmysqlclient.so.18 /usr/lib/libmysqlclient.so.18
echo "/usr/local/webserver/mysql/lib/mysql" >> /etc/ld.so.conf

3. 创建配置文件

#++++++++++++++++++++++++++++++++ multi ++++++++++++++++++++++++++
[mysqld_multi]
mysqld = /usr/local/webserver/mysql/bin/mysqld_safe
mysqladmin = /usr/local/webserver/mysql/bin/mysqladmin
use = root
#password =
log = /data/mysql/multi.log
[client]
#default-character-set = utf8
#++++++++++++++++++++++++++++++++++3306++++++++++++++++++++++++++
[mysqld3306]
user = mysql
port = 3306
socket = /data/mysql/data3306/sock/mysql.sock
pid-file = /data/mysql/data3306/sock/mysql.pid
datadir = /data/mysql/data3306/data
tmpdir = /data/mysql/data3306/tmpdir
big_tables
skip_external_locking
skip-name-resolve
lower_case_table_names = 1
back_log = 100
#default-storage-engine = INNODB
max_connections = 800
max_connect_errors = 100000
interactive_timeout = 172800
connect_timeout = 10
max_allowed_packet = 4M
max_heap_table_size = 128M
tmp_table_size = 128M
max_length_for_sort_data = 4096
net_buffer_length = 8K
sort_buffer_size = 8M
join_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 2M
table_cache = 1024
thread_cache_size = 64
thread_concurrency = 8
query_cache_type = 0
query_cache_size = 64M
query_cache_limit = 1M
#******************************* Logs related settings ***************************
log-error = /data/mysql/data3306/log/error.log
log_warnings
long_query_time = 1
slow_query_log
slow_query_log_file = /data/mysql/data3306/log/slow-query.log
log_queries_not_using_indexes
binlog_cache_size = 8M
max_binlog_size = 512M
log-bin = /data/mysql/data3306/binlog/mysql-bin3306
log-bin-index = /data/mysql/data3306/binlog/mysql-bin3306.index
expire_logs_days = 3
#******************************* Replication related settings **********************
server-id = 3306
report_port = 3306
report_user = repl
slave_net_timeout = 60
innodb_flush_log_at_trx_commit = 2
sync_binlog = 0
binlog-format = mixed
transaction_isolation = REPEATABLE-READ
#******************************* MyISAM Specific options ****************************
key_buffer_size = 32M
bulk_insert_buffer_size = 16M
myisam_sort_buffer_size = 64M
myisam_max_sort_file_size = 1G
myisam_repair_threads = 1
myisam_recover
#***************************** INNODB Specific options ******************************
innodb_file_per_table = 1
innodb_autoinc_lock_mode = 1
innodb_fast_shutdown = 2
innodb_additional_mem_pool_size = 64M
innodb_buffer_pool_size = 5G
innodb_data_home_dir = /data/mysql/data3306/innodb_ts
innodb_data_file_path = ibdata1:256M:autoextend
innodb_file_io_threads = 4
innodb_thread_concurrency = 0
innodb_log_buffer_size = 8M
innodb_log_file_size = 128M
#innodb_log_files_in_group = 5
innodb_log_group_home_dir = /data/mysql/data3306/innodb_log
innodb_max_dirty_pages_pct = 20
innodb_lock_wait_timeout = 120
innodb_flush_method=O_DIRECT
#++++++++++++++++++++++++++++++++++3307++++++++++++++++++++++++++
[mysqld3307]
user = mysql
port = 3307
socket = /data/mysql/data3307/sock/mysql.sock
pid-file = /data/mysql/data3307/sock/mysql.pid
datadir = /data/mysql/data3307/data
tmpdir = /data/mysql/data3307/tmpdir
big_tables
skip_external_locking
skip-name-resolve
lower_case_table_names = 1
back_log = 100
#default-storage-engine = INNODB
max_connections = 800
max_connect_errors = 100000
interactive_timeout = 172800
connect_timeout = 10
max_allowed_packet = 4M
max_heap_table_size = 128M
tmp_table_size = 128M
max_length_for_sort_data = 4096
net_buffer_length = 8K
sort_buffer_size = 8M
join_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 2M
table_cache = 1024
thread_cache_size = 64
thread_concurrency = 8
query_cache_type = 0
query_cache_size = 64M
query_cache_limit = 1M
#******************************* Logs related settings ***************************
log-error = /data/mysql/data3307/log/error.log
log_warnings
long_query_time = 1
slow_query_log
slow_query_log_file = /data/mysql/data3307/log/slow-query.log
log_queries_not_using_indexes
binlog_cache_size = 8M
max_binlog_size = 512M
log-bin = /data/mysql/data3307/binlog/mysql-bin3307
log-bin-index = /data/mysql/data3307/binlog/mysql-bin3307.index
expire_logs_days = 3
#******************************* Replication related settings **********************
server-id = 3307
report_port = 3307
report_user = repl
slave_net_timeout = 60
innodb_flush_log_at_trx_commit = 2
sync_binlog = 0
binlog-format = mixed
transaction_isolation = REPEATABLE-READ
#******************************* MyISAM Specific options ****************************
key_buffer_size = 32M
bulk_insert_buffer_size = 16M
myisam_sort_buffer_size = 64M
myisam_max_sort_file_size = 1G
myisam_repair_threads = 1
myisam_recover
#***************************** INNODB Specific options ******************************
innodb_file_per_table = 1
innodb_autoinc_lock_mode = 1
innodb_fast_shutdown = 2
innodb_additional_mem_pool_size = 64M
innodb_buffer_pool_size = 5G
innodb_data_home_dir = /data/mysql/data3307/innodb_ts
innodb_data_file_path = ibdata1:256M:autoextend
innodb_file_io_threads = 4
innodb_thread_concurrency = 0
innodb_log_buffer_size = 8M
innodb_log_file_size = 128M
#innodb_log_files_in_group = 5
innodb_log_group_home_dir = /data/mysql/data3307/innodb_log
innodb_max_dirty_pages_pct = 20
innodb_lock_wait_timeout = 120
innodb_flush_method=O_DIRECT
[mysql]
no-auto-rehash
#prompt=”\u@\h : \d \r:\m:\s>”
prompt="（\u:MySQL5_10@qzhijun:R:m:\s）[\d]> "
#tee=”/tmp/query.log”
#pager=”less -i -n -S”
max_allowed_packet = 1G
[mysqldump]
quick
max_allowed_packet = 1G
[mysqld_safe]
open-files-limit = 8192
[myisamchk]
key_buffer = 512M
sort_buffer_size = 128M
read_buffer = 8M
write_buffer = 8M
[mysqlhotcopy]
interactive-timeout

Oracle中报ora-01033:oracle initializationg or shutdown in progress错误Oracle RMAN 备份命令超详细解释相关资讯 MySQL基础教程 MySQL实例 MySQL多实例

mysqld_multi 多实例启动工具（07月25日）
MySQL数据库实例参数对比脚本（05月11日）
MySQL管理多个实例的方法（12/21/2015 13:29:47）

MySQL多实例应用配置部署指南（06月08日）
MySQL基础教程：关于varchar（N）（01月22日）
MySQL多实例配置（10/28/2015 19:56:57）

本文评论查看全部评论（0）