If someone asks me about MySQL Backup advice my first question would be if they have LVM installed or have some systems with similar features set for other operation systems. Veritas File System can do it for Solaris. Most SAN systems would work as well.

What is really needed is ability to create atomic snapshot of the volume, which can be later mounted same as original file system

Why snapshot based MySQL backups are great ?

There are number of reasons:

Almost Hot backup In most cases you can perform this type of backup while your application is running. No need to shut down server, make it read only or anything like it.

Support for all local disk based storage engines It works with MyISAM and Innodb and BDB, It also should work with Solid, PrimeXT and Falcon storage engines.

Fast Backup You simply do file copy in the binary …


'stop')

echo -n 'Are you sure? (y|N)';
read SANITYCHECK;
if [ $SANITYCHECK != 'y' ]; then
echo "EXITING INPUT NOT y";
exit 1;
fi



Why? Well imagine you have 20 servers running mysql for the site. You spent 100K to get these servers redundant, automatically failover if a few go down-which gets the company a High Availability score of 99.999% in the database backend and then someone runs

dsh -N ALL_MY_DB_SERVERS /etc/init.d/mysql restart

when the intention was to restart another process like say the monitoring process.



DOH!!

I have now uploaded my MySQL Playground and you can get it from

http://www.db4free.org/mysql-playground.tar.gz

The size of the file is 1 GB, so please take into account that it might take some time to download the file.

After download, just extract it to your "Virtual machines" directory:

tar xvfz mysql-playground.tar.gz

... and load it into your VMWare Server (should work for VMWare Workstation, VMWare Player too, I guess). I have assigned 768 MB RAM to this machine, so depending on your total amount of memory available you might have to change this.

The virtual machine runs SuSE Linux 10.1 (32-bit). The root password is simply "root" and there's a normal user named "user" with password …

best practices

Most developers are used to programming in procedural or object-oriented languages. SQL, as a declarative language, is quite different. In declarative languages like SQL, you program what you want the result to be, not the procedure to get it. For instance, “give me all the people with the first name starting with the letter S from a certain table.” Unlike procedural programming (or even methods in object-oriented languages), you do not say how to get the information. This is, I believe, why many developers want to give the query optimizer “hints” on how to do its job.

That being said, I will list the top 8 Basic SQL Practices I live by, and attempt to enforce. Please feel free to comment adding your own (or post your own, linking back here).

In no particular order:

1) Always use explicit joins. If I mean …

Securing plain text passwords in MySQL is NEVER a good idea. As a DBA you should take great care in protecting the users' information. Fortunately MySQL provides you with several options to protect passwords.

After a quick scan of the manual, you may be tempted to store the password by applying the password function to it which is NOT a good idea. MySQL itself advises against using PASSWORD to manage application passwords.

Instead of using PASSWORD(), we can use SHA1 or MD5. Unfortunately exploits for both of these encryption functions have been quite common these days. Still, SHA1 or MD5 keep your password more protected than storing them as plain text.

You can apply SHA1 algorithm to a password string:
…


'stop')

echo -n 'Are you sure? (y|N)';
read SANITYCHECK;
if [ $SANITYCHECK != 'y' ]; then
echo "EXITING INPUT NOT y";
exit 1;
fi



Why? Well imagine you have 20 servers running mysql for the site. You spent 100K to get these servers redundant, automatically failover if a few go down-which gets the company a High Availability score of 99.999% in the database backend and then someone runs

dsh -N ALL_MY_DB_SERVERS /etc/init.d/mysql restart

when the intention was to restart another process like say the monitoring process.



DOH!!

Back in January I posted a simple MySQL duplicate index finder tool. Because I read requests for such a tool on the MySQL Performance Blog I decided to open a new project on Google's code hosting service as well as a new blog to track it.

So if you are interested and maybe even want to contribute to it, go have a look.

After a successful training (teaching) week, I'm on my way back home.
I also had a chat at AuckLUG (facilitated by the Auckland Novell
offices) and got invited to an "NZ 2.0" meeting at Galbraith's
brewhouse by Nat Torkington. That was particularly interesting.

Airports are always good fun, lots of computers and other signs at
work. On the way in the main arrivals/departures displays were low on
virtual memory (photo attached), one the way out I saw a shop
advertising LCD tell that its Norton Antivirus subscription had
almost expired and that it had protected against 78000 viruses
already (how can that box possibly be so exposed?) and another screen
in the departure lounge displayed a blue screen of death. Not picking
on Auckland, this is pretty typical. Sigh.

There was also a sign on a garbage bin saying "no spitting, please
use …

After a successful training (teaching) week, I'm on my way back home.
I also had a chat at AuckLUG (facilitated by the Auckland Novell offices) and got invited to an "NZ 2.0" meeting at Galbraith's brewhouse by Nat Torkington. That was particularly interesting.

Airports are always good fun, lots of computers and other signs at work. On the way in the main arrivals/departures displays were low on virtual memory (photo attached), one the way out I saw a shop advertising LCD tell that its Norton Antivirus subscription had almost expired and that it had protected against 78000 viruses already (how can that box possibly be so exposed?) and another screen in the departure lounge displayed a blue screen of death. Not picking on Auckland, this is pretty typical. Sigh.



There was also a sign on a garbage bin saying "no …