Thursday, January 15, 2009
Case sensitivity in MySQL/Linux/Perl
A fun interesting-ism of MySQL is case insensitivity for just about everything. Bewares though! The Perl DBI interface is not entirely case insensitive! In attempts to make things clean and look great our friendly local DBA converted all tables to lowers. Thus breaking many Perl scripts :(
Backups
Creating a consistent backup in MySQL is very simple.
FLUSH TABLES with READ LOCK;
[MyISAM - copy all DB files from the filesystem]
Tables must be flushed to disk and write locked. For the love of god, do not just copy files while the database is live. Not only is resultant copy corrupt requiring a table repair/check - you have no idea at what point the "snapshot" was taken. Additionally, running sync() is redundant, but some folks suggest it.
Alternatively, my favorite method is a simple SELECT [data you want saved] INTO while the database is running.
FLUSH TABLES with READ LOCK;
[MyISAM - copy all DB files from the filesystem]
Tables must be flushed to disk and write locked. For the love of god, do not just copy files while the database is live. Not only is resultant copy corrupt requiring a table repair/check - you have no idea at what point the "snapshot" was taken. Additionally, running sync() is redundant, but some folks suggest it.
Alternatively, my favorite method is a simple SELECT [data you want saved] INTO while the database is running.
Subscribe to:
Posts (Atom)