MySQL

Mysqldump all databases and gzip

So, quick and simple MySQL backup in a CRON.

#!/bin/bash
_hour=`date '+%H'`;
_user="root";
_pass="thepassword";
_dest="/srv/backups/daily/";
mysqldump -u $_user -p$_pass --all-databases --ignore-table=mysql.event | gzip > $_dest_$_hour.sql.gz

I’ve used this in an hourly CRON so that I always have 24 hours of backups.

Note: The ignore of mysql.event is to stop an annoying new feature of a later version of MySQL that seems to report a notice that its skipped this table. I don’t really need it so I am ignoring it.

Numerical Data Types in MySQL

In most MySQL GUI’s that offer the ability to alter the table definitions of a schema, if you add a column that is an INT, if defaults the syntax to be:

ALTER TABLE {table} ADD COLUMN {field} INT(11) DEFAULT NULL;

This is okay, but its really important to understand your datatypes.

What does the INT(11) really mean?

Basically, a common misperception is that the number in brackets is the length of the field. Probably because thats how the syntax works for VARCHAR(34) will allow a maximum length of 34 characters.

For integers, thats not strictly true. If you create an INT field, it will be an INT, no matter what you put in brackets. An INT field has the following maximum storage:

Unsigned - Min : 0,           Max : 4294967295
  Signed - Min : -2147483648  Max : 2147483648

So whats the (11) for you ask?

Basically, this is only used by the MySQL command line client, and this is the maximum length that it will display. The full value will be stored FYI.

So, the important issue is, you really should create your fields with the full understanding of what data may be stored within. There are more integer datatypes than just INT.

TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT

The min and max stored data values are as follows

Unsigned (Min is always 0)

TINYINT    - Max : 255
SMALLINT   - Max : 65535
MEDIUMINT  - Max : 16777215
INT        - Max : 4294967295
BIGINT     - Max : 18446744073709551615

Signed

TINYINT    - Min : -127                 Max : 127
SMALLINT   - Min : -32768               Max : 32768
MEDIUMINT  - Min : -8388608             Max : 8388608
INT        - Min : -2147483648          Max : 2147483648
BIGINT     - Min : -9223372036854775808 Max : 9223372036854775808

Convert a MySQL database to UTF8

This is a “script” to convert your latin1 myisam database to utf8 innodb
Dump the database

mysqldump -u root -p database_to_convert > db.latin1.sql

Make a copy of the dump

cp db.latin1.sql db.utf8.sql

Change any default table charset to be utf8

sed -i 's/DEFAULT CHARSET=latin1/DEFAULT CHARSET=utf8/g' db.utf8.sql

Now, remove all field collations for Latin1

sed -i 's/COLLATE=latin1_general_ci//g' db.utf8.sql

Change any MyISAM engines to InnoDB

sed -i 's/ENGINE=MyISAM/ENGINE=InnoDB/g' db.utf8.sql

Re-import the database

mysql -u root -p database_to_convert < db.utf8.sql

And thats it.

MySQL Profiling

MySQL allows you to profile queries, enabling you to see whats being run on your server. They are stored in a special profile table in the INFORMATION_SCHEMA database.

You can drill right down into the query to see how much CPU time, etc was used.

To see if it is switched on,

SELECT @@PROFILING;

This will return 1 or 0 to let you know if its on.

Turn on MySQL profiling:

SET PROFILING = 1;

Show the list of queries

SHOW PROFILES;

If you want more information about how long a query took, and which parts took longer:

SHOW PROFILE FOR QUERY 1;

If you want to get uber geeky, you can drill down into certain deep parts of the query:

SHOW PROFILE CPU FOR QUERY 1;

This will show you the CPU timing, etc. You can use the following options to view different parts:

ALL, BLOCK IO, CONTEXT SWITCHES, CPU, IPC, PAGE FAULTS, SOURCE, SWAPS

I’m not sure you will ever need these, but the CPU one /might/ be helpful to see slow queries.

MySQL Administration (Remote Access)

So, once you’re all set up and running, you may want to give users access from the outside world. This is clearly an entry point for hackers and script kiddies, so be sure that you want to open up your database to the net.

I believe that, out of the box, MySQL is bound to the localhost IP address (127.0.0.1) so that will need changing first of all.

Edit my.cnf and find the line ‘bind-address’:

bind-address=127.0.0.1

Now change this to allow access from other IP addresses, if you want *all* access then change it to 0.0.0.0 or comment it out completely. You can only set it to one IP address, or all. (as shown)

