0

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?

  • Note. This concept is a good idea only in certain situations. The most common case where it is likely to be useful: SSD for data & spinning drive for logs. If all your drives are 'equal', it is better to use RAID striping. – Rick James Jul 17 '16 at 17:02
  • That's there but I'm moving it out of default because I do not want it to be in root partition – tesla747 Jul 17 '16 at 17:03
  • Yes, it is good to keep everything you can out of the root partition. Usually it is too small to survive a few upgrades. – Rick James Jul 17 '16 at 17:05
  • @RickJames few upgrades? Rick can you elaborate on that? – tesla747 Jul 17 '16 at 17:06
  • OS upgrades (and other things that insist on being in the root partition) need room to grow. – Rick James Jul 17 '16 at 19:02
0

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
  • ok in this case if I mention the above two variables, where will the databases be stored at? in data-dir? – tesla747 Jul 17 '16 at 13:06
  • @tesla747 databases will be stored inside the datadir path in this case /var/lib/mysql – jerichorivera Jul 17 '16 at 13:08
  • Ok If I just mention the datadir and not the innodb_data_home_dir then both databases and system tablespace goes into the path mentioned by the datadir correct? – tesla747 Jul 17 '16 at 13:13
  • @tesla747 yes, the default value for innodb_data_home_dir is MySQL's data directory – jerichorivera Jul 17 '16 at 13:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.