CODEWITHSUNDEEP
mariadb
Jonathan
Jonathan

Reputation: 2845

mariadb move system database to new location

I have just installed Mariadb 10.1.16 on Ubuntu 16.04 but the default data directory is not suitable because of insufficient space and I need to move it to another volume. I have added the datadir entry to my mysql.cnf file but when I try to load the mysql service I get the following result:

ago 12 08:27:40 aristotle mysqld[13786]: 2016-08-12  8:27:40 140584295778560 [Note] Plugin 'FEEDBACK' is disabled.
ago 12 08:27:40 aristotle mysqld[13786]: 2016-08-12  8:27:40 140583574173440 [Note] InnoDB: Dumping buffer pool(s) not yet started
ago 12 08:27:40 aristotle mysqld[13786]: 2016-08-12  8:27:40 140584295778560 [ERROR] Could not open mysql.plugin table. Some plugins may be not loaded
ago 12 08:27:40 aristotle mysqld[13786]: 2016-08-12  8:27:40 140584295778560 [ERROR] Can't open and lock privilege tables: Table 'mysql.servers' doesn't exist
ago 12 08:27:40 aristotle mysqld[13786]: 2016-08-12  8:27:40 140584295778560 [Note] Server socket created on IP: '::'.
ago 12 08:27:40 aristotle mysqld[13786]: 2016-08-12  8:27:40 140584295778560 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't 
ago 12 08:27:40 aristotle systemd[1]: mariadb.service: Main process exited, code=exited, status=1/FAILURE
ago 12 08:27:40 aristotle systemd[1]: Failed to start MariaDB database server.
ago 12 08:27:40 aristotle systemd[1]: mariadb.service: Unit entered failed state.
ago 12 08:27:40 aristotle systemd[1]: mariadb.service: Failed with result 'exit-code'.

My configuration file is:

[client]
# Default is Latin1, if you need UTF-8 set this (also in server section)
default-character-set = utf8 

[mysqld]
#
# * Character sets
# 
# Default is Latin1, if you need UTF-8 set all this (also in client section)
#
character-set-server  = utf8 
collation-server      = utf8_general_ci 
character_set_server   = utf8 
collation_server       = utf8_general_ci
datadir               = /BulkData/Mariadb

What do I need to do to have my database(s) on a different volume?

Upvotes: 0

Views: 3382

Answers (1)

Hackerman
Hackerman

Reputation: 12295

In this case scenario you have two options, increase the partition size(root), or just copy to another directory and create a symbolic link. The first option seems more natural, but, if you do something wrong that could compromise your entire system. The second one is my choice, because it doesn't involve to much risk, and it's easier. If you really want to go with the first one, you can check this link: http://www.hiroom2.com/2016/05/19/ubuntu-16-04-extend-and-reduce-lvm-root-filesystem/

If you want to go with the second option, you just need to:

  • Identify the destination partition(plenty of space), running the command: df -h

  • Once the destination partition is checked, then stop the MySQLservice: service mysql stop

  • Move the folder(it's shorter than copy and remove it, also you don't have to check permissions): mv /var/lib/mysql /usr/lib/mysql

  • Create the symbolic link: ln -s /usr/lib/mysql /var/lib/mysql

  • Start the service: service mysql start

Upvotes: 1

Related Questions