How to export and import very large mySQL databases如何在出口和進口非常大的MySQL數據庫

I had a very large web site (30,000+ pages) that I wanted to move from one web host to another.我有一個非常大的網站（ 30000 +頁） ，我想從一個網頁到另一個主機。 Normally I would use phpMyAdmin to export tables or the entire database.通常我會用phpMyAdmin出口表或整個數據庫。 This mySQL database was of course too large for that and phpMyAdmin would timeout trying to export, even if I exported only one table at a time (one table had 1.5 million records). MySQL數據庫這當然是過於龐大，以及超時phpMyAdmin將試圖出口，即使我只有一個出口表的時間（一表了150萬的記錄） 。 It’s very easy to quickly export and import very, very large mySQL databases with even hundreds of thousands of records.這是很容易的快速出口和進口非常，非常大的MySQL數據庫，甚至數以十萬計的記錄。

To accomplish this we can’t do it via web page tools or methods.為實現這一目標，我們不能做到這一點通過網頁上的工具或方法。 So, we have to use the command line in UNIX.因此，我們必須使用命令行的UNIX 。 This will require a telnet (preferably secure telnet) account and a basic understanding on simple UNIX commands.這將需要遠程登錄（最好是安全的遠程登錄）的帳戶，並有基本的了解簡單的UNIX命令。 You will also enough disk space free to extract your database.你也將足夠的磁盤空間自由提取您的數據庫。 If you don’t have telnet access to your web site, you can always submit a support ticket telling them what to do based on these instructions.如果你沒有遠程登錄訪問您的網站上，您隨時可以提交支援票證告訴他們做什麼，根據這些指示。 You will need to know in advance your database name, your database username and password, and whether or not your server is ‘localhost’ or a server name (and what it is).您將需要預先知道你的數據庫名稱，您的數據庫用戶名和密碼，不論您的服務器是'本地'或服務器的名字（和它是什麼） 。 This information is normally already entered in your site’s database config or settings file.這種信息通常是已經進入您網站的數據庫配置或設置文件。 If you are using Wordpress it’s in your /wp-config.php file in the root of your site, if you’re using Drupal it’s stored in your /sites/default/settings.php file.如果您使用的是WordPress的是在你的/可濕性粉劑， config.php文件的根目錄中您的網站，如果您使用Drupal它儲存在您的/網站/預設/ settings.php文件。

Here are the instructions to export your mySQL database into one file on the UNIX command line:以下是指示匯出您的MySQL數據庫到一個文件上的UNIX命令行：

• login to your web site using telnet登錄到您的網站使用telnet
• navigate to the directory where you want to store your extracted database file瀏覽到目錄下您想要儲存您的數據庫文件中提取
• run the following on the command line:運行下面的命令行：
  mysqldump -a -u USERNAME -p DATABASE > FILENAME.mysql mysqldump一聚氯乙烯用戶名- P數據庫“ FILENAME.mysql
• check the size of your file (command: ls -al)檢查大小的文件（命令：掃描儀LS -基地）
• gzip your file to make the download faster using the command: gzip FILENAME.mysql gzip格式壓縮您的檔案，使下載速度更快使用命令： gzip格式壓縮FILENAME.mysql
• download your file in ftp to your local pc to upload and import into your new database下載您的文件中的FTP到本地電腦上傳和導入新的數據庫

Again, if you don’t have telnet access you can probably submit a ticket at your web host and they will extract the file for you so you can download it.同樣，如果你沒有遠程登錄訪問您可能提交一份門票在您的網頁主機，他們將提取該文件為您，您可以下載它。 Once downloaded you need to upload the file to your new web host so you can import it into a fresh database.一旦下載您需要上傳文件到您的新網頁主機，因此您可以將其導入到一個新的數據庫。 First, I want to make you aware of a PHP script called首先，我要你知道，所謂的PHP腳本 Big Dump大型自卸 that does a ’staggered import’ of mysql files into a new database.這不一個交錯進口'的MySQL檔案進入了一個新的數據庫。 Even though it’s staggered (importing in chunks) it has it’s limits.即使它的交錯（進口的塊）有它的局限性。 I have successfully used it many times on databases under 50MB.我已經成功地使用了很多次關於下50MB的數據庫。 It has also failed miserably for me on a 191MB database.它也失敗，我就191MB數據庫。 If your database is too big to import using phpMyAdmin but under 50MB - give it a try first.如果您的數據庫太大，進口使用phpMyAdmin但根據50MB的-給它一個嘗試第一次。 Otherwise you’ll have to import on the command.否則，你就必須進口的命令。 If you use Big Dump you just enter your mysql server settings into the file itself, and I have had the best results uploading the mysql file to the server (same dir you place bigdump.php in) and hardcode the filename into the file before trying the import.如果您使用的大型自卸你只需輸入你的MySQL服務器中設置到文件本身，我有最好的結果上傳數據庫文件保存到服務器（同一目錄您bigdump.php中）和硬編碼的文件名的文件，然後試圖進口。

Now, if your file is >50MB or bigdump.php doesn’t work - I’m going to show you how to do a manual import of your mysql file:現在，如果您的檔案是“ 50MB的或bigdump.php不工作-我要告訴你如何做一本手冊進口你的M ySQL文件：

