How To Change The MySQL Data Directory to Another Location on Ubuntu 16.04

UbuntuMySQLDatabase

In this article, we will learn how to change the MySQL Data Directory or relocate the MySQL Database data to the new location, this situation may be used when the database is growing very fast, or for some security reasons we want to move the data directory to the new location.

Prerequisites

  • An Ubuntu machine with a non-root user with Sudo permission.
  • MySQL installed and working.
  • A new volume or location where we want to move the database data location, the new location will be /mnt/data_vol/MySQL as the data_vol is the new volume attached to the machine or server.

Changing the MySQL Data Folder Location

Before we proceed further, we will first find the current location of the data directory

$ mysql –u root –p
Output:
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 472
Server version: 5.6.30-0ubuntu0.14.04.1 (Ubuntu)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>

When prompted for the root password of the MySQL, please enter the password. Run the below command to know the current working data directory for MySQL.

Mysql> select @@datadir;
Output:
+-----------------+
| @@datadir       |
+-----------------+
| /var/lib/mysql/ |
+-----------------+
1 row in set (0.00 sec)

As the output will show that the MySQL database uses the /var/lib/MySQL as the default folder as the data directory. Before we modify anything first, we will check the integrity of the data, we will stop the MySQL and check the status

$ sudo systemctl stop mysql

As systemctl will not display anything for the services command

$ sudo systemctl status mysql
Output:
mysql.service - MySQL Community Server
   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
   Active: inactive (dead) since Mon 2016-09-12 13:57:43 IST; 1s ago
   Process: 17669 ExecStartPost=/usr/share/mysql/mysql-systemd-start post (code=exited, status=0/SUCCESS)
   Process: 17668 ExecStart=/usr/sbin/mysqld (code=exited, status=0/SUCCESS)
   Process: 17664 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
   Main PID: 17668 (code=exited, status=0/SUCCESS)
Sep 12 13:55:14 ubuntu-16 systemd[1]: Starting MySQL Community Server...
Sep 12 13:55:15 ubuntu-16 systemd[1]: Started MySQL Community Server.
Sep 12 13:57:40 ubuntu-16 systemd[1]: Stopping MySQL Community Server...
Sep 12 13:57:43 ubuntu-16 systemd[1]: Stopped MySQL Community Server.

Once, we confirm that the MySQL is stopped, we will move the data to the new location. To move the data, we will use Rsync with –a option which preserves the permission of the data files and –v which show the verbose output.

Below is the full command to move the data to the new location –

$ rsync –av /var/lib/mysql /mnt/data_vol/
OutPut:
sending incremental file list
mysql/
mysql/auto.cnf
mysql/debian-5.7.flag
mysql/ib_buffer_pool
mysql/ib_logfile0
mysql/ib_logfile1
mysql/ibdata1
mysql/mysql/
mysql/mysql/columns_priv.MYD
mysql/mysql/columns_priv.MYI
mysql/mysql/columns_priv.frm
mysql/mysql/db.MYD
mysql/mysql/db.MYI
mysql/mysql/db.frm
mysql/mysql/db.opt
….
mysql/sys/x@0024user_summary.frmmysql/sys/x@0024user_summary_by_file_io.frm
mysql/sys/x@0024user_summary_by_file_io_type.frm
mysql/sys/x@0024user_summary_by_stages.frm
mysql/sys/x@0024user_summary_by_statement_latency.frm
mysql/sys/x@0024user_summary_by_statement_type.frm
mysql/sys/x@0024wait_classes_global_by_avg_latency.frm
mysql/sys/x@0024wait_classes_global_by_latency.frm
mysql/sys/x@0024waits_by_host_by_latency.frm
mysqlsys//x@0024waits_by_user_by_latency.frm
mysql/sys/x@0024waits_global_by_latency.frm
sent 199,384,083 bytes received 6,858 bytes 132,927,294.00 bytes/sec
total size is 199,307,568 speedup is 1.00

After the rsync, successfully move the data folder to the new location. For security reasons we will keep the data folder till confirms that the data is at the new location, we will rename the present data directory from /var/lib/mysql to /var/lib/mysql_backup. below is the command to change the current data directory.

Below is the command to change the current data directory –

$ sudo mv /var/lib/mysql /var/lib/mysql_backup

Now, we will change the default data directory, for changing we have a lot of ways, but we will edit the mysqld.cnf file which is located in the /etc/mysql/mysql.conf.d/mysqld.cnf.

To edit the mysqld.cnf, below is the command

$ sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
Output:
[mysqld_safe]
socket    = /var/run/mysqld/mysqld.sock
nice      = 0
[mysqld]
#
# * Basic Settings
#
user             = mysql
pid-file         = /var/run/mysqld/mysqld.pid
socket           = /var/run/mysqld/mysqld.sock
port             = 3306
basedir          = /usr
datadir          = /mnt/data_vol/mysql/
tmpdir           = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking

Changing the Apparmor Alias Settings

Also, we needed to edit the /etc/apparmor.d/tunables/alias

At the bottom of the file, we needed to add the following lines in the alias rules.

$ sudo vi /etc/apparmor.d/tunables/alias
Output:
# ------------------------------------------------------------------
#
# Copyright (C) 2010 Canonical Ltd.
#
# This program is free software; you can redistribute it and/or
# modify it under the terms of version 2 of the GNU General Public
# License published by the Free Software Foundation.
#
# ------------------------------------------------------------------
# Alias rules can be used to rewrite paths and are done after variable
# resolution. For example, if '/usr' is on removable media:
# alias /usr/ -> /mnt/usr/,
#
# Or if mysql databases are stored in /home:
# alias /var/lib/mysql/ -> /home/mysql/,
alias /var/lib/mysql/ -> /mnt/data_vol/mysql

Once the file is edited, we need to restart the apparmor.

Below is the command to restart the apparmor.

As we have changed the default data directory, we needed to run the below command which will create the minimal directory folder structure to pass the script environments.

$ sudo mkdir /var/lib/mysql/mysql –p

Now we will restart the mysql services.

$ sudo systemctl start mysql

Now we will check the status of the MySQL services with the below command

$ sudo systemctl status mysql
Output:
mysql.service - MySQL Community Server
    Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
    Active: active (running) since Mon 2016-09-12 14:17:27 IST; 23s ago
   Process: 18481 ExecStartPost=/usr/share/mysql/mysql-systemd-start post (code=exited, status=0/SUCC
   Process: 18477 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCES
  Main PID: 18480 (mysqld)
     Tasks: 28 (limit: 512)
    Memory: 137.3M
   CPU: 329ms
CGroup: /system.slice/mysql.service
        └─18480 /usr/sbin/mysqld
Sep 12 14:17:26 ubuntu-16 systemd[1]: Starting MySQL Community Server...
Sep 12 14:17:27 ubuntu-16 systemd[1]: Started MySQL Community Server.

To make sure that the new data directory is changed, we will run the below command

$ mysql -uroot -p
Output:
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.13-0ubuntu0.16.04.2 (Ubuntu)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select @@datadir
+----------------------+
| @@datadir            |
+----------------------+
| /mnt/data_vol/mysql/ |
+-----------------+
1 row in set (0.00 sec)
mysql>

Once we confirm that the data directory is changed, we will remove the default data directory which at /var/lib/mysql_backup, below is the command to remove the old database directory.

$ sudo rm –rf /var/lib/mysql_backup

In the above configuration and steps, we have learned to relocate the MySQL data directory to the new location which will help us to secure or to store more data to a different location.

raja
Updated on 21-Jan-2020 08:05:52

Advertisements