MySQL Logs – How to Move Log Files and Data in MySQL

MySQLmysql-5.6percona

Is it possible to separate out data files and log files of MySQL from one drive to another?

For eg:

Data (actual database data and system tablespace ibdata) in one partition, say /data and log files (ibdata0 and ibdata1 only) in one partition, say /redo?

Is it possible? I know the binary logs can be moved to a different partition but I have the doubt for above scenario.

I'm using Percona Server 5.6 on linux.

Edit:

Just checked out, If I set innodb_log_group_home_dir to a different location, would it work?

Best Answer

Yes, using innodb_log_group_home_dir will put your ib_logfile* on the specified location. You can also opt to do the following:

[mysqld]
datadir=/var/lib/mysql
innodb_log_group_home_dir=/iblog
innodb_data_home_dir=/ibdata

So the system tablespace (ibdata1) goes inside /ibdatafiles folder, while the redo log files (ib_logfile*) goes inside /iblogfiles.

# ls -l /ibdata
total 12296
-rw-rw---- 1 mysql mysql 12582912 Jul 17 08:59 ibdata1
# ls -l /iblog
total 98320
-rw-rw---- 1 mysql mysql 50331648 Jul 17 08:59 ib_logfile0
-rw-rw---- 1 mysql mysql 50331648 Jul 17 08:59 ib_logfile1
# ls -l /var/lib/mysql
total 48
-rw-rw---- 1 mysql mysql  56 Jul 17 08:59 auto.cnf
-rw-rw---- 1 mysql mysql   5 Jul 17 08:59 centos1.pid
drwx------ 2 mysql mysql   0 Jul 17 08:59 mysql
-rw-rw---- 1 mysql mysql 151 Jul 17 08:59 mysql-bin.000001
-rw-rw---- 1 mysql mysql  19 Jul 17 08:59 mysql-bin.index
srwxrwxrwx 1 mysql mysql   0 Jul 17 08:59 mysql.sock
drwx------ 2 mysql mysql   0 Jul 17 08:59 performance_schema
drwx------ 2 mysql mysql   0 Jul 17 08:59 test
Related Question