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.
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
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!
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
