Monday, August 12, 2013

mysql not starting up after a change in configuration file

you have just made a change in the configuration file and trying to restart mysql but terminal is showing "failed to start". Now, if you will try to run the mysql console then it will show you the error regarding "can't find the socket" and this is obvious because the mysqld is not running in the back ground.

To know what's happening with your mysql you can go to "mysql-error.log" that is residing mainly in the data-directory of your mysql-server.

suppose you've encountered the error as follows:

InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 134217728 bytes!

what it is trying to say is that "you have changed a new variable but the file corresponding to that variable in the data directory is not meeting to the specified requirement because it is somewhat smaller in size than that of the one defined in the configuration file".
now you've got the problem and thinking how to increase the size of that file that is in the data-directory then there is no need to do that because ib_logfile0 is the system generated file that would be generated automatically when you start the mysql. So, what you have to do is remove the file that is being generated previously and try to start the mysql.

still not starting!!!

now you have to go to the mysql-error.log and i know what is creating the problem:

InnoDB: Error: log file ./ib_logfile1 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 134217728 bytes!

this is the same file as that of the previous one and what you have to do is to delete it as you'd done before.

now try to restart mysql it will work.

now go to the data-directory and you will find the ib_logfile0 and
ib_logfile1 there.

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