Monday, July 22, 2013

MySQL Sample Databases

I saw a post on the site about the sample databases and I thought it might be worth a post to give a quick overview to them for others.

The Sample databases can be found here:
You can load these databases via the MySQL command line:

$ tar -vxf sakila-db.tar.gz
$cd sakila-db
$ mysql -u root -p < sakila-schema.sql
Enter password:
$ mysql -u root -p < sakila-data.sql
Enter password:

$ gzip -d world_innodb.sql.gz
$ mysql -u root -p -e "DROP SCHEMA IF EXISTS world";
Enter password:
$ mysql -u root -p -e "CREATE SCHEMA world";
Enter password:
$ mysql -u root -p world < world_innodb.sql
Enter password:

You get the idea. Sakila example database has the Drop SCHEMA and CREATE SCHEMA commands in the file so no need to do that step for that SCHEMA.

You can also use MySQL Workbench to load this data.
  • Create a connection handle to you database. 
  • Use this newly created connection handle to set up a Server Administration instance. 
  • Double click on your new instance.
  • Under Data Export / Restore you should see a Data import.
  • Import from a Self-Contained File
    • File path will be the location of your sakila-schema.sql then repeat for sakila-data.sql
    • You can select a schema or create a new one in the case of world.
    • Select start import and then you will be on the Import Progress view.
You now have access to the sample databases in your database. 

$ mysql -u root -p
> use sakila
> show tables;
> select * from actor limit 3;
| actor_id | first_name | last_name | last_update         |
|        1 | PENELOPE   | GUINESS   | 2006-02-15 04:34:33 |
|        2 | NICK       | WAHLBERG  | 2006-02-15 04:34:33 |
|        3 | ED         | CHASE     | 2006-02-15 04:34:33 |
Via workbench :
  • Close the admin tab
  • Select your connection handle under the SQL Development
  • You can either just type in select * from actor limit 3; and hit the lighting bolt.
  • Or you can type parts of the command and double click on table name or column names to have it populate the names for you. Then select the lighting bolt. 
Now you have data to start playing and learning with.

If you want to add tables to this you can use the MySQL command line or SQL Development and right click on "Tables" under the Schema of your choice and "create new table"

Wednesday, July 17, 2013

Check in on your status variables in MySQL.

So you have your database running as well as expected.
But is it ? Could it be operating better?

When is the last time you checked on some of your status variables ?

Some key status variables to monitor are:

So to put it simply.... check your status !

Monday, July 15, 2013

MySQL Distributions Survey

I have created this general MySQL Distributions Survey. The results will be available at the end of the survey. All questions are required ( only 4 questions ) I have tried to target each survey to the language that this blog is presented in.
Results are not going to any of the MySQL Distributions but here for public viewing.

Please take the survey here:

Friday, July 12, 2013

Export CSV directly from MySQL

First another blog post about this is here:
 But since I saw this posted on the I thought I would give a little longer example.

So for this example I am using the World database. It is available free to download here:

mysql>desc City;
| Field       | Type     | Null | Key | Default | Extra          |
| ID          | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name        | char(35) | NO   |     |         |                |
| CountryCode | char(3)  | NO   | MUL |         |                |
| District    | char(20) | NO   |     |         |                |
| Population  | int(11)  | NO   |     | 0       |                |

mysql> SELECT ID, Name, CountryCode , District , Population
 INTO OUTFILE '/tmp/City_data.csv'
 FROM City ;
Query OK, 4079 rows affected (0.04 sec)

# more /tmp/City_data.csv

Thursday, July 4, 2013


A recent forum post made me stop and think for a moment..,589573,589573#msg-589573

The problem was the user wanted to update just the word audi and not the word auditor.
It was solved by taking advantage of the period easily once I stopped trying to use SUBSTRING and LOCATE.  They wanted a quick and easy fix after all.

root@localhost [test]> CREATE TABLE `forumpost` (
    ->   `name` varchar(255) DEFAULT NULL
    -> ) ENGINE=InnoDB;

root@localhost [test]> insert into forumpost value ('An auditor drives an audi.'),('An auditor drives a volvo.');

root@localhost [test]> select * from forumpost;
| name                       |
| An auditor drives an audi. |
| An auditor drives a volvo. |

So now let us update it the quick and easy way by taking advantage of the period

root@localhost [test]>UPDATE forumpost SET name = REPLACE(name, 'audi.', 'toyota.') WHERE name LIKE '%audi.';
Query OK, 1 row affected (0.20 sec)
Rows matched: 1  Changed: 1  Warnings: 0

root@localhost [test]> select * from forumpost;
| name                         |
| An auditor drives an toyota. |
| An auditor drives a volvo.   |

But... what about the valid options of SUBSTRING and LOCATE.....

root@localhost [test]> insert into forumpost value ('An auditor drives an audi.');
root@localhost [test]> insert into forumpost value ('An auditor drives an audi car');
root@localhost [test]> select * from forumpost;
| name                          |
| An auditor drives an toyota.  |
| An auditor drives a volvo.    |
| An auditor drives an audi.    |
| An auditor drives an audi car |

