2018-02-06 centos7 安裝Mariadb并創(chuàng)建多實(shí)例 來源ZHANGSIR.org

一、安裝

1.1 yum 安裝

[root@linux-node1?~]#?yum?install?mariadb?mariadb-server?-y

1.2 啟動(dòng)并初始化

[root@linux-node1?~]#?service?mariadb?start??

[root@linux-node1?~]#?mysql_secure_installation

NOTE:?RUNNING?ALL?PARTS?OF?THIS?SCRIPT?IS?RECOMMENDED?FOR?ALL?MariaDB

??????SERVERS?IN?PRODUCTION?USE!??PLEASE?READ?EACH?STEP?CAREFULLY!

In?order?to?log?into?MariaDB?to?secure?it,?we'll?need?the?current

password?for?the?root?user.??If?you've?just?installed?MariaDB,?and

you?haven't?set?the?root?password?yet,?the?password?will?be?blank,

so?you?should?just?press?enter?here.

Enter?current?password?for?root?(enter?for?none):?

OK,?successfully?used?password,?moving?on...

Setting?the?root?password?ensures?that?nobody?can?log?into?the?MariaDB

root?user?without?the?proper?authorisation.

Set?root?password??[Y/n]?Y

New?password:?

Re-enter?new?password:?

Password?updated?successfully!

Reloading?privilege?tables..

?...?Success!

By?default,?a?MariaDB?installation?has?an?anonymous?user,?allowing?anyone

to?log?into?MariaDB?without?having?to?have?a?user?account?created?for

them.??This?is?intended?only?for?testing,?and?to?make?the?installation

go?a?bit?smoother.??You?should?remove?them?before?moving?into?a

production?environment.

Remove?anonymous?users??[Y/n]?Y

?...?Success!

Normally,?root?should?only?be?allowed?to?connect?from?'localhost'.??This

ensures?that?someone?cannot?guess?at?the?root?password?from?the?network.

Disallow?root?login?remotely??[Y/n]?n

?...?skipping.

By?default,?MariaDB?comes?with?a?database?named?'test'?that?anyone?can

access.??This?is?also?intended?only?for?testing,?and?should?be?removed

before?moving?into?a?production?environment.

Remove?test?database?and?access?to?it??[Y/n]?n

?...?skipping.

Reloading?the?privilege?tables?will?ensure?that?all?changes?made?so?far

will?take?effect?immediately.

Reload?privilege?tables?now??[Y/n]?Y

?...?Success!

Cleaning?up...

All?done!??If?you've?completed?all?of?the?above?steps,?your?MariaDB

installation?should?now?be?secure.

Thanks?for?using?MariaDB!

1.3 檢查登錄情況

[root@linux-node1?~]#?mysql?-uroot?-p123456

Welcome?to?the?MariaDB?monitor.??Commands?end?with?;?or?\g.

Your?MariaDB?connection?id?is?7

Server?version:?5.5.52-MariaDB?MariaDB?Server

Copyright?(c)?2000,?2016,?Oracle,?MariaDB?Corporation?Ab?and?others.

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

MariaDB?[(none)]>

二章姓、創(chuàng)建多實(shí)例

2.1 創(chuàng)建目錄

[root@linux-node1?~]#?mkdir?/home/multiMysql

[root@linux-node1?~]#?mkdir?/home/multiMysql/{etc,socket,bin,datadir}

在/home目錄下創(chuàng)建multiMysql文件夾墨林，并在里面創(chuàng)建etc,socket,bin,datadir這四個(gè)文件夾備用。

現(xiàn)在我們?cè)赿atadir中創(chuàng)建3個(gè)文件夾以放置三個(gè)實(shí)例的數(shù)據(jù)文件：3307,3308,3309

[root@linux-node1?~]#?mkdir?/home/multiMysql/datadir/{3307,3308,3309}

然后用mysql_install_db來生成即將使用的多個(gè)實(shí)例的數(shù)據(jù)文件，首先需要對(duì)/home/multiMysql進(jìn)行遞歸授權(quán)防止之后的操作出現(xiàn)權(quán)限不夠的情況：

[root@linux-node1?~]#?chmod?-R?777?/home/multiMysql

2.2 初始化實(shí)例

$?mysql_install_db?--basedir=/usr?--datadir=/home/multiMysql/datadir/3307?--user=mysql?

$?mysql_install_db?--basedir=/usr?--datadir=/home/multiMysql/datadir/3308?--user=mysql?

$?mysql_install_db?--basedir=/usr?--datadir=/home/multiMysql/datadir/3309?--user=mysql

