Ubuntu – Mysql large database backup

database-administrationdatabase-backupMySQLUbuntu

How can I backup a large database in mysql?
Folder size of Database is more than 5GB & contains 1.5 Lakh tables.

I have tried to backup server through phpmyadmin but no luck.

Best Answer

At a 5GB data size, you almost surely cannot and should not use a dump (myslqdump, phpmyadmin, etc) as a backup. The reason isn't the backup. The reason is because the restore will take a long time, potentially many days depending on the table structure and your server hardware. You need some type of file backup. Whether you use LVM snapshot or Percona XtraBackup or rsync or something else will depend on the storage engine you use, the hardware, filesystem, and a number of other factors. This is much too complex of a question to answer without a lot more detail, but I can simplify as follows:

  • Whatever you do should be recovery-focused, not backup-focused, so first determine your RPO (recovery point objective) and RTO (recovery time objective) and make sure that your solution will satisfy those requirements.
  • If you use only InnoDB, or mostly InnoDB except for system tables and maybe a few other small and infrequently updated tables, then Percona XtraBackup is potentially a good option.
  • Otherwise you should consider a filesystem (LVM) snapshot technique.
  • If you cannot do this, then you probably need to take backups from a replica, and you may want to do this anyway. But setting up the replica to begin with requires a backup, and you also need to verify the replica's data integrity routinely (weekly at a minimum) with pt-table-checksum from Percona Toolkit.
Related Topic