Ensure your users can connect from the IP address you set it to:

GRANT <privileges> TO 'user'@'<ipaddress> IDENTIFIED BY '<password>';

for example

GRANT SELECT,INSERT,DELETE,UPDATE TO 'user1'@'203.122.211.90' IDENTIFIED BY 'letm3in';

Or, to all this user access from anywhere:

GRANT SELECT,INSERT,DELETE,UPDATE TO 'user1'@'%' IDENTIFIED BY 'letm3in';

Finally, restart the server and you’re done.

Let me just re-iterate, ensure you will be able to connect to the database from anywhere before restarting the server. Don’t blame me if you lock down a remote server with no access to it!

You can’t specify target table ‘xxx’ for update in FROM clause

I recently needed to update a whole bunch of records using an IN() on the target table:

UPDATE table1
    SET field1 = 'some value'
    WHERE table_id IN (
        SELECT table_id 
        FROM table1
        WHERE field2 = 'another value'
        );

This isn’t possible due to the target table being used in the WHERE. But the workaround is simple enough, use a temporary table:

UPDATE table1
    SET field1 = 'some value'
    WHERE table_id IN (
        SELECT table_id FROM (
            SELECT table_id 
            FROM table1
            WHERE field2 = 'another value'
            ) AS temporary_table 
        );

The resulting temporary table will allow the usage of the IN() function.

It is a bit slower, but its a ‘workaround’

Failing that, you could get a list of the ID’s by doing a separate query and then pasting the results into the IN() but then you:

a) run the risk of data changing between the queries, and
b) having to do more work, i.e reformatting and replacing carriage returns with commas, etc.

It all depends on your database/table structure and each case has its own decisive factors.

MySQL Get row with greatest parent row

If you have a database of CDs, and artists, etc.

Say you want to get a list of artists with their most recent releases:

SELECT 
    artist.name, 
    album.title, 
    album.release_date
FROM artist
LEFT JOIN 
    album ON artist.artistID = album.artistID
WHERE album.albumID = (
    SELECT MAX(albumID) FROM album WHERE artist.artistID =  album.artistID
    )

Make sure you have indexed correctly, this could be a slow one.

How to add a new user to MySQL

Don’t leave the root user as ‘the’ user for your web application. That’s just not a good idea. For instance, if you are running a whole bunch of sites from one database, and you security reasons you need to change the root password, you’ll have to update all the configs for the sites. Also, if one site gets hacked, they have the password for /all/ your databases and they can pretty much do whatever they like. Instead, have an extremely tough password for the root account and ONLY use that for root based activity. Have separate (and equally tough) passwords for each application. Keep the data separate as much as the access is concerned. Limit the databases that the users can see, to the ACTUAL databases they need access to. The more you tighten your defences, the safer you become, this is also known as multiple level security.

Anyway, to add a user, log in as root:

mysql> CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

Replace username with the username and password with the desired password. The localhost part is where you specify ‘from what domain/ip address’ the user can connect to this database server. You can really nail down security so that unless they are connecting from the specified address, then they cannot connect at all. Useful if you know the web server ip/domain (in this case they are on the same machine). You can allow connections form anywhere by substituting the localhost with % (which is a wildcard)

mysql> GRANT SELECT,INSERT,UPDATE,DELETE ON databasename.* TO 'username'@'localhost';

The above gives the standard access to the user (SELECT INSERT UPDATE and DELETE) – this user will not be able to ALTER tables, DROP columns or tables, etc. This is important really.

Finally, flush the priveleges, although after many years of working with MySQL I am still unsure what this actually does. Something to do with re-caching the users and releasing table locks, etc.

mysql> FLUSH PRIVELEGES;

Of course you can use your favourite MySQL GUI to make these changes, but I think its better if you know the syntax yourself. One day you may have to make these changes remotely using a terminal of some kind. And then what are you going to do?

Where is my.cnf on Mac OSX?

On Mountain Lion, I found that the my.cnf file is missing. This is because Mac uses the default MySQL settings and therefore doesn’t require a config file.

If you want to change the default settings, you’ll need to create a my.cnf in /etc/:

You’ll need to sudo into Terminal, and copy one of the default configs from the MySQL folder to the /etc/ folder:

sudo cp /usr/local/mysql/support.files/my-huge.cnf /etc/my.cnf

Then just use Nano or whatever to update the file, then restart MySQL and you should be good to go with your new settings.