mysql dba系統學習（4）mysql的多執行個體multi啟動停止

mysql的多執行個體配置安裝

一，什麼情況下我們會考慮一台物理伺服器上部署多個執行個體，大致有以下幾種情況：

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_safemysqladmin =/usr/local/mysql/bin/mysqladminuser =testpassword =test[client]#password       = your_passwordport            = 3306socket          = /tmp/mysql.sock[mysqld3306]port            = 3306socket          = /tmp/mysql3306.sockskip-external-lockingkey_buffer_size = 16Mmax_allowed_packet = 1Mtable_open_cache = 64sort_buffer_size = 512Knet_buffer_length = 8Kread_buffer_size = 256Kread_rnd_buffer_size = 512Kmyisam_sort_buffer_size = 8Mlog-bin=mysql-binbinlog_format=mixeddatadir = /mysql/dataserver-id       = 1[mysqld3307]port            = 3307socket          = /tmp/mysql3307.sockskip-external-lockingkey_buffer_size = 16Mmax_allowed_packet = 1Mtable_open_cache = 64sort_buffer_size = 512Knet_buffer_length = 8Kread_buffer_size = 256Kread_rnd_buffer_size = 512Kmyisam_sort_buffer_size = 8Mlog-bin=mysql-binbinlog_format=mixeddatadir = /mysql/data2server-id       = 1[mysqld3308]port            = 3308socket          = /tmp/mysql3308.sockskip-external-lockingkey_buffer_size = 16Mmax_allowed_packet = 1Mtable_open_cache = 64sort_buffer_size = 512Knet_buffer_length = 8Kread_buffer_size = 256Kread_rnd_buffer_size = 512Kmyisam_sort_buffer_size = 8Mlog-bin=mysql-binbinlog_format=mixeddatadir = /mysql/data3server-id       = 1[mysqldump]quickmax_allowed_packet = 16M[mysql]no-auto-rehash[myisamchk]key_buffer_size = 20Msort_buffer_size = 20Mread_buffer = 2Mwrite_buffer = 2M[mysqlhotcopy]interactive-timeout

查看本欄目更多精彩內容：http://www.bianceng.cnhttp://www.bianceng.cn/database/MySQL/