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.Ã，一个谷歌是您的朋友-永远的搜寻解决您的问题！