Databases Reference
In-Depth Information
Locking, flushing, and unlocking tables
You're bound to need to lock and/or flush one or more tables. You can either lock
the desired tables by naming them all, or just lock everything globally:
mysql>
LOCK TABLES
<database.table>
READ [, ...];
mysql>
FLUSH TABLES;
mysql>
FLUSH TABLES
<database.table>
[, ...];
mysql>
FLUSH TABLES WITH READ LOCK;
mysql>
UNLOCK TABLES;
Be very careful about race conditions when getting lists of tables and locking them.
New tables could be created, or tables could be dropped or renamed. If you lock
and back them up one at a time, you won't get consistent backups.
Flushing binary logs
It's handy to ask the server to begin a new binary log (do this after locking the
tables, but before taking a backup):
mysql>
FLUSH LOGS;
It makes recovery and incremental backups easier if you don't have to think about
starting in the middle of a log file. This does have some side effects with regard to
flushing and reopening error logs and potentially destroying old log entries, so be
careful you're not throwing away data you need.
Getting binary log positions
Your script should get and record both the master and replica status—even if the
server is just a master or just a replica:
mysql>
SHOW MASTER STATUS\G
mysql>
SHOW SLAVE STATUS\G
Issue both statements and ignore any errors you get, so your script gets all the
information possible.
Dumping data
Your best options are to use
mysqldump
,
mydumper
, or
SELECT INTO OUTFILE
.
Copying data
Use one of the methods we showed throughout the chapter.
These are the building blocks of any backup script. The hard part is to script the man-
agement and recovery tasks. If you want inspiration for how to do this, you can take a
look at the source code for ZRM.
