I am using CentOS with cPanel. On my server, all MySQL databases save at /var/lib/mysql
. Now /var
is 100% full and MySQL has stopped working. How can I move the databases to a new directory like /home/mysql
especially considering that this server is managed with cPanel?
4 Answers
I can't confirm this from cpanel, as I don't have access, but this is an example from the console connected by ssh and sudo to root. There are arguments for using a bind entry in /etc/fstab instead of a symlink, but this works for me.
My normal procedure is to stop mysql, move the directory contents, link the original, and restart mysqld.
[tomh@workstation001 ~]$ sudo su -
[root@workstation001 ~]#
[root@workstation001 ~]# service mysqld stop
Stopping mysqld (via systemctl):
[ OK ]
[root@workstation001 ~]# mv /var/lib/mysql/ /opt/
[root@workstation001 ~]# ln -s /opt/mysql /var/lib/
[root@workstation001 ~]# ls -la /var/lib/mysql
lrwxrwxrwx 1 root root 10 Feb 26 23:02 /var/lib/mysql -> /opt/mysql
[root@workstation001 ~]# service mysqld start
Starting mysqld (via systemctl): [ OK ]
[root@workstation001 ~]# mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.18-log MySQL Community Server (GPL)
mysql> show databases;
...
| Database |
+-----------------------+
| information_schema |
| mysql |
| performance_schema |
| test
I just noticed a similar question, of which this is close to a duplicate of, which mentions some issues of doing that above with selinux enabled; http://crashmag.net/change-the-default-mysql-data-directory-with-selinux-enabled
So if you have selinux there are some additional steps.
-
After follow these steps i try to start ten i got this error root@ns1 [/var/lib]# service mysqld start mysqld: unrecognized service When i replace mysqld by mysql then output is as follow root@ns1 [/var/lib]# service mysql start Starting MySQL. ERROR! Manager of pid-file quit without updating file. Feb 27, 2012 at 8:02
-
What version of centos and mysql-server do you have and where did you get it? the base repos package for centos 5 and 6 call the service mysqld...– TomFeb 27, 2012 at 9:30
-
update your question with the output of
ls -lh /etc/init.d
andls -lh /var/lib/mysql
andls -lh /var/lib/mysql
– TomFeb 27, 2012 at 9:39 -
I was linking
/var/lib/mysql
to the newdata_dir
to lazy manage the defaultmysql.sock
but recently I figure that it's not a good idea because socket and symlink is bad. So justmkdir /var/lib/mysql
andchown mysql:mysql /var/lib/mysql
, MySQL will create its socket and voila. Nov 12, 2017 at 9:13
if you want to change mysql
data directory, you should do edit the mysql config file and change datadir
value.
datadir = /home/user/data
Your new data directory MUST be owned by mysql
and has proper SELinux security context.
chown -R mysql:mysql /home/user/data
chcon -R -t mysqld_db_t /home/user/data
Then restart the mysql
server:
systemctl restart mysql
# or
/etc/init.d/mysqld restart
-
pstump forgot to mention that you also need to shutdown mysql and copy all your data files to the new location before starting it up again.– symcbeanOct 5, 2015 at 12:23
-
Note that the official syntax of
chown
isuser:group
whileuser.group
is an undocumented feature. I've changed that. Mar 8, 2021 at 8:49
I found this step by step guide working for me.
You must install:
yum install policycoreutils-python
Guide:
View the SELinux context of the default database location for mysql:
~]# ls -lZ /var/lib/mysql
drwx------. mysql mysql system_u:object_r:mysqld_db_t:s0 mysql
This shows mysqld_db_t
which is the default context element for the location of database files. This context will have to be manually applied to the new database location that will be used in this example in order for it to function properly.
Stop the mysqld daemon:
~]# systemctl stop mariadb.service
Create a new directory for the new location of the database(s). In this example, /mysql/ is used:
~]# mkdir -p /mysql
Copy the database files from the old location to the new location:
~]# cp -R /var/lib/mysql/* /mysql/
Change the ownership of this location to allow access by the mysql user and group. This sets the traditional Unix permissions which SELinux will still observe:
~]# chown -R mysql:mysql /mysql
Run the following command to see the initial context of the new directory:
~]# ls -lZ /mysql
drwxr-xr-x. mysql mysql unconfined_u:object_r:usr_t:s0 mysql
The context usr_t of this newly created directory is not currently suitable to SELinux as a location for MariaDB database files. Once the context has been changed, MariaDB will be able to function properly in this area.
Open the main MariaDB configuration file /etc/my.cnf
with a text editor and modify the datadir
option so that it refers to the new location. In this example the value that should be entered is /mysql
:
[mysqld]
datadir=/mysql
Save this file and exit.
Start mysqld
. The service should fail to start, and a denial message will be logged to the /var/log/messages
file:
~]# systemctl start mariadb.service
Job for mariadb.service failed. See 'systemctl status postgresql.service' and 'journalctl -xn' for details.
However, if the audit daemon is running and with him the setroubleshoot
service, the denial will be logged to the /var/log/audit/audit.log
file instead:
SELinux is preventing `/usr/libexec/mysqld` "write" access on /mysql. For complete SELinux messages. run `sealert -l b3f01aff-7fa6-4ebe-ad46-abaef6f8ad71`
The reason for this denial is that mysql is not labelled correctly for MariaDB data files. SELinux is stopping MariaDB from having access to the content labelled as usr_t
. Perform the following steps to resolve this problem:
Run the following command to add a context mapping for mysql. Note that the semanageutility
is not installed by default. If it missing on your system, install the policycoreutils-python
package.
~]# semanage fcontext -a -t mysqld_db_t "/mysql(/.*)?"
This mapping is written to the /etc/selinux/targeted/contexts/files/file_contexts.local
file:
~]# grep -i mysql /etc/selinux/targeted/contexts/files/file_contexts.local
/mysql(/.*)? system_u:object_r:mysqld_db_t:s0
Now use the restorecon
utility to apply this context mapping to the running system:
~]# restorecon -R -v /mysql
Now that the mysql location has been labelled with the correct context for MariaDB, mysqld starts:
~]# systemctl start mariadb.service
Confirm the context has changed for mysql:
~]$ ls -lZ /mysql
drwxr-xr-x. mysql mysql system_u:object_r:mysqld_db_t:s0 mysql
The location has been changed and labelled, and mysqld
has started successfully. At this point all running services should be tested to confirm normal operation.
-
This is by far the best explanation for this problem I have encountered so far! Mar 16, 2017 at 9:32
Stopping the Default Install/Instance
service mysqld stop
Clear Current Config
rm /etc/my.cnf
Uninstal the Default Install/Instance
yum remove mysql mysql-server -y
Clear Current Datadir
test -d /var/lib/mysql/ && rm -rf /var/lib/mysql/
Clear the 'New' Datadir
test -d /mysql/mysql/ && rm -rf /mysql/mysql/
Install it again
yum install mysql mysql-server -y
Check the service status
service mysqld status
Start it - just to create a first/default structure
service mysqld start
Check the service status
service mysqld status
Interrupt the current MySQL server installation
service mysqld stop
Ensure that you don´t have anymore instance/service running
ps axu | grep mysql
Move the mysql data directory to '/mysql' partition and create the symbolic link
test -d /var/lib/mysql/ && mv /var/lib/mysql/ /mysql/ && ln -s /mysql/mysql /var/lib/
Check symbolic link and the real path
ls -lrth /var/lib/ | grep mysql
Set permission on new Datadir
chown -R mysql:mysql /mysql/mysql
Start it
service mysqld start
Try to connect (keep in mind that the default install of MySQL doesn´t set a 'pwd' for 'root' user and then you should connect with 'blank password'
mysql -u root -p --host 127.0.0.1
Once connected to MySQL, create a new db just to test if it´s working and where MySQL will create folder/file structure
create database DBTesteNew;
exit
Check if the new db is on the 'new datadir'
ls /mysql/mysql
Make sure the mysqld is set to start on boot time
chkconfig mysqld on
restart
reboot
-
Link - only answers are frowned upon. Please provide content here and link elsewhere for details if needed.– EEAAOct 4, 2014 at 2:12
-
Getting there....perhaps try another whack at formatting things properly?– EEAAOct 4, 2014 at 2:22
-
Hangs and will not start if I change the data-dir with the permissions on the dir correct. Change it back to its default dir, and it starts fine. Try using a symlink in /var/lib pointing to the "real data folder" and will not start. Try copying its default data-dir (created on install and works in /var/lib) to the new location - either pointing to it with config, or leaving default config and symlinking - will not start. I do not want my data in /var in the system partition - so how to change the location and get it to start? Uninstalled/reinstalled at least 6 times now.– JosephKFeb 10, 2016 at 4:58
/etc/systemd/system/mariadb.service.d/homedir.conf
file to allow the DBs stored in the /home directory. mariadb.com/kb/en/library/what-to-do-if-mariadb-doesnt-start/…