First test your options so you make sure you can find what you are after..

root@localhost [test]> SELECT * FROM forumpost WHERE name REGEXP 'audi car$';
| name                          |
| An auditor drives an audi car |

root@localhost [test]> SELECT * FROM forumpost WHERE name LIKE '%audi car%';
| name                          |
| An auditor drives an audi car |

That really did not do too much since we just changed the period for the word car.  So keep going....

We need to pull just the word audi from the line with audi car.

root@localhost [test]> SELECT SUBSTRING(name,-8,4), name FROM forumpost WHERE SUBSTRING(name,-8,4) = 'audi';
| SUBSTRING(name,-8,4) | name                          |
| audi                 | An auditor drives an audi car |

The SUBSTRING allowed me to pull the first 4 characters after I counted back 8 characters from the end.

So what if you do not know the location of the characters?
To start with you should review you data to make sure you know what you are after. But the characters might move around your string so lets work with LOCATE.

I will add another row just for tests.

root@localhost [test]>  insert into forumpost value ('An auditor drives an red audi car');
Query OK, 1 row affected (0.04 sec)

root@localhost [test]> select * from forumpost;
| name                               |
| An auditor drives an toyota.       |
| An auditor drives a volvo.         |
| An auditor drives an audi.         |
| An auditor drives an audi car      |
| An auditor drives an audi blue car |
| An auditor drives an red audi car  |

So regardless of the ending we can see that audi always after auditor so we just need to skip over that word. The word auditor is in the first 8 character so skip those.

root@localhost [test]> SELECT LOCATE('audi', name,8), name FROM forumpost WHERE LOCATE('audi', name,8) > 0 ;
| LOCATE('audi', name,8) | name                               |
|                     22 | An auditor drives an audi.         |
|                     22 | An auditor drives an audi car      |
|                     22 | An auditor drives an audi blue car |
|                     26 | An auditor drives an red audi car  |

OK so we found the ones we are after. Now we need to write the update statement.

We cannot use the replace this time.

 UPDATE forumpost SET name = REPLACE(name, LOCATE('audi', name,8), 'mercedes') WHERE LOCATE('audi', name,8) > 0 ;
Query OK, 0 rows affected (0.02 sec)
Rows matched: 4  Changed: 0  Warnings: 0

Notice it found the rows but did not change anything.

So try again and I do not want to assume the location 8. I want the 2nd value of audi.
So  a test shows that with SUBSTRING_INDEX I can skip the 1st one and USE CONCAT

SELECT name , CONCAT ( SUBSTRING_INDEX(name, 'audi', 2) , ' mercedes ' , SUBSTRING_INDEX(name, 'audi', -1) )  as newvalue
FROM forumpost
WHERE LOCATE('audi', name,10) > 0 ;
| name                              | newvalue                                |
| An auditor drives an audi.        | An auditor drives an  mercedes .        |
| An auditor drives an audi.        | An auditor drives an  mercedes .        |
| An auditor drives an audi car     | An auditor drives an  mercedes  car     |
| An auditor drives an red audi car | An auditor drives an red  mercedes  car |

root@localhost [test]> UPDATE forumpost SET name = CONCAT(SUBSTRING_INDEX(name, 'audi', 2) , ' mercedes ' , SUBSTRING_INDEX(name, 'audi', -1) )
WHERE LOCATE('audi', name,10) > 0 ;
Query OK, 4 rows affected (0.03 sec)
Rows matched: 4  Changed: 4  Warnings: 0

root@localhost [test]> select * from forumpost;
| name                                    |
| An auditor drives an  mercedes .        |
| An auditor drives a volvo.              |
| An auditor drives an  mercedes .        |
| An auditor drives an  mercedes  car     |
| An auditor drives an red  mercedes  car |
5 rows in set (0.00 sec)

Now, granted the grammer with the use of "an" is invalid but that is another story.

More information on these can be found here:

Tuesday, July 2, 2013

Understand your MySQL Server

I figured it might be helpful to write out a few guidelines to help everyone understand different aspects of their MySQL server.

This data has been gathered over time and I will do my best to reference posts. Overall consider your best sources of information to be listed here:
So for the sake of this example let us assume you  have just taken over responsibility for a MySQL server. What is the best way to gather valid need to know information...

Do you know the password to the database ? If it is not using the mysql_config_editor (so pre 5.6) you can do a little digging to find out. Otherwise you have to restart it and force your way in. We assume you do not want to restart it. If you have to figure out how to use --skip-grant-tables review some previous blog posts with examples:

So first some placed to look for the password. This also means that if you do one of these people can find the password later.
  • find out if someone used the password on the command line. 
    • history | grep mysql
  • crontab -l
    • If you see backup scripts or etc look in those scripts for a password 
  • locate .my.cnf 
  • cat .bash_history | grep mysql
  • try mysql without a password but of course the hope is your box is secure.
  • With MySQL 5.6 check if you have a configured account already 
    • mysql_config_editor print --all 
OK so you have the account information and you are on the box.
Before you jump into the MySQL server know a little about your server first.
Some of this information you might just know but to be safe.
Run the following on a Linux system for example to get the OS, Distro , Memory, disk information.
  • cat /etc/*-release
  • uname -a
  • df -ah
  • mount
  • free -m
  • top
  • Look over this post for Swappiness info
  • Do you have a specific cnf file being used?
    • ps -ef | grep mysql | grep cnf
    • Datadir : 
      • ps -ef | grep mysql | grep datadir

So now jump into the MySQL server and start to gather some data. While a review of the my.cnf file can give you most of this information you can also gather it from the server.

show variables like '%log%';

Look over your error (.err) file to review any problems. This should be the first place you look after an error with start up and etc.

So how is the server running? To quickly gather the following run the status command.
  • Version
  • Threads
  • Questions
  • Slow queries
  • Opens
  • Flush tables
  • Open tables
  • Queries per second avg

mysql> status

The "mysqladmin extended-status" command can also give you information to use grep with but inside the server is also valid.

Keep an eye on the following information.
Some of this is also posted here

You can review the post mentioned to see a nice table that explains some of these values.  These are the ones I think you should keep an eye on though.

> show status like 'Handler_read_first';
> show status like 'Key_reads';
> show status like 'Select_full_join';

Find out the percentage overall of your queries are running slow. Hopefully this is a small number.
Use the status information to populate these variables for a quick check. 

 set @slow_queries=<value in the status output>;
set @questions=<value in the status output>;
SELECT ROUND(100* (@slow_queries /  @questions),2) "Slow Query percentage" \G

Learn to understand the output from : SHOW ENGINE INNODB STATUS\G
Keep an eye on deadlock information so you can start to debug those.

Does your system have triggers, events, stored procedures?

mysql> SELECT db, name FROM mysql.proc;
mysql> SELECT EVENT_NAME FROM information_schema.EVENTS ;

Know your server variables

            show variables like '%innodb%';
            show variables like '%thread%';
            show variables like '%table%';
            show variables like '%buffer%';
            show variables like '%cache%';

Check your user accounts.
Do they all have valid passwords.

SELECT Host, User ,
WHEN  CHARACTER_LENGTH(Password) = 16 THEN 'Pre-4.1 I should update this'
WHEN  CHARACTER_LENGTH(Password) > 16 THEN 'Valid password'
WHEN  CHARACTER_LENGTH(Password) =0 THEN ' BLANK PASSWORD so I just do not care if people steal my stuff'
END as Password
FROM mysql.user;

Understand your InnoDB log file and figure out a good buffer pool size.
Baron posted about this here:
Read his post to understand more about this. Below is an example of how to use this information as well. I used his numbers for the example to help.  Like Baron said, run this when traffic is heavy to get valid information.

mysql>pager grep sequence; show engine innodb status\G select sleep(60); show engine innodb status\G  pager;
mysql>SET @sequence1= 3836410803;
mysql>SET @sequence2= 3838334638;
mysql>select ( ( (@sequence2 - @sequence1) / 1024 / 1024 )* 60 ) /2 as innodb_log_file_size_used ;
mysql>select (@@innodb_log_file_size / 1024) / 1024 as current_log_file_MB_size;

Buffer pool size.
While the following query is posted around the net and does give a valid starting point for a buffer pool, I have also seen some invalid or unrealistic numbers based on the server it had.  Personally, review the results of the query below. Review the results of peak traffic and the amount data you are sending to the logs per Baron's post. Then take into account how much memory you have on the server.  The more your database runs in memory the faster it is to get results, but you have to take into account what else is the database doing. So you have to use your own judgement and research to get a good value for the buffer pool size. Another option could be to use the, answer the questions and see what it tells you .

mysql>select ( ( (@sequence2 - @sequence1) / 1024 / 1024 )* 60 ) *2  as innodb_buffer_pool_GB_test ;

SUBSTR(' KMG',IF(PowerOf1024<0,0,
IF(PowerOf1024>3,0,PowerOf1024))+1,1)) recommended_innodb_buffer_pool_size
FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.tables
WHERE engine='InnoDB') A,
(SELECT 3 PowerOf1024) B \G

Next you should review this open ark blog post to dig into your indexes and etc more. Great information on that post and site overall.

To dig into more data about your server....

What are the oldest tables, maybe need to archive those?

FROM information_schema.TABLES
WHERE TABLE_SCHEMA NOT IN ('information_schema','mysql','test','performance_schema')  AND `UPDATE_TIME` IS NOT NULL ORDER BY `UPDATE_TIME` LIMIT 25;

What is taking most the space?

SELECT concat(table_schema,'.',table_name) table_name,
concat(round(data_length/(1024*1024),2),'M') data_length
FROM information_schema.TABLES
ORDER BY data_length DESC LIMIT 5;

This is just a starting point for you to understand what is going on with your server. Use the sites listed within this site to learn more.