Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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 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.
1K+ Views
