badtimbo
08-15-2007, 01:54 AM
I am Using:
PHP 5
Windows XP Professional
Quick 'n Easy Web Server
MySQL 5.0
A table in our database got corrupted and it was keeping our site whole site from being able to access the database. Thankfully, however, the table ended up being one we don't even use so my co worker just deleted the physical MySQL file for that table out of the MySQL folder for that table. (on our computer that's C:\MySQL\MySQL Server 5.0\Data\dr_database). That fixed the problem, however it gave us a big scare so my co worker decided now was the time to find out how to back up our dr_database. She is pretty experienced with MySQL but she had never done this before so she decided to make a database named dr_database_test to test her backup import procedures on to make sure she could do it right. Last thing we needed was a false sense of security from a database back up that wouldn't even work. It would not be so nice to have the database totally crash and find out our backup isn't any good!!
In one of her tests she did, to make sure she was getting the hang of this, she exported merely the database structure for the dr_database. However in making the export she forgot to uncheck the box that adds a CREATE DATABASE and DROP IF EXISTS to the export. So when she got on the dr_database_test and imported the export file it dropped the dr_database and re added it which means all its millions of rows of data was GONE!
However, I remembered that earlier that day, right after that table in the database got corrupted I browsed to the MySQL folder with the physical files to the database and copied them over to our backup drive. This included the db.opt file. In remembering this I browsed to the now empty C:\MySQL\MySQL Server 5.0\Data\dr_database folder (except for the new db.opt file), deleted the db.opt file, and pasted everything in. Then I deleted the files for that corrupted table.
I opened up SQLyog Enterprise (similar to phpMyAdmin except it is stand alone: it does not run through a browser) and sifted through the dr_database. All the data was back in the tables. Great! But then MySQL crashed so badly that it shut off and I could not get it to come back on even after restarting the computer. I went back a few days to a Windows System Restore Point and MySQL would work fine. When I pasted the database files in the dr_database folder, as described above, it would still keep running. However the moment anything would query the database, whether it was opening up our site which queries the database on every page, or querying it through SQL yog it would shut off and the only way to get it to start again would be rebooting the computer.
Do I need to disconnect the database and flush the privileges in the command line? Just a thought
PHP 5
Windows XP Professional
Quick 'n Easy Web Server
MySQL 5.0
A table in our database got corrupted and it was keeping our site whole site from being able to access the database. Thankfully, however, the table ended up being one we don't even use so my co worker just deleted the physical MySQL file for that table out of the MySQL folder for that table. (on our computer that's C:\MySQL\MySQL Server 5.0\Data\dr_database). That fixed the problem, however it gave us a big scare so my co worker decided now was the time to find out how to back up our dr_database. She is pretty experienced with MySQL but she had never done this before so she decided to make a database named dr_database_test to test her backup import procedures on to make sure she could do it right. Last thing we needed was a false sense of security from a database back up that wouldn't even work. It would not be so nice to have the database totally crash and find out our backup isn't any good!!
In one of her tests she did, to make sure she was getting the hang of this, she exported merely the database structure for the dr_database. However in making the export she forgot to uncheck the box that adds a CREATE DATABASE and DROP IF EXISTS to the export. So when she got on the dr_database_test and imported the export file it dropped the dr_database and re added it which means all its millions of rows of data was GONE!
However, I remembered that earlier that day, right after that table in the database got corrupted I browsed to the MySQL folder with the physical files to the database and copied them over to our backup drive. This included the db.opt file. In remembering this I browsed to the now empty C:\MySQL\MySQL Server 5.0\Data\dr_database folder (except for the new db.opt file), deleted the db.opt file, and pasted everything in. Then I deleted the files for that corrupted table.
I opened up SQLyog Enterprise (similar to phpMyAdmin except it is stand alone: it does not run through a browser) and sifted through the dr_database. All the data was back in the tables. Great! But then MySQL crashed so badly that it shut off and I could not get it to come back on even after restarting the computer. I went back a few days to a Windows System Restore Point and MySQL would work fine. When I pasted the database files in the dr_database folder, as described above, it would still keep running. However the moment anything would query the database, whether it was opening up our site which queries the database on every page, or querying it through SQL yog it would shut off and the only way to get it to start again would be rebooting the computer.
Do I need to disconnect the database and flush the privileges in the command line? Just a thought