其中的參數(shù)--basedir是指mysql的二進(jìn)制文件目錄（誤？），--datadir是指即將安裝到的數(shù)據(jù)庫(kù)文件目錄海铆，如果不知道--basedir該怎么填，可以登錄進(jìn)mysql后查詢：

MariaDB?[(none)]>?show?variables?like?'%basedir%';?

+---------------+-------+

|?Variable_name?|?Value?|

+---------------+-------+

|?basedir???????|?/usr??|

+---------------+-------+

1?row?in?set?(0.01?sec)

--user是指mysql實(shí)例將使用的在linux系統(tǒng)中的用戶挣惰，最好命名為mysql卧斟，yum安裝后一般都有這個(gè)用戶殴边，如果沒有可以自主創(chuàng)建：

2.3 創(chuàng)建多實(shí)例配置文件

2.3.1 創(chuàng)建共用配置文件

$?mkdir?/home/multiMysql/etc/my.cnf.d/?

#vim?/home/multiMysql/etc/my.cnf.d/my.cnf

[mysqld]??

skip-name-resolve??

lower_case_table_names=1??

innodb_file_per_table=1??

back_log?=?50??

max_connections?=?300??

max_connect_errors?=?1000??

table_open_cache?=?2048??

max_allowed_packet?=?16M??

binlog_cache_size?=?2M??

max_heap_table_size?=?64M??

sort_buffer_size?=?2M??

join_buffer_size?=?2M??

thread_cache_size?=?64??

thread_concurrency?=?8??

query_cache_size?=?64M??

query_cache_limit?=?2M??

ft_min_word_len?=?4??

default-storage-engine?=?innodb??

thread_stack?=?192K??

transaction_isolation?=?REPEATABLE-READ??

tmp_table_size?=?64M??

log-bin=mysql-bin??

binlog_format=row

slow_query_log??

long_query_time?=?1??

server-id?=?1??

key_buffer_size?=?8M??

read_buffer_size?=?2M??

read_rnd_buffer_size?=?2M??

bulk_insert_buffer_size?=?64M??

myisam_sort_buffer_size?=?128M??

myisam_max_sort_file_size?=?10G??

myisam_repair_threads?=?1??

myisam_recover??

innodb_additional_mem_pool_size?=?16M??

innodb_buffer_pool_size?=?200M??

innodb_data_file_path?=?ibdata1:10M:autoextend??

innodb_file_io_threads?=?8??

innodb_thread_concurrency?=?16??

innodb_flush_log_at_trx_commit?=?1??

innodb_log_buffer_size?=?16M??

innodb_log_file_size?=?512M??

innodb_log_files_in_group?=?3??

innodb_max_dirty_pages_pct?=?60??

innodb_lock_wait_timeout?=?120??

[mysqldump]??

quick??

max_allowed_packet?=?256M??

[mysql]??

no-auto-rehash??

prompt=\\u@\\d?\\R:\\m>??

[myisamchk]??

key_buffer_size?=?512M??

sort_buffer_size?=?512M??

read_buffer?=?8M??

write_buffer?=?8M??

[mysqlhotcopy]??

interactive-timeout??

[mysqld_safe]??

open-files-limit?=?8192

2.3.2 創(chuàng)建三個(gè)實(shí)例的配置文件：

#3307

#vim?3307.cnf?

[client]??

port?=?3307??

socket?=?/home/multiMysql/socket/mysql3307.sock??

[mysqld]??

datadir=/home/multiMysql/datadir/3307??

port?=?3307??

server_id?=1

socket?=?/home/multiMysql/socket/mysql3307.sock??


!includedir?/home/multiMysql/etc/my.cnf.d

#3308

vim?3308.cnf

[client]??

port?=?3308??

socket?=?/home/multiMysql/socket/mysql3308.sock??

[mysqld]??

datadir=/home/multiMysql/datadir/3308

port?=?3308

socket?=?/home/multiMysql/socket/mysql3308.sock

#3309

#vim?3309.cnf

[client]??

port?=?3309

socket?=?/home/multiMysql/socket/mysql3309.sock??

[mysqld]??

datadir=/home/multiMysql/datadir/3309

port?=?3309

socket?=?/home/multiMysql/socket/mysql3309.sock

2.4 啟動(dòng)實(shí)例

$???/usr/bin/mysqld_safe?--defaults-file=/home/multiMysql/etc/3307.cnf?&

$???/usr/bin/mysqld_safe?--defaults-file=/home/multiMysql/etc/3308.cnf?&

