Mysql database back up using source directory

Mysql database recovery using source directory

If you do not have .sql file of your mysql database, it doesn't mean you can not deploy the database on the server. If you have source directory of mysql database, it is quite fine to recover database.

Here is the path of source directory of mysql databases :
If you are using xampp, the path is /opt/lampp/var/mysql
If you are using default mysql, the path is /var/lib/mysql

In source directory, it saves the data as .frm files.

If phpmyadmin is not opened because of corrupt mysql packages (re-installation is not possible) and command line mysql is not working, now it looks like your all databases are gone but if source directory is fine, every thing is fine.

Keep back up of this source directory.

Now if you want to shift the databases on other system, just copy source directories of databases and put into mysql source directory of other system. Now you will try to see these databases in phpmyadmin or using command line but it doesn't show any data.

Here is the most important part, it does not show data because of wrong owner and permission. You need to set it correct.

# sudo chown -R mysql:mysql /opt/lampp/var/mysql/new-database-dir

Now set owner and group both mysql for this new directory.

# sudo chown -R mysql:mysql /var/lib/mysql/new-database-dir

(according to your mysql)

Set the permission 755(maximum open) for the new directory, you can set little less according to security of your network.

# sudo chmod -R 755 /opt/lampp/var/mysql/new-database-dir

or

# sudo chmod -R 755 /var/lib/mysql/new-database-dir

(according to your mysql) 

Note : This is one of the efficient way to deploy mysql database without using .sql file. Just put this mysql database directory(containing .frm files) in source directory of mysql, set the proper owner and permission and your mysql database is deployed.