• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
sharadchhetri

sharadchhetri

Tutorials On Linux, Unix & Open Source

  • Home
  • Linux Commands
  • Resources
    • Learn Linux
  • My WordPress plugins

How to change mysql default data directory in Ubuntu

May 18, 2013 by Sharad Chhetri 2 Comments

How to change mysql data directory in Ubuntu

In this tutorial we will learn how to change the default path of mysql data directory.
The mysql server bydefault keep the data in /var/lib/mysql . If you want to change its path follow the given below steps.

Lets have an overview for changing mysql data directory method:

(1) Copy the new mysql directory to new location in server
(2) edit the my.cnf file and give new mysql data directory path
(3) edit /etc/apparmor.d/usr.sbin.mysqld file and add 2 lines for new mysql data directory.
(4) Restart the apparmor service
(5) Restart the mysql server service.

For reference purpose I will copy the /var/lib/mysql to /var/lib/mysqlnew .

Note:
(a) Inside /var/lib/mysql . the new database will be in directory with same name. for eg. If my database name is linuxdb then same name of directory you will find i.e /var/lib/mysql/linuxdb . Hence same databases should be exist in new mysql data dir.

(b) Given below are the important files that should be present in new mysql data dir.
ibdata1,ib_logfileN,mysql_upgrade_info,debian-5.5.flag

-rw-r–r– 1 mysql mysql 0 May 1 14:54 debian-5.5.flag
-rw-rw—- 1 mysql mysql 27262976 May 18 12:58 ibdata1
-rw-rw—- 1 mysql mysql 5242880 May 18 12:58 ib_logfile0
-rw-rw—- 1 mysql mysql 5242880 Mar 5 11:07 ib_logfile1
-rw-rw—- 1 mysql mysql 6 May 1 14:55 mysql_upgrade_info

(c) The ownership and group of entire /var/lib/mysql is mysql:mysql . Just as u can see in above box.

Now follow the given below steps to change mysql data directory

Step1: Copy the /var/lib/mysql to new name /var/lib/mysqlnew

cp -prvf /var/lib/mysql /var/lib/mysqlnew

check the ownsership and group of new dir mysqlnew. It should mysql:mysql

root@sharad-sapplica:/var/lib# ls -ld mysqlnew/
drwx—— 7 mysql mysql 4096 May 18 13:00 mysqlnew/
root@sharad-sapplica:/var/lib# ls -la mysqlnew/
total 36896
drwx—— 7 mysql mysql 4096 May 18 13:00 .
drwxr-xr-x 83 root root 4096 May 18 12:56 ..
-rw-r–r– 1 mysql mysql 0 May 1 14:54 debian-5.5.flag
-rw-rw—- 1 mysql mysql 27262976 May 17 19:31 ibdata1
-rw-rw—- 1 mysql mysql 5242880 May 18 13:00 ib_logfile0
-rw-rw—- 1 mysql mysql 5242880 Mar 5 11:07 ib_logfile1
drwx—— 2 mysql mysql 4096 May 1 14:55 mysql
-rw-rw—- 1 mysql mysql 6 May 1 14:55 mysql_upgrade_info
drwx—— 2 mysql mysql 4096 May 1 14:55 performance_schema
drwx—— 2 mysql mysql 4096 Mar 5 11:07 test
root@sharad-sapplica:/var/lib#

Step 2: Now edit my.cnf file
and change the value of datadir with new path of mysql data directory.
Then save and exit

vi /etc/mysql/my.cnf

#datadir = /var/lib/mysql
datadir = /var/lib/mysqlnew

Note: After doing step 2, if you try to restart the mysql service it will not start and give some error in /var/log/syslog .
For reference below is the syslog details.

# tail -3 /var/log/syslog

May 18 12:58:19 mypc kernel: [ 7780.046897] type=1400 audit(1368862099.538:51): apparmor=”STATUS” operation=”profile_replace” name=”/usr/sbin/mysqld” pid=8171 comm=”apparmor_parser”
May 18 12:58:19 mypc kernel: [ 7780.065904] type=1400 audit(1368862099.558:52): apparmor=”DENIED” operation=”mknod” parent=1 profile=”/usr/sbin/mysqld” name=”/var/lib/mysqlnew/mypc.lower-test” pid=8175 comm=”mysqld” requested_mask=”c” denied_mask=”c” fsuid=0 ouid=0
May 18 12:58:19 mypc kernel: [ 7780.065975] type=1400 audit(1368862099.558:53): apparmor=”DENIED” operation=”mknod” parent=1 profile=”/usr/sbin/mysqld” name=”/var/lib/mysqlnew/mypc.lower-test” pid=8175 comm=”mysqld” requested_mask=”c” denied_mask=”c” fsuid=0 ouid=0
May 18 12:58:19 mypc kernel: [ 7780.069767] type=1400 audit(1368862099.562:54): apparmor=”DENIED” operation=”open” parent=1 profile=”/usr/sbin/mysqld” name=”/var/lib/mysqlnew/mysql/plugin.frm” pid=8175 comm=”mysqld” requested_mask=”r” denied_mask=”r” fsuid=121 ouid=121