$???/usr/bin/mysqld_safe?--defaults-file=/home/multiMysql/etc/3309.cnf?&

2.5 ?登錄查看

[root@linux-node1?etc]#?mysql?-u?root?-S?/home/multiMysql/socket/mysql3307.sock?

Welcome?to?the?MariaDB?monitor.??Commands?end?with?;?or?\g.

Your?MariaDB?connection?id?is?1

Server?version:?5.5.52-MariaDB?MariaDB?Server

Copyright?(c)?2000,?2016,?Oracle,?MariaDB?Corporation?Ab?and?others.

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

MariaDB?[(none)]>?

MariaDB?[(none)]>?\q

Bye

[root@linux-node1?etc]#?mysql?-u?root?-S?/home/multiMysql/socket/mysql3308.sock?

Welcome?to?the?MariaDB?monitor.??Commands?end?with?;?or?\g.

Your?MariaDB?connection?id?is?1

Server?version:?5.5.52-MariaDB?MariaDB?Server

Copyright?(c)?2000,?2016,?Oracle,?MariaDB?Corporation?Ab?and?others.

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

MariaDB?[(none)]>?\q

Bye

[root@linux-node1?etc]#?mysql?-u?root?-S?/home/multiMysql/socket/mysql3309.sock?

Welcome?to?the?MariaDB?monitor.??Commands?end?with?;?or?\g.

Your?MariaDB?connection?id?is?1

Server?version:?5.5.52-MariaDB?MariaDB?Server

Copyright?(c)?2000,?2016,?Oracle,?MariaDB?Corporation?Ab?and?others.

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

MariaDB?[(none)]>

2.6 創(chuàng)建密碼

MariaDB?[(none)]>?update?mysql.user?set?password=password('123456')?where?User="root"?and?Host="localhost";

MariaDB?[(none)]>?flush?privileges;

2.7 停止實(shí)例

$?mysqladmin?-uroot??-p123456?-S?/home/multiMysql/socket/mysql3307.sock?shutdown

$?mysqladmin?-uroot??-p123456?-S?/home/multiMysql/socket/mysql3308.sock?shutdown

$?mysqladmin?-uroot??-p123456?-S?/home/multiMysql/socket/mysql3309.sock?shutdown

三、啟動(dòng)腳本

#3307

#vim?/home/multiMysql/bin/mysql3307

#!/bin/bash??

mysql_port=3307

mysql_username="root"??

mysql_password="123456"??

function_start_mysql()??

{??

printf?"Starting?MySQL...\n"??

mysqld_safe?--defaults-file=/home/multiMysql/etc/${mysql_port}.cnf?2>&1?>?/dev/null?&??

}??

function_stop_mysql()??

{??

printf?"Stoping?MySQL...\n"??

mysqladmin?-u?${mysql_username}?-p${mysql_password}?-S?/home/multiMysql/socket/mysql${mysql_port}.sock?shutdown??

}??

function_restart_mysql()??

{??

printf?"Restarting?MySQL...\n"??

function_stop_mysql??

function_start_mysql??

}??

function_kill_mysql()??

{??

kill?-9?$(ps?-ef?|?grep?'bin/mysqld_safe'?|?grep?${mysql_port}?|?awk?'{printf?$2}')??

kill?-9?$(ps?-ef?|?grep?'libexec/mysqld'?|?grep?${mysql_port}?|?awk?'{printf?$2}')??

}??

case?$1?in??

start)??

function_start_mysql;;??

stop)??

function_stop_mysql;;??

kill)??

function_kill_mysql;;??

restart)??

function_stop_mysql??

function_start_mysql;;??

*)??

echo?"Usage:?/data/dbdata_${mysql_port}/mysqld?{start|stop|restart|kill}";;??

esac

#3308

#vim?/home/multiMysql/bin/mysql3308

#!/bin/bash??

mysql_port=3308??

mysql_username="root"??

mysql_password="123456"??

function_start_mysql()??

{??

printf?"Starting?MySQL...\n"??

mysqld_safe?--defaults-file=/home/multiMysql/etc/${mysql_port}.cnf?2>&1?>?/dev/null?&??

}??

function_stop_mysql()??

{??

printf?"Stoping?MySQL...\n"??

mysqladmin?-u?${mysql_username}?-p${mysql_password}?-S?/home/multiMysql/socket/mysql${mysql_port}.sock?shutdown??

}??

function_restart_mysql()??

{??

printf?"Restarting?MySQL...\n"??

function_stop_mysql??

function_start_mysql??

}??

function_kill_mysql()??

