What’s the Best Way to Backup a Large MySQL Database?

When you see the "MySQL has gone away" error, it often means the server restarted for some reason, such as software updates. It might be worth investigating the timing of these restarts and seeing if you can adjust your backup schedule. Checking the MySQL server error log and cron log could provide insights. For backup alternatives, you might look into online tools like Percona Xtra Backup for incremental backups.

Depending on your OS, I run a cron job every night at 3 AM that backs up my 75GB MySQL database table by table, along with related site code and configurations, to an AWS S3 bucket using s3cmd. To manage costs, the S3 bucket removes files older than 7 days, and it takes less than 10 minutes to run without any MySQL connectivity issues. Here’s a rough outline of the script I use if you're interested.

For large MySQL databases, consider image-based backups or snapshot backups like LVM or EBS. They can capture the entire data volume quickly without putting much strain on the DB server. This method allows for fast and reliable recovery points, especially if you combine it with flushing tables or using lock steps for consistency.

Dumping the database shouldn’t lead to any errors, no matter how large it is. You should first check why your backups are failing. An ideal solution would be to set up replication and take dumps from the replica for snapshots, although this requires some initial configuration on the database side. A useful tool for MySQL dumps is mydumper, which supports multithreading and can split dumps into separate files per table.

You might want to dig deeper into mysqldump features and configure your server settings that might be causing the failures, potentially checking the max packet size. I back up a 350GB table every night without any issues.