Back up and restore a database

To back up and restore a database, you export it with mysqldump, compress the file, copy it off the server, and import it into a schema you can afford to break first. A dump is portable SQL for the tables and data you chose. It is not your uploads folder, not cron jobs, and not a full account snapshot. GRANTs live in mysql.* unless you explicitly dumped them. Never leave a backup.sql.gz file inside public_html, because scanners look for those names.

Create the dump and move it off the server

From SSH, run the dump as the database user so you only touch schemas that account can read:

mysqldump -u user -p dbname | gzip > ~/db-$(date +%F).sql.gz

That writes a gzipped SQL file in your home directory with today’s date in the name. The -p flag prompts for the password interactively. Do not put the password on the command line, where process lists and shell history can expose it. If the account has no shell access, use phpMyAdmin → Export and choose gzip. That is the same idea, with a smaller size limit before the web request times out. Copy the finished file to your laptop or object storage. JetBackup runs daily on our side. Your off-box copy is still yours, and you want both.

Restore into a schema you can test first

Create a new schema in cPanel, import the dump there, and point a staging site at it before you touch production. Restoring over the live database because a filename looked right is a common support ticket. Read the schema name in the dump header and in the panel before you pipe anything into mysql. If you must overwrite production, take a fresh dump first, then import during a maintenance window, then bring the application back up against the restored data.

1. Dump and gzip the database. Confirm the file is non-empty and decompresses cleanly.
2. Copy the archive off the server to storage you control.
3. Create a spare schema and import the dump into that schema only.
4. Spot-check tables, users, and a few posts or orders in the application.
5. Only then cut the app over, or replace production if that was the goal.

What a restore does not bring back

A restore does not rebuild indexes you dropped with dump flags, and it does not restore cron or wp-content/uploads. After a large import, wait before you start OPTIMIZE loops on every table. See repair and OPTIMIZE TABLE if the next step is cleaning the new schema.