Step 3: After reading the syslog we get to know apparmor is denying the new configuration for mysql. To rectify this issue we will edit the file /etc/apparmor.d/usr.sbin.mysqld. And paste the new path of mysql data directory.
And restart the apparmor service.

See the below method

# vi /etc/apparmor.d/usr.sbin.mysqld

/var/lib/mysql/ r,
/var/lib/mysql/** rwk,
/var/lib/mysqlnew/ r,
/var/lib/mysqlnew/** rwk,

Note: I kept the 2 lines for /var/lib/mysql in file as it is.Only added new 2 lines.(see the Bolded 2 lines)

# /etc/init.d/apparmor restart

Step 4: Now restart the mysql service .And try to connect the mysql-server.

#/etc/init.d/mysql restart

# mysql -u root -p

Note: Do not apply the steps directly in Live Production servers. First test the method in staging or test machines.

It depend upon the scenario on which you are working either you are copying the same data dir within same system or copying it from another server to new server.
The above given eg. is just for an reference.

Share this:

  • Twitter
  • Facebook
  • More
  • Print
  • Email
  • LinkedIn
  • Reddit
  • Tumblr
  • Pinterest
  • Pocket
  • Telegram
  • WhatsApp
  • Mastodon

Related posts:

  1. Change mysql default port number in linux
  2. Your data directory and files are probably accessible from the internet because the .htaccess file does not work owncloud
  3. Change default editor of crontab in Ubuntu
  4. How to check default storage engine in MySQL server
  5. Change the default port number of ssh server
  6. how to change path of tmpdir in MySQL Server On Ubuntu
  7. shell-init: error retrieving current directory: getcwd: cannot access parent directories: No such file or directory
  8. Find mysql commands history without mysql server login
  9. How to set default storage engine to MyISAM to InnoDB vice versa
  10. how to mount NAS storage in owncloud to keep all users data

Filed Under: Linux, mysql Tagged With: mysql

Reader Interactions

Comments

  1. Dhruv shah says

    June 3, 2016 at 10:19 am

    Dude,
    This is seriously the best resource on the net for this particular procedure.
    Crisp and to the point.
    Thank you

    Reply
    • sharad chhetri says

      June 3, 2016 at 2:30 pm

      Thanks of lot Dhruv,Loved the feedback.

      Regards
      Sharad

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Primary Sidebar

Our Social Media Presence

  • Facebook
  • GitHub
  • Twitter

Linux Command

What is Linux Internal And External Command

Linux Basic Commands With Examples For Every Beginner

tr command to convert lines to space , tab and vertical tab

smbpasswd command not found on CentOS 7 and RHEL 7

Solution : semanage command not found

Unix / Linux : How to print duplicate lines from file

More Posts from this Category

You Might Like These Articles!

simplecodesyntax wordpress plugin

SimpleCodeSyntax : My Another WordPress Plugin

Install Nginx

How To Install Nginx On Ubuntu 22.04 LTS

Install Latest Git package in Ubuntu Operating System

How To Always Install Latest Git Package In Ubuntu Operating System

Bash script for installing VirtualBox on Ubuntu 22.04 LTS Desktop

Install VirtualBox On Ubuntu 22.04 LTS Desktop (Bash Script)

libfuse

dlopen(): error loading libfuse.so.2 – Got Error On Ubuntu

Failed to open/create the internal network

VirtualBox Error: Failed to open/create the internal network

Always Useful Tips And Tricks

GNU Screen

How to convert rpm file into deb file

How to backup and restore iptables on Linux systems

Nagios HTTP WARNING: HTTP/1.1 403 Forbidden

Convert hyphen to underscore in between all filenames shell script

How to page scroll up/down in Linux terminal

Error: Could not stat() command file ‘/var/lib/nagios3/rw/nagios.cmd’!

Explore 90+ Article On "Linux Tips And Tricks"

Copyright © 2023 ยท
The material in this site cannot be republished either online or offline, without our permission.
Proudly Blogging From Bharat.

  • Contact
  • About Me
  • My WordPress plugins
  • Privacy Policy