4 Tips for working with MySQL

Working as a search engine optimisation programmer I have found these tips useful in my day-to-day work when altering MySQL databases.

Synchronize MySQL tables

Writing code on any website whilst it is in use can be tricky, but doing mission critical code work on an e-commerce site can be down right dangerous. So it is imperative that you make a copy of that database locally so that you can work on it in a test environment.

Here is something I picked up the other day which allows you to make a copy of a remote database on your local machine. The only proviso is that you are running MySQL on a Linux (or UNIX) machine.

To synchronize tables sitting on a local MySQL server to a remote MySQL server do the following.

mysqldump -al --user=[local-username]
--password=[local-passwd]
-h localhost
--add-drop-table [local-db-name] |
mysql -h [remote-host]
--user=[remote-username]
--password=[remote-passwd] [remote-db-name]

To synchronize tables sitting on an online MySQL server to a local MySQL server do the following.

mysqldump -al -h [remote-host]
--password=[remote-passwd]
--user=[remote-username]
--add-drop-table [remote-db-name] |
mysql --user=[local-username]
--password=[local-passwd]
-h localhost [local-db-name]

Simple!

Toggle a TINYINT field

The datatype TINYINT is used by MySQL to store boolean values. MySQL stores the value as TINYINT(1) which is the same as a bit so the value is either 0 (false) or 1 (true). Here is a simple bit of code if you ever want to toggle the value that is already present in this field without having to see what that value is first.

UPDATE table_name SET field_name = 1 - field_name

REPLACE

The REPLACE command in MySQL works in much the same way as INSERT. It has exactly the same syntax, you just need to swap the command INSERT with the command REPLACE. INSERT works by inserting a row into a database, as you would expect. REPLACE works by looking to see if the primary key for the table has already been entered. If it is not then the row is inserted, but if it has then the offending row is deleted and the new row is inserted. This can save a lot of time (and overhead) if you want to keep updating and inserting data into a database, especially if you are not sure if the data is already present. You don’t need to run a SELECT query and analyse the data first just to make sure, REPLACE will do that for you on the server side.

GUI for MySQL

Finally, a couple of tools that I use quite a bit are the free MySQL administration tools that can be downloaded from the MySQL site. Officially called MySQL GUI Tools the download contains a MySQL administrator, query browser and a migration toolkit.

The MySQL administrator will allow you to set any configuration for a MySQL server as well as schedule backups, user administration and even replication.

The MySQL query browser is a fantastic resource if you are administering a database, or just trying to learn SQL. Providing you have sufficient access you can create or drop tables and schemas, run single SQL commands, or full SQL scripts. In MySQL 5 you can also create views and stored procedures.

The MySQL migration toolkit is a neat graphical version of what I talked about in the first tip, that of replicating the data and tables from one database to another. The migration toolkit is much more than just an application that can copy a database. You can take the data from a database (not just MySQL), completely alter it and then insert it into a different database. The good thing about this tool is that it doesn’t act as a funnel for all of the data running through it. You can migrate between two external databases and none of the exchanged data will run through the migration tool.

I hope some of these tips are useful to you.

Phil,
Programmer, Research and Development