{??

kill?-9?$(ps?-ef?|?grep?'bin/mysqld_safe'?|?grep?${mysql_port}?|?awk?'{printf?$2}')??

kill?-9?$(ps?-ef?|?grep?'libexec/mysqld'?|?grep?${mysql_port}?|?awk?'{printf?$2}')??

}??

case?$1?in??

start)??

function_start_mysql;;??

stop)??

function_stop_mysql;;??

kill)??

function_kill_mysql;;??

restart)??

function_stop_mysql??

function_start_mysql;;??

*)??

echo?"Usage:?/data/dbdata_${mysql_port}/mysqld?{start|stop|restart|kill}";;??

esac

#3309

#vim?/home/multiMysql/bin/mysql3309

#!/bin/bash??

mysql_port=3309??

mysql_username="root"??

mysql_password="123456"??

function_start_mysql()??

{??

printf?"Starting?MySQL...\n"??

mysqld_safe?--defaults-file=/home/multiMysql/etc/${mysql_port}.cnf?2>&1?>?/dev/null?&??

}??

function_stop_mysql()??

{??

printf?"Stoping?MySQL...\n"??

mysqladmin?-u?${mysql_username}?-p${mysql_password}?-S?/home/multiMysql/socket/mysql${mysql_port}.sock?shutdown??

}??

function_restart_mysql()??

{??

printf?"Restarting?MySQL...\n"??

function_stop_mysql??

function_start_mysql??

}??

function_kill_mysql()??

{??

kill?-9?$(ps?-ef?|?grep?'bin/mysqld_safe'?|?grep?${mysql_port}?|?awk?'{printf?$2}')??

kill?-9?$(ps?-ef?|?grep?'libexec/mysqld'?|?grep?${mysql_port}?|?awk?'{printf?$2}')??

}??

case?$1?in??

start)??

function_start_mysql;;??

stop)??

function_stop_mysql;;??

kill)??

function_kill_mysql;;??

restart)??

function_stop_mysql??

function_start_mysql;;??

*)??

echo?"Usage:?/data/dbdata_${mysql_port}/mysqld?{start|stop|restart|kill}";;??

esac


######################################################################

$???/usr/bin/mysqld_safe?--defaults-file=/home/multiMysql/etc/3307.cnf?&

$???/usr/bin/mysqld_safe?--defaults-file=/home/multiMysql/etc/3308.cnf?&

$???/usr/bin/mysqld_safe?--defaults-file=/home/multiMysql/etc/3309.cnf?&

遇到問題

