Raws li ib qho ntawm cov kev tshaj tawm dawb muaj nyob hauv databases, MySQL yog qhov kev xaiv nrov rau ntau lub vev xaib. Thaum raug rau hauv internet, koj app raug rau siab phem tawm tsam. Yog tias koj lub server raug cuam tshuam, qhov zoo tshaj plaws, koj yuav tsum rov nruab daim ntawv thov; qhov phem tshaj, koj yuav poob koj cov ntaub ntawv. Tsis tas li ntawd, koj tuaj yeem tau txais nyob rau hauv qhov xwm txheej uas koj yuav tsum tau hloov pauv cov ntaub ntawv los ntawm ib lub server mus rau lwm qhov.
mysqldum yog siv rau dab tsi?
Cov cuab yeej mysqldump muaj koj them rau ob qho tib si server cuam tshuam thiab kev tsiv teb tsaws chaw. Nws txoj haujlwm tseem ceeb yog nqa MySQL database thiab muab pov tseg ua cov ntawv nyeem. Tab sis tsis muaj cov ntawv nyeem; cov ntaub ntawv yog ib txheej ntawm SQL nqe lus. Cov nqe lus no, thaum ua tiav, rov tsim kho cov ntaub ntawv rau lub xeev meej nws tau nyob rau hauv thaum lub pov tseg raug tua.
Siv mysqldump los tsim kev xa tawm ntawm cov ntaub ntawv khaws cia, lossis thaum tsiv cov ntaub ntawv mus rau tus tswv tsev tshiab. Hauv ob qho xwm txheej, cov ntawv nyeem yuav raug xa rov qab rau hauv MySQL database server. Nws yuav ua tiav tag nrho cov nqe lus SQL hauv cov ntaub ntawv, uas rov tsim kho cov ntaub ntawv rau nws lub xeev qub. Qhov no tsis siv mysqldump hais kom ua, tab sis nws yuav ua tsis tau yog tias tsis muaj qhov kev pab cuam no.
The MySQL docs sau lwm txoj hauv kev los ua thaub qab, tab sis cov no muaj qhov tsis zoo:
- Hotcopying a database los ntawm MySQL Enterprise yog ib txoj hauv kev zoo kom ua tiav cov thaub qab no - yog tias koj tsis mloog Enterprise tus nqi tag.
- Kev theej cov ntaub ntawv cov ntaub ntawv teev npe tuaj yeem ua kom yuam kev thaum tsiv mus thoob plaws kev khiav haujlwm, vim tias qhov chaw yuav txawv.
- Exporting to a delimited text file will muab cov ntsiab lus rau koj, tab sis koj yuav tsum rov tsim cov qauv.
- Koj tuaj yeem feem ntau backup databases los ntawm GUI cov kev pab cuam xws li MySQL Workbench. Tab sis qhov no yog cov txheej txheem ntawm phau ntawv; tsis yog ib yam dab tsi uas koj tuaj yeem sau lossis suav nrog hauv ib txoj haujlwm batch.
Install the mysqldump Tool
Rau Windows, tshawb xyuas peb cov lus qhia rau nruab MySQL ntawm Windows 7 (tus txheej txheem nruab yog tib yam rau Windows 10). Ntawm macOS, saib peb cov lus qhia rau nruab MySQL ntawm macOS 10.7 (dua, laus dua tab sis tseem siv tau). Cov neeg siv ntawm Ubuntu-based Linux systems tuaj yeem siv cov lus txib hauv qab no los nruab MySQL tus neeg siv khoom thiab cov khoom siv:
sudo apt nruab mysql-client
Extract a MySQL pov tseg
Ib zaug ntsia, siv mysqldump kom tau txais tag nrho cov ntaub ntawv thaub qab.
mysqldump -h [koj DB host lub npe lossis IP] -u [tus neeg siv DB lub npe] -p [lub npe database] > db_backup.sql
Ntawm no yog cov lus piav qhia ntawm tus chij siv hauv cov lus txib no:
- - h: Tus chij no yog tus tswv tsev database. Nws tuaj yeem yog lub npe tag nrho (piv txwv li, myhost.domain.com) lossis tus IP chaw nyob. Cia qhov no dawb paug yog tias koj khiav cov lus txib ntawm tib tus tswv tsev li MySQL server.
- - u: Koj tus neeg siv lub npe.
- - p: Yog tias koj ua tiav qhov kev teeb tsa MySQL kom raug, koj yuav xav tau tus password los txuas. Tus chij no tsis muaj kev sib cav qhia koj rau tus password thaum koj ua tiav cov lus txib. Qee lub sij hawm nws muaj txiaj ntsig los muab tus password ncaj qha raws li kev sib cav rau tus chij no, piv txwv li, hauv tsab ntawv thaub qab. Tab sis ntawm qhov kev ceeb toom, koj yuav tsum tsis txhob, vim yog tias ib tug neeg nkag mus rau koj lub computer, lawv tuaj yeem tau txais lo lus zais no hauv keeb kwm hais kom ua.
- > db_backup.sql: Qhov no qhia mysqldump coj nws cov zis mus rau ib daim ntawv. Nquag, cov lus txib tso tawm txhua yam rau lub console, txhais tau tias koj yuav pom ob peb nqe lus SQL ntawm qhov screen. Lub cim > funnels cov zis rau hauv cov ntawv sau npe. Yog tias tsis muaj cov ntaub ntawv no, nws raug tsim los.
Thaum nws tiav lawm, koj yuav muaj. SQL cov ntaub ntawv. Qhov no yog cov ntawv nyeem uas muaj SQL nqe lus. Koj tuaj yeem qhib nws hauv cov ntawv nyeem los tshuaj xyuas cov ntsiab lus. Nov yog ntawm kev xa tawm los ntawm WordPress database uas qhia tau hais tias cov ntaub ntawv no tau muab tso ua ke li cas.
Cov ntaub ntawv muab faib ua ntu. Thawj ntu teeb tsa lub rooj rau WordPress lus. Ntu thib ob rov tsim dua cov ntsiab lus hauv cov ntxhuav (hauv qhov piv txwv no, cov lus sau tseg). Thaum koj rov xa cov ntaub ntawv MySQL rov qab, cov lus txib ua haujlwm los ntawm cov ntaub ntawv, ua tiav cov lus, thiab rov tsim cov ntaub ntawv raws li nws tau ua.
Ntshuam ib daim ntawv pov tseg MySQL
Ua ntej koj tuaj yeem xa cov ntaub ntawv pov tseg, koj yuav xav tau cov ntaub ntawv uas twb tau tsim lawm thiab nws lub npe siv thiab tus password siv tau. Koj yuav tsum muaj tag nrho cov kev tso cai rau lub database. Koj tsis tas yuav GRANT kev tso cai, tab sis nws yooj yim dua los tso cai rau lawv txhua tus.
Kawm ntxiv txog kev tso cai database ua ntej koj hloov lub luag haujlwm kev nyab xeeb hauv koj lub database.
Txhawm rau rov xa koj cov ntaub ntawv, nkag mus rau hauv MySQL server nrog mysql hais kom ua. Ntaus siv [database name] ntawm qhov kev hais kom sai, thiab hloov lub npe ntawm cov ntaub ntawv. Nkag mus source [filename], thiab hloov lub npe ntawm cov ntaub ntawv pov tseg uas koj tau ua dhau los.
Thaum koj ua tiav, ib daim ntawv teev cov lus zoo li ceeb toom tias SQL nqe lus tau ua tiav. Ua tib zoo saib xyuas qhov ua yuam kev, tab sis yog tias koj muaj kev tso cai zoo, koj yuav tsum ua zoo.
Thaum cov txheej txheem tiav, koj yuav muaj qhov sib npaug ntawm cov ntaub ntawv qub. Txhawm rau txheeb xyuas qhov zoo sib xws ntawm cov ntaub ntawv, ua lwm qhov pov tseg tom qab sib piv ob qhov txiaj ntsig. Siv cov ntawv nyeem lossis cov cuab yeej sib txawv los sib piv ob cov ntaub ntawv.
Nws muaj ob qhov sib txawv ntawm cov ntaub ntawv no, raws li sawv cev los ntawm kab liab nyob rau sab saum toj thiab hauv qab ntawm txoj cai scrollbar. Thawj yog cov kab uas muaj cov npe database, thiab qhov no txawv vim tias cov ntaub ntawv muaj npe txawv. Qhov thib ob yog lub timestamp rau cov ntaub ntawv pov tseg. Qhov no txawv vim qhov thib ob database tau rov tsim dua tom qab thawj zaug. Txwv tsis pub, cov ntaub ntawv yog tib yam, txhais tau tias cov databases uas generated lawv zoo li.
FAQ
Koj ua li cas kho qhov yuam kev mysqldump: Kev nkag tsis pom zoo thaum siv cov rooj xauv?
Nug koj tus thawj tswj hwm database kom muab txoj cai LOCK rau koj. Yog tias qhov no tsis daws qhov teeb meem, sim ua haujlwm tib yam mysqldump hais kom ua ntxiv rau - -single-transaction tus chij, xws li [ $ mysqldump --single-transaction] [-u user] [-p DBNAME] > backup.sql
Koj puas tuaj yeem siv lo lus "qhov twg" nrog mysqldump?
Siv WHERE clause thaum tsim cov thaub qab uas tsuas yog suav nrog cov kab ua tiav cov xwm txheej tau muab. Piv txwv li, txhawm rau pov tseg cov ntaub ntawv tsuas yog los ntawm kab nrog kab id ntau dua 100, sau "mysqldump my_db_name my_table_name --where="id > 100" > my_backup.sql"
