Tag: MySQL

Tuning LAMP Server Performance

Having trouble with a crashing webserver? Is MySQL or Apache eating up your RAM and trashing your drive swapping? Don’t worry, there are good tools to help you get started in tuning your LAMP server to avoid crashes.

There are two scripts that I find invaluable in getting me a first and fast opinion on the current status of the server. The trick is often to get the settings right so that they do not risk eating up your RAM for breakfast. Here are two scripts to help you get your MySQL and Apache settings right for you.

MySQLtuner
This script will test your MySQL settings and suggest performance improvements. By using statistics from MySQL of current state of performance it will suggest modifications to your settings.

Apachebuddy
Inspired by MySQLtuner.pl Apachebuddy.pl does the same thing to Apache server settings. It checks your current settings and calculates average as well as maximum RAM usage and suggest improvements.

These script does of course not replace knowledge. Use them as a first opinion but then educate yourself about the settings before changing anything.

Both scripts have nifty URLs to download from:
mysqltuner.pl
apachebuddy.pl

Creating a MySQL Master – Slave connection

I’ve setup several MySQL Master – Slave connections and like to share my procedure. During the trials there are several details I’ve come to learn how to handle and my own set of “best practices”.

The MySQL Master Slave connection works under the premise that “a statement executed on the master should create the exactly same result when executed on the slave given that their database is equal”. For this to work we need to start two servers that are identical and then make one follow the other.

We used MySQL Server 5.5.11 when creating the master slave connection in the guide below. Please consult the MySQL Documentation if you are using a different version.

Step 1: Setup servers
First of all you will need two MySQL-servers. The standard community edition works fine. They should be of the exact same version to avoid any problem that bugs in one or the other might introduce. If you introduce a slave into an existing MySQL server you will need to make plan for a downtime for the duration of running the “mysqldump” command.

TIP: Save the MySQL installation file if you want to add more servers later since you will need the exact same version.

Step 2: Configuration
Edit the my.ini-file off the future master and add the following settings:

# Unique Server ID
server-id=1
# Name of binary log
log-bin=mysql-bin

The Server ID can be any number as long as there are no two servers with the same number in the replication chain, i.e. in our case the slave must have a different number.

The log-bin setting tells the server to make a binary log of every statement executed on the server.

Edit the my.ini file off the future slave and add the following settings:

# Unique Server ID
server-id=2

TIP: Add the setting relay-log=relay-bin to name the relay log. Otherwise MySQL by default uses [hostname]-relay-bin. The problem with the default is that if the host ever change hostname the replication will break. It also breaks if you want to make a copy of the slave to a second slave (if you do not add the setting to the new slave as well).

As mentioned before, the Server ID of the slave needs to be different from the Server ID of the master. When these changes are done, restart the service on both MySQL machines to let the changes take effect. Use the following commands to restart the service:

Linux (requires super user access):

user@host:~$ service mysql restart

Windows (requires administrator privileges):

C:\net stop mysql
C:\net start mysql

After the changes you should see a binary log starting to grow in data data directory of your future master.

TIP: If you have made other modifications to the my.ini file these needs to be copied as well to the slave, otherwise the slave riscs behaving differently from the master.

Step 3: Create a user
The replication will be using a normal user with the replication privilege. I opted to create a new user for this using the following commands:

mysql> CREATE USER 'slave'@'%' IDENTIFIED BY 'mytrickypassword';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slav'@'%';

The user will be created on the master but if you replicate all databases (as this guide will) then the user will also be replicated to the slave.

TIP: You can use any password you like BUT the password will be visible in plain text on the slave server! In the file master.info that will be created later in this tutorial all the master information will be stored including username and password.

TIP: Make the slave user limited to a certain domain or IP so that security riscs will be minimized. In the above example the user slave can log in from any host.

Step 4: Copy database
Now the time critical portion of this tutorial begins, from here until the datadump is complete the master database will be unavailable for writing.

Execute the following command on the MySQL Master:

mysql> FLUSH TABLES WITH READ LOCK;

Now all tables will be locked so that no transactions can occur. This is required since we need to make a full database dump of the current state of MySQL Master. Next execute the following command:

mysql> SHOW MASTER STATUS;

Write down the reply of the following values: File and Position. An example would be:

File: mysql-bin.00001
Position: 1337

From the command line on the MySQL master issue the following command (change password etc as needed):

C:\mysqldump --user=root --password=rootpassword --all-databases --master-data --result-file=mydump.sql

TIP: Are you using non UTF-8 encoding? Add “–default-character-set=latin1” to the command line where latin1 is the encoding you are using. If you do not supply an encoding MySQL will assume UTF-8.

When the dump is complete and you have a file called mydump.sql you can unlock the tables. Issue the unlock command on the master:

mysql> UNLOCK TABLES;

The master server will now be on-line and working again.

Step 5: Create the slave
Copy the file mydump.sql to the slave server. When it is done execute the following command from the mysql command line (you might have to specify exact location of the mydump.sql file):

mysql> source mydump.sql

TIP: Do NOT use “mysql -u root -p < mydump.sql” from the normal command line since that can corrupt the encoding, again if you use non-standard encoding.

The database on the slave is now identical to what the master from a specific point in time. Now configure the slave to connect to the master and follow it from that point in time.

CHANGE MASTER TO
MASTER_HOST='master.example.com',
MASTER_USER='slave',
MASTER_PASSWORD='mytrickypassword',
MASTER_LOG_FILE='mysql-bin.00001',
MASTER_LOG_POS=1337;

Make sure that MASTER_HOST is the name or IP of the MySQL Master. MASTER_USER and MASTER_PASSWORD are the same as created in step 3 above. MASTER_LOG_FILE and MASTER_LOG_POS are the same as read from step 4 above.

TIP: Since we used the flag –master-data when creating mydump.sql the MASTER_LOG_FILE and MASTER_LOG_POS should allready be set. The remaining settings are however needed.

TIP: Unless you specifically need it I recommend to avoid using binary logging on the slave while it tries to “catch up” with the master. Also the “bin-log” command only triggers logging of commands executed directly on the server, not from replication. To make the slave write replication to it’s own binary log the following setting must be added: “log-slave-updates=1”.

Start the slave with the following command from MySQL command line:

mysql> START SLAVE;

Step 6: DONE
Congratulations, your slave server is now replicating everything on the master server. Depending on how long time it took between step 4 and step 5 the slave should most likely allready have caught up the the master. To check on the status run the following command on the slave server:

mysql> SHOW SLAVE STATUS;

Especially noteworthy fields in this reply are “Slave_IO_State” that informs us of what the slave is up to, most common reply here is “Waiting for master to send event”. “Seconds_Behind_Master” tells us how many seconds behind the slave server is at the moment. If the slave server has been done or restored to an old backup this value can be very high. Normally this value is zero indicating that the slave is up to speed.

TIP: Did you know you can “daisy chain” MySQL servers. Just setup the slave as master to a new slave! There are however some further considerations for doing that, maybe a future blog post!

TIP: The slave server is perfect to use as a “live backup” in case the master should fail. You can also temporarily stop/lock the slave to make a complete database backup without having to worry about downtime of the service. The slave will catch up with the master again once started.

TIP: As with every security meassure in information technology, try this out before trusting how it works! I give NO GUARANTEE OF ANYTHING WRITTEN IN THIS GUIDE, you have to try and verify it yourself. This works for me, it doesn’t necessarily work for you.

More tips, comments or questions? Please feel free to comment below!

WordPress 3.0.1 ute

Så kör jag bloggen på nya WordPress 3.0.1! Den nya version tre känns vid första anblick inte speciellt annorlunda från tidigare versioner, dock känns det dagligen som man hittar någon ny liten finess.

En av de mer välkomna nyheterna är den anpassningsbara menyn. Detta öppnar för nya möjligheter att utveckla mallar med special-menyer för olika ändamål.

