Monday, August 12, 2013

mysql data-loss when re-installed

There is a possibility when you came to a situation when your MySql-server is showing you problem when you are trying  to start it.
In the end what you have to do is to remove the mysql completely and re-install it.Yes! thats the easiest way to get back your mysql in a running state but what about your database that was there in the previous mysql "flushed out" you can't take a dump of your database because your mysql is not running so there is no way you can run mysqldump to take dump of your data.
to avoid this situation what you can do is:
Daily dumping of data
you have to dump your data on daily basis so that in the end you'll have fully updated data. and you can say "yes i can install a new mysql server without any hitch". But what about last hour data, you cannot take dump of that data because your mysql-server just crashed today and you are only having data of the last night now this is going to be a headache.but there is another method to get rid of this situation
copying the mysql data directory
now, you want to take the full backup of your updated data you dont need to take dump of your database daily nor you have to run a data-recovery have to just copy the data that is in your mysq data-directory i.e /var/lib/mysql/"DATA" (here you will find various files like .frm,.ibd etc these are the files that keeps a record of metadata and other useful info of the tables) into a preferred location and then remove the previous mysql now before installing the new copy of the same you have to copy the same data into that mysql data-directory (make sure that you have not removed the data directory by sudo rm -rf /var/lib/mysql). now, install a fresh copy of mysql and run mtysql. run "show databases" and you'll get to know all the databases are there as if nothing happened to your old mysql-server.
NOTE:this information is only for the person who is using mysql for practice, on servers there are various backup tools running to take the complete and incremental backups.
->try to restart the mysql to your level best only after that follow this method.
go ahead give it a try

No comments:

Post a Comment