如题所述
ä¸ã 使ç¨mysqlç¸å
³å½ä»¤è¿è¡ç®åçæ¬å°å¤ä»½
1 mysqlldumpå½ä»¤
mysqldump æ¯éç¨SQL级å«çå¤ä»½æºå¶ï¼å®å°æ°æ®è¡¨å¯¼æ SQL èæ¬æ件ï¼å¨ä¸åç MySQL çæ¬ä¹é´å级æ¶ç¸å¯¹æ¯è¾åéï¼è¿ä¹æ¯æ常ç¨çå¤ä»½æ¹æ³ã
ä½¿ç¨ mysqldumpè¿è¡å¤ä»½é常ç®åï¼å¦æè¦å¤ä»½æ°æ®åºâ db_backup â,使ç¨å½ä»¤ï¼
#mysqldump âu -p phpbb_db_backup > /usr/backups/mysql/db_backup2008-1-6.sql
è¿å¯ä»¥ä½¿ç¨gzipå½ä»¤å¯¹å¤ä»½æ件è¿è¡å缩ï¼
#mysqldump db_backup | gzip > /usr/backups/mysql/ db_backup2008-1-6.sql.gz (å¤ä»½åçæçsqlä¸å«å»ºåºè¯å¥ï¼)
åªå¤ä»½ä¸äºé¢ç¹æ´æ°çæ°æ®åºè¡¨ï¼
## mysqldump sample_db articles comments links > /usr/backups/mysql/sample_db.art_comm_lin.2008-1-6.sql
ä¸é¢çå½ä»¤ä¼å¤ä»½articles, comments, ålinks ä¸ä¸ªè¡¨ã
æ¢å¤æ°æ®ä½¿ç¨å½ä»¤ï¼
#mysql âu -p db_backup </usr/backups/mysql/ db_backup2008-1-6.sql
注æ使ç¨è¿ä¸ªå½ä»¤æ¶å¿ é¡»ä¿è¯æ°æ®åºæ£å¨è¿è¡ã
2 ä½¿ç¨ SOURCE è¯æ³
å ¶å®è¿ä¸æ¯æ åç SQL è¯æ³ï¼èæ¯ mysql 客æ·ç«¯æä¾çåè½ï¼ä¾å¦ï¼
# SOURCE /tmp/db_name.sql;
è¿ééè¦æå®æ件çç»å¯¹è·¯å¾ï¼å¹¶ä¸å¿ é¡»æ¯ mysqld è¿è¡ç¨æ·(ä¾å¦ nobody)ææé读åçæ件ã
3 mysqlhotcopyå¤ä»½
mysqlhotcopy åªè½ç¨äºå¤ä»½ MyISAMï¼å¹¶ä¸åªè½è¿è¡å¨ linux åUnix å NetWare ç³»ç»ä¸ãmysqlhotcopy æ¯æä¸æ¬¡æ§æ·è´å¤ä¸ªæ°æ®åºï¼åæ¶è¿æ¯ææ£å表达ã以ä¸æ¯å 个ä¾åï¼
#mysqlhotcopy -h=localhost -u=goodcjh -p=goodcjh db_name /tmp
(ææ°æ®åºç®å½ db_name æ·è´å° /tmp ä¸)
注æï¼æ³è¦ä½¿ç¨ mysqlhotcopyï¼å¿ é¡»è¦æ SELECTãRELOAD(è¦æ§è¡ FLUSH TABLES) æéï¼å¹¶ä¸è¿å¿ é¡»è¦è½å¤æ读å datadir/db_name ç®å½çæéã
è¿åæ°æ®åºæ¹æ³ï¼
mysqlhotcopy å¤ä»½åºæ¥çæ¯æ´ä¸ªæ°æ®åºç®å½ï¼ä½¿ç¨æ¶å¯ä»¥ç´æ¥æ·è´å° mysqld æå®ç ç®å½ (å¨è¿éæ¯ /usr/local/mysql/data/)ç®å½ä¸å³å¯ï¼åæ¶è¦æ³¨ææéçé®é¢ï¼å¦å¤é¦å åºå½å é¤æ°æ®åºæ§å¯æ¬å¦ä¸ä¾ï¼
# /bin/rm -rf /mysql-backup/**//*old
å ³émysql æå¡å¨ãå¤å¶æ件ãæ¥è¯¢å¯å¨mysqlæå¡å¨çä¸ä¸ªæ¥éª¤ï¼
# /etc/init.d/mysqld stop
Stopping MySQL: [ OK ]
# cp -af /mysql-backup/**//* /var/lib/mysql /
# /etc/init.d/mysqld start
Starting MySQL: [ OK ]
#chown -R nobody:nobody /usr/local/mysql/data/ (å° db_name ç®å½çå±ä¸»æ¹æ mysqld è¿è¡ç¨æ·)
äºã使ç¨ç½ç»å¤ä»½
å°MYSQLæ°æ®æ¾å¨ä¸å°è®¡ç®æºä¸æ¯ä¸å®å ¨çï¼æ以åºå½ææ°æ®å¤ä»½å°å±åç½ä¸å ¶ä»Linux计ç®æºä¸ãå设Mysqlæå¡å¨IPå°åæ¯ï¼192.168.1.3ãå±åç½ä½¿ç¨Linuxçè¿ç¨è®¡ç®æºIPå°åæ¯192.168.1.4ï¼ç±»ä¼¼äºwindowsçç½ç»å ±äº«ï¼UNIX(Linux)ç³»ç»ä¹æèªå·±çç½ç»å ±äº«ï¼é£å°±æ¯NFS(ç½ç»æ件系ç»)ï¼å¨linux客æ·ç«¯ææ¥(mount)NFSç£çå ±äº«ä¹åï¼å¿ é¡»å é 置好NFSæå¡ç«¯ãlinuxç³»ç»NFSæå¡ç«¯é ç½®æ¹æ³å¦ä¸ï¼
(1)ä¿®æ¹ /etc/exports,å¢å å ±äº«ç®å½
/export/home/sunky 192.168.1.4(rw)
/export/home/sunky1 *(rw)
/export/home/sunky2 linux-client(rw)
注ï¼/export/home/ç®å½ä¸çsunkyãsunky1ãsunky2æ¯åå¤å ±äº«çç®å½ï¼10.140.133.23ã*ãlinux-clientæ¯è¢«å 许ææ¥æ¤å ±äº«linux客æ·æºçIPå°åæ主æºåãå¦æè¦ä½¿ç¨ä¸»æºålinux-clientå¿ é¡»å¨æå¡ç«¯ä¸»æº/etc/hostsæ件éå¢å linux-client主æºipå®ä¹ãæ ¼å¼å¦ä¸ï¼
192.168.1.4 linux-client
è¥ä¿®æ¹/etc/exportæ件å¢å æ°çå ±äº«ï¼åºå åæ¢NFSæå¡ï¼åå¯å¨NFSæå¡æ¹è½ä½¿æ°å¢å çå ±äº«èµ·ä½ç¨ã使ç¨å½ä»¤exportfs -rvä¹å¯ä»¥è¾¾å°åæ ·çææãlinux客æ·ç«¯ææ¥(mount)å ¶ä»linuxç³»ç»æUNIXç³»ç»çNFSå ±äº«ãè¿éæ们å设192.168.1.4æ¯NFSæå¡ç«¯ç主æºIPå°åï¼å½ç¶è¿éä¹å¯ä»¥ä½¿ç¨ä¸»æºåï¼ä½å¿ é¡»å¨æ¬æº/etc/hostsæ件éå¢å æå¡ç«¯ipå®ä¹ã/export/home/sunky为æå¡ç«¯å ±äº«çç®å½ãå¦æ¤å°±å¯ä»¥å¨linux客æ·ç«¯éè¿/mnt/nfsæ¥è®¿é®å ¶å®linuxç³»ç»æUNIXç³»ç»ä»¥NFSæ¹å¼å ±äº«åºæ¥çæ件äºã
æMYSQLæ°æ®å¤ä»½å°ä½¿ç¨Linuxçè¿ç¨è®¡ç®æºéè¦å¨ä¸¤ç«¯é½å®è£ NFSåè®®ï¼Network File Systemï¼ï¼è¿ç¨NFS计ç®æºå®è£ NFSåè®®åè¿è¦ä¿®æ¹é ç½®æ件ï¼/etc/exports,å å ¥ä¸è¡ï¼
/usr/backups/mysql/ 192.168.1.4 (rw, no_root_squash)
表示å°/usr/backups/mysql/ç®å½å ±äº«ãè¿ä¸ªç®å½å ·æè¿ç¨rootç¨æ·è¯»åæéãä¿åNFSé ç½®æ件ï¼ç¶å使ç¨å½ä»¤ï¼
#exportfs -a âr
ç¶åéæ°å¯å¨NFSæå¡ï¼
#service nfsd start
è¿ç¨è®¡ç®æºè®¾å®åï¼å¨MYSQLæå¡å¨/mnt ç®å½ä¸å»ºç«ä¸ä¸ªbackup_shareç®å½ï¼
#mkdir /mnt/backup_share
å°è¿ç¨çLinux计ç®æºç/usr/backups/mysql/ç®å½æè½½å°MYSQLæå¡å¨ç/mnt/backup_shareç®å½ä¸ï¼
# mount -t nfs 192.168.1.4ï¼/usr/backups/mysql /mnt/backup_share
å°ç®å½æè½½è¿æ¥åï¼åªè¦è¿å ¥/mnt/backup_share ç®å½ï¼å°±çäºå°äºIPå°åï¼192.168.1.4é£é¨NFS 计ç®æºç/usr/backups/mysql ç®å½ä¸ãä¸é¢ä½¿ç¨mysqldumpæâphpbb_db_backupâå¤ä»½å°è¿ç¨è®¡ç®æºï¼
# mysqldump db_backup > /mnt/backup_share/ db_backup2008-1-6.sql
èªå¨å®æç½ç»å¤ä»½çæ¹æ³ï¼
Linux æå¡å¨ä¸çç¨åºæ¯å¤©é½å¨æ´æ° MySQL æ°æ®åºï¼äºæ¯å°±æ³èµ·åä¸ä¸ª shell èæ¬ï¼ç»å crontabï¼å®æ¶å¤ä»½æ°æ®åºã建ç«ä¸ä¸ªshellèæ¬ï¼sample_db_backup.sh
# At the very end the $(date +%F) èªå¨æ·»å å¤ä»½æ¥æ
mysqldump -u <username> -p <password> -h <hostname> sample_db > /mnt/backup_share/sample_db.$(date +%F)
#un-mount the filesystem
umount /mnt/backup_share
# mount \u2013o soft 192.168.1.4:/archive /mnt/backup_share
说æï¼mount NFSæå¡å¨çä¸ä¸ªéè¦åæ°ï¼hard ï¼ç¡¬ï¼ mountæsoftï¼è½¯ï¼mountã
硬æè½½: NFS客æ·æºä¼ä¸æçå°è¯ä¸NFSæå¡å¨çè¿æ¥ï¼å¨åå°ï¼ä¸è¬ä¸ä¼ç»åºä»»ä½æ示信æ¯ï¼ï¼ç´å°æè½½ä¸ä¸ºæ¢ã
软æè½½:ä¼å¨åå°å°è¯ä¸NFSæå¡å¨çè¿æ¥ï¼æ¯é»è®¤çè¿æ¥æ¹å¼ãå½æ¶å°é误信æ¯åç»æ¢mountå°è¯ï¼å¹¶ç»åºç¸å ³ä¿¡æ¯ã
对äºå°åºæ¯ä½¿ç¨ç¡¬æè½½è¿æ¯è½¯æè½½çé®é¢ï¼è¿ä¸»è¦åå³äºä½ 访é®ä»ä¹ä¿¡æ¯æå ³ãä¾å¦ä½ æ¯æ³å¯çNFSæå¡å¨çè§é¢æ件æ¶ï¼ä½ ç»å¯¹ä¸ä¼å¸æç±äºä¸äºæå¤çæ åµï¼å¦ç½ç»é度ä¸ä¸ååçå¾æ ¢ï¼è使系ç»è¾åºå¤§éçé误信æ¯ï¼å¦ææ¤æ¶ä½ ç¨çæ¯ç¡¬æè½½æ¹å¼çè¯ï¼ç³»ç»å°±ä¼çå¾ ï¼ç´å°è½å¤éæ°ä¸NFS æå¡å¨å»ºç«è¿æ¥ä¼ è¾ä¿¡æ¯ãå¦å¤å¦ææ¯éå ³é®æ°æ®çè¯ä¹å¯ä»¥ä½¿ç¨è½¯æè½½æ¹å¼ï¼å¦FTPä¸äºæ°æ®çï¼è¿æ ·å¨è¿ç¨æºå¨ææ¶è¿æ¥ä¸ä¸æå ³éæ¶å°±ä¸ä¼æèµ·ä½ çä¼è¯è¿ç¨ã
ä¸é¢å»ºç«èæ¬æ件æéï¼chmod +x ./sample_db_backup.sh
ç¶å使ç¨å°æ¤èæ¬å å° /etc/crontab å®æ¶ä»»å¡ä¸ï¼
01 5 * * 0 mysql /home/mysql/ sample_db_backup.sh
好äºï¼æ¯å¨æ¥åæ¨ 5:01 ç³»ç»å°±ä¼èªå¨è¿è¡ sample_db_backup.sh æ件éè¿ç½ç»å¤ä»½ MySQL æ°æ®åºäºã
ä¸ãå®æ¶æ¢å¤M y S Q Læ°æ®æ¹æ³
å¨å¯¹MySQLæ°æ®åè¡¨æ ¼ç»æè¿è¡å¤ä»½æ¶ï¼mysqldumpæ¯ä¸ä¸ªé常æç¨çå·¥å ·ãç¶èï¼é常æ åµä¸ï¼ä¸è¬ä¸å¤©åªå¤ä»½ä¸æ¬¡ï¼æè å¨ä¸ä¸ªç¹å®çé´éå¤ä»½ä¸æ¬¡ãå¦æå¨åå¤ä»½å®æçä¸æ®µæ¶é´ä»¥å æ°æ®ä¸¢å¤±ï¼é£ä¹è¿äºæ°æ®å¾æå¯è½æ æ³æ¢å¤ãæä»ä¹æ¹æ³å¯ä»¥å¯¹æ°æ®è¿è¡å®æ¶æ§å°ä¿æ¤å¢?äºå®ä¸ï¼ç°å¨æå ç§æ¹æ³é½å¯ä»¥å®ç°MySQLæ°æ®åºçå®æ¶ä¿æ¤ãè¿éä»ç»å ¶ä¸ä¸ç§ï¼å³ä½¿ç¨äºè¿å¶æ¥å¿è¿è¡æ°æ®æ¢å¤ã
1 设置äºè¿å¶æ¥å¿æ¹æ³
è¦æ³ä»äºè¿å¶æ¥å¿æ¢å¤æ°æ®ï¼ä½ éè¦ç¥éå½åäºè¿å¶æ¥å¿æ件çè·¯å¾åæ件åãä¸è¬å¯ä»¥ä»é项æ件(å³my.cnf or my.iniï¼åå³äºä½ çç³»ç»)ä¸æ¾å°è·¯å¾ãå¦ææªå å«å¨é项æ件ä¸ï¼å½æå¡å¨å¯å¨æ¶ï¼å¯ä»¥å¨å½ä»¤è¡ä¸ä»¥é项çå½¢å¼ç»åºãå¯ç¨äºè¿å¶æ¥å¿çé项为-- log-binãè¦æ³ç¡®å®å½åçäºè¿å¶æ¥å¿æ件çæ件åï¼è¾å ¥ä¸é¢çMySQLè¯å¥ï¼
# SHOW BINLOG EVENTS \G
2 æç®åçæ°æ®æ¢å¤
æ¯å¤©å¤ä»½åè¿è¡äºè¿å¶æ¥å¿çç¡®æ¯ä¸ä¸ªå¨MySQLæå¡å¨ä¸æ¢å¤æ°æ®çä¸éæ¹æ³ãæ¯å¦ï¼å¯ä»¥æ¯å¤©å¨æ·±å¤ä½¿ç¨mysqldump对æ°æ®è¿è¡å¤ä»½ï¼å¦ææ天å¨æ°æ®å¤ä»½å®æåçä¸æ®µæ¶é´éï¼ç±äºæç§åå æ°æ®ä¸¢å¤±ï¼å¯ä»¥ä½¿ç¨ä»¥ä¸æ¹æ³æ¥å¯¹å ¶è¿è¡æ¢å¤ãé¦å ï¼åæ¢MySQLæå¡å¨ï¼ç¶å使ç¨ä»¥ä¸å½ä»¤éæ°å¯å¨MySQLæå¡å¨ã该å½ä»¤å°ä¿è¯æ¯æä¸å¯ä»¥è®¿é®è¯¥æ°æ®åºæå¡å¨ç人ï¼
# /etc/init.d/mysqld stop
Stopping MySQL: [ OK ]
# mysqld --socket=/tmp/mysql_restore.sock --skip-networking
è¿éï¼ ä¸socketé项å°ä¸ºU n i x ç³»ç»å½åä¸ä¸ªä¸åçSocketæ件ãä¸æ¦æå¡å¨å¤äºç¬å æ§å¶ä¹ä¸ï¼å°±å¯ä»¥æ¾å¿å°å¯¹æ°æ®åºè¿è¡æä½ï¼èä¸ç¨æ å¿å¨è¿è¡æ°æ®æ¢å¤çè¿ç¨ä¸æç¨æ·å°è¯è®¿é®æ°æ®åºè导è´æ´å¤ç麻ç¦ãè¿è¡æ¢å¤ç第ä¸ä¸ªæ¥éª¤æ¯æ¢å¤æä¸å¤ä»½å¥½çdumpæ件ï¼
#mysql -u root -pmypwd --socket=/tmp/mysql_restore.sock < /var/backup/20080120.sql
该å½ä»¤å¯ä»¥å°æ°æ®åºçå 容æ¢å¤è³æä¸ååå®æå¤ä»½çå 容ãè¦æ¢å¤dumpæ件å建åçæ°æ®åºäºå¡å¤çï¼ å¯ä»¥ä½¿ç¨mysqlbinlogå·¥å ·ãå¦ææ¯å¤©æä¸è¿è¡å¤ä»½æä½æ¶é½å¯¹æ¥å¿è¿è¡flushæä½ï¼åå¯ä»¥ä½¿ç¨ä»¥ä¸å½ä»¤è¡å·¥å ·å°æ´ä¸ªäºè¿å¶æ¥å¿æ件è¿è¡æ¢å¤ï¼
mysqlbinlog /var/log/mysql/bin.123456 \
| mysql -u root -pmypwd --socket=/tmp/mysql_restore.sock
3 é对æä¸æ¶é®ç¹çæ¢å¤
对äºMySQL 4.1.4ï¼å¯ä»¥å¨mysqlbinlogè¯å¥ä¸éè¿--start-dateå--stop-dateé项æå®DATETIMEæ ¼å¼çèµ·æ¢æ¶é´ãå设ç¨æ·å¨2008-1-22ä¸å10ç¹æ§è¡çSQLè¯å¥å é¤äºä¸ä¸ªå¤§çæ°æ®è¡¨ï¼åå¯ä»¥ä½¿ç¨ä»¥ä¸å½ä»¤è¿è¡æ¢å¤ï¼è¦æ³æ¢å¤è¡¨åæ°æ®ï¼ä½ å¯ä»¥æ¢å¤åæä¸çå¤ä»½ï¼å¹¶è¾å ¥ï¼
#mysqlbinlog --stop-date="2008-1-22 9:59:59"
/var/log/mysql/bin.123456 |
mysql -u root -pmypwd \
--socket=/tmp/mysql_restore.sock
#mysql -u root -pmypwd
该è¯å¥å°æ¢å¤ææç»å®ä¸stop-dateæ¥æä¹åçæ°æ®ãå¦æå¨æ§è¡æSQLè¯å¥æ°å°æ¶ä¹åæåç°æ§è¡äºé误æä½ï¼é£ä¹å¯è½è¿éè¦æ¢å¤ä¹åè¾å ¥çä¸äºæ°æ®ãè¿æ¶ï¼ ä¹å¯ä»¥éè¿mysqlbinlogæ¥å®æ该åè½ï¼
#mysqlbinlog --start-date="2008-1-22 10:01:00" \
/var/log/mysql/bin.123456 \
| mysql -u root -pmypwd \
--socket=/tmp/mysql_restore.sock
#mysql -u root -pmypwd
å¨è¯¥è¡ä¸ï¼ä»ä¸å10:01ç»å½çSQLè¯å¥å°è¿è¡ãç»åæ§è¡åå¤ç转å¨æ件åmysqlbinlogç两è¡å¯ä»¥å°æææ°æ®æ¢å¤å°ä¸å10:00åä¸ç§éãä½ åºæ£æ¥æ¥å¿ä»¥ç¡®ä¿æ¶é´ç¡®åã
4 使ç¨Positionè¿è¡æ¢å¤
ä¹å¯ä»¥ä¸æå®æ¥æåæ¶é´ï¼è使ç¨mysqlbinlogçé项--start-positionå--stop-positionæ¥æå®æ¥å¿ä½ç½®ãå®ä»¬çä½ç¨ä¸èµ·æ¢æ¥é项ç¸åï¼ä¸åçæ¯ç»åºäºä»æ¥å¿èµ·çä½ç½®å·ã使ç¨æ¥å¿ä½ç½®æ¯æ´åç¡®çæ¢å¤æ¹æ³ï¼ç¹å«æ¯å½ç±äºç ´åæ§SQLè¯å¥åæ¶åç许å¤äºå¡çæ¶åãè¦æ³ç¡®å®ä½ç½®å·ï¼å¯ä»¥è¿è¡mysqlbinlog寻æ¾æ§è¡äºä¸ææçäºå¡çæ¶é´èå´ï¼ä½åºå°ç»æéæ°æåææ¬æ件以便è¿è¡æ£æ¥ãæä½å½ä»¤ä¸ºï¼
mysqlbinlog --start-date="2005-04-20 9:55:00" --stop-date="2005-04-20 10:05:00"
/var/log/mysql/bin.123456 > /tmp/mysql_restore.sql
该å½ä»¤å°å¨/tmpç®å½å建å°çææ¬æ件ï¼å°æ¾ç¤ºæ§è¡äºé误çSQLè¯å¥æ¶çSQLè¯å¥ãä½ å¯ä»¥ç¨viæè geditææ¬ç¼è¾å¨æå¼è¯¥æ件ï¼å¯»æ¾ä½ ä¸è¦æ³éå¤çè¯å¥ãå¦æäºè¿å¶æ¥å¿ä¸çä½ç½®å·ç¨äºåæ¢å继ç»æ¢å¤æä½ï¼åºè¿è¡æ³¨éãç¨log_poså ä¸ä¸ªæ°åæ¥æ è®°ä½ç½®ã使ç¨ä½ç½®å·æ¢å¤äºä»¥åçå¤ä»½æ件åï¼ä½ åºä»å½ä»¤è¡è¾å ¥ä¸é¢å 容ï¼
mysqlbinlog --stop-position="368312" /var/log/mysql/bin.123456
| mysql -u root -pmypwd
mysqlbinlog --start-position="368315" /var/log/mysql/bin.123456
| mysql -u root -pmypwd
ä¸é¢ç第1è¡å°æ¢å¤å°åæ¢ä½ç½®ä¸ºæ¢çææäºå¡ãä¸ä¸è¡å°æ¢å¤ä»ç»å®çèµ·å§ä½ç½®ç´å°äºè¿å¶æ¥å¿ç»æçææäºå¡ãå 为mysqlbinlogçè¾åºå æ¬æ¯ä¸ªSQLè¯å¥è®°å½ä¹åçSET TIMESTAMPè¯å¥ï¼æ¢å¤çæ°æ®åç¸å ³MySQLæ¥å¿å°ååºäºå¡æ§è¡çåæ¶é´ã
1 mysqlldumpå½ä»¤
mysqldump æ¯éç¨SQL级å«çå¤ä»½æºå¶ï¼å®å°æ°æ®è¡¨å¯¼æ SQL èæ¬æ件ï¼å¨ä¸åç MySQL çæ¬ä¹é´å级æ¶ç¸å¯¹æ¯è¾åéï¼è¿ä¹æ¯æ常ç¨çå¤ä»½æ¹æ³ã
ä½¿ç¨ mysqldumpè¿è¡å¤ä»½é常ç®åï¼å¦æè¦å¤ä»½æ°æ®åºâ db_backup â,使ç¨å½ä»¤ï¼
#mysqldump âu -p phpbb_db_backup > /usr/backups/mysql/db_backup2008-1-6.sql
è¿å¯ä»¥ä½¿ç¨gzipå½ä»¤å¯¹å¤ä»½æ件è¿è¡å缩ï¼
#mysqldump db_backup | gzip > /usr/backups/mysql/ db_backup2008-1-6.sql.gz (å¤ä»½åçæçsqlä¸å«å»ºåºè¯å¥ï¼)
åªå¤ä»½ä¸äºé¢ç¹æ´æ°çæ°æ®åºè¡¨ï¼
## mysqldump sample_db articles comments links > /usr/backups/mysql/sample_db.art_comm_lin.2008-1-6.sql
ä¸é¢çå½ä»¤ä¼å¤ä»½articles, comments, ålinks ä¸ä¸ªè¡¨ã
æ¢å¤æ°æ®ä½¿ç¨å½ä»¤ï¼
#mysql âu -p db_backup </usr/backups/mysql/ db_backup2008-1-6.sql
注æ使ç¨è¿ä¸ªå½ä»¤æ¶å¿ é¡»ä¿è¯æ°æ®åºæ£å¨è¿è¡ã
2 ä½¿ç¨ SOURCE è¯æ³
å ¶å®è¿ä¸æ¯æ åç SQL è¯æ³ï¼èæ¯ mysql 客æ·ç«¯æä¾çåè½ï¼ä¾å¦ï¼
# SOURCE /tmp/db_name.sql;
è¿ééè¦æå®æ件çç»å¯¹è·¯å¾ï¼å¹¶ä¸å¿ é¡»æ¯ mysqld è¿è¡ç¨æ·(ä¾å¦ nobody)ææé读åçæ件ã
3 mysqlhotcopyå¤ä»½
mysqlhotcopy åªè½ç¨äºå¤ä»½ MyISAMï¼å¹¶ä¸åªè½è¿è¡å¨ linux åUnix å NetWare ç³»ç»ä¸ãmysqlhotcopy æ¯æä¸æ¬¡æ§æ·è´å¤ä¸ªæ°æ®åºï¼åæ¶è¿æ¯ææ£å表达ã以ä¸æ¯å 个ä¾åï¼
#mysqlhotcopy -h=localhost -u=goodcjh -p=goodcjh db_name /tmp
(ææ°æ®åºç®å½ db_name æ·è´å° /tmp ä¸)
注æï¼æ³è¦ä½¿ç¨ mysqlhotcopyï¼å¿ é¡»è¦æ SELECTãRELOAD(è¦æ§è¡ FLUSH TABLES) æéï¼å¹¶ä¸è¿å¿ é¡»è¦è½å¤æ读å datadir/db_name ç®å½çæéã
è¿åæ°æ®åºæ¹æ³ï¼
mysqlhotcopy å¤ä»½åºæ¥çæ¯æ´ä¸ªæ°æ®åºç®å½ï¼ä½¿ç¨æ¶å¯ä»¥ç´æ¥æ·è´å° mysqld æå®ç ç®å½ (å¨è¿éæ¯ /usr/local/mysql/data/)ç®å½ä¸å³å¯ï¼åæ¶è¦æ³¨ææéçé®é¢ï¼å¦å¤é¦å åºå½å é¤æ°æ®åºæ§å¯æ¬å¦ä¸ä¾ï¼
# /bin/rm -rf /mysql-backup/**//*old
å ³émysql æå¡å¨ãå¤å¶æ件ãæ¥è¯¢å¯å¨mysqlæå¡å¨çä¸ä¸ªæ¥éª¤ï¼
# /etc/init.d/mysqld stop
Stopping MySQL: [ OK ]
# cp -af /mysql-backup/**//* /var/lib/mysql /
# /etc/init.d/mysqld start
Starting MySQL: [ OK ]
#chown -R nobody:nobody /usr/local/mysql/data/ (å° db_name ç®å½çå±ä¸»æ¹æ mysqld è¿è¡ç¨æ·)
äºã使ç¨ç½ç»å¤ä»½
å°MYSQLæ°æ®æ¾å¨ä¸å°è®¡ç®æºä¸æ¯ä¸å®å ¨çï¼æ以åºå½ææ°æ®å¤ä»½å°å±åç½ä¸å ¶ä»Linux计ç®æºä¸ãå设Mysqlæå¡å¨IPå°åæ¯ï¼192.168.1.3ãå±åç½ä½¿ç¨Linuxçè¿ç¨è®¡ç®æºIPå°åæ¯192.168.1.4ï¼ç±»ä¼¼äºwindowsçç½ç»å ±äº«ï¼UNIX(Linux)ç³»ç»ä¹æèªå·±çç½ç»å ±äº«ï¼é£å°±æ¯NFS(ç½ç»æ件系ç»)ï¼å¨linux客æ·ç«¯ææ¥(mount)NFSç£çå ±äº«ä¹åï¼å¿ é¡»å é 置好NFSæå¡ç«¯ãlinuxç³»ç»NFSæå¡ç«¯é ç½®æ¹æ³å¦ä¸ï¼
(1)ä¿®æ¹ /etc/exports,å¢å å ±äº«ç®å½
/export/home/sunky 192.168.1.4(rw)
/export/home/sunky1 *(rw)
/export/home/sunky2 linux-client(rw)
注ï¼/export/home/ç®å½ä¸çsunkyãsunky1ãsunky2æ¯åå¤å ±äº«çç®å½ï¼10.140.133.23ã*ãlinux-clientæ¯è¢«å 许ææ¥æ¤å ±äº«linux客æ·æºçIPå°åæ主æºåãå¦æè¦ä½¿ç¨ä¸»æºålinux-clientå¿ é¡»å¨æå¡ç«¯ä¸»æº/etc/hostsæ件éå¢å linux-client主æºipå®ä¹ãæ ¼å¼å¦ä¸ï¼
192.168.1.4 linux-client
è¥ä¿®æ¹/etc/exportæ件å¢å æ°çå ±äº«ï¼åºå åæ¢NFSæå¡ï¼åå¯å¨NFSæå¡æ¹è½ä½¿æ°å¢å çå ±äº«èµ·ä½ç¨ã使ç¨å½ä»¤exportfs -rvä¹å¯ä»¥è¾¾å°åæ ·çææãlinux客æ·ç«¯ææ¥(mount)å ¶ä»linuxç³»ç»æUNIXç³»ç»çNFSå ±äº«ãè¿éæ们å设192.168.1.4æ¯NFSæå¡ç«¯ç主æºIPå°åï¼å½ç¶è¿éä¹å¯ä»¥ä½¿ç¨ä¸»æºåï¼ä½å¿ é¡»å¨æ¬æº/etc/hostsæ件éå¢å æå¡ç«¯ipå®ä¹ã/export/home/sunky为æå¡ç«¯å ±äº«çç®å½ãå¦æ¤å°±å¯ä»¥å¨linux客æ·ç«¯éè¿/mnt/nfsæ¥è®¿é®å ¶å®linuxç³»ç»æUNIXç³»ç»ä»¥NFSæ¹å¼å ±äº«åºæ¥çæ件äºã
æMYSQLæ°æ®å¤ä»½å°ä½¿ç¨Linuxçè¿ç¨è®¡ç®æºéè¦å¨ä¸¤ç«¯é½å®è£ NFSåè®®ï¼Network File Systemï¼ï¼è¿ç¨NFS计ç®æºå®è£ NFSåè®®åè¿è¦ä¿®æ¹é ç½®æ件ï¼/etc/exports,å å ¥ä¸è¡ï¼
/usr/backups/mysql/ 192.168.1.4 (rw, no_root_squash)
表示å°/usr/backups/mysql/ç®å½å ±äº«ãè¿ä¸ªç®å½å ·æè¿ç¨rootç¨æ·è¯»åæéãä¿åNFSé ç½®æ件ï¼ç¶å使ç¨å½ä»¤ï¼
#exportfs -a âr
ç¶åéæ°å¯å¨NFSæå¡ï¼
#service nfsd start
è¿ç¨è®¡ç®æºè®¾å®åï¼å¨MYSQLæå¡å¨/mnt ç®å½ä¸å»ºç«ä¸ä¸ªbackup_shareç®å½ï¼
#mkdir /mnt/backup_share
å°è¿ç¨çLinux计ç®æºç/usr/backups/mysql/ç®å½æè½½å°MYSQLæå¡å¨ç/mnt/backup_shareç®å½ä¸ï¼
# mount -t nfs 192.168.1.4ï¼/usr/backups/mysql /mnt/backup_share
å°ç®å½æè½½è¿æ¥åï¼åªè¦è¿å ¥/mnt/backup_share ç®å½ï¼å°±çäºå°äºIPå°åï¼192.168.1.4é£é¨NFS 计ç®æºç/usr/backups/mysql ç®å½ä¸ãä¸é¢ä½¿ç¨mysqldumpæâphpbb_db_backupâå¤ä»½å°è¿ç¨è®¡ç®æºï¼
# mysqldump db_backup > /mnt/backup_share/ db_backup2008-1-6.sql
èªå¨å®æç½ç»å¤ä»½çæ¹æ³ï¼
Linux æå¡å¨ä¸çç¨åºæ¯å¤©é½å¨æ´æ° MySQL æ°æ®åºï¼äºæ¯å°±æ³èµ·åä¸ä¸ª shell èæ¬ï¼ç»å crontabï¼å®æ¶å¤ä»½æ°æ®åºã建ç«ä¸ä¸ªshellèæ¬ï¼sample_db_backup.sh
# At the very end the $(date +%F) èªå¨æ·»å å¤ä»½æ¥æ
mysqldump -u <username> -p <password> -h <hostname> sample_db > /mnt/backup_share/sample_db.$(date +%F)
#un-mount the filesystem
umount /mnt/backup_share
# mount \u2013o soft 192.168.1.4:/archive /mnt/backup_share
说æï¼mount NFSæå¡å¨çä¸ä¸ªéè¦åæ°ï¼hard ï¼ç¡¬ï¼ mountæsoftï¼è½¯ï¼mountã
硬æè½½: NFS客æ·æºä¼ä¸æçå°è¯ä¸NFSæå¡å¨çè¿æ¥ï¼å¨åå°ï¼ä¸è¬ä¸ä¼ç»åºä»»ä½æ示信æ¯ï¼ï¼ç´å°æè½½ä¸ä¸ºæ¢ã
软æè½½:ä¼å¨åå°å°è¯ä¸NFSæå¡å¨çè¿æ¥ï¼æ¯é»è®¤çè¿æ¥æ¹å¼ãå½æ¶å°é误信æ¯åç»æ¢mountå°è¯ï¼å¹¶ç»åºç¸å ³ä¿¡æ¯ã
对äºå°åºæ¯ä½¿ç¨ç¡¬æè½½è¿æ¯è½¯æè½½çé®é¢ï¼è¿ä¸»è¦åå³äºä½ 访é®ä»ä¹ä¿¡æ¯æå ³ãä¾å¦ä½ æ¯æ³å¯çNFSæå¡å¨çè§é¢æ件æ¶ï¼ä½ ç»å¯¹ä¸ä¼å¸æç±äºä¸äºæå¤çæ åµï¼å¦ç½ç»é度ä¸ä¸ååçå¾æ ¢ï¼è使系ç»è¾åºå¤§éçé误信æ¯ï¼å¦ææ¤æ¶ä½ ç¨çæ¯ç¡¬æè½½æ¹å¼çè¯ï¼ç³»ç»å°±ä¼çå¾ ï¼ç´å°è½å¤éæ°ä¸NFS æå¡å¨å»ºç«è¿æ¥ä¼ è¾ä¿¡æ¯ãå¦å¤å¦ææ¯éå ³é®æ°æ®çè¯ä¹å¯ä»¥ä½¿ç¨è½¯æè½½æ¹å¼ï¼å¦FTPä¸äºæ°æ®çï¼è¿æ ·å¨è¿ç¨æºå¨ææ¶è¿æ¥ä¸ä¸æå ³éæ¶å°±ä¸ä¼æèµ·ä½ çä¼è¯è¿ç¨ã
ä¸é¢å»ºç«èæ¬æ件æéï¼chmod +x ./sample_db_backup.sh
ç¶å使ç¨å°æ¤èæ¬å å° /etc/crontab å®æ¶ä»»å¡ä¸ï¼
01 5 * * 0 mysql /home/mysql/ sample_db_backup.sh
好äºï¼æ¯å¨æ¥åæ¨ 5:01 ç³»ç»å°±ä¼èªå¨è¿è¡ sample_db_backup.sh æ件éè¿ç½ç»å¤ä»½ MySQL æ°æ®åºäºã
ä¸ãå®æ¶æ¢å¤M y S Q Læ°æ®æ¹æ³
å¨å¯¹MySQLæ°æ®åè¡¨æ ¼ç»æè¿è¡å¤ä»½æ¶ï¼mysqldumpæ¯ä¸ä¸ªé常æç¨çå·¥å ·ãç¶èï¼é常æ åµä¸ï¼ä¸è¬ä¸å¤©åªå¤ä»½ä¸æ¬¡ï¼æè å¨ä¸ä¸ªç¹å®çé´éå¤ä»½ä¸æ¬¡ãå¦æå¨åå¤ä»½å®æçä¸æ®µæ¶é´ä»¥å æ°æ®ä¸¢å¤±ï¼é£ä¹è¿äºæ°æ®å¾æå¯è½æ æ³æ¢å¤ãæä»ä¹æ¹æ³å¯ä»¥å¯¹æ°æ®è¿è¡å®æ¶æ§å°ä¿æ¤å¢?äºå®ä¸ï¼ç°å¨æå ç§æ¹æ³é½å¯ä»¥å®ç°MySQLæ°æ®åºçå®æ¶ä¿æ¤ãè¿éä»ç»å ¶ä¸ä¸ç§ï¼å³ä½¿ç¨äºè¿å¶æ¥å¿è¿è¡æ°æ®æ¢å¤ã
1 设置äºè¿å¶æ¥å¿æ¹æ³
è¦æ³ä»äºè¿å¶æ¥å¿æ¢å¤æ°æ®ï¼ä½ éè¦ç¥éå½åäºè¿å¶æ¥å¿æ件çè·¯å¾åæ件åãä¸è¬å¯ä»¥ä»é项æ件(å³my.cnf or my.iniï¼åå³äºä½ çç³»ç»)ä¸æ¾å°è·¯å¾ãå¦ææªå å«å¨é项æ件ä¸ï¼å½æå¡å¨å¯å¨æ¶ï¼å¯ä»¥å¨å½ä»¤è¡ä¸ä»¥é项çå½¢å¼ç»åºãå¯ç¨äºè¿å¶æ¥å¿çé项为-- log-binãè¦æ³ç¡®å®å½åçäºè¿å¶æ¥å¿æ件çæ件åï¼è¾å ¥ä¸é¢çMySQLè¯å¥ï¼
# SHOW BINLOG EVENTS \G
2 æç®åçæ°æ®æ¢å¤
æ¯å¤©å¤ä»½åè¿è¡äºè¿å¶æ¥å¿çç¡®æ¯ä¸ä¸ªå¨MySQLæå¡å¨ä¸æ¢å¤æ°æ®çä¸éæ¹æ³ãæ¯å¦ï¼å¯ä»¥æ¯å¤©å¨æ·±å¤ä½¿ç¨mysqldump对æ°æ®è¿è¡å¤ä»½ï¼å¦ææ天å¨æ°æ®å¤ä»½å®æåçä¸æ®µæ¶é´éï¼ç±äºæç§åå æ°æ®ä¸¢å¤±ï¼å¯ä»¥ä½¿ç¨ä»¥ä¸æ¹æ³æ¥å¯¹å ¶è¿è¡æ¢å¤ãé¦å ï¼åæ¢MySQLæå¡å¨ï¼ç¶å使ç¨ä»¥ä¸å½ä»¤éæ°å¯å¨MySQLæå¡å¨ã该å½ä»¤å°ä¿è¯æ¯æä¸å¯ä»¥è®¿é®è¯¥æ°æ®åºæå¡å¨ç人ï¼
# /etc/init.d/mysqld stop
Stopping MySQL: [ OK ]
# mysqld --socket=/tmp/mysql_restore.sock --skip-networking
è¿éï¼ ä¸socketé项å°ä¸ºU n i x ç³»ç»å½åä¸ä¸ªä¸åçSocketæ件ãä¸æ¦æå¡å¨å¤äºç¬å æ§å¶ä¹ä¸ï¼å°±å¯ä»¥æ¾å¿å°å¯¹æ°æ®åºè¿è¡æä½ï¼èä¸ç¨æ å¿å¨è¿è¡æ°æ®æ¢å¤çè¿ç¨ä¸æç¨æ·å°è¯è®¿é®æ°æ®åºè导è´æ´å¤ç麻ç¦ãè¿è¡æ¢å¤ç第ä¸ä¸ªæ¥éª¤æ¯æ¢å¤æä¸å¤ä»½å¥½çdumpæ件ï¼
#mysql -u root -pmypwd --socket=/tmp/mysql_restore.sock < /var/backup/20080120.sql
该å½ä»¤å¯ä»¥å°æ°æ®åºçå 容æ¢å¤è³æä¸ååå®æå¤ä»½çå 容ãè¦æ¢å¤dumpæ件å建åçæ°æ®åºäºå¡å¤çï¼ å¯ä»¥ä½¿ç¨mysqlbinlogå·¥å ·ãå¦ææ¯å¤©æä¸è¿è¡å¤ä»½æä½æ¶é½å¯¹æ¥å¿è¿è¡flushæä½ï¼åå¯ä»¥ä½¿ç¨ä»¥ä¸å½ä»¤è¡å·¥å ·å°æ´ä¸ªäºè¿å¶æ¥å¿æ件è¿è¡æ¢å¤ï¼
mysqlbinlog /var/log/mysql/bin.123456 \
| mysql -u root -pmypwd --socket=/tmp/mysql_restore.sock
3 é对æä¸æ¶é®ç¹çæ¢å¤
对äºMySQL 4.1.4ï¼å¯ä»¥å¨mysqlbinlogè¯å¥ä¸éè¿--start-dateå--stop-dateé项æå®DATETIMEæ ¼å¼çèµ·æ¢æ¶é´ãå设ç¨æ·å¨2008-1-22ä¸å10ç¹æ§è¡çSQLè¯å¥å é¤äºä¸ä¸ªå¤§çæ°æ®è¡¨ï¼åå¯ä»¥ä½¿ç¨ä»¥ä¸å½ä»¤è¿è¡æ¢å¤ï¼è¦æ³æ¢å¤è¡¨åæ°æ®ï¼ä½ å¯ä»¥æ¢å¤åæä¸çå¤ä»½ï¼å¹¶è¾å ¥ï¼
#mysqlbinlog --stop-date="2008-1-22 9:59:59"
/var/log/mysql/bin.123456 |
mysql -u root -pmypwd \
--socket=/tmp/mysql_restore.sock
#mysql -u root -pmypwd
该è¯å¥å°æ¢å¤ææç»å®ä¸stop-dateæ¥æä¹åçæ°æ®ãå¦æå¨æ§è¡æSQLè¯å¥æ°å°æ¶ä¹åæåç°æ§è¡äºé误æä½ï¼é£ä¹å¯è½è¿éè¦æ¢å¤ä¹åè¾å ¥çä¸äºæ°æ®ãè¿æ¶ï¼ ä¹å¯ä»¥éè¿mysqlbinlogæ¥å®æ该åè½ï¼
#mysqlbinlog --start-date="2008-1-22 10:01:00" \
/var/log/mysql/bin.123456 \
| mysql -u root -pmypwd \
--socket=/tmp/mysql_restore.sock
#mysql -u root -pmypwd
å¨è¯¥è¡ä¸ï¼ä»ä¸å10:01ç»å½çSQLè¯å¥å°è¿è¡ãç»åæ§è¡åå¤ç转å¨æ件åmysqlbinlogç两è¡å¯ä»¥å°æææ°æ®æ¢å¤å°ä¸å10:00åä¸ç§éãä½ åºæ£æ¥æ¥å¿ä»¥ç¡®ä¿æ¶é´ç¡®åã
4 使ç¨Positionè¿è¡æ¢å¤
ä¹å¯ä»¥ä¸æå®æ¥æåæ¶é´ï¼è使ç¨mysqlbinlogçé项--start-positionå--stop-positionæ¥æå®æ¥å¿ä½ç½®ãå®ä»¬çä½ç¨ä¸èµ·æ¢æ¥é项ç¸åï¼ä¸åçæ¯ç»åºäºä»æ¥å¿èµ·çä½ç½®å·ã使ç¨æ¥å¿ä½ç½®æ¯æ´åç¡®çæ¢å¤æ¹æ³ï¼ç¹å«æ¯å½ç±äºç ´åæ§SQLè¯å¥åæ¶åç许å¤äºå¡çæ¶åãè¦æ³ç¡®å®ä½ç½®å·ï¼å¯ä»¥è¿è¡mysqlbinlog寻æ¾æ§è¡äºä¸ææçäºå¡çæ¶é´èå´ï¼ä½åºå°ç»æéæ°æåææ¬æ件以便è¿è¡æ£æ¥ãæä½å½ä»¤ä¸ºï¼
mysqlbinlog --start-date="2005-04-20 9:55:00" --stop-date="2005-04-20 10:05:00"
/var/log/mysql/bin.123456 > /tmp/mysql_restore.sql
该å½ä»¤å°å¨/tmpç®å½å建å°çææ¬æ件ï¼å°æ¾ç¤ºæ§è¡äºé误çSQLè¯å¥æ¶çSQLè¯å¥ãä½ å¯ä»¥ç¨viæè geditææ¬ç¼è¾å¨æå¼è¯¥æ件ï¼å¯»æ¾ä½ ä¸è¦æ³éå¤çè¯å¥ãå¦æäºè¿å¶æ¥å¿ä¸çä½ç½®å·ç¨äºåæ¢å继ç»æ¢å¤æä½ï¼åºè¿è¡æ³¨éãç¨log_poså ä¸ä¸ªæ°åæ¥æ è®°ä½ç½®ã使ç¨ä½ç½®å·æ¢å¤äºä»¥åçå¤ä»½æ件åï¼ä½ åºä»å½ä»¤è¡è¾å ¥ä¸é¢å 容ï¼
mysqlbinlog --stop-position="368312" /var/log/mysql/bin.123456
| mysql -u root -pmypwd
mysqlbinlog --start-position="368315" /var/log/mysql/bin.123456
| mysql -u root -pmypwd
ä¸é¢ç第1è¡å°æ¢å¤å°åæ¢ä½ç½®ä¸ºæ¢çææäºå¡ãä¸ä¸è¡å°æ¢å¤ä»ç»å®çèµ·å§ä½ç½®ç´å°äºè¿å¶æ¥å¿ç»æçææäºå¡ãå 为mysqlbinlogçè¾åºå æ¬æ¯ä¸ªSQLè¯å¥è®°å½ä¹åçSET TIMESTAMPè¯å¥ï¼æ¢å¤çæ°æ®åç¸å ³MySQLæ¥å¿å°ååºäºå¡æ§è¡çåæ¶é´ã
温馨提示:答案为网友推荐,仅供参考
第1个回答 2017-07-31
oracle 是 在RMAN环境下 使用 backup database 可实现数据库的全备。