How to change the MySQL data directory to another location on Ubuntu 16.04

In this article, we will learn how to change the MySQL data directory or relocate the MySQL database data to a new location. This situation may be used when the database grows very fast, or when something goes wrong. For some security reasons we want to move the data directory to a new location.

Prerequisites

• An Ubuntu computer with Sudo privileges as a non-root user.
• MySQL is installed and running.
• The new volume or location we want to move the database data location to, the new location will be /mnt/data_vol/MySQL because data_vol is a new volume connected to the computer

Changing the MySQL data folder location

Before continuing, we 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 MySQL root password, enter the password. Run the following command to know MySQL's current working data directory.

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

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

$ sudo systemctl stop mysql

because systemctl will not show 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 the MySQL is stopped, we will move the data to the new location. To move the data, we will use Rsync with the –a option, which preserves permissions on the data files, and –v which displays verbose output.

Below is the complete 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 rsync, the data folder was successfully moved to the new location. For security reasons, we will retain the data folder until we confirm that the data is in the new location, and we will rename the current 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, there are many ways to change it, but we will edit the file located at /etc/mysql/mysql mysqld.cnf file in .conf.d/mysqld.cnf.

Edit mysqld.cnf, the command is as follows

$ 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

Change Apparmor alias settings

In addition, we need to edit /etc/apparmor.d/tunables/alias

At the bottom of the file we need 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

After editing the file, we need to restart apparmor.

The following is the command to restart apparmor.

Since we changed the default data directory, we need to run the following command, which will create a minimal directory folder structure to pass the scripting environment.

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

Now we will restart the mysql service.

$ sudo systemctl start mysql

Now we will check the status of the MySQL service using the following 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 ensure that the new data directory has been changed, we will run the following 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 the data Directory changes, we will delete the default data directory, which is located at /var/lib/mysql_backup, below is the command to delete the old database directory.

$ sudo rm –rf /var/lib/mysql_backup

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

The above is the detailed content of How to change the MySQL data directory to another location on Ubuntu 16.04. For more information, please follow other related articles on the PHP Chinese website!