MySQL Backups truncated..

Events happening in the community are now at Drupal community events on www.drupal.org.
Posted by wipeout_dude on March 17, 2012 at 6:39pm

Hi,

I seem to be having an odd issue that I have only just noticed when trying to restore some data.. Fortunately I had an alternative backup..

Before doing what I was doing I took a backup using backup_migrate and the download method to save the backup on my PC.. After doing what I needed the site broke.. I tried to restore and it failed.. I looked into the backup file and noticed that it was incomplete.. It hadn't backed up all the tables..

When backing up to the server (Manual Backup Directory) it works fine.. I don't remember this being an issue before switching to nginx..

I have tested with phpmyadmin and am having similar issues which indicates its not to do with Drupal or backup_migrate..

Has anyone else come across this and how to fix it?

Thanks..

Comments

Try increasing max_allowed_packet

Posted by esod on March 25, 2012 at 4:47pm

Sounds like it's a MySQL issue on your localhost.

If I don't increase the max_allowed_packet variable in MySQL, I'll get "MySQL has gone away" notices when uploading a database. When I check the database after a "MySQL has gone away" notice, I'll see that only some of the tables have been uploaded. It's possible this is what you're experiencing except it's occurring during a download.The "MySQL has gone away" notice may be in your log or somewhere. You're probably more interested in resolving the issue. Here's how to increase the size of the max_allowed_packet in MySQL.

Log in to MySQL.
At the command line, type the following command (don't type the dollar sign).

$ mysql -u YOURDBUSERACCOUNT (PROBABLY root) -p
You'll then be prompted for your password.
Enter password: YOURPASSWORD

Once logged in to MySQL determine the current size of the max_allowed_packet by typing (don't type mysql>)

mysql> SHOW VARIABLES LIKE 'max_allowed_packet';

the returned result will probably look like this:
+-----------------+--------+
| Variable_name | Value |
+-----------------+--------+
| max_allowed_packet | 1048576 |
+-----------------+---------+

which is 1M (megabyte).

Change the max_allowed_packet with:
mysql> SET GLOBAL max_allowed_packet=16777216;
for 16M which is probably large enough.

or

mysql> SET GLOBAL max_allowed_packet=33553408;
for 32M which is surely large enough.

quit MySQL with

mysql> \q

Log in to MySQL again and show the variable. It should be changed to 16M or 32M, whichever one you chose with SET GLOBAL.

You can also edit the .ini or .cnf (depends on your MYSQL setup) file that is read by MySQL. I think it's easier to use the command line.

You may also want to check the max_allowed_packet variable on your server and use that value for your localhost. Then you'll know you have the answer. ;)

Correction:
mysql> SET GLOBAL max_allowed_packet=33553408;
only changes max_allowed_packet in memory. The next time you restart your MySQL server max_allowed_packet will revert to its default, 1M.

Make the declaration max_allowed_packet = 32M in the my.ini or my.cnf file, whichever one exists for your setup. If neither exists, make one of them in a place where mysql will recognize it.

You were correct that my

Posted by wipeout_dude on March 17, 2012 at 11:23pm

You were correct that my max_allowed_packet was set at 1M but after increasing it to 16M the problem still persists unfortunately..

Since there is no issue from the command line would suggest that its not mysql related but rather either php or nginx and since I have never had the issue crop up before on Apache/PHP setup's causes me to think its probably nginx..

I have looked through the config files but no specific limits are jumping out at me..

Just really glad I had an alternate backup or I would have been in big trouble.. :)

What happens to the db with mysqldump or drush sql-dump?

Posted by esod on March 18, 2012 at 1:12am

Too bad max_allowed_packet didn't fix things up.

For mysqldump, your command will look like:
$ mysqldump -u YOURDBUSERNAME -pYOURDBPASSWORD -h IFDBISREMOTE DBNAME > OUTPUTNAME.sql

If you have Drush installed, you can use:
$ drush sql-dump > OUTPUTNAME.sql

Check if the result contains your entire database.

I'm not sure which setting it

Posted by brianmercer on March 18, 2012 at 1:28am

I'm not sure which setting it is for nginx.conf, but I would try
client_max_body_size = 16M
or maybe it's affected by the php.ini setting:
post_max_size = 16M
But either way I'd either do like esod say and use drush with something like this in /etc/cron.d/drupalbam :

10 5 * * * www-data /usr/bin/drush @mysitealias -q bam-backup

or, I personally just use automysqlbackup, but backup_migrate does make it easier to restore.

Or use

Posted by perusio on March 18, 2012 at 1:46am

drush_sql_extras and do a simple

drush @mysitealias sqbz

to get a pretty dump that is timestamped and compressed with pbzip2 if available. You can even encrypt the dump ;)

I have yet to add support for mydumper (high performance dumps and restores) and versioned dumps with git-annex. It's coming :)

MySQL MyDumper support in Drush

Posted by texas-bronius on October 15, 2012 at 8:01pm

