Home / server / Questions / 117197
In Process
mrpatg
Asked: 2010-02-27 01:29:58 +0800 CST

trouble backing up large mysql database

  • 772

I have a wordpress MU database with something like 10,000+ tables for various user's blogs. I need to upgrade wordpress MU to newest version, but want to backup the DB before hand.

PHPMyAdmin fails to even load the page when i click export. Ive tried going into the server (windows) and using dos command line:

mysqldump -u USERNAME -p PASSWORD> BACKUP.sql

but it hangs for a minute and gives me the error:

error 23: out of resources when opinging file '.\USERNAME\wp_1037_links.MYD' (Errorcode: 24) when using LOCK Tables

What am i doing wrong, or should i be doing? Is PHPMyAdmin right for something this size? Is there a better way of doing this than the two methods i tried?

**Note that this is not my site, so any suggestions as to the setup of the DB ill have to run by the owner. Im just here for WP related crap, this is kind of out of scope for what i was brought on to do.

mysql command-line-interface wordpress wordpress-mu

10 Answers

  1. Try the following: mysqldump --lock-all-table -u USERNAME -p PASSWORD > BACKUP.sql

    Be aware that if this is live, it may stop the sites functioning.

    • 1

  2. The error indicates you are running out of resources on the server; the reason is probably due to locking: mysqldump implicitly locks tables while reading them. If there is a lot of activity on the database, MySQL will need to keep a copy of the older data available for the mysqldump transaction; for large enough tables, this can be a real issue.

    If you're not too concerned with data consistency, you can use --skip-lock-tables (-q actually won't work, it only prevents output buffering. This may be useful, but will not solve your problem) and --single-transaction.

    • 1

  3. I had the same problem and I figured its the open_files_limit variable for my.cnf

    just update your my.cnf

    open_files_limit=20000

    [& restart]

    the value should be 2 * number of tables you have in you DB. If that doesn't work, try 3* number of tables. If that also doesn't , don't go incrementing, as that's not the problem in your case. Search for solution again ;-)

    • 1

  4. you could try mysql administrator, but it seem starnge that mysqldump throws such an error.

    • 0

  5. error 23: out of resources when opening file '.\USERNAME\wp_1037_links.MYD'

    This sounds more like a general RAM or hard disk problem on your server. Is that a virtual machine? Check your hard disk quota first. It could be that mySQL is running out of space even trying to access the table - in that case even a compressed backup won't help.

    • 0

  6. The error you are experiencing is:
    shell>perror 23
    Error code 23: Too many open files in system
    shell>perror 24
    Error code 24: Too many open files
    More info here:
    forums.mysql.com/read.php?35,7639,7639#msg-7639

    But as you probably already know mysqldump is not the only way to perform a mysql backup and it's not the best way either.
    I am assuming you're using Innodb as the storage engine, so I would recommend using innodb's 'hot' backup utility : ibbackup.
    ( it can also perform backup for myisam but it has no advantages over mysqlhotcopy)
    But for real Mysql database protection you could recommend activating the binary logs on the mysql server (they say it only decreases performance by 1%) so you could do point-in-time restores as shown here: dev.mysql.com/doc/refman/5.0/en/point-in-time-recovery.html.

    If none of the alternatives above are an option for you why not:
    1. SELECT INTO ... OUTFILE
    2. considering your provider's environment you could do filesystem snapshots.
    3. or (I know I'm pushing it) Mysql replication slaves :)
    4. if using myisam just flush tables with read lock and copy the files
    5. Request upgrade of mysql server to 6.0 (when it reaches GA) and perform backups like this:
    mysql> BACKUP DATABASE 'db-name' to 'c:\backups\whynotswitchtounix.bkp';
    (notice the subliminal message :) )

    • 0

  7. mysqldump -q -u username -ppassword database > file.sql

    -q avoids locking the tables during the dump

    • 0

  8. Indeed, InnoDB Hot Backup utility is a great tool, but you may prefer a free tool recently released by Percona: XtraBackup. It performs online backup of InnoDB and XtraDB tables and can create full and incremental backups.

    • 0

  9. try using gzip to zip your sql files for backup http://www.gzip.org/

    and perhaps extending your virtual memory...

    • -1

  10. make the owner contact the hosting company for a quick backup

    • -2