mysql dba系统学习（4）mysql的多实例multi启动停止

一，什么情况下我们会考虑一台物理服务器上部署多个实例，大致有以下几种情况：

1，采用了数据伪分布式架构的原因，而项目启动初期又不一定有那多的用户量，为此先一组物理数据库服务器，但部署多个实例，方便后续迁移；

2，为规避mysql对SMP架构不支持的缺陷，使用多实例绑定处理器的办法（NUMA处理器必须支持，不过现在大部分处理器都支持的！），把不同的数据库分配到不同的实例上提供数据服务；

3，一台物理数据库服务器支撑多个数据库的数据服务，为提高mysql复制的从机的恢复效率，采用多实例部署；

已经为双主复制的mysql数据库服务器架构，想部分重要业务的数据多一份异地机房的热备份，而mysql复制暂不支持多主的复制模式，且不给用户提供服务，为有效控制成本，会考虑异地机房部署一台性能超好的物理服务器，甚至外加磁盘柜的方式，为此也会部署多实例；

4，传统游戏行业的MMO/MMORPG，以及Web Game，每一个服都对应一个数据库，而可能要做很多数据查询和数据订正的工作，为减少维护而出错的概率，也可能采用多实例部署的方式，按区的概念分配数据库；

二，mysql的多实例的介绍

mysqld_multi被设计为了管理实现多个mysqld服务进程

三，配置多实例mysqld及初始化

1，创建实例的数据文件目录

[root@test4 ~]# cd /mysql/
[root@test4 mysql]# ls
data
[root@test4 mysql]# mkdir data2/
[root@test4 mysql]# mkdir data3/

2，初始化多实例mysql
[root@test4 mysql]# mysql_install_db   --datadir=/mysql/data2/  --user=mysqlInstalling

MySQL system tables...
OK

[root@test4 mysql]# mysql_install_db   --datadir=/mysql/data3/  --user=mysqlInstalling MySQL

system tables...
OKFilling help tables...
OK
To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/usr/local/mysql/bin/mysqladmin -u root password 'new-password'
/usr/local/mysql/bin/mysqladmin -u root -h test4.wolf.org password 'new-password'
Alternatively you can run:
/usr/local/mysql/bin/mysql_secure_installation
which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.
See the manual for more instructions.
You can start the MySQL daemon with:
cd /usr/local/mysql ; /usr/local/mysql/bin/mysqld_safe &
You can test the MySQL daemon with mysql-test-run.pl
cd /usr/local/mysql/mysql-test ; perl mysql-test-run.pl
Please report any problems with the /usr/local/mysql/bin/mysqlbug script!

3，配置mysqld的多实例my.cnf文件

(1)配置my.cnf文件详解

[root@test4 ~]# cat  /etc/my.cnf
[mysqld_multi]
mysqld =/usr/local/mysql/bin/mysqld_safe
mysqladmin =/usr/local/mysql/bin/mysqladmin
user =test
password =test
[client]
#password       = your_password
port            = 3306
socket          = /tmp/mysql.sock
[mysqld3306]
port            = 3306
socket          = /tmp/mysql3306.sock
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
log-bin=mysql-bin
binlog_format=mixed
datadir = /mysql/data
server-id       = 1
[mysqld3307]
port            = 3307
socket          = /tmp/mysql3307.sock
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
log-bin=mysql-bin
binlog_format=mixed
datadir = /mysql/data2
server-id       = 1
[mysqld3308]
port            = 3308
socket          = /tmp/mysql3308.sock
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
log-bin=mysql-bin
binlog_format=mixed
datadir = /mysql/data3
server-id       = 1
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout

查看本栏目更多精彩内容：http://www.bianceng.cn/database/MySQL/

（编辑：滁州站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!