7

I have an innodb table with 80 million rows and it's extremely slow with mysqldump so I'm thinking of copying the datadir directory for a backup / transfer.

I used to copy database with only MyISAM tables in Windows version of MySQL and it worked perfectly. Now that I'm copying a database directory with a mix of InnoDB and MyISAM tables from Linux to Windows, would this approach work as well?

I'm currently doing this right now but I want to know if I'm missing anything? Ain't InnoDB tables data spread across various places such as in ibdata1? I don't have to backup ibdata1 or anything right?

5

Please tell me you are not (and not even thinking of) using file system copying on a database server that's up and running? That is virtually guaranteed to lead to corruption of your copies, leaving your backups useless. You won't receive any errors - except, of course, when you try to restore :-)

It's not very clear from your post (correct me if I'm wrong), but you're now running your database on Linux? If so, then use Percona's XtraBackup for your InnoDB tables. This tool performs non-blocking hot backups of InnoDB tables.Take a look at my answer here for some MySQL backup options. For MyISAM, you could look at MyDumper if mysqldump isn't performant.

If your server is still on Windows, then you're out of luck. XtraBackup doesn't work on Windows, nor does MyDumper (AFAIK). You could check this out (disclaimer - never used).

Again, I'm not totally clear on what, exactly, you mean here.

Now that I'm copying a database directory with a mix of InnoDB and MyISAM tables from Linux to Windows, would this approach work as well?

If you mean that you are copying the mysqldump from Linux to Windows, then yes, that will work as any file copy. If, on the other hand, you mean that you are copying (using the file system) data files on a running server and tranferring those copies from Linux to Windows, then your "backups" will be useless on both systems.

Ain't InnoDB tables data spread across various places such as in ibdata1? I don't have to backup ibdata1 or anything right?

If you are backing up the database using mysqldump, then that is sufficient. If you are simply copying files from one location to another on a running database server, then it really doesn't matter what you do or don't copy, because your backup will be useless.

[EDIT] in response to the original poster's comments.

From here (correct answer)

mysqldump -uuser -ppass --single-transaction --routines --triggers --all-databases > backup_db.sql

Just substitute your schema name for --all-databases. You say that your "table" is InnoDB - is the rest of the database/schema InnoDB? If not, you should make it so. You seem to think that the datadir has to be copied in some shape or form - it DOES NOT. The dump contains the necessary data to reconstruct your database.

In the event of a problem, you restore your database from the last BACKUP (i.e. the dump). There are other solutions which allow PITR (Point In Time Recovery), but they are more complex and replication can also address this issue.

See also these posts (1, 2).

My take on the content of these posts (check the reputations of the posters) is that with InnoDB, the above command will allow for a backup while at the same time permitting reads and writes to the database - note that performance may be affected.

  • 1
    What if the server is offline thus the database is not up and running? – datasn.io Aug 22 '14 at 10:08
  • 1
    That's fine - using the file system to copy files is only a problem when another programme is modifying them at the same time - you end up with inconsistent copies. If you can do that with your system, great - many people can't afford their systems to be offline while a file system backup is being performed. – Vérace Aug 22 '14 at 11:32
  • how kind of command should I perform before making such a copy so as to make sure the database (with InnoDB tables) is locked against any write AND cache are flushed into the database? Do I just do mysqld stop? What if I cannot stop mysqld? Is there any way to just lock the database for writes and flush all cache into the database? – datasn.io Aug 22 '14 at 14:19
  • YOu said "What if the server is offline thus the database is not up and running?". If that is the case, then the shutdown process will flush everything to disk and you can perform a file system copy (i.e. cp my_files my_destination). As for stopping the mysql daemon, that will depend on your distro but normally it's something like "sudo service mysql stop". I run MySQL from source code, so I do ./bin/mysqladmin -u root -pxyx shutdown. The . directory is my "basedir" for my install. Or maybe for your "sudo /etc/init.d/mysql start"? If you're running Linux and want hot backups, use XtraBackup. – Vérace Aug 22 '14 at 14:27
  • I mean if there is any other way than just turning down mysqld? Such as a simple command to run in mysql command line that locks the specific database against any writes so I can properly copy the its datadir for transfer / backup? Without having to turning down entire mysqld.... – datasn.io Aug 22 '14 at 14:47
2

Per the Cold Backup section of the MySQL Manual (http://dev.mysql.com/doc/refman/5.6/en/innodb-backup.html), you need ibdata1, ib_logfiles.

Obviously, this requires that you stop the server and flush all caches to disk.

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.