數(shù)據(jù)庫(kù)初始化后珍语，必須用mysqld_safe進(jìn)行后臺(tái)啟動(dòng)锤岸，然后通過端口號(hào)進(jìn)行登錄，修改數(shù)據(jù)庫(kù)的秘碼以及權(quán)限調(diào)整板乙。開始我直接用寫好的腳本進(jìn)行啟動(dòng)的是偷，密碼都是自己設(shè)定的肯定成功不了，看報(bào)錯(cuò)在百度也解決不了問題募逞，浪費(fèi)一天時(shí)間蛋铆，看楊哥博客仔細(xì)看才發(fā)現(xiàn)出的問題，不夠細(xì)心放接。感謝楊哥的博客非常認(rèn)真５拭ΑＩぁ咙咽！

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
  • 人面猴
    序言：七十年代末栓霜，一起剝皮案震驚了整個(gè)濱河市，隨后出現(xiàn)的幾起案子苟蹈，更是在濱河造成了極大的恐慌糊渊，老刑警劉巖，帶你破解...
    沈念sama閱讀 218,755評(píng)論 6贊 507
  • 死咒
    序言：濱河連續(xù)發(fā)生了三起死亡事件慧脱，死亡現(xiàn)場(chǎng)離奇詭異渺绒，居然都是意外死亡，警方通過查閱死者的電腦和手機(jī)菱鸥，發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 93,305評(píng)論 3贊 395
  • 救了他兩次的神仙讓他今天三更去死
    文/潘曉璐 我一進(jìn)店門芒篷，熙熙樓的掌柜王于貴愁眉苦臉地迎上來，“玉大人采缚，你說我怎么就攤上這事∧铀” “怎么了扳抽？”我有些...
    開封第一講書人閱讀 165,138評(píng)論 0贊 355
  • 道士緝兇錄：失蹤的賣姜人
    文/不壞的土叔 我叫張陵，是天一觀的道長(zhǎng)殖侵。 經(jīng)常有香客問我贸呢，道長(zhǎng)，這世上最難降的妖魔是什么拢军？ 我笑而不...
    開封第一講書人閱讀 58,791評(píng)論 1贊 295
  • ?港島之戀（遺憾婚禮）
    正文 為了忘掉前任楞陷，我火速辦了婚禮，結(jié)果婚禮上茉唉，老公的妹妹穿的比我還像新娘固蛾。我一直安慰自己结执，他們只是感情好，可當(dāng)我...
    茶點(diǎn)故事閱讀 67,794評(píng)論 6贊 392
  • 惡毒庶女頂嫁案：這布局不是一般人想出來的
    文/花漫 我一把揭開白布艾凯。 她就那樣靜靜地躺著献幔，像睡著了一般。 火紅的嫁衣襯著肌膚如雪趾诗。 梳的紋絲不亂的頭發(fā)上蜡感，一...
    開封第一講書人閱讀 51,631評(píng)論 1贊 305
  • 城市分裂傳說
    那天，我揣著相機(jī)與錄音恃泪，去河邊找鬼郑兴。 笑死，一個(gè)胖子當(dāng)著我的面吹牛贝乎，可吹牛的內(nèi)容都是我干的情连。 我是一名探鬼主播，決...
    沈念sama閱讀 40,362評(píng)論 3贊 418
  • 雙鴛鴦連環(huán)套：你想象不到人心有多黑
    文/蒼蘭香墨 我猛地睜開眼糕非，長(zhǎng)吁一口氣：“原來是場(chǎng)噩夢(mèng)啊……” “哼蒙具！你這毒婦竟也來了？” 一聲冷哼從身側(cè)響起朽肥，我...
    開封第一講書人閱讀 39,264評(píng)論 0贊 276
  • 萬榮殺人案實(shí)錄
    序言：老撾萬榮一對(duì)情侶失蹤禁筏，失蹤者是張志新（化名）和其女友劉穎，沒想到半個(gè)月后衡招，有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體篱昔，經(jīng)...
    沈念sama閱讀 45,724評(píng)論 1贊 315
  • ?護(hù)林員之死
    正文 獨(dú)居荒郊野嶺守林人離奇死亡，尸身上長(zhǎng)有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點(diǎn)故事閱讀 37,900評(píng)論 3贊 336
  • ?白月光啟示錄
    正文 我和宋清朗相戀三年始腾，在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了州刽。 大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片。...
    茶點(diǎn)故事閱讀 40,040評(píng)論 1贊 350
  • 活死人
    序言：一個(gè)原本活蹦亂跳的男人離奇死亡浪箭，死狀恐怖穗椅，靈堂內(nèi)的尸體忽然破棺而出，到底是詐尸還是另有隱情奶栖，我是刑警寧澤匹表，帶...
    沈念sama閱讀 35,742評(píng)論 5贊 346
  • ?日本核電站爆炸內(nèi)幕
    正文 年R本政府宣布，位于F島的核電站宣鄙，受9級(jí)特大地震影響袍镀，放射性物質(zhì)發(fā)生泄漏。R本人自食惡果不足惜冻晤，卻給世界環(huán)境...
    茶點(diǎn)故事閱讀 41,364評(píng)論 3贊 330
  • 男人毒藥：我在死后第九天來索命
    文/蒙蒙 一苇羡、第九天 我趴在偏房一處隱蔽的房頂上張望。 院中可真熱鬧鼻弧，春花似錦设江、人聲如沸锦茁。這莊子的主人今日做“春日...
    開封第一講書人閱讀 31,944評(píng)論 0贊 22
  • 一樁弒父案绣硝，背后竟有這般陰謀
    文/蒼蘭香墨 我抬頭看了看天上的太陽(yáng)蜻势。三九已至，卻和暖如春鹉胖，著一層夾襖步出監(jiān)牢的瞬間握玛，已是汗流浹背。 一陣腳步聲響...
    開封第一講書人閱讀 33,060評(píng)論 1贊 270
  • 情欲美人皮
    我被黑心中介騙來泰國(guó)打工甫菠， 沒想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留挠铲，地道東北人。 一個(gè)月前我還...
    沈念sama閱讀 48,247評(píng)論 3贊 371
  • 代替公主和親
    正文 我出身青樓寂诱，卻偏偏與公主長(zhǎng)得像拂苹，于是被迫代替她去往敵國(guó)和親。 傳聞我的和親對(duì)象是個(gè)殘疾皇子痰洒，可洞房花燭夜當(dāng)晚...
    茶點(diǎn)故事閱讀 44,979評(píng)論 2贊 355

推薦閱讀更多精彩內(nèi)容