Development

Speed up MySQL dump import

During my work, sometimes I need to restore some recent SQL dump of production database. Sometimes dumps are over few GB, and I do not need all the info there for my task.

Yesterday I was restoring dump where 80% of data was stored in tables log and log_item.
Actually, I did not need this data. Restoring the dump without this tables would save me at least hour of my time.

As usually, I started searching for some ready solutions. And did not find any that was matching my needs. Most forums threads suggested generating dump where this tables would be initially ignored. Unfortunately, that was not my case because dump was already generated.

So I came up with my own resolution of the problem.

For my file dump.sql, I have executed the following script in linux shell console:

sed ‘/INSERT INTO `log`/d’ dump.sql | \
sed ‘/INSERT INTO `log_item`/d’ > dump-lite.sql

I got file dump-lite.sql, and it was much smaller then the original file.
That saved my time. Hope, some day it will save your time too.

Development

Dump single row

Today I have faced situation when I needed to write migration script that would delete single row from DB table.
In this case I also needed to write a method that would perform an opposite action – restore deleted row.
Table had many columns, and I was very lazy and did not want to write `insert` query manually.
Table was a big one, so dumping all rows was also not an option – it could be hard to open such file in text editor.

After googling for similar solutions and collecting different pieces I have wrote my own.

Here is how it works, as example, demonstrated on the Sakila DB actors table. For example we need to export actor #2:

mysqldump -u root sakila actor --no-create-info --skip-add-locks --comments=0 --where="actor_id=1" | grep -v '^/*![0-4][0-9]{4}.*/;$'

MySQL flags used:

–no-create-info – means I do not need to export CREATE TABLE code
–skip-add-locks – means I do not need LOCK TABLE statements
–comments=0 – means I do not need any comments
–where=”actor_id=1″ – means I need to export only those rows which match the following condition

Since even after providing all this flags result still contained version-specific SQL statements, I have added the following grep expression to clean the result:

grep -v '^/*![0-4][0-9]{4}.*/;$'

Finally, here is the output:

INSERT INTO `actor` VALUES (1,'PENELOPE','GUINESS','2006-02-15 02:34:33');
Development

Adding multiple DB columns to a MySQL table

Some time ago I find out one of the most widespread mistakes that I did when wrote SQL patches to alter MySQL tables.
For my project, I needed to add 10 new columns to a 2.5 million rows table.
I wrote a path that looked the following way:

ALTER TABLE `table1` ADD COLUMN `column1` int(10) unsigned DEFAULT NULL;
ALTER TABLE `table1` ADD COLUMN `column2` int(10) unsigned DEFAULT NULL;
ALTER TABLE `table1` ADD COLUMN `column3` int(10) unsigned DEFAULT NULL;
ALTER TABLE `table1` ADD COLUMN `column4` int(10) unsigned DEFAULT NULL;

When I tested the patch, it was executing too long, and it made me very sad.
I tried to check out why does it take so long to execute this batch.

When I entered SHOW PROCESSLIST to see what is MySQL doing so long, I saw that most of time MySQL is `copying data to tmp table`.
And it was doing it when executing each ALTER query.

So, I decided to rewrite patch the following way:

ALTER TABLE `table1` ADD COLUMN `column1` int(10) unsigned DEFAULT NULL,
ADD COLUMN `column2` int(10) unsigned DEFAULT NULL,
ADD COLUMN `column3` int(10) unsigned DEFAULT NULL,
ADD COLUMN `column4` int(10) unsigned DEFAULT NULL;

New patch was executing much faster, since MySQL needed to copy data to tmp table only once.
That has helped me very much since I had very short time frame to patch the live DB.

Such small thing..
Bad that I did not know this before. Good that now I know it.

Development

Designing multi-timezone application

Sometimes I need to design application so that people from different timezones will be able to work with same shared data. This data records will have DATETIME columns. Application will need to display same date to different people with different timezones. Ability to filter data records by date ranges  should be also implemented.

In such cases it is very important to design application correctly from the begining, because it will be hard to make such important changes later, when some functionality will be ready.

Here is the way I usually do it.

 

Storage

First we need to decide in which timezone will we store DATETIME values in the DB. I usually store all date values in the database in UTC timezone. When selection is done, it is important to care about the way how your application connects to database. For this needs, after  connecting to DB my application runs the following init statement:

SET time_zone = UTC

After running this statement connection will work in UTC timezone. For example, running MySQL’s NOW() will return value in UTC timezone.

 

Manipulation

Most common operations our application will need to do will be:

– searching some records by user’s date range

– displaying dates in user’s time zone

Let’s imagine that our user’s timezone is ‘Europe/Kiev’. Also, let’s imagine that we have a simple DB table `news` which has 3 columns: INT `id`, DATETIME `created_ts`, VARCHAR `title`.

Task 1 – find ID’s of the records which were created today (it’s ‘2015-03-15 08:00:00’ in user’s timezone).

Solution:

SELECT `id` FROM `news`

WHERE DATE(`created_ts`) = DATE(CONVERT_TZ('2015-03-15 08:00:00', 'Europe/Kiev', 'UTC'));

 

Task 2 – select last 5 records in the following format: id, title, local_date.

Solution:

SELECT `id`, `title`, CONVERT_TZ(`created_ts`, 'UTC', 'Europe/Kiev') AS local_date FROM `news` ORDER BY `created_ts` DESC LIMIT 5;

 

Summary

It is extremely important to design multi-timezones applications correctly from the begining. However, the solution is very easy, you simply need to follow two important rules all the time:

1) Store all DATE and DATETIME values in DB in single timezone.

2) Do not forget to set correct timezones for each DB connection.

2) Convert DATE and DATETIME values from one timezone to oher when doing requests to DB.

Combining all the mentioned recommendations you can design a multi-timezone MySQL-based web application so that it will be easy to scale it and re-use it’s data for multiple purposes in future.

Server Administration

Upgrading Apache from 2.2 to 2.4.

Few weeks ago I found that my production web-server is affected by several vulnerabilities. After researching, I have realized the fact that all of them are related with server software, but not the application itself.

So, I decided to upgrade software.

One of the thing I have updated was Apache web server.

I have expected that everything will go fine.

But unfortunately after performing update server did not start successfully.

I have spent next hour pulling hair out of my head and fixing all the problems one by one.

After reading some docs and stackoverflow threads I understood that if I read all this important things before, I could avoid putting the server down.

So, I am writing this article to share this knowledge with myself (for future) and you too.

Configuration changes and transformations.
Deny from all

Order deny,allow
Deny from all

Now becomes:

Require all denied

Allow from all

Order allow,deny
Allow from all

Now becomes:

Require all granted

Config files includes

In file /etc/apache2/apache2.conf the following section

Include sites-enabled/

Now becomes

IncludeOptional sites-enabled/*.conf

So, if your sites-enabled files did not have .conf extension, you need to update their extensions now or update this configuration value.

I have mentioned most important problems I have faced. You can grab more details about updating apache at the official page devoted to this problem. This page contains much more information then I have mentioned http://httpd.apache.org/docs/2.4/upgrading.html