Moving your mysql database to another hard disk

Recently, my server's only hard disk was almost full. I bought a new hard disk with bigger size and I decided to just add it as a second hard disk. Since I need to move it to the 2nd hard disk, I need to find a proper way to move the db with minimum downtime. So I googled around and found a solution.

First, I needed to format the 2nd hard disk and I chose xfs as the filesystem. I created 2 partitions using Linux's fdisk for this task. First partition is 10 GB and 2nd one is around 900 GB. That's approximately added up to 1 TB. Then I mounted the 2nd partition in current partition eg /media/hd2 as follows:

mount -t xfs /dev/sdb5 /media/hd2

where /dev/sdb5 is the partition and /media/hd2 is the mounting dir.

Stop mysql db before doing anything:

service mysql stop

Afterthat, I copied the entire db to newly mounted hard disk:

cp -rv /var/lib/mysql /media/hd2

It will take a while if you have huge databases.

Then, change the ownership of the dir to user and group of mysql:

chown -R mysql:mysql /media/hd2/mysql

You need to change the mysql config file in /etc/my.cnf to point to the dir:

[mysqld]

user = mysql

datadir = /media/hd2/mysql

port = 3306

socket = /var/lib/mysql/mysql.sock

pid-file = /var/run/mysqld/mysqld.pid

Now you can restart mysql db:

service mysql start

If there are no errors on startup, you can test by login to your mysql db and do sql query.

You can leave other settings as it is. If this doesn't work and if you use innodb, you may want to change these lines:

# Uncomment the following if you are using InnoDB tables

#innodb_data_home_dir = /var/lib/mysql/

#innodb_data_file_path = ibdata1:10M:autoextend

#innodb_log_group_home_dir = /var/lib/mysql/

#innodb_log_arch_dir = /var/lib/mysql/

Make sure you test your dbs before deleting the old ones. Have fun!

We are using MySQL, help save it

As Oracle are progressing to acquire Sun, the future of MySQL is at stake. Read Monty's article about this at http://monty-says.blogspot.com/2009/12/help-saving-mysql.html to understand this issue better.

Maatkit: A great MySQL toolbox

What is Maatkit? (copied verbatim from its website)

Maatkit makes MySQL easier and safer to manage. It provides simple, predictable ways to do things you cannot otherwise do. It would be nice if these features were included with MySQL, but they are not. That's why Maatkit is now shipping by default with many GNU/Linux distributions such as Debian and CentOS. Unfortunately, it isn't included in Mandriva repo. In that case, we can download it here or read here for more ways to download it.

You can use Maatkit to prove replication is working correctly, fix corrupted data, automate repetitive tasks, speed up your servers, and much, much more.

Maatkit is sponsored by Percona, who releases it under the GPL and offers paid support and sponsorship of new features. Maatkit is hosted on Google Code and free support is available in the Maatkit Discuss Google Group.

What can Maatkit do and more can be read on the website.

Jeremy Zawodny highlights a few of his favorite utilities here.

postfix can't connect to MySQL

I got this error in syslog when postfix was trying to connect to MySQL:

Apr 13 17:34:53 webmail postfix/smtpd[6726]: warning: connect to mysql server localhost: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
Apr 13 17:34:53 webmail postfix/smtpd[6726]: NOQUEUE: reject: RCPT from rv-out-0506.google.com[209.85.198.233]: 451 4.3.0 : Temporary lookup failure; from= to= proto=ESMTP helo=

I got a reference to MySQL database in my main.cf which triggered the error:

local_recipient_maps = mysql:/etc/postfix/sql-recipients.cf

From the error it was obvious postfix couldn't connect to MySQL. Email from outside wouldn't be received properly by dbmail. I checked MySQL service and it was running and I could log into MySQL manually. I found a solution after googling a bit. The cause was in file /etc/postfix/master.cf. I got smtp service of postfix to run chroot'ed (see y below).

#service type private unpriv chroot wakeup maxproc command + args
smtp inet n - y - - smtpd

So I changed the line to:

#service type private unpriv chroot wakeup maxproc command + args
smtp inet n - n - - smtpd


Voila!. It worked. See log below:

Apr 13 18:03:02 webmail postfix/smtpd[7039]: connect from rv-out-0506.google.com[209.85.198.239]
Apr 13 18:03:03 webmail sqlgrey: grey: domain awl match: updating 209.85.198(209.85.198.239), gmail.com
Apr 13 18:03:03 webmail postfix/smtpd[7039]: B3246A3075: client=rv-out-0506.google.com[209.85.198.239]
Apr 13 18:03:04 webmail postfix/cleanup[7042]: B3246A3075: message-id=<23c8d5620904130314j7f4c619di57c7d8c0d217ed62@mail.gmail.com>
Apr 13 18:03:04 webmail postfix/qmgr[7033]: B3246A3075: from=, size=2277, nrcpt=1 (queue active)
Apr 13 18:03:05 webmail postfix/smtpd[7046]: connect from webmail.myfakedomain.net[127.0.0.1]
Apr 13 18:03:05 webmail postfix/smtpd[7046]: 26BBFA3076: client=rv-out-0506.google.com[209.85.198.239]
Apr 13 18:03:05 webmail postfix/cleanup[7042]: 26BBFA3076: message-id=<23c8d5620904130314j7f4c619di57c7d8c0d217ed62@mail.gmail.com>
Apr 13 18:03:05 webmail postfix/qmgr[7033]: 26BBFA3076: from=, size=2751, nrcpt=1 (queue active)
Apr 13 18:03:05 webmail postfix/smtpd[7046]: disconnect from webmail.myfakedomain.net[127.0.0.1]
Apr 13 18:03:05 webmail dbmail/lmtpd[20480]: Message:[serverchild] serverchild.c,PerformChildTask(+349): incoming connection from [127.0.0.1] by pid [20480]
Apr 13 18:03:05 webmail postfix/lmtp[7043]: B3246A3075: to=, relay=127.0.0.1[127.0.0.1]:10025, delay=2, delays=0.96/0.01/0/1, dsn=2.0.0, status=sent (250 2.0.0 Ok, id=01032-05, from MTA([127.0.0.1]:10026): 250 2.0.0 Ok: queued as 26BBFA3076)
Apr 13 18:03:05 webmail postfix/qmgr[7033]: B3246A3075: removed

Tips for MySQL

MySQL is the most popular database in the world. I'm not doubt about it. Most of forums, portals and web-based database applications use it as the back-end engine.

Some tips of using MySQL on Linux

Login to MySQL using mysql client in console/terminal:

mysql -u username -p dbname

or

mysql -u username -ppassword dbname

or (using current username to log in)

mysql -ppassword dbname

security tip: username root is the default administrator. Do not use it in a live environment. Create a new one and set the appropriate permission for it.

Create a new database:
mysqladmin -u username -ppassword create databasename

(username is the administrator username that able to create a new database ie root)

or you can log in to mysql using mysql client in console. Example:

//create table with myisam engine.

CREATE TABLE mytable (
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id),
value_a TINYINT
) TYPE=MYISAM


//create table with HEAP engine.

CREATE TABLE mytable (
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id),
value_a TINYINT
) TYPE=HEAP

Delete a database:
Login to mysql and issue command drop database databasename.

(Make sure you use usernames with correct priviledge to drop a database)

What is the size of my database?
database size = the sum of all table sizes + all index sizes

1. Open a text editor (eg. Notepad)
2. Copy and paste the code below into your text editor ( replace username, password and dbid accordingly):
   
   mysql database size

   if ($filesize < filesize ="">

   # in at least kilobytes.

   for ($i = 0; $filesize > 1024; $i++) $filesize /= 1024;

   $file_size_info['size'] = ceil($filesize);

   $file_size_info['type'] = $bytes[$i];

   return $file_size_info; } $db_server = 'mysqlhost'; $db_user = 'username'; $db_pwd = 'password'; $db_name = 'dbid';

   $db_link = @mysql_connect($db_server, $db_user, $db_pwd)

   or exit('Could not connect: ' . mysql_error()); $db = @mysql_select_db($db_name, $db_link) or exit('Could not select database: ' . mysql_error());

   // Calculate DB size by adding table size + index size:

   $rows = mysql_query("SHOW table STATUS"); $dbsize = 0;

   while ($row = mysql_fetch_array($rows)) {$dbsize += $row['Data_length'] + $row['Index_length']; } print "database size is: $dbsize bytes "; print 'or';

   $dbsize = file_size_info($dbsize); print "database size is: {$dbsize['size']} {$dbsize['type']}"; ?>

put this php script into your accessible directory. (taken from here).

Nice reading : Overcoming MySQL's 4GB limit by Jeremy Zawodny.

To know what engine your database is using:

SHOW TABLE STATUS FROM yourdbname

MySQL has support for ISAM,MyISAM , HEAP, BerkeleyDB and InnoDB database engine. Depending on how your MySQL packages are compiled, it may or may not support for all these engines.

Bear in mind that ISAM and MyISAM engines lack foreign key and transactional support. BerkeleyDb and InnoDB overcome that limitation. However, BerkeleyDB and InnoDB are much slower compared to ISAM and MyISAM. If your database is of type ISAM/MyISAM, fortunately you can convert it to InnoDB using this command:

ALTER TABLE isamtable CHANGE TYPE=InnoDB

or you can use utility mysql_convert_table_format :

mysql_convert_table_format --user=username --pasword=password --type=innodb databasename tables

(if tables is omitted, all tables will be converted. That means you can convert certain tables to InnoDB and leave the rest with ISAM. One database uses more than one engine. That's the flexibility MySQL provides. Flexibility is the key here.)

MySQL makes this happen with three steps. First, an exact copy of the table is created. Next, any incoming data changes are queued, while the copy is moved to the other engine. Finally, any queued data changes are committed to the new table, and the original one is deleted.

Migrating mails from old server to new server

I have done migrating my users' mails to new server. dbmail saves all mails into database (in my case mysql), backing up and restoring should be trivial.

1. backup (using mysqldump)
~#mysqldump -u user -pPassword dbmail > dbmail.sql

2. restore
~#mysql -u user -pPassword dbmail < dbmail.sql

Voila. It's done!.

I have users' preferences and address books saved in database too. So the method of backing up and restoring to the new server should be the same.