Science And Technology News

Information Mobile News

Learn to Move MySQL Data Directory to New Location

by · Published · Updated

By default MySQL database will be created under /var/lib/mysql directory. This might be Ok if you are running a small database that doesn’t take much space. But on production, for a larger database, you might not have enough space under root partition.

In that case, you may want to move your MySQL database from root partition to a different partition.

To change the MySQL directory, on a high level, you have to perform the following three steps:

  1. Move MySQL database files from /var/lib/mysql to a different partition
  2. Modify the my.cnf file with the new directory location
  3. Update security settings to reflect the directory change: On CentOS or RedHat, modify SELinux settings. On Ubuntu or Debian, modify the AppArmor settings.

This tutorial explains the details on how to perform the above three steps to move your MySQL data to a different directory.

Take a Backup of Current MySQL

Before you do anything, stop the MySQL database and take a cold backup of your database.

By default MySQL will database will be under /var/lib/mysql directory. Copy this mysql directory to a different location for backup.

service mysqld stopcp -r /var/lib/mysql /backup/mysql

Or, if you prefer, you can use mysqldump to take a MySQL DB backup.

Move MySQL Data Directory to Different Partition

In this example, my root partition is /dev/sda1, which doesn’t have much space for the default /var/lib/mysql directory. But, I have /data partition on /dev/sdb1 disk which has lot of space.

So, I’ll be moving the MySQL database from / partition to /data partition.

Create the following directory and move the mysql data from /var/lib to /data/var/lib as shown below.

mkdir -p /data/var/libcd /var/libmv mysql /data/var/lib/

Few points to consider:

  • If possible, try to use the move command to move the directory over (instead of copy). When you perform copy, the SELinux context will be lost, and you have to manually set those later (as explained below). But, when you move, the appropriate SELinux context for MySQL are kept in tact and you don’t have to worry about changing it.
  • You can also move mysql directory to /data/var/lib/ directory, and create a symbolic link from /var/lib pointing to /data/var/lib. But, In this particular instance, I prefer the above simple moving the directory without symbolic link to avoid confusion.

Also, if you’ve copied the directory (instead of move), make sure you change the ownership appropriately. If not, you might get this error message: MySQL error: 1017Can’t find file: (errno: 13)

chown -R mysql:mysql /data

Modify my.cnf and Start MySQL

In the /etc/my.cnf file, you need to modify both datadir and socket parameter and point them to the new directory as shown below.

# vi /etc/my.cnfdatadir=/data/var/lib/mysqlsocket=/data/var/lib/mysql/mysql.sock

Finally, restart the MySQL database.

# service mysqld startStarting mysqld:  [  OK  ]

If you have a tmpdir parameter already defined in your my.cnf file, change the directory for that parameter also:

tmpdir=/data/var/lib/mysql

After changing the datadir and socket in the my.cnf file, if MySQL doesn’t start, or fails with a permission denied error message, then you need to set the SELinux (or AppArmor) as explained in the following sections.

SELinux Context Type for MySQL

Use ls -Z command to view the SELinux context. Before moving the directory, the following was the SELinux context on my MySQL database. In this example, “thegeekstuff” is the MySQL database under /var/lib/mysql directory.

As you see here, mysqld_db_t is the SELinux context type.

# ls -Z /var/lib/mysqldrwx------. mysql mysql unconfined_u:object_r:mysqld_db_t:s0 thegeekstuff-rw-rw----. mysql mysql unconfined_u:object_r:mysqld_db_t:s0 ibdata1-rw-rw----. mysql mysql unconfined_u:object_r:mysqld_db_t:s0 ib_logfile0..

After moving the directory to the new location, you should see the exact SELinux as pre-move.

ls -Z /data/var/lib/mysql

Note: If you did a copy of the directory (instead of move), you’ll notice that it has changed. In that case, change the SELinux context as explained below.

When the SELinux context is wrong, you’ll see following error message (or something similar to this) in your audit log (or /var/log/messages)

