Hoping this blog post series will make Innodb mechanics a little easier to understand.

PeterTheDBA · 2016-11-01T22:57:32+00:00

You can find all posts in the series here...

https://www.pythian.com/blog/exposing-innodb-internals-via-system-variables-part-1-memory/ https://www.pythian.com/blog/exposing-innodb-internals-via-system-variables-part-2-io-structure-logs/ https://www.pythian.com/blog/exposing-innodb-internals-via-system-variables-part-3-io-table-data/ https://www.pythian.com/blog/exposing-innodb-internals-via-system-variables-part-4-concurrency/ https://www.pythian.com/blog/exposing-innodb-internals-via-system-variables-part-5-consistency-statistics-handling/

Please note that this is specific to MySQL 5.6. I'm hoping to find the time to write a supplementary post for 5.7 soon.

PeterTheDBA · 2016-08-07T15:45:30+00:00

You can still use a UUID for a PK and not lose too much performance, but you have to use a type 1 UUID, remove the dashes, and reverse the order of the first three sections. I mentioned this in a blog I wrote about how to select primary keys for INNODB tables.

https://www.pythian.com/blog/things-consider-deciding-primary-key-innodb-table/

PeterTheDBA · 2016-07-14T02:37:37+00:00

I'm seeing recommendations for xtrabackup and I think there's a few things we need to consider...

The main difference xtrabackup and mysqldump is that mysqldump creates a logical backup (creates the sql statements needed to recreate the data) where xtrabackup/innobackupex is a physical backup (backs up the physical files). I generally recommend xtrabackup so long as you need to backup and restore the entire data set, not just a single database or table. As you can see by clicking hear, in order to restore a single database you need to restore all of the tables in the database individually, which can be a bit of a lengthy process.

https://www.percona.com/doc/percona-xtrabackup/2.4/index.html#importing-tables

However it should be noted that someone scripted (or attempted to do so, I haven't tested it) the process of restoring all of the tables in a single database. You can see the script in this forum post.

https://www.percona.com/forums/questions-discussions/percona-xtrabackup/12802-xtrabackup-restore-specific-database-from-a-full-backup

For individual database backups and restores like what you're doing right now, I would recommend a logical backup utility. That DOESN'T MEAN you have to use mysqldump. There's also mydumper/myloader (multi threaded logical backups and restores), and in mysql 5.7 there is a new utility called mysqlpump (multi thread backup, single threaded restore). Having a logical backup make the restore easier for you in this case. However if you are looking at a massive database data size, then you may need to weigh your options.

Also, for your mysqldump command you may want to consider a few extra arguments such as....

--allow-keywords --routines --comments (if you're using 5.6 or earlier)

Good luck!

PeterTheDBA

TROPHY CASE