今天从服务器上的Mysql数据库往我自己本地的测试环境导数据,开始想用phpMyadmin从网页直接导入,结果文件太大,而phpmyadmin导入数据有最多2M的限制。
phpmyadmin给出的文档是这样的
Starting with version 2.7.0, the import engine has been re–written and these problems should not occur. If possible, upgrade your phpMyAdmin to the latest version to take advantage of the new import features.
The first things to check (or ask your host provider to check) are the values of upload_max_filesize, memory_limit and post_max_size in the php.ini configuration file. All of these three settings limit the maximum size of data that can be submitted and handled by PHP. One user also said that post_max_size and memory_limit need to be larger than upload_max_filesize.
There exist several workarounds if your upload is too big or your hosting provider is unwilling to change the settings:
Look at the $cfg['UploadDir'] feature. This allows one to upload a file to the server via scp, ftp, or your favorite file transfer method. PhpMyAdmin is then able to import the files from the temporary directory. More information is available in the Configuration section of this document.
Using a utility (such as BigDump) to split the files before uploading. We cannot support this or any third party applications, but are aware of users having success with it.
If you have shell (command line) access, use MySQL to import the files directly. You can do this by issuing the "source" command from within MySQL: source filename.sql.
也就是说解决方案有两种:
方法一
以上文件大致说明的意思就是说,遇到导入过大文件时,首先检查php.ini 配置文件中的以下三个地方,upload_max_filesize, memory_limit 和post_max_size,并且推荐修改的值要稍大于导入的巨大sql数据库文件;依照这个提示,我修改了以上三个在php.ini中的值以后,重启 了php环境,再次导入时,phpmyadmin还是显示导入最大限制:20,48KB,80M数据库文件还是无法导入成功。
方法二
在phpMyAdmin的目录下,找到根目录的config.inc.php文件,如下图:
打开config.inc.php文件,查找$cfg['UploadDir'],这个参数就是设定导入文件存放的目录,这里把值设定为:ImportSQLFile。如下图
在phpMyAdmin目录下,建立以ImportSQLFile命名的文件夹,如下图
把我们需要导入的数据文件,放到ImportSQLFile文件夹下面,非常简单,不截图了。
5
登入phpMyAdmin,选择需要导入的数据,点击导航条上面的“导入”按钮,如下图:
选中“从网站服务器上传文件夹ImportSQLFile/中选择:”选项,并需要导入的数据文件,如下图:
最后点击“执行”,即可导入成功。
这是网上说的方法,在我这里都没有成功。
最后解决了,用wamp的mysql控制台进入mysql命令,用use scenicnav; source C:/scenicnav.sql;命令导入成功了。
也可以进入cmd cd d:/mysql/bin 然后执行mysql -u root -p scenicnav < scenicnav.sql命令(注:scenicnav.sql文件放到了bin目录下)因为之前有导入错误的数据,可以先把原来建的数据库删除drop database scenicnav; 然后create database scenicnav; 导入成功。
另:中间出现了几个小插曲,导入数据库的时候提示错误 unknown command ‘\\’,然后我就在sql文件中查找\\,原来执行命令的时候,数据库将数据库中的一个数据行的值\\当成了命令来处理,将其删除就ok了。