# cat /var/log/audit/audit.log:type=AVC msg=audit(1447281394.928:20831): avc:  denied  { read } for  pid=21346 comm="mysqld" name="mysql" dev=sda1 ino=5506027 scontext=unconfined_u:system_r:mysqld_t:s0 tcontext=unconfined_u:object_r:var_lib_t:s0

Also, you’ll see the following in your mysqld.log file when MySQL DB failed to start.

# cat /var/log/mysqld.log:mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql[Warning] Can't create test file /var/lib/mysql/devdb..lower-test/usr/libexec/mysqld: Can't change dir to '/var/lib/mysql/' (Errcode: 13)[ERROR] Aborting[Note] /usr/libexec/mysqld: Shutdown complete

SELinux Setup for MySQL on CentOS / RedHat (Option 1)

Using chcon command, you can change the SELinux context type in the new directory as shown below.

chcon -R -t mysqld_db_t /data

In the above command:

  • -t option is used to specify the SELinux context type that should be set. In this example, we are setting it to mysqld_db_t type.
  • chcon is the command to change the SELinux context
  • -R option will recursively change context for the given directory and all the sub directory.
  • /data is the directory on which this command will be executed.

Note: Start changing the context from the top level directory /data (and not from the mysql directory), which will include mysql directory and all the sub-directory and files.

SELinux Setup for MySQL on CentOS / RedHat (Option 2)

Using restorecon command, you can restore the SELinux context to the correct type. But, in this case, you should inform SELinux what is the correct context by adding mysqld_db_t type to the SELinux context map.

To add the SELinux type to the context map, use the semanage command. Install policycoreutils-python package which contains semanage command.

yum -y install policycoreutils-python

Next, execute the following command to set the SELinux context map on the new directory.

semanage fcontext -a -t mysqld_db_t "/data(/.*)?"

In the above command, we are adding mysqld_db_t to the context map for the /data directory all the sub-directories and files underneath.

Finally, use the restorecon command, which will restore the appropriate SELinux context to the new /data directory.

restorecon -Rv /data

Verify that the moved new /data directory and mysql sub-folders has the correct SELinux context.

# ls -Z /data/var/lib/mysqldrwx------. mysql mysql unconfined_u:object_r:mysqld_db_t:s0 thegeekstuff-rw-rw----. mysql mysql unconfined_u:object_r:mysqld_db_t:s0 ibdata1-rw-rw----. mysql mysql unconfined_u:object_r:mysqld_db_t:s0 ib_logfile0..

Note: You can also use -e option along with the fcontext. This will make the context label for the /data and its subdirectories same as the context label of /var/lib/mysql

semanage fcontext -a -e /var/lib/mysql /data

AppArmor Setup for MySQL on Ubuntu / Debian

After moving the MySQL data diretory to a new location, if you don’t do the following, on Ubuntu, you will get this error while starting the mysql database: “(errno: 13)” (permission denied).

Modify the usr.sbin.mysqld file as shown below, and add the following two lines. Don’t forget the comma at the end of the line, which is required.

# vi /etc/apparmor.d/usr.sbin.mysqld/data/var/lib/mysql/ r,/data/var/lib/mysql/** rwk,

Next, execute the following command to reparse this new apparmor config file for mysql, and restart the apparmor.

sudo apparmor_parser -r /etc/apparmor.d/usr.sbin.mysqldsudo /etc/init.d/apparmor reload

Note: Alternatively, you can also add an alias in the AppArmor alias file as shown below. Again, don’t forget the comma at the end of this alias line.

# vi  /etc/apparmor.d/tunables/aliasalias /var/lib/mysql/ -> /newpath/,

MySQL Client socket Parameter

After the above change, you might be getting this error message while connecting from mysql client: ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’ (2)

# mysql -u root -pMyPasswordERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

If that happens, pass the –socket parameter to the mysql client, and point it to the mysql.sock file that is located under the new directory.

mysql -u root -pMyPassword --socket=/data/var/lib/mysql/mysql.sock

If you are calling mysql client locally, you can also use -h option and pass 127.0.0.1 as shown below. This will also avoid the mysql.sock error message.

mysql -u root -pMyPassword -h127.0.0.1

>

Tags:

You may also like...