Do not set MySQLs slave_net_timeout too small... If your master is bursty, and your replication delay backs up... Disaster ensues! slave_net_timeout was set to 20s on a server farm of ours. Slaves ended up having a new relay log file created every 20s of 300bytes. Hours later the filesystem was jacked.
Thursday, February 5, 2009
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)