Having fun with the WordPress Database

Today I’ve improved the starting page of this domain, www.fireflake.com. It looked very booring and I thought I’d have to do something with it but at the same time I knew I was to lazy to acctually maintain some interesting subjects there as well. The solution was simple! Use my blogs to feed the information to the starting page! Here are some of my php and sql code used, in case you want to try something similar yourself.

First of all, if you’ve never done any PHP before, do not fear, it’s super easy! Without explaining it, here are my open and close database files (I keep them seperate since they will most likely be included in many pages):

open_db.php:

<?php
$dbhost = ‘localhost’;
$dbuser = ‘username’;
$dbpass = ‘password’;

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die                      (‘Error connecting to mysql’);

$dbname = ‘dbname’;
mysql_select_db($dbname);
?>

close_db.php:

<?php
mysql_close($conn);
?>

Just modify these files with the values for your database and you are ready to do some mysql-powered-php-scripting! Next simply include these files in the file where you want to use the database, in my example index.php:

<!DOCTYPE html PUBLIC “-//W3C//DTD HTML 4.01 Strict//EN” “http://www.w3.org/TR/html4/strict.dtd”>
<?php include ‘open_db.php’; ?>
<html>
…
</html>
<?php include ‘close_db.php’; ?>

Now lets make the MySQL database print something to the index-page! Here is an example:

<?php
$query  = “select now() as mysqltime”;
$result = mysql_query($query);
while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
echo $row[‘mysqltime’];
}
?>

This will print a timestring from your MySQL. I used this as an example just because it should work everywhere. Now simply substitute the query with something you want from your database and the printout inside the loop with what (and how) you want it printed!

To take a WordPress example, I want to use the tags/categories as keywords for the META-tag of my start page. I use a cleaverly written SQL query that will give me all the unique keywords for all my three blogs sorted in order of usage:

<?php
$query  = “SELECT name,`count` FROM tech_terms t join tech_term_taxonomy tt on (t.term_id = tt.term_id)
union distinct
SELECT name,`count` FROM game_terms t join game_term_taxonomy tt on (t.term_id = tt.term_id)
union distinct
SELECT name,`count` FROM 3wp_terms t join 3wp_term_taxonomy tt on (t.term_id = tt.term_id)
order by `count` desc limit 0,50;”;
$result = mysql_query($query);
$keywords = array();
while($row = mysql_fetch_array($result, MYSQL_ASSOC)){
array_push($keywords,$row[‘name’]);
}
$keywords = array_unique($keywords);
foreach ($keywords as $key){
$meta_key_string .= $key.”,”;
}
?>
<meta name=”keywords” content=”<?php echo $meta_key_string ?>fireflake”>

This will make my home meta tag always up to date with what I write about! The keywords will also be listed in order of relevance since they are ordered by “count” from the database.

Another great little script takes out the latest posts in a blog and prints them (and links it to the main article):

<?php
$query  = “select * from tech_posts where post_status = ‘publish’ order by post_date desc limit 0,2”;
$result = mysql_query($query);
while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
echo “<h3><a href=”” . $row[‘guid’] . “”>” . $row[‘post_title’] .”</a></h3>”;
echo “<p>” . str_replace(“n”,”<br />n”,$row[‘post_content’]) .”</p><br clear=”all”>”;
}
?>

There are probably lots of fun you can have with the WordPress database, it’s very simple and easy to learn so it’s very easy to start writing code like this!

Hope you found something useful here!

Virtual Ubuntu 8.04 JeOS LAMP Server

I’ve found the perfect portable development environment for me! First of all I wanted a virtual environment in VMWare since I have access to that software at several locations. Secondly since I would be using it on laptops I wanted the virtual machine to use as little memory and disc space as possible.

Requirements for this tutorial:

First I create a new virtual hardware for the Ubuntu distribution. Standard options straight through except I choose to have a virtual harddrive split into 2 Gb files and only created when needed (to save space, since this is kind of one of the main points of JeOS). Next I choose the ISO-image of the downloaded JeOS distribution as an imported CD-ROM drive for the virtual machine and boot it up.

