Syed Jahanzaib Personal Blog to Share Knowledge !

June 16, 2017

Quick Notes on moving MySQL database(s) directory to New Partition

Filed under: Linux Related — Tags: , , — Syed Jahanzaib / Pinochio~:) @ 4:59 PM


This post described method on how you can move mySQL DB directory to another partition/drive.


Government base telecom authority called PTA (in PK) regularly issues letter to local ISP’s asking them to keep user traffic data for period of minimum one year.

As shown here (Thanks to Mr. Khalid for providing this notice copy)

pta letter

We know that almost all small scale ISP are natting there users by using local dsl or other medium bandwidth, and keeping private IP data traffic is (almost) useless because finding any thing from such data is like finding a needle in the haystack. If OP is assigning users with public ip, then OP should record users public ip assignment only like showed here, but as private natted IP are still being used therefore OP should save users traffic as per law despite of it will almost useless in practical)

If you are using Radius Manager, we have module base additional option for CONNECTION TRACKING. Using this method, we can track all users outgoing connection in mysql DB.



Connection Tracking requires lot of disk space for local ISP. I would recommend to use SSD disk as SSD disks are way faster compared tot he spindle base storage , SSD offers fast disk access with minimal latency, but as SSD are costly therefore as alternative, we can add secondary disk for mysql like 2.TB and move our MySQL DB in this drive, rest OS or RM will operate from our primary SSD.


We have 2 disk in system as follows

  1. 128 GB SSD [Ubuntu 12.4 installed along with radius manager 4.1.5]
  2. 2 TB SATA Disk [Empty & Mounted as /2tb, howto mount disk read this ]

So our requirement is to move MySQL DB to this 2 TB disk.

Quick Cmd’s …

First login to MySQL and see your current Data Directory location.

mysql> select @@datadir;
| @@datadir |
| /var/lib/mysql |
1 row in set (0.00 sec)

Now we need to move this folder to our new 2tb. Follow below ,,,

Stop MySQL Service & Moves files to 2 TB Disk

sudo service mysql stop

Copy mysql DB Data directory to our 2tb disk with permissions intact, this part is crucial, pay attention to this section. We will use RSYNC to have same permission level in new folder.

sudo rsync -av /var/lib/mysql /2tb/

Rename current MySQL DB directory /var/lib/mysql to .bak [for backup purposes so that in case any thing goes wrong , we still have this restore point]

sudo mv /var/lib/mysql /var/lib/mysql.bak

Change PATH in MySQL INF file

Edit mysql inf file to change the DB directory

sudo nano /etc/mysql/my.cnf

in this file, find DATADIR line and change the old path to new one As shown below …

datadir = /2tb/mysql

Save & Exit

Apparmor Section [for Ubuntu OS]:

Allow new folder in APPARMOR (if you will skip this, you will get access / permission  errors on mysql restart section)

sudo nano /etc/apparmor.d/tunables/alias

at the bottom add this line

alias /var/lib/mysql/ -> /2tb/mysql/,

Save n Exit.

It is also recommended to disable SELINUX.

Note: As mentioned by one member, you need to restart APPARMOR services,

/etc/init.d/apparmor restart

Start MySQL & Test

Now start the mysql service

service mysql start

& if all ok you may see following …

mysql start/running, process 1881

further verify it with process check

root@radius:~# ps aux |grep mysql
mysql 1881 0.1 3.9 328928 40536 ? Ssl 16:09 0:00 /usr/sbin/mysqld

Login to my mysql and verify all db/tables showing ok

root@radius:~# mysql -uroot -pMYSQLPASSWORD
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 964
Server version: 5.5.54-0ubuntu0.12.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
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select @@datadir;
| @@datadir |
| /2tb/mysql/ |
1 row in set (0.00 sec)



Syed Jahanzaib


  1. DO we need to change anything in radiusmanager conf file? Because i am getting could not connect to localhost error after following above steps.


    Comment by sanketabad — June 24, 2017 @ 12:10 PM

  2. after editing the apparmor alias file, I had to restart appamor before mysql could start successfully:
    /etc/init.d/apparmor restart
    With this one addition, your procedure above worked for me, thanks for documenting this.


    Comment by Mike Higgins — October 22, 2019 @ 11:14 AM

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: