Mysql – Restore dropped thesql database

MySQLmysqldumprestore

I create a database each year from the previous year, back up and then clean up the tables. So I have databases for the past 3 years.

This year I did not do a backup and accidentally dropped the database of 2011. The schema is always the same for all the databases, but of course the entries are different for each year.

After dropping the database of 2011, the folder no longer exists in C:\Program Files\MySQL\MySQL Server 5.0\data, but I see ib_logfile0 and ibdata1 files under the above path. Is there any way I can restore the dropped database?

There was no backup done for 2011 and I do not know what is binary logging.

Best Answer

You can try and use data recovery software to recover the blocks as odds are the blocks haven't been overwritten yet. If they have been overwritten, then the data is gone.

I'm sorry to say, but you've just learned why backups are important.

Why not to include mysql ???

When you mysqldump the mysql schema, you should make yourself aware of differences in MySQL versions, particularly the mysql.user table.

In MySQL 5.0, mysql.user has 37 columns
In MySQL 5.1, mysql.user has 39 columns
In MySQL 5.5, mysql.user has 42 columns

Restoring a mysqldump from one version can result in certain privileges disappearing when restored :

Make sure you handle dumping user grants as a special script. There are two methods for this:

METHOD #1 : Use pt-show-grants

This Percona Toolkit program move print out the User Permission in Pure SQL. You could run the result output into a Text File. Then, execute the Text File in MySQL 5.5.24. End of Story.

pt-show-grants ... > MySQLUserGrants.sql

METHOD #2 : Emulate pt-show-grants

I made my own technique for pt-show-grants

mysql -hhostaddr -umyuserid -pmypassword --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql -hhostaddr -umyuserid -pmypassword --skip-column-names -A | sed 's/$/;/g' > MySQLUserGrants.sql

I have discussed this before

Apr 09, 2012 : Mysql users deleted
Apr 12, 2012 : Cannot GRANT privileges as root
Jun 13, 2012 : Fastest way to move a database from one server to another

Why include mysql ???

The only case for which you can mysqldump the mysql schema is to restore it to the same version of mysql

UPDATE 2014-12-30 17:42 EST

My original answer covered just the MySQL Grants since this particular aspect of the mysql schema is the easiest to break and overlook.

There are other reasons not to just backup the mysql schema unless it is necessary

If you want to retain the timestamps for all Stored Procedures, you can mysqldump the mysql.proc table by itself and load it. You can also just copy the proc.frm, proc.MYD, and proc.MYI then reload it by copying it back into the mysql subsfolder and runninf FLUSH TABLES;
If you use table based logging for the general log (mysql.general_log) and slow log (mysql.slow_log), you do not want to backup these logs and move them to another server where the logs do not semantically apply. It is your choice if you want to restore them.
If you set any timezone info in the mysql, be very careful if you do not want to restore the mysql schema in a data center that is in another timezone.

If you want to be careful about what you want to backup and restore in the mysql schema, you should mysqldump the mysql schema in a separate file.

The number of tables in the mysql schema, as well as the number of columns, will vary from version to version. So, you should login to mysql and run

SHOW TABLES FROM mysql;

The list of tables may not include events (like MySQL 5.0), may not have InnoDB system tables (like MySQL 5.6 has), may not include tablespace grants (like MySQL 5.6), and so forth. Sp, if you feel you must backup the mysql schema, you be very conservative (ok very picky and careful) about what tables you truly need to backup from the mysql schema.

Mysql – Restore InnoDB tables / database using only data files

Unfortunately, there is really not an easy way to get this table back. Although the data is likely still in the ibdata1 file, only specialized tools can recover it. If this data is business critical, some consulting companies such as Percona can help you to recover it, but that will be quite expensive (thousands of dollars at least), and not all of the data may be recoverable.

If this data is important to you enough to spend the money or time recovering it, save a copy of the ibdata1 file immediately as the disk space will be more likely to be re-used the longer the server is running.