昨天晚上在群中和一些网友聊到了MySQL多实例的话题&＃xff0c;最早接触MySQL多实例还是在1年前&＃xff0c;那会我刚步入运维行业&＃xff0c;做过MySQL多实例的相关实验&＃xff0c;在后来的工作中也很少用到多实例&＃xff0c;一直就淡漠了它&＃xff0c;昨天再次提及&＃xff0c;故此再次重新整理下以前的笔记&＃xff0c;参考一些大牛的观点&＃xff0c;也参考我的好友贺总(尊称)的意见&＃xff0c;特此写下这篇文章&＃xff01;废话不说&＃xff0c;切入正题....
在同一台物理服务器上部署多个实例&＃xff0c;而多实例的部署方式简单&＃xff0c;但是如何才能减少我们生产环境的维护成本&＃xff0c;如何减少我们出错的机会&＃xff0c;如何方便我们后续的迁移和清理等工作&＃xff0c;以及如何借助多实例绑定的方式提高服务器的CPU资源利用率.
什么情况下我们会考虑一台物理服务器上部署多个实例&＃xff0c;大致有以下几种情况&＃xff1a;
u采用了数据伪分布式架构的原因&＃xff0c;而项目启动初期又不一定有那多的用户量&＃xff0c;为此先一组物理数据库服务器&＃xff0c;但部署多个实例&＃xff0c;方便后续迁移&＃xff1b;
u为规避mysql对SMP架构不支持的缺陷&＃xff0c;使用多实例绑定处理器的办法(NUMA处理器必须支持&＃xff0c;不过现在大部分处理器都支持的&＃xff01;)&＃xff0c;把不同的数据库分配到不同的实例上提供数据服务&＃xff1b;
u一台物理数据库服务器支撑多个数据库的数据服务&＃xff0c;为提高mysql复制的从机的恢复效率&＃xff0c;采用多实例部署&＃xff1b;
u已经为双主复制的mysql数据库服务器架构&＃xff0c;想部分重要业务的数据多一份异地机房的热备份&＃xff0c;而mysql复制暂不支持多主的复制模式&＃xff0c;且不给用户提供服务&＃xff0c;为有效控制成本&＃xff0c;会考虑异地机房部署一台性能超好的物理服务器&＃xff0c;甚至外加磁盘柜的方式&＃xff0c;为此也会部署多实例&＃xff1b;
u传统游戏行业的MMO/MMORPG&＃xff0c;以及Web Game&＃xff0c;每一个服都对应一个数据库&＃xff0c;而可能要做很多数据查询和数据订正的工作&＃xff0c;为减少维护而出错的概率&＃xff0c;也可能采用多实例部署的方式&＃xff0c;按区的概念分配数据库&＃xff1b;
下面是具体的搭建细节&＃xff01;
首先说明下MySQL的运行平台&＃xff1a;CentOS 5.8 x86_64MySQL-5.5.25
准备的软件列表&＃xff1a;mysql-5.5.25.tar.gzcmake-2.8.4.tar.gzlibunwind-1.0.1.tar.gz
gperftools-2.0.tar.gz
1.安装Tcmalloc优化加速mysql
64位操作系统要先安装libunwind库&＃xff0c;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在高并发下的性能&＃xff1a;[root&＃64;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 &＃39;/# executing mysqld_safe/a\export LD_PRELOAD&＃61;/usr/local/lib/libtcmalloc.so&＃39; /usr/local/webserver/mysql/bin/mysqld_safe
2.安装mysqluseradd -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&＃61;/usr/local/webserver/mysql -DMYSQL_UNIX_ADDR&＃61;/tmp/mysql.sock -DDEFAULT_CHARSET&＃61;utf8 -DDEFAULT_COLLATION&＃61;utf8_general_ci -DEXTRA_CHARSETS&＃61;all -DWITH_MYISAM_STORAGE_ENGINE&＃61;1 -DWITH_INNOBASE_STORAGE_ENGINE&＃61;1 -DWITH_READLINE&＃61;1 -DENABLED_LOCAL_INFILE&＃61;1 -DMYSQL_DATADIR&＃61;/data/mysql/data -DMYSQL_TCP_PORT&＃61;3306 -DWITH_DEBUG&＃61;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.创建配置文件
#&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43; multi &＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;
[mysqld_multi]
mysqld &＃61; /usr/local/webserver/mysql/bin/mysqld_safe
mysqladmin &＃61; /usr/local/webserver/mysql/bin/mysqladmin
use &＃61; root
#password &＃61;
log &＃61; /data/mysql/multi.log
[client]
#default-character-set &＃61; utf8
#&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;3306&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;
[mysqld3306]
user &＃61; mysql
port &＃61; 3306
socket &＃61; /data/mysql/data3306/sock/mysql.sock
pid-file &＃61; /data/mysql/data3306/sock/mysql.pid
datadir &＃61; /data/mysql/data3306/data
tmpdir &＃61; /data/mysql/data3306/tmpdir
big_tables
skip_external_locking
skip-name-resolve
lower_case_table_names &＃61; 1
back_log &＃61; 100
#default-storage-engine &＃61; INNODB
max_connections &＃61; 800
max_connect_errors &＃61; 100000
interactive_timeout &＃61; 172800
connect_timeout &＃61; 10
max_allowed_packet &＃61; 4M
max_heap_table_size &＃61; 128M
tmp_table_size &＃61; 128M
max_length_for_sort_data &＃61; 4096
net_buffer_length &＃61; 8K
sort_buffer_size &＃61; 8M
join_buffer_size &＃61; 1M
read_buffer_size &＃61; 1M
read_rnd_buffer_size &＃61; 2M
table_cache &＃61; 1024
thread_cache_size &＃61; 64
thread_concurrency &＃61; 8
query_cache_type &＃61; 0
query_cache_size &＃61; 64M
query_cache_limit &＃61; 1M
#******************************* Logs related settings ***************************
log-error &＃61; /data/mysql/data3306/log/error.log
log_warnings
long_query_time &＃61; 1
slow_query_log
slow_query_log_file &＃61; /data/mysql/data3306/log/slow-query.log
log_queries_not_using_indexes
binlog_cache_size &＃61; 8M
max_binlog_size &＃61; 512M
log-bin &＃61; /data/mysql/data3306/binlog/mysql-bin3306
log-bin-index &＃61; /data/mysql/data3306/binlog/mysql-bin3306.index
expire_logs_days &＃61; 3
#******************************* Replication related settings **********************
server-id &＃61; 3306
report_port &＃61; 3306
report_user &＃61; repl
slave_net_timeout &＃61; 60
innodb_flush_log_at_trx_commit &＃61; 2
sync_binlog &＃61; 0
binlog-format &＃61; mixed
transaction_isolation &＃61; REPEATABLE-READ
#******************************* MyISAM Specific options ****************************
key_buffer_size &＃61; 32M
bulk_insert_buffer_size &＃61; 16M
myisam_sort_buffer_size &＃61; 64M
myisam_max_sort_file_size &＃61; 1G
myisam_repair_threads &＃61; 1
myisam_recover
#***************************** INNODB Specific options ******************************
innodb_file_per_table &＃61; 1
innodb_autoinc_lock_mode &＃61; 1
innodb_fast_shutdown &＃61; 2
innodb_additional_mem_pool_size &＃61; 64M
innodb_buffer_pool_size &＃61; 5G
innodb_data_home_dir &＃61; /data/mysql/data3306/innodb_ts
innodb_data_file_path &＃61; ibdata1:256M:autoextend
innodb_file_io_threads &＃61; 4
innodb_thread_concurrency &＃61; 0
innodb_log_buffer_size &＃61; 8M
innodb_log_file_size &＃61; 128M
#innodb_log_files_in_group &＃61; 5
innodb_log_group_home_dir &＃61; /data/mysql/data3306/innodb_log
innodb_max_dirty_pages_pct &＃61; 20
innodb_lock_wait_timeout &＃61; 120
innodb_flush_method&＃61;O_DIRECT
#&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;3307&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;
[mysqld3307]
user &＃61; mysql
port &＃61; 3307
socket &＃61; /data/mysql/data3307/sock/mysql.sock
pid-file &＃61; /data/mysql/data3307/sock/mysql.pid
datadir &＃61; /data/mysql/data3307/data
tmpdir &＃61; /data/mysql/data3307/tmpdir
big_tables
skip_external_locking
skip-name-resolve
lower_case_table_names &＃61; 1
back_log &＃61; 100
#default-storage-engine &＃61; INNODB
max_connections &＃61; 800
max_connect_errors &＃61; 100000
interactive_timeout &＃61; 172800
connect_timeout &＃61; 10
max_allowed_packet &＃61; 4M
max_heap_table_size &＃61; 128M
tmp_table_size &＃61; 128M
max_length_for_sort_data &＃61; 4096
net_buffer_length &＃61; 8K
sort_buffer_size &＃61; 8M
join_buffer_size &＃61; 1M
read_buffer_size &＃61; 1M
read_rnd_buffer_size &＃61; 2M
table_cache &＃61; 1024
thread_cache_size &＃61; 64
thread_concurrency &＃61; 8
query_cache_type &＃61; 0
query_cache_size &＃61; 64M
query_cache_limit &＃61; 1M
#******************************* Logs related settings ***************************
log-error &＃61; /data/mysql/data3307/log/error.log
log_warnings
long_query_time &＃61; 1
slow_query_log
slow_query_log_file &＃61; /data/mysql/data3307/log/slow-query.log
log_queries_not_using_indexes
binlog_cache_size &＃61; 8M
max_binlog_size &＃61; 512M
log-bin &＃61; /data/mysql/data3307/binlog/mysql-bin3307
log-bin-index &＃61; /data/mysql/data3307/binlog/mysql-bin3307.index
expire_logs_days &＃61; 3
#******************************* Replication related settings **********************
server-id &＃61; 3307
report_port &＃61; 3307
report_user &＃61; repl
slave_net_timeout &＃61; 60
innodb_flush_log_at_trx_commit &＃61; 2
sync_binlog &＃61; 0
binlog-format &＃61; mixed
transaction_isolation &＃61; REPEATABLE-READ
#******************************* MyISAM Specific options ****************************
key_buffer_size &＃61; 32M
bulk_insert_buffer_size &＃61; 16M
myisam_sort_buffer_size &＃61; 64M
myisam_max_sort_file_size &＃61; 1G
myisam_repair_threads &＃61; 1
myisam_recover
#***************************** INNODB Specific options ******************************
innodb_file_per_table &＃61; 1
innodb_autoinc_lock_mode &＃61; 1
innodb_fast_shutdown &＃61; 2
innodb_additional_mem_pool_size &＃61; 64M
innodb_buffer_pool_size &＃61; 5G
innodb_data_home_dir &＃61; /data/mysql/data3307/innodb_ts
innodb_data_file_path &＃61; ibdata1:256M:autoextend
innodb_file_io_threads &＃61; 4
innodb_thread_concurrency &＃61; 0
innodb_log_buffer_size &＃61; 8M
innodb_log_file_size &＃61; 128M
#innodb_log_files_in_group &＃61; 5
innodb_log_group_home_dir &＃61; /data/mysql/data3307/innodb_log
innodb_max_dirty_pages_pct &＃61; 20
innodb_lock_wait_timeout &＃61; 120
innodb_flush_method&＃61;O_DIRECT
[mysql]
no-auto-rehash
#prompt&＃61;”\\u&＃64;\\h : \\d \\r:\\m:\\s>”
prompt&＃61;"(\\u:MySQL5_10&＃64;qzhijun:\R:\m:\\s)[\\d]> "
#tee&＃61;”/tmp/query.log”
#pager&＃61;”less -i -n -S”
max_allowed_packet &＃61; 1G
[mysqldump]
quick
max_allowed_packet &＃61; 1G
[mysqld_safe]
open-files-limit &＃61; 8192
[myisamchk]
key_buffer &＃61; 512M
sort_buffer_size &＃61; 128M
read_buffer &＃61; 8M
write_buffer &＃61; 8M
[mysqlhotcopy]
interactive-timeout
还有几点需要说明的&＃xff1a;a.配置文件中写了两个mysqld节点&＃xff0c;也就是启用了两个实例&＃xff01;要是有多个实例&＃xff0c;就可以在配置文件加相应的mysqld节点&＃xff0c;也可以给定每个实例单个配置&＃xff0c;但是个人推荐用一个配置文件。b.配置文件中参数比较多&＃xff0c;可能有些版本不同导致有些参数不适用&＃xff0c;在后面启动时可能会报错&＃xff0c;请大家多看错误日志&＃xff01;基本上通过查看错误日志能解决90%以上的问题&＃xff0c;这也是排除故障的最基础的方法&＃xff0c;大家应该养成这样的习惯。c.错误日志不能完全理解为&＃xff0c;错误日志记录的全是错误日志&＃xff0c;它记录了错误信息、警告信息、服务启动状态信息等&＃xff0c;这是大部分网友容易误解的&＃xff01;d.这里的配置文件内容仅供参考&＃xff0c;自己可以参考安装目录support-files目录下相关配置文件&＃xff0c;根据自己的生产需求来选择配置文件。
配置文件中对于多实例部分参数说明&＃xff1a;u[mysqld_multi]节点下相关参数
user参数&＃xff1a;该参数为mysqld_multi命令配置一个统一默认的管理帐号&＃xff0c;能够统一管理旗下所有mysqld服务节点的运行、管理、检查等相关信息&＃xff0c;若在此设置的话&＃xff0c;命令方式执行时没制定就使用此参数的值&＃xff1b;
password参数&＃xff1a;该参数是对应user的密码&＃xff0c;但是密码信息我们一般都不写到配置文件中&＃xff0c;以避免泄漏&＃xff0c;而是执行命令的时候再输入&＃xff1b;
log&＃xff1a;该参数用于记录mysqld_multi执行命令的日志信息&＃xff0c;以及出错信息&＃xff0c;以便于我们查找问题的根源&＃xff1b;
u[mysqld]节点下相关参数
该节点作为MySQL的服务节点&＃xff0c;是整个配置文件的重点&＃xff0c;也是难点&＃xff0c;相关的参数调优等都集结在此节点之下。一台主机部署要多个实例&＃xff0c;就涉及到各个实例各自的数据文件如何存放和隔离的问题&＃xff0c;处于实例相关的目录和数据清理方便&＃xff0c;以及维护成本更低&＃xff0c;减少维护时的出错概率&＃xff0c;我们采用上述样例配置文件中的目录结构&＃xff0c;以及目录和数据文件命名方式&＃xff0c;接下来我们主要阐述部分参数设置的意义&＃xff1a;
user&＃xff1a;该参数为mysqld服务启动后&＃xff0c;mysqld使用何系统帐号运行mysqld服务的问题&＃xff0c;不是指mysql授权表中创建的帐号&＃xff0c;而是指操作系统级别中的帐号。我们安装mysql软件的时候&＃xff0c;一般都会创建一个mysql帐号及为其制定用户编号&＃xff0c;那么就可以把帐号名称mysql或mysql名称对应的用户编号&＃xff0c;设置成参数user的值&＃xff1b;
port&＃xff1a;该参数是指定该服务节点下实例的监听端口。其它大部分相关参数这里不再说明&＃xff0c;请参考mysql5.5的官方手册&＃xff01;
4.初使化MySQL两个实例
初使化mysqld3306实例&＃xff1a;
/usr/local/webserver/mysql/scripts/mysql_install_db --basedir&＃61;/usr/local/webserver/mysql/ --datadir&＃61;/data/mysql/data3306/data/ --user&＃61;mysql
初使化mysqld3307实例&＃xff1a;
/usr/local/webserver/mysql/scripts/mysql_install_db --basedir&＃61;/usr/local/webserver/mysql/ --datadir&＃61;/data/mysql/data3307/data/ --user&＃61;mysql
初使化实例分别指定不同的数据目录&＃xff0c;要注意&＃xff01;
初使化出现如下字样&＃xff0c;说明初使化成功&＃xff1a;
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时&＃xff0c;启动mysql服务时&＃xff0c;总是启动不成功&＃xff0c;很有可能就是各网友朋友不够细心&＃xff0c;MySQL初使化这步没成功导致&＃xff01;
5.一切准备就绪&＃xff0c;启动MySQL实例
启动命令mysqld_multi
命令执行语法&＃xff1a;
mysqld_multi [OPTIONS] {start|stop|report} [GNR,GNR,GNR...]或者mysqld_multi [OPTIONS] {start|stop|report} [GNR-GNR,GNR,GNR-GNR,...]
示例&＃xff1a;mysqld_multi start 3306,3307或者mysqld_multi start 3306-3307
更多mysqld_multi命令的用法请参考官方文档和网络上其它的文档&＃xff01;同时也可以拷贝MySQL安装目录下support-files/mysqld_multi.server脚本来启动多实例&＃xff0c;这里不再说明&＃xff01;mysqld_multi start 3306
mysqld_multi start 3307
启动完成后&＃xff0c;查看各实例错误日志文件&＃xff1a;
[root&＃64;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&＃39;s own implementation
121019 20:33:38 InnoDB: Compressed tables use zlib1.2.3
121019 20:33:38 InnoDB: Initializing buffer pool, size &＃61;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.6started; 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: &＃39;5.5.12-log&＃39; socket: &＃39;/data/mysql/data3306/sock/mysql.sock&＃39; port: 3306 Source distribution
[root&＃64;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&＃39;s own implementation
121019 20:47:22 InnoDB: Compressed tables use zlib1.2.3
121019 20:47:22 InnoDB: Initializing buffer pool, size &＃61;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.6started; 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: &＃39;
5.5.12-log&＃39; socket: &＃39;/data/mysql/data3307/sock/mysql.sock&＃39; port: 3307 Source distribution
下面再看看mysqld_multi日志&＃xff1a;
[root&＃64;MySQL5_10 ~]#cat /data/mysql/multi.log
Starting MySQL servers
121019 20:33:37 mysqld_safe Logging to &＃39;/data/mysql/data3306/log/error.log&＃39;.
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 &＃39;/data/mysql/data3307/log/error.log&＃39;.
121019 20:47:22 mysqld_safe Starting mysqld daemon with databases from /data/mysql/data3307/data
出现上面信息&＃xff0c;说明多实例启动没有问题&＃xff01;
也可通过查看端口信息&＃xff1a;
[root&＃64;MySQL5_10 ~]# lsof -i:3306
COMMAND PID USER FD TYPE DEVICE SIZE NODE NAME
mysqld 3463 mysql 13u IPv4 9377 TCP *:mysql (LISTEN)
[root&＃64;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&＃64;MySQL5_10 ~]# ps aux |grep mysqld |grep -v grep
显示结果略.........
................
由于51CTO博客不可以发超过8万字&＃xff0c;所以把ps的结果省略了&＃xff0c;不然可以看到启动的参数都是mysqld节点下面设置的相关参数&＃xff0c;都能在这里显示出来&＃xff01;
我们还可以查看下各实例的监听端口&＃xff1a;
[root&＃64;MySQL5_10 ~]# netstat -ntlp |grep mysql
tcp 0 00.0.0.0:33060.0.0.0:* LISTEN 3463/mysqld
tcp 0 00.0.0.0:33070.0.0.0:* LISTEN9674/mysqld
客户端登录各实例&＃xff1a;
[root&＃64;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 &＃39;help;&＃39; or &＃39;\h&＃39; for help. Type &＃39;\c&＃39; to clear the current input statement.
(root:MySQL5_10&＃64;qzhijun:10:30:46)[(none)]>show databases;
&＃43;--------------------&＃43;
| Database |
&＃43;--------------------&＃43;
| information_schema |
| mysql |
| performance_schema |
| test |
&＃43;--------------------&＃43;
4 rows in set (0.03 sec)
(root:MySQL5_10&＃64;qzhijun:10:31:07)[(none)]> \q
[root&＃64;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 &＃39;help;&＃39; or &＃39;\h&＃39; for help. Type &＃39;\c&＃39; to clear the current input statement.
(root:MySQL5_10&＃64;qzhijun:10:31:34)[(none)]>
一个简单和多实例到此OK........
京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有