WAF probe half1.html

phpMyAdmin is fine for a quick export, but once your database crosses a few hundred MB it starts timing out, truncating exports, or just spinning until the browser gives up. If you're running a VPS - no cPanel, no GUI backup button - you need to know mysqldump cold. Here's how to back up and restore MySQL from the command line without losing data or locking up your live site.

Why the Browser-Based Export Breaks First

phpMyAdmin routes everything through PHP's execution time and memory limits, plus your browser's own connection timeout. A 50MB database exports fine. A 2GB WooCommerce or multisite database usually doesn't - the process either hits max_execution_time and dies mid-export, or the download itself gets cut off and you end up with a truncated .sql file that fails silently on restore.

mysqldump runs directly on the server, outside PHP entirely, so none of those limits apply. It's also the only practical option if you're managing a bare VPS running your own MySQL or MariaDB instance with no control panel in front of it.

Taking a Backup with mysqldump

SSH into the server and run:

mysqldump -u dbuser -p dbname > backup.sql

You'll be prompted for the password interactively - don't put it directly after -p on the command line, since that leaves it sitting in your shell history and in ps aux output for anyone else on the box to see.

For a WordPress site, add a couple of flags that matter more than people expect:

mysqldump -u dbuser -p --single-transaction --quick --lock-tables=false dbname > backup.sql

• --single-transaction - takes a consistent snapshot using InnoDB's transaction isolation instead of locking every table. Without it, a large dump can lock writes on a live site for the entire export.
• --quick - streams rows instead of buffering the whole table in memory first, which matters once a table has millions of rows.
• --lock-tables=false - pairs with --single-transaction so InnoDB tables aren't locked while MyISAM tables (if you have any left) still get a light lock.

"mysqldump: Got error: 1044: Access denied"

Cause: the MySQL user you're dumping with doesn't have SELECT, LOCK TABLES, and SHOW VIEW privileges on that database - common when you created a scoped-down app user for WordPress's wp-config.php and are now trying to reuse it for backups.

Fix: grant the missing privileges or dump as root:

GRANT SELECT, LOCK TABLES, SHOW VIEW, RELOAD ON dbname.* TO 'dbuser'@'localhost';
FLUSH PRIVILEGES;

"mysqldump: Error 2013: Lost connection to MySQL server during query"

Cause: usually one oversized row or BLOB column tripping max_allowed_packet on the server side, not a network issue.

Fix: bump the packet size for the dump session:

mysqldump -u dbuser -p --max_allowed_packet=512M dbname > backup.sql

If it still drops, check SHOW VARIABLES LIKE 'max_allowed_packet'; in a MySQL shell and raise the server-wide value in /etc/mysql/my.cnf under [mysqld], then restart MySQL.

Backup file is 0 bytes or stops halfway

Cause: almost always disk space. mysqldump doesn't check free space before it starts writing - it just writes until the disk is full, then fails partway with no obvious error in your terminal.

Fix: check df -h before every large dump, and dump straight to compressed output so the file never gets that big in the first place:

mysqldump -u dbuser -p --single-transaction dbname | gz