0

i have a mysql database where i need to take a backup of the log database.

the problem is the log database i over 200gb and contains over 280 millions of rows, i need right now to remove a kind of data but before i can do it i need to take a backup.

i have trying mysqldump function but the problem is here its lock everything and block the website there running right now, and i have trying to do it over ssh tunnel the problems is.

1) the database its contain over 200gb data 2) the internet line its between 20-40mbit internet in download 3) the web server's its pretty slow and i have calculate its take over 4-5 hours thats mean the store is down in thats time.

are there a easy and smart method to take a remotely backup of so larges database before i remove something in the log table?

2

If your big tables are innodb, you can use --single-transaction to not lock the table.

Other possibility is Percona XtraBackup

Or pt-archiver (again Percona)

  • Thanks for helping, i study more of single-transaction and more stuff about mysqldump and i have this artcile back now, and you help me to resovle the problem thanks a lot. - article: parisnakitakejser.com/backup-huges-mysql-database-200gb – ParisNakitaKejser May 22 '15 at 7:59
  • 1
    just small note: "just only the single row you create backup of" - this does not seem right to me - --single-transaction will start one (read-only) transaction, so it can use innodb row versions to keep working on other things, default locks on mysqldump were meant for myisam afaik, where transactions are not supported so dump without locks is inconsistent. – jkavalik May 22 '15 at 8:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.