Home » Posts tagged "MySQL"

SQL: Find & Replace text in all records

On type of query I always have to run when moving a website from its test environment to its live environment is to replace the test domain in all content pages with the live domain. With SQL this can be easily done with the REPLACE(text, search, substitution) function.

For example:

UPDATE some_table
SET some_field = REPLACE(some_field, 'test.example.com', 'example.com')

This updates all records in the table some_table and replaces test.example.com with example.com in the field some_field.

Knowing which SQL functions exist can really help a lot and save time to update data.

Prevent a MySQL query from using the query cache


Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in /var/www/blog_htbaa_com/www/wp-content/plugins/source-code-syntax-highlighting-plugin-for-wordpress/geshi.php on line 2147

Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in /var/www/blog_htbaa_com/www/wp-content/plugins/source-code-syntax-highlighting-plugin-for-wordpress/geshi.php on line 2147

Whilst I was busy trying to optimize a MySQL query I got annoyed by the fact that when you make use of MySQL’s query cache that a query gets cached. When that happens a second time you execute the query it’s being looked up and the cached results are being returned. If you want to optimize a query and test its performance this can be quite annoying.

Luckily I found out there’s an easy solution to this problem. Just make use of SQL_NO_CACHE┬álike this:

  1.  

Now when you run this query it makes sure MySQL doesn’t store it in its query cache.

Using rsnapshot for daily MySQL backups


Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in /var/www/blog_htbaa_com/www/wp-content/plugins/source-code-syntax-highlighting-plugin-for-wordpress/geshi.php on line 2137

Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in /var/www/blog_htbaa_com/www/wp-content/plugins/source-code-syntax-highlighting-plugin-for-wordpress/geshi.php on line 2137

Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in /var/www/blog_htbaa_com/www/wp-content/plugins/source-code-syntax-highlighting-plugin-for-wordpress/geshi.php on line 2137

Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in /var/www/blog_htbaa_com/www/wp-content/plugins/source-code-syntax-highlighting-plugin-for-wordpress/geshi.php on line 2137

Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in /var/www/blog_htbaa_com/www/wp-content/plugins/source-code-syntax-highlighting-plugin-for-wordpress/geshi.php on line 2137

Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in /var/www/blog_htbaa_com/www/wp-content/plugins/source-code-syntax-highlighting-plugin-for-wordpress/geshi.php on line 2137

Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in /var/www/blog_htbaa_com/www/wp-content/plugins/source-code-syntax-highlighting-plugin-for-wordpress/geshi.php on line 2137

Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in /var/www/blog_htbaa_com/www/wp-content/plugins/source-code-syntax-highlighting-plugin-for-wordpress/geshi.php on line 2137

Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in /var/www/blog_htbaa_com/www/wp-content/plugins/source-code-syntax-highlighting-plugin-for-wordpress/geshi.php on line 2137

Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in /var/www/blog_htbaa_com/www/wp-content/plugins/source-code-syntax-highlighting-plugin-for-wordpress/geshi.php on line 2137

Now that I’m using a CloudServer I figured I had to do something about backups as well. I’ve been using rsnapshot at work for a few years now and it’s an ideal backup solution. Because rsnapshot (which uses rsync as its base) makes use of hard links backups are very efficient. Basically it means that if a file isn’t changed it’s not duplicated, but a hard link is created instead. This way only changed and new files get copied. Deleted files will eventually be removed as well as soon as one of the backups expires.

Using rsnapshot for file backups is easy and the configuration explains this easy enough. Backing up MySQL databases however is a different story. When using MyISAM tables you might get away with copying and pasting the database files. With InnoDB not so much. Proper backups can be made with mysqldump. In my case I don’t have many databases so I want one big SQL backup file.

First, lets set up our backup script. I’ve stored it under /usr/local/bin/backup_mysql.sh with permissions set at 0700. The reason for these permissions is that I store my password inside the script and only want root to be able to open it. It also needs to be executable. I know it’s better to use a configuration file and use source to include the variables, but for my current use it’ll do (do note at work I’ve done this the proper way!). I also use the root user instead of a special read-only user. I know this is bad practice, but again I’ve done this the proper way at work. It’s an exercise for you to do it properly (hehe, that’s another way to put that I’m lazy). Anyway, here’s the script.

  1.  

Nothing exciting. Change the credentials as required. Again, use source if you want to store the credentials inside a proper configuration file. Now, calling this script ./backup_mysql.sh will backup all databases inside the file mysqldump.sql in the current work directory.

Next is setting up /etc/rsnapshot.conf which is easy as well. At the end of this file you’ll find all the instructions of which directories to backup. Enter the next line. Do note that rsnapshot.conf uses a tab to separate values.

  1. backup_script   /usr/local/bin/backup_mysql.sh  mysql/

To enable daily backups uncomment the next line:

  1. #interval       hourly  6
  2. interval        daily   7
  3. #interval       weekly  4
  4. #interval       monthly 3

To make rsnapshot run every day update /etc/cron.d/rsnapshot:

  1.  

Finally, try running rsnapshot. After it has run check your backup directory, which on Ubuntu 10.04 defaults to /.snapshots/. Be sure to mount /.snapshots/ on a separate drive. Because what use is it if your data drive contains your backup and decides to commit suicide?

$ sudo rsnapshot daily

Your MySQL backup file should now be located at /.snapshots/daily.0/mysql/mysqldump.sql. You can restore this file with the MySQL commandline client, or through MySQL Administrator from the MySQL GUI Tools.