Skip to main content

Mysql Database Moving Data Directory to New Location.

How to move an existing data directory in mysql to a new location. We were running out of space and had to move the existing data directory to a new drive. Below are the steps to move the data directory to new location.
Stop mysql using the following command:
sudo service mysqld stop
Copy the existing data directory (default located in /var/lib/mysql) using the following command:
sudo mkdir /db 
sudo cp -R -p /var/lib/mysql /db
Update the mysql configuration file with new path /db/mysql the following command:
sudo vim /etc/my.cnf
Here is how the configuration looks like.
[ahmed@localhost mysql]$ cat /etc/my.cnf 
[mysqld]
datadir=/db/mysql
socket=/db/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[ahmed@localhost mysql]$
Look for the entry for datadir, and change the path (which should be /var/lib/mysql) to the new data /db/mysql directory.
In the terminal, enter the command:
sudo vim /etc/rc.d/init.d/mysqld
Look for lines beginning with /var/lib/mysql. Change /var/lib/mysql in the lines with the new path /db/mysql. Save and close the file.
51 get_mysql_option mysqld datadir "/db/mysql"
52 datadir="$result"
53 echo $datadir
54 get_mysql_option mysqld socket "$datadir/mysql.sock"
55 socketfile="$result"
56 get_mysql_option mysqld_safe log-error "/var/log/mysqld.log"
57 errlogfile="$result"
58 get_mysql_option mysqld_safe pid-file "/var/run/mysqld/mysqld.pid"
59 mypidfile="$result"
Move the existing mysql directory 
sudo mv /var/lib/mysql /var/lib/mysql_old
Create a symbolic link to the new path.
cd /var/lib
sudo ln -s /db/mysql mysql
Restart mysql with the command:
sudo service mysqld restart
Now login to mysql and you can access the same databases you had before.
​
Labels:

Comments

Post a Comment

Popular posts from this blog

Zabbix History Table Clean Up

Zabbix history table gets really big, and if you are in a situation where you want to clean it up. Then we can do so, using the below steps. Stop zabbix server. Take table backup - just in case. Create a temporary table. Update the temporary table with data required, upto a specific date using epoch . Move old table to a different table name. Move updated (new temporary) table to original table which needs to be cleaned-up. Drop the old table. (Optional) Restart Zabbix Since this is not offical procedure, but it has worked for me so use it at your own risk. Here is another post which will help is reducing the size of history tables - http://zabbixzone.com/zabbix/history-and-trends/ Zabbix Version : Zabbix v2.4 Make sure MySql 5.1 is set with InnoDB as innodb_file_per_table=ON Step 1 Stop the Zabbix server sudo service zabbix-server stop Script. echo "------------------------------------------" echo " 1. Stopping Zabbix Server ...
1 comment
Read more

Installing Zabbix Version 2.4 Offline (Zabbix Server without Internet).

There might be situations where you have a remote/zabbix server which does not have internet connectivity, due to security or other reasons. So we create a custom repo on the remote/zabbix server so that we can install zabbix using rpms Here is how we are planning to do this. Download all the dependency rpms on a machine which has internet connection, using yum-downloadonly or repotrack . Transfer all the rpms to the remote server. Create a repo on the remote server. Update yum configuration. Install. NOTE: This method can be used to install any application, but here we have used zabbix as we had this requirement for a zabbix server. Download dependent rpms . On a machine which has internet connection install the package below. And download all the rpms . Make sure the system are similar (not required to be identical - At-least the OS should be of same version) mkdir /zabbix_rpms yum install yum-downloadonly Downloading all the rpms to location /zabbix_rpms/ ,...
1 comment
Read more

Access Filter in SSSD `ldap_access_filter` [SSSD Access denied / Permission denied ]

Access Filter Setup with SSSD ldap_access_filter (string) If using access_provider = ldap , this option is mandatory. It specifies an LDAP search filter criteria that must be met for the user to be granted access on this host. If access_provider = ldap and this option is not set, it will result in all users being denied access. Use access_provider = allow to change this default behaviour. Example: access_provider = ldap ldap_access_filter = memberOf=cn=allowed_user_groups,ou=Groups,dc=example,dc=com Prerequisites yum install sssd Single LDAP Group Under domain/default in /etc/sssd/sssd.conf add: access_provider = ldap ldap_access_filter = memberOf=cn=Group Name,ou=Groups,dc=example,dc=com Multiple LDAP Groups Under domain/default in /etc/sssd/sssd.conf add: access_provider = ldap ldap_access_filter = (|(memberOf=cn=System Adminstrators,ou=Groups,dc=example,dc=com)(memberOf=cn=Database Users,ou=Groups,dc=example,dc=com)) ldap_access_filter accepts standa...
1 comment
Read more