• upload your mysql file to your new web site上傳您的數據庫文件保存到您的新網站
• create a new blank database using myPHPAdmin創建一個新的空白數據庫使用myPHPAdmin
• note your database username and password, and your mysql servername說明你的數據庫用戶名和密碼，你的MySQL服務器
• unzip your database file with this command: gzip -d FILENAME.mysql解壓您的數據庫文件，此命令： gzip格式壓縮一d FILENAME.mysql
• run the following on the command line to import:運行下面的命令行導入：
  mysql -u USERNAME -p DATABASE < FILENAME.mysql MySQL的聚氯乙烯用戶名- P數據庫“ FILENAME.mysql
• check your database in myPHPAdmin to make sure all the tables imported properly檢查您的數據庫中myPHPAdmin ，以確保所有表格輸入正確

That’s it!這樣就大功告成了！ You’ve now learned how to successfully export and import large mySQL databases when myPHPAdmin or other tools won’t work!您現在學會了如何成功地出口和進口大型MySQL數據庫時， myPHPAdmin或其他工具將無法正常工作！ Again, if you don’t have telnet access, you can always ask your (new) web host to import the file for you.同樣，如果你沒有遠程登錄訪問，您可以隨時請您（新的）的網頁主機進口的文件你。 I did when I migrated from a host I used for 9 years to goDaddy.我沒有當我移居從主機我用了9年來goDaddy 。 They don’t offer telnet access, so I uploaded the 191MB mysql file and gave them a call to submit a ticket.他們不提供遠程登錄訪問，所以我上傳191MB數據庫文件，並給他們打電話到提交一份門票。 In less than 2 hours the import was done and I was very happy!在不到2個小時的進口做，我感到非常高興！

I’ve had a lot of people ask my why I use goDaddy, and I have to tell you…I have hosted with dozens of companies.我已經有很多人問我為什麼我使用goDaddy ，我必須告訴你...我已與幾十主辦的公司。 I actually setup a small account with goDaddy 6 months ago just to try it out (it was only $3.99 per month).事實上，我安裝一個小型的帳戶goDaddy 6個月前剛剛試試（只有3.99美元每月） 。 Now, 6 months later I’ve moved everything to them.現在， 6個月後，我已經從他們的一切。 All of my domain registrations, and all of my sites are completely hosted with goDaddy as of today (that big web site was the last one!).我所有的域名註冊，和我所有的網站是完全託管goDaddy截至今日（即大網站是最後一個！ ） 。 I was paying $29.99 per month for a professional webmaster account.我是29.99美元支付，每月一個專業網站管理員的帳戶。 I get the exact same thing (and more) with goDaddy for $14.99 per month.我得到的確切同樣的事情（更）與goDaddy為14.99美元每月。 They have deals all the time where I pickup new domains for $1.99, and they had a special just today (when I called about the database import) where in July all web hosting was 25% off.他們處理所有的時間，我皮卡新領域1.99美元的價格，他們有一個特殊的，僅僅是今天（當我呼籲有關數據庫進口）凡在7月的所有網頁主機為25 ％趕走。 It’s not just 25% off for new customers - it’s 25% off for ALL customers, whether you upgrade or just want to pay ahead.這不僅是關閉的25 ％為新客戶-這是2 5％ ，關閉所有客戶，不管你升級或者只是想提前支付。 So I paid up a few months in advance to save even more money.因此，我付出了幾個月提前保存甚至更多的錢。 That’s my shameless self-promotion for the day - use the host I use:這是我的無恥的自我推銷的一天-使用主機使用： www.GoDaddy.com

*UPDATE* *更新*

I almost forgot to add why this was a ‘blogging mistake’!  I was going to migrate all of my web sites to goDaddy last month.  I setup a new account a moved all but 2 or 3 sites - mainly because I couldn’t figure out how to get those huge databases off my old server and into my goDaddy account.  Because I couldn’t figure it out my monthly hosting renewal came up, and I ended up paying for another month.  Since I had a dedicated server (that I was grossly under utilizing), that blogging mistake ended up costing me $214!!  Then I finally did a quick google search for “export large mysql database” and figured it out in an hour.  My mistake was not doing enough research to fix the problem - it’s not like I was tackling something others hadn’t been through (and documented) before.  Google is your friend - always search for solutions to your problems!我幾乎忘了補充，為什麼這是一個'博客錯誤' ！一個，一個我要遷移我所有的網站goDaddy去年month.à ，一個設置一個新的帳戶，但將所有2個或3個網站-主要是因為我無法弄清楚如何讓這些龐大的數據庫了我的舊服務器和我的goDaddy account.à ，一個因為我不能真正理解數字我每月舉辦續期來到了，我最終支付一個月。一個，一個由於我有一個專用服務器（我是利用嚴重不足） ，即博客錯誤上漲成本箱214美元！一個，一個接著我終於做了快速谷歌搜索“出口大MySQL數據庫” ，並理解了它在hour.à ，我的錯誤是沒有採取足夠的研究，以解決這一問題-它不喜歡我是解決其他一些尚未通過（和記錄） b efore.Ã，一個谷歌是您的朋友-永遠的搜尋解決您的問題！