MySQL5.6多實例部署-創(chuàng)新互聯(lián)

無論是迫于預(yù)算，亦或者是領(lǐng)導(dǎo)要求，多實例的安裝也是DBA必須掌握的技術(shù)，她的啟停和登錄方式和單實例安裝數(shù)據(jù)庫略有不同，本文記錄下如何完成MySQL5.6多實例部署。

創(chuàng)新互聯(lián)-專業(yè)網(wǎng)站定制、快速模板網(wǎng)站建設(shè)、高性價比康樂網(wǎng)站開發(fā)、企業(yè)建站全套包干低至880元,成熟完善的模板庫,直接使用。一站式康樂網(wǎng)站制作公司更省心,省錢,快速模板網(wǎng)站建設(shè)找我們，業(yè)務(wù)覆蓋康樂地區(qū)。費(fèi)用合理售后完善，10余年實體公司更值得信賴。

首先我們看一下my.cnf和單實例的區(qū)分：

[root@HE1 scripts]#
cat /etc/my.cnf
[client]
#port = 3306
#socket = /tmp/mysql.sock
#default-character-set = utf8

[mysql]
#default-character-set = utf8

[mysqld3306]
port = 3306
basedir = /usr/local/mysql
datadir = /data/mysql_3306
socket = /tmp/mysql_3306.sock
slow_query_log_file = /data/mysql_3306/slow.log
log-error = /data/mysql_3306/error.log
log-bin = /data/mysql_3306/mysql-bin
sync_binlog = 1
binlog_format = row
transaction_isolation = REPEATABLE-READ
innodb_buffer_pool_size = 100m

[mysqld3308]
port = 3308
basedir = /usr/local/mysql
datadir = /data/mysql_3308
socket = /tmp/mysql_3308.sock
slow_query_log = 1
slow_query_log_file = /data/mysql_3308/slow.log
log-error = /data/mysql_3308/error.log
long_query_time = 1
log-bin = /data/mysql_3308/mysql-bin
sync_binlog = 1
binlog_cache_size = 4M
default-storage-engine = InnoDB
binlog_format = row
transaction_isolation = REPEATABLE-READ
innodb_buffer_pool_size = 100m

[mysqld_multi]
mysqld=/usr/local/mysql/bin/mysqld_safe
mysqladmin=/usr/local/mysql/bin/mysqladmin

[mysqldump]
quick
max_allowed_packet = 32M

可以看出，多實例的my.cnf實際上就是如上所示，本文為了演示實驗環(huán)境，innodb_buffer_pool_size僅僅開了100m，真實的生產(chǎn)庫中多實例部署該參數(shù)要開大些，兩個實例該參數(shù)的值達(dá)到內(nèi)存的50%-80%都可以。

下面開始初始化我們的數(shù)據(jù)庫
首先創(chuàng)建我們的數(shù)據(jù)目錄
[root@HE1 ~]#mkdir -p /data/mysql_3306
[root@HE1 ~]#mkdir -p /data/mysql_3308
[root@HE1 ~]#echo "export PATH=$PATH:/usr/local/mysql/bin:/usr/local/mysql/lib" >>/etc/profile

進(jìn)入到mysql的scripts文件夾下對數(shù)據(jù)庫進(jìn)行初始化，這里我們對3306端口數(shù)據(jù)庫進(jìn)行初始化
[root@HE1 scripts]#./mysql_install_db --basedir=/usr/local/mysql --datadir=/data/mysql_3306 --defaults-file=/etc/my.cnf --user=mysql

這里我們對3308端口數(shù)據(jù)庫進(jìn)行初始化
[root@HE1 scripts]# ./mysql_install_db --basedir=/usr/local/mysql --datadir=/data/mysql_3308 --defaults-file=/etc/my.cnf --user=mysql

初始化完成后，我們便可以啟停數(shù)據(jù)庫了，和單實例不同，多實例采用mysqld_multi來啟停數(shù)據(jù)庫
[root@HE1 bin]# ./mysqld_multi --defaults-file=/etc/my.cnf --user=root --password=MANAGER start 3306,3308

可以利用mysqld_multi的report命令來檢測多實例的運(yùn)行狀況
1234 [root@HE1 bin]#./mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld3306 is running
MySQL server from group: mysqld3308 is running

登錄方式和單實例大體相同，不過由于多實例的存在，我們需要指定不同的端口號
[root@HE1 bin]# mysql -uroot -p -P3306 -h 192.168.1.48
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6 Server version: 5.6.16-log MySQL Community Server (GPL)

Copyright (c) 2000,
2014, 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.

mysql> show databases;
+--------------------+
| Database      |
+--------------------+
| information_schema
|
| 3306db       |
| mysql       |
| performance_schema
|
| test        |
+--------------------+
5 rows in set (0.00 sec)

當(dāng)然，利用socket文件登錄也是可以的
[root@HE1 bin]#mysql -uroot -p -S /data/mysql_3306/mysql_3306.sock
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7 Server version: 5.6.16-log MySQL Community Server (GPL)

Copyright (c) 2000,
2014, 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.

mysql> show databases;
+--------------------+
| Database      |
+--------------------+
| information_schema
|
| 3306db       |
| mysql       |
| performance_schema
|
| test        |
+--------------------+
5 rows in set (0.00 sec)

這里是登錄3308端口數(shù)據(jù)庫
[root@HE1 bin]#mysql -uroot -p -P3308 -h 192.168.1.48
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8 Server version: 5.6.16-log MySQL Community Server (GPL)

Copyright (c) 2000,
2014, 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.

Type 'help;' or '\h'
for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database      |
+--------------------+
| information_schema
|
| 3308db       |
| mysql       |
| performance_schema
|
| test        |
+--------------------+
5 rows in set (0.00sec)

mysql> quit
Bye

利用3308端口的socket文件登錄數(shù)據(jù)庫
[root@HE1 bin]#mysql -uroot -p -S /data/mysql_3308/mysql_3308.sock
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9 Server version: 5.6.16-log MySQL Community Server (GPL)

Copyright (c) 2000,
2014, 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.

mysql> show databases;
+--------------------+
| Database      |
+--------------------+
| information_schema
|
| 3308db       |
| mysql       |
| performance_schema
|
| test        |
+--------------------+
5 rows in set (0.00sec)

至此，MySQL5.6多實例部署完成。

另外有需要云服務(wù)器可以了解下創(chuàng)新互聯(lián)cdcxhl.cn，海內(nèi)外云服務(wù)器15元起步，三天無理由+7*72小時售后在線，公司持有idc許可證，提供“云服務(wù)器、裸金屬服務(wù)器、高防服務(wù)器、香港服務(wù)器、美國服務(wù)器、虛擬主機(jī)、免備案服務(wù)器”等云主機(jī)租用服務(wù)以及企業(yè)上云的綜合解決方案，具有“安全穩(wěn)定、簡單易用、服務(wù)可用性高、性價比高”等特點(diǎn)與優(yōu)勢，專為企業(yè)上云打造定制，能夠滿足用戶豐富、多元化的應(yīng)用場景需求。

網(wǎng)頁標(biāo)題：MySQL5.6多實例部署-創(chuàng)新互聯(lián)
當(dāng)前URL：http://muchs.cn/article30/cociso.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供外貿(mào)網(wǎng)站建設(shè)、網(wǎng)站策劃、用戶體驗、網(wǎng)站導(dǎo)航、品牌網(wǎng)站建設(shè)、響應(yīng)式網(wǎng)站

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)