Diamond Notes

This is a little simpler, but iti s good to cover this system variable for the administrators who are just starting out. In your my.cnf (or my.ini on Windows) file you need to specify the maximum number of connections (clients) that are allowed to connect to your MySQL server at one time. You will want to set it at a reasonable level for your system load. Don’t over-inflate as it does use an (admittedly fairly small) amount of memory.

As an example, if you want to allow a maximum of 400 connections to your server the following would be in your [mysqld] section:

max_connections = 400

Sometimes things go bump in the night and break. There are various ways to fix tables when this happens. From the mysql command line you can do the following:

> check table table_name;

This does a check of the table structure and contents. It works on both MyISAM and InnoDB tables.

> repair table table_name;

This will correct corrupted tables. It only works for MyISAM tables.

> analyze table table_name;

This updates information that the server stores about the tables. This information is used by the server optimizer when determining the best choice for query execution. This will work for both InnoDB and MyISAM.

> optimize table table_name;

The optimize table command will compact and optimize a MyISAM table by creating a new copy of the table on the filesystem. While it is doing this it updates index statistics, and sorts the index pages themselves if necessary. optimize table will work for InnoDB but simply maps to an alter table command which performs the same essential operations.

Recently I came across something new (for me anyways). There are times when it can be useful to temporarily turn off binary logging. You can disable it in the my.cnf file, but that requires a daemon restart. Coming from the Reference Manual:

“A client that has the SUPER privilege can disable binary logging of its own statements by using a SET SQL_LOG_BIN=0 statement”

As a brief example, if I am loading a large table it could be good to disable logging before beginning the import.

mysql> SET SQL_LOG_BIN=0;

mysql> LOAD DATA INFILE ‘honking_big_file’ INTO BIG_TABLE;

I don’t know about you, but more than once I have started a large import and had /var fill up because of logging. Just remember, if you need the table replicated to a slave server this will defeat replication.

Hope that is useful.

When it comes to benchmarking it is useful to turn your query cache off sometimes. This can be done on both client connection level and the global/server level.

Client level:

to turn the cache off

SET SESSION query_cache_type = OFF;

and then to turn it back on

SET SESSION query_cache_type = ON;

Server level:

to turn off the cache

SET GLOBAL query_cache_type = OFF;

and to turn it back on

SET GLOBAL query_cache_type = ON;

Been a bit behind on tip of the days if you haven’t noticed. Sorry about that . It’s getting crazy trying to get out the next issue of the magazine, training our new jr dba (who suggested this tip..thanks David!!) and trying to get things in line for both a short vacation and then the Users Conference next month.

Excuses over, here is the tip. Today’s tip is dealing with the ENUM (enumeration) data type. ENUM is very similar to the SET data type. It can hold a list of allowable values. The values in the list are called members. An ENUM list can hold up to 65,535 members. Try typing that many in!! As an example of a very simple table:

CREATE TABLE enums (

userid INT NOT NULL AUTO_INCREMENT

value ENUM( ‘FALSE’, ‘TRUE’ )

PRIMARY KEY (userid)

);

Here is what is important to understand. Internally to the MySQL database enums are stored as integers beginning with a value of one and going to N where N is the number of members in the list. In our example above ‘FALSE’ would be represented internally as a ‘1′ and ‘TRUE’ is represented as a ‘2′.

There can be subtle “problems” (errors) when you don’t take into account the string nature of what you are inserting into a ENUM type and its internal integer representation.

If, instead of the above table example, I used the following table:

CREATE TABLE enums (

userid INT NOT NULL AUTO_INCREMENT

value ENUM(’0′,’1′)

PRIMARY KEY (userid)

);

Here I stored the values as ‘0′ for false and  ‘1′ for true which is a fairly common programming practice. I would argue that you shouldn’t, but let us assume you did. Internally these strings of ‘0′ and ‘1′ are stored as integers of ‘1′ and ‘2′.  Confused yet?

Do you see the problem?

What if I do this:

INSERT INTO enums (value) VALUES (’1′);

I get the expected value returned when I SELECT:

userid = 1

value = 1

ie we just set userid of one to “true”.

Now we insert this way:

INSERT INTO enums (value) VALUES (1);

meaning to insert a value of TRUE into the value. However, that isn’t what happened. The 1 is an integer, so MySQL uses the first value of the list which is a ‘0′ .. ie false

While the example is a bit contrived, it goes to show you that you have to be careful how you insert when using enums.

This might not be quite what you think.  One of the sys admins here at work ran into a small problem when moving some databases from one server to another.  When using mysqlimport to bring in the databases on the new server it (mysqlimport) choked on a table that had a space in the name.  He couldn’t get it to work until he used load data infile and put quotes around the table name: “table name”.  I just checked the man pages for mysqlimport and didn’t see any options that would help.

