phpMyAdmin can't export database

问题:

I have a VPS machine and I installed phpMyAdmin and inserted the database in. But now when I want to make a backup and export the database, it says:

Here is the error.log: http://pastebin.com/44N4YcAk

[Tue Jun 18 21:40:16 2013] [error] [client] PHP Fatal error: Allowed memory size of 16777216 bytes exhausted (tried to allocate 491520 bytes) in /usr/share/phpmyadmin/libraries/tcpdf/tcpdf.php

回答1:

you should not be using phpmyadmin for this, you should be using mysqldump. from the command line its

mysqldump -uMYSQL-USER -h server -pMYSQL-USER database_name > /path-to-export

from a php script

$command = "mysqldump -uMYSQL-USER -h server -pMYSQL-USER database_name > /path-to-export/file.sql";
exec($command, $output, $return_var);

this is easy to automate with a cronjob

回答2:

Go through this post, it shows how to increase the memory; or just use command line to import:

How to increase memory size for phpmyadmin

回答3:

It says at the bottom

 PHP Fatal error:  Allowed memory size of 16777216 bytes exhausted (tried to allocate 491520 bytes) in /usr/share/phpmyadmin/libraries/tcpdf/tcpdf.php on line 22694, 

Either your VPS is out of memory or your PHP settings are not allowing more than 16MB of memory to be allocated.

Increase the memory in php.ini or just use mysqldump

php.ini change

memory_limit = 64M  

I strongly recomend using mysqldump though here is an extract from my backup script

#!/bin/bash
time=`date +%Y-%m-%d_%H-%M-%S`
mysqldump -u mysqluser -pmysqlpassword --all-databases | 7za a -si database/backup-${time}.sql.7z -p7zpass

That saves the backup in a 7zip file protected by the password 7zpass

回答4:

do this command:

nano /usr/share/phpmyadmin/export.php

press ctrl+w and find:

// now export the triggers (needs to be done after the data because

this command used twice in the file after this command you see nested if, so in the nested if change:

break 2;

to

break;

in both cases after this your export function will work well

回答5:

Tip:

Change the mysql engine to InnoDB instead of MyISAM, the main purpose is claiming storage after cleaning tables or dropping them.