Throughout the installation I just make the default choices (with only a few exceptions for country and keyboard layout). The installation is fast and the system is up and running, but no graphics of course! Just a waste on a server machine anyway. The first thing I want is the comfort of continueing from my main computer and therefore I install OpenSSH server.

sudo apt-get install openssh-server

After the installation I can freely connect from any other machine on my network which in my case is much more confortable than sitting peeking into a virtual machine on my serverbox.

To logon remotely I need to know the current IP-adress of the JeOS virutal system, to easily get it I write:

ifconfig | grep -w inet

This will show me the current available IP-adresses on the machine, one of them is the loopback (127.0.0.1) and if there are others (there should be only one with the current installation) those are the public IP-addresses. In my case it’s 192.168.0.16. If you are running IPv6 change “inet” to “inet6” to get the public IPv6 IP address.

After I logon (in my case using PuTTY from a Windows machine) I start the process of making it a LAMP server (Linux Apache MySQL PHP5). To make things easy Ubuntu provides a command called “tasksel” that packages an installation togheter into one command:

sudo tasksel install lamp-server

This is suprisingly easy, just wait and the only thing the installation asked me about was what root password I wanted for MySQL.

I use nano to edit text files, you might use something else, either way I need to install it since it’s not included in the standard JeOS distribution.

sudo apt-get install nano

Now we need to make MySQL accessible from remote computers (default setting is to only allow localhost). To do this we need to edit the file /etc/mysql/my.cnf

sudo nano /etc/mysql/my.cnf

Please not that this can be considred unsafe since it exposes the MySQL interface to the whole network! This is only done since I want a development machine that I need to administrate remotely (might be from the same computer but with the server running virtually in the background). To change the bind address you have several options, but first off all find this line within my.cnf:

bind-address = localhost

Instead of localhost you can enter the IP you want it to bind to, if you comment out the line MySQL will by default bind to you’re current IP (which is recommended if you have a dynamic IP-address).

Now we need to create a database and give remote access to it. We need to start MySQL on the command line, so we run:

mysql -u root -p

MySQL will now prompt for your password (the one you supplied earlier in the installation) and then you are in. First I create a new database, the purpose of which is to develop WWW-services so I aptly name it “www”.

create database www;

Next I want to grant myself remote access to this database so I run:

grant all privileges on *.* to ‘magnus’@’%’ identified by ‘passwordgoeshere’ with grant option;

Again, this is UNSAFE for production environment, the above command grants limitless access to the database to any host that identifies itself as me with my password. Consult the MySQL documentation for better limited access and also (if you know which host(s) you will need access from) change the wildcard to a specific IP-number.

I can now access and administrate the MySQL database using whichever tools one favours, I recommend the GUI tools from MySQL.

Another handy tool is phpmyadmin, with this php-based GUI you can administrate the MySQL database through the web browser:

sudo apt-get install phpmyadmin

After the installation simply point the web browser to the /phpmyadmin folder on the webserver of the virtual machine. For example if you’re virtual machine have the IP 192.168.0.50 then the address would be http://192.168.0.50/phpmyadmin

Done! You can now run the virtual server with Apache2, MySQL and PHP5 remotely from any computer in your network and administrate it through SSH. At the end of the installation my virtual machine used 720 Mb discspace which should be compared with the several gigabyte discspace used by a standard Ubuntu installation. Also with the absense of graphical interface the memory consumption is very low and the virtual machine can run on as low as 128 Mb of RAM if needed.

…

I can add as a bonus that when I later installed a SAMBA server and WordPress 2.6 on the virtual machine the size grew in total to just below 800 Mb and memory set at 256 Mb of RAM. This will have to be a topic for another post though.

Updated 2008-08-02
* added instruction of how to obtain the IP-address of the JeOS virtual machine.
* fixed typo in grant statement