phpMyAdmin管理mysql的有用小技巧

来源：互联网发布：mysql mha 编辑：程序博客网时间：2024/05/04 11:18

1.突破mysql导入数据库文件最大限制的方法

遇到导入过大.sql文件时，先检查php.ini中的upload_max_filesize，修改其值，并且推荐修改的值要稍大于导入的巨大sql数据库文件。重启php环境（我用的是Apache2.2），再次导入即可。（我的环境：win2003+mysql+Apache2.2+php+phpMyAdmin2.11.1.2）

参考：1.16 I cannot upload big dump files (memory, HTTPHTTPHTTP or timeout problems).

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.

数据备份和恢复

默认的数据导出、和导入最大文件有2M的最大限制。如果要操作大于2M的数据库备份文件就需要预先将文件上传到phpmyadmin的某个目录。

1.首先在phpmyadmin个跟目录建立一个目录，比如叫ports

2.在config.default.php中搜$cfg['UploadDir']，这个变量定义保存导入文件存放的目录，它下面的$cfg['SaveDir']定义的是数据导出文件的存放目录，我们都定义为ports

$cfg['UploadDir'] = 'ports';

$cfg['SaveDir'] = 'ports';

要注意的是ports目录的权限，saveDir只要能让php脚本往里边写东西。最后的就是操作完了，务必清空改目录，防止数据被别有用心的人下载。

2.导入Excel数据到Mysql中

工具：phpMyAdmin

2.1整理Excel：保证Excel中数据按MYSQL中相关表的字段顺序列排列好，而且要保证列数与字段数相同（即使Mysql中首字段是auto_increment，Excel中也要包含该列，否则导入时会提示：Invalidfield count in CSV input on line 1.）。

（注意特殊字段的表示方式，例如:时间字段必须用2007-10-10形式表示,如果表示成2007-1-1,可能会导入的为空值）。

2.2另存为.CSV:将你的EXCEL另存为.CSV格式(此格式中,认为两个字段之间是以","即逗号来表示)。

2.3找到要导入数据的表：单击“Import”，在“File toimport”出选择2.2步的.CSV,在“Format of importedfile”处选择“CSV”，在“Options”处勾选“Ignore duplicaterows”，并将“分隔字段的字符”改为“,”(英文逗号)。

2.4 单击“执行”就OK了。

将你的字段按MYSQL中相关表的字段顺序列排列好,注意特殊字段的表示方式(例如:时间字段必须用2007-10-10形式表示,如果表示成2007-1-1,可能会导入的为空值).

参考：阿宇Blog http://blog.chinaunix.net/u1/34688/showart_381536.html