Using XFS File System for MySQL
MySQL is preferred to be run on XFS file system due to its performance on direct IO. Even though many benchmarks have already come out with the latest Linux default file system Ext4 versus XFS, it still convenience to use this file system for our MySQL data directory.
XFS is not come by default in CentOS. So we need to install required utilities to manage XFS. I will use a different virtual hard disk specifically for this and mapped it to MySQL data directory.
OS: CentOS 6.2 64bit
Device: /dev/sdb
Old MySQL data directory: /var/lib/mysql
New MySQL data directory: /mysql
1. Since this is new hard disk without any partition table, we need to create partition table first:
$ fdisk /dev/sdb
Sequence pressed on keyboard: n > p > 1 > Enter > Enter > w
2. You should see disk partition has been created as /dev/sdb1 as below:
$ fdisk -l /dev/sdb
Disk /dev/sdb: 11.8 GB, 11811160064 bytes
255 heads, 63 sectors/track, 1435 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0xaa7ca5e3
Device Boot Start End Blocks Id System
/dev/sdb1 1 1435 11526606 83 Linux
3. Install XFS utilities via yum:
$ yum install -y xfs*
4. Format the drive with the XFS file system:
$ mkfs.xfs /dev/sdb1
5. Check the file system, create mount point and mount the file system:
$ xfs_check /dev/sdb1
$ mkdir /mysql
$ mount /dev/sdb1 /mysql
6. To allow it mount automatically after boot, at this line into /etc/fstab. Open the file using text editor and add following line:
/dev/sdb1 /mysql xfs defaults 0 0
7. Stop MySQL and copy over the data using rsync (to remain permission, ownership and timestamp) and assign correct ownership to the directory:
$ service mysql stop
$ rsync -avz /var/lib/mysql/ /mysql
$ chown mysql.mysql /mysql
8. Change the value of MySQL configuration file to be mapped into the new directory. Open /etc/my.cnf and change or add following line under [mysqld] directive:
datadir = /mysql
9. Start MySQL service:
$ service mysql start
Done. You can verify this in MySQL by executing following command:
mysql> SHOW VARIABLES LIKE ‘datadir’;
+—————+———+
| Variable_name | Value |
+—————+———+
| datadir | /mysql/ |
+—————+———+
1 row in set (0.00 sec)
To check the mount status:
$ mount | grep xfs
/dev/sdb1 on /mysql type xfs (rw)
To repair the XFS file system, we need to unmount the file system first:
$ umount /mysql
$ xfs_repair /dev/sdb1