Now I would recommend that you never use spaces in your table names, but if you do and try to restore a backup keep this in mind!!

Thanks for the tip Justin!!

This is the best “set” of information in one place about the future plans for MySQL server.  It looks like it is from a presentation give by Robin Schumacher.  I don’t remember where I first saw this so I apologize for not linking to the source of the news.

http://www.day32.com/MySQL/MySQL_Roadmap_2008_2009.pdf

Thanks to David, our Jr. DBA, we get the tip of the day. Did you realize that NULL (the topic of another post here) is not equal to the empty string (’ ‘). It is true. If you compare a NULL and an empty string you can see this:

mysql> select ‘ ‘ is NULL;
+————-+
| ‘ ‘ is null |
+————-+
| 0 |
+————-+
1 row in set (0.05 sec)

mysql> select ‘ ‘ is not NULL;
+—————–+
| ‘ ‘ is not NULL |
+—————–+
| 1 |
+—————–+
1 row in set (0.00 sec)

The zero returned means false, the one means true. This has implications to your code. For example, since an empty string is not NULL it can be inserted into a column that is set to NOT NULL.

For a more in depth discussion take a look here: http://dev.mysql.com/doc/refman/5.0/en/working-with-null.html

If you are a newbie here is one for you…

Go to amazon.com and have them send this out: MySQL Tutorial

It is an excellent introduction to MySQL, SQL and  even some database administration thrown in for good measure.

The book is a little dated because it was released in December 2003, but is still a great way to get involved with the MySQL world.  At around 250 pages it isn’t so big that it overwhelms you and the exercises at the end of each chapter are a very helpful way to insure that you understand the material.

This tip is a follow-up to yesterday’s post about setting up a new slave server with a new master (one that has no current data on it). Many times you need to add a slave to a master server that already has data on it. This is a little trickier, but it is possible.

There are other ways of doing this. For me, this is typically the fastest way to do it and the one I use most often.

Assumptions I am making: your operating system is Linux and you are using what is called LVM (logical volume manager). The lvm programs are used to manage the partitions and filesystems on your server. You can use it to, among other things, resize partitions and most importantly, in this case, take “snapshots” of your partitions. WARNING: before performing this on production systems you NEED to research LVM and test it out on test servers.

step one - set up your slave server with the operating systems and MySQL. Your master server MUST have LVM on it (and I would recommend you set up LVM on the slave also).

step two - on the master you need to add the replication user:

GRANT REPLICATION SLAVE ON *.* TO ‘repl_user’@‘ip_of_new_slave_server’

IDENTIFIED BY ‘repl_user_password’;

step three - open up two terminal screens on the master server.

step four - Issue a ‘FLUSH TABLES WITH READ LOCK’ on the master server’s mysql client. This will block all database activity on the master server.

step five - As soon as the ‘FLUSH TABLES’ command returns issue a ‘SHOW MASTER STATUS’ command. You need to save the information returned as you will need it in step eleven.

step six - In the second terminal screen you need to create the lvm snapshot. This is done by running the following command:

lvcreate -s -n snap-data -L 10G /dev/vg0/data

The exact format will vary somewhat depending on your setup. In this case it creates a snapshot of the data partition that is 10 gigabytes in size. This means that up to 10 gigabytes of changes can be recorded before the snapshot fills up.

step seven - Now back in the original terminal screen (the one that you issued the ‘FLUSH TABLES’ and ‘SHOW MASTER STATUS’ commands) you need to execute an ‘UNLOCK TABLES’ command. This will return your database to normal activity. You need to execute steps four through seven as quickly as possible.

step eight - Now you need to mount the snapshot partition. Do something like this (as root):

mount /dev/vg0/snap-data /mnt

step nine - Now you rsync the files to the slave server:

rsync -Wav --delete --progress /mnt/mysql slave:/data/mysql

step ten - Start the mysqld daemon on the slave server. It might spit and sputter a bit as it is starting up, but only once have I had the daemon not start up (and that appeared to be a completely unrelated issue). In the last year I have probably done this procedure fifty times so it is well tested!

step eleven - Now it is time to tell the slave server about the master. You will also need the IP address of the master server. On Linux run the ifconfig command to determine the IP address. To put in the information on the slave server:

RESET SLAVE;

CHANGE MASTER TO master_log_file=‘master_log’,

master_log_pos=master_log_position, master_user=‘repl_user’,

master_password=‘repl_user_password’, master_host=‘ip_of_master’;

SHOW SLAVE STATUS;

step twelve - Cleanup!! Now you need to remove the snapshot partition you made on the master:

umount /mnt

lvremove /dev/vg0/snap-data

That’s it. A little more complicated. But worth it!!