Looks like there's a little noise recently on Twitter re. mydumper, and my first thought was to look for Drush support. Just curious, any traction in that direction? Is it plausible? Maybe (and I really know nothing about what I'm talking about) if it's just as reliable, maybe it's viable to wire drush sql-dump to first look for mydumper (and perhaps propose it to promote it if not found) and only fallback to mysqldump by default?

Indeed

Posted by perusio on October 15, 2012 at 10:56pm

adding mydumper support to drush_sql_extras has been in my plans for some time. I think it would speed dumps and restores significantly. TBD.

HI, Changing

Posted by wipeout_dude on March 18, 2012 at 9:36am

HI,

Changing client_max_body_size in nginx didn't solve the problem.. post_max_size in php is already at 20M..

drush sql-dump > OUTPUTNAME.sql works fine which suggests that PHP is working fine..

This did show up in the nginx error log..

2012年03月18日 09:27:13 [crit] 16047#0: *12 open() "/var/lib/nginx/fastcgi/1/00/0000000001" failed (13: Permission denied) while reading upstream, client: 127.0.0.1, server: localhost, request: "POST /phpmyadmin/export.php HTTP/1.1", upstream: "fastcgi://unix:/tmp/php-fpm_www-data.sock:", host: "localhost", referrer: "http://localhost/phpmyadmin/db_export.php?db=dev_db1&server=1&token=c84f1c6b08e05baf6b0d2006d6127683"

Seems nginx cant access the dump file but it manages to get some of it.. Its very strange..

I suggest

Posted by perusio on March 18, 2012 at 2:45pm

not using phpmyadmin at all. It's a complete disaster in security terms.

Your problem is probably related to incorrect specification of the PHP script. It seems that this script export.php is not allowed to be processed by FCGI.

Its not just phpmyadmin,

Posted by wipeout_dude on March 18, 2012 at 3:18pm

Its not just phpmyadmin, backup_migrate is causing the same issue..

2012年03月18日 15:11:40 [crit] 16076#0: *506 open() "/var/lib/nginx/fastcgi/1/00/0000000001" failed (13: Permission denied) while reading upstream, client: x.x.x.x, server: mydomain.com, request: "POST /admin/config/system/backup_migrate HTTP/1.1", upstream: "fastcgi://unix:/tmp/php-fpm_www-data.sock:", host: "www.mydomain.com", referrer: "https://www.mydomain.com/admin/config/system/backup_migrate"

The good thing at least is that I am aware of it.. It could have been potentially very costly had I not had a second backup option in place and now at least I know not to trust these web based backups with nginx.. At least until a solution is found..

That "Permission Denied"

Posted by brianmercer on March 18, 2012 at 3:12pm

That "Permission Denied" suggests that your nginx process can't access the directory that it uses for fastcgi file buffering. So I'd check the ownership of /var/lib/nginx and do this to be safe:

chown -R www-data:www-data /var/lib/nginx

Where did you install nginx from, btw?

And if the truncated output is a matter of nginx fastcgi buffers then you could do this in nginx.conf as well:

fastcgi_buffer_size 32k;
fastcgi_buffers 16 32k;
fastcgi_busy_buffers_size 64k;
fastcgi_temp_file_write_size 64k;

I see in my own config that I

Posted by brianmercer on March 18, 2012 at 3:15pm

I see in my own config that I have this:

fastcgi_temp_path /var/tmp/nginx;

and /var/tmp is 777, world writable, so no permission problems.

Dude you are a genius!! :) I

Posted by wipeout_dude on March 18, 2012 at 3:26pm

Dude you are a genius!! :)

I checked /var/lib/nginx/fastcgi which was owned by www-data and writeable but didn't check the 0 - 9 directories below that.. They were owned by root.. As were the 01 - 10 directories below that..

I "chown -R *" in the /var/lib/nginx/fastcgi directory and tested again and its all working fine now..

Thanks!

Great. I wonder how that

Posted by brianmercer on March 18, 2012 at 3:35pm

Great. I wonder how that could happen.

You have

user www-data;

in your nginx.conf? What package did you use to install nginx, btw. Or was it from source?

I am using nginx from

Posted by wipeout_dude on March 18, 2012 at 4:37pm

I am using nginx from http://ppa.launchpad.net/nginx/stable/ubuntu

Perhaps I'll try clear it out and recreate the directories this evening.. Just glad its working properly now so I can carry on with my work on the site and can easily pull off backups as needed..

I'd also clear out all the

Posted by brianmercer on March 18, 2012 at 3:52pm

I'd also clear out all the buffers with

rm -r /var/lib/nginx/*

and restart nginx and then run another backup and see if nginx writes the buffer directories with the correct ownership this time.

Nginx

Group organizers

Group notifications

This group offers an RSS feed. Or subscribe to these personalized, sitewide feeds:

AltStyle によって変換されたページ (->オリジナル) /