Moving MySQL Server To A Custom Directory On Ubuntu Linux
Here we will discuss the process of moving MySQL to another directory on Ubuntu Server.
systemctl stop mysql
rsync -av /var/lib/mysql /new/dbroot/
sending incremental file list
mysql/
mysql/#ib_16384_0.dblwr
mysql/#ib_16384_1.dblwr
mysql/auto.cnf
mysql/binlog.000001
mysql/binlog.000002
mysql/binlog.000003
mysql/binlog.index
mysql/ca-key.pem
mysql/ca.pem
mysql/client-cert.pem
mysql/client-key.pem
mysql/debian-5.7.flag
mysql/ib_buffer_pool
mysql/ibdata1
mysql/mysql.ibd
mysql/private_key.pem
mysql/public_key.pem
mysql/server-cert.pem
mysql/server-key.pem
mysql/undo_001
mysql/undo_002
mysql/#innodb_redo/
mysql/#innodb_redo/#ib_redo10_tmp
mysql/#innodb_redo/#ib_redo11_tmp
mysql/#innodb_redo/#ib_redo12_tmp
mysql/#innodb_redo/#ib_redo13_tmp
mysql/#innodb_redo/#ib_redo14_tmp
mysql/#innodb_redo/#ib_redo15_tmp
mysql/#innodb_redo/#ib_redo16_tmp
mysql/#innodb_redo/#ib_redo17_tmp
mysql/#innodb_redo/#ib_redo18_tmp
mysql/#innodb_redo/#ib_redo19_tmp
mysql/#innodb_redo/#ib_redo20_tmp
mysql/#innodb_redo/#ib_redo21_tmp
mysql/#innodb_redo/#ib_redo22_tmp
mysql/#innodb_redo/#ib_redo23_tmp
mysql/#innodb_redo/#ib_redo24_tmp
mysql/#innodb_redo/#ib_redo25_tmp
mysql/#innodb_redo/#ib_redo26_tmp
mysql/#innodb_redo/#ib_redo27_tmp
mysql/#innodb_redo/#ib_redo28_tmp
mysql/#innodb_redo/#ib_redo29_tmp
mysql/#innodb_redo/#ib_redo30_tmp
mysql/#innodb_redo/#ib_redo31_tmp
mysql/#innodb_redo/#ib_redo32_tmp
mysql/#innodb_redo/#ib_redo33_tmp
mysql/#innodb_redo/#ib_redo34_tmp
mysql/#innodb_redo/#ib_redo35_tmp
mysql/#innodb_redo/#ib_redo36_tmp
mysql/#innodb_redo/#ib_redo37_tmp
mysql/#innodb_redo/#ib_redo6
mysql/#innodb_redo/#ib_redo7_tmp
mysql/#innodb_redo/#ib_redo8_tmp
mysql/#innodb_redo/#ib_redo9_tmp
mysql/#innodb_temp/
mysql/mysql/
...
mysql/performance_schema/table_io_waits_s_107.sdi
mysql/performance_schema/table_io_waits_s_108.sdi
mysql/performance_schema/table_lock_waits_109.sdi
mysql/performance_schema/threads_110.sdi
mysql/performance_schema/tls_channel_stat_190.sdi
mysql/performance_schema/user_defined_fun_188.sdi
mysql/performance_schema/user_variables_b_176.sdi
mysql/performance_schema/users_144.sdi
mysql/performance_schema/variables_by_thr_183.sdi
mysql/performance_schema/variables_info_186.sdi
mysql/sys/
mysql/sys/sys_config.ibd
sent 187,674,392 bytes received 3,286 bytes 375,355,356.00 bytes/sec
total size is 187,617,366 speedup is 1.00
mv /var/lib/mysql /var/lib/mysql.bak
vim /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
#
# * Basic Settings
#
user = mysql
# pid-file = /var/run/mysqld/mysqld.pid
#socket = /var/run/mysqld/mysqld.sock
# port = 3306
# datadir = /var/lib/mysql
datadir = /new/dbroot/mysql
vim /etc/apparmor.d/tunables/alias
# ------------------------------------------------------------------
#
# 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/ -> /new/dbroot/mysql/,
If rsync doesn’t keep the permissions of the previous mysql database root.
chown -R mysql:mysql /new/dbroot/mysql
The script that starts the MySQL service
/usr/share/mysql/mysql-systemd-start
if [ ! -d /var/lib/mysql ] && [ ! -L /var/lib/mysql ]; then
echo "MySQL data dir not found at /var/lib/mysql. Please create one."
exit 1
fi
if [ ! -d /var/lib/mysql/mysql ] && [ ! -L /var/lib/mysql/mysql ]; then
echo "MySQL system database not found. Please run mysql_install_db tool."
exit 1
fi
mkdir -p /var/lib/mysql/mysql
systemctl restart apparmor.service
systemctl start mysql.service
No systemd errors show be displayed from these commands and we should have the ability to login to MySQL using the mysql command as root. On Ubuntu you don’t necessarily have to type in a password to the MySQL database if you’re logged into the system as root.
mysql -u root
For clean up we remove the /var/lib/mysql.bak file from the server.
rm -rf /var/lib/mysql.bak