NYCPHP Meetup

[Matt Juszczak]

Just seeing the original thread now.

Randal, when you just randomly "downloaded the files", did you lock the 
database first?  Especially with InnoDB, that'll cause some major issues 
if you don't.

If you can take another backup, login to the old server first, run "flush 
tables with read lock", keep your session open, and then re-download the 
files.  Once the download is complete, you can run "unlock tables".

Another easy solution to take a backup, especially if the database 
instance isn't that large:

mysqldump -u<username> -p<password> --opt --all-databases | gzip > 
backup.sql.gz

That'll get you a logical snapshot of the database, which will be much 
easier to restore on a new database server with perhaps different 
configuration.

On Wed, 7 Jul 2010, Randal Rust wrote:

> This is primarily a MySQL-related question, but I figure someone here
> probably has an answer, or can send me in the right direction.
>
> About 10:00 yesterday I got an email from a client, saying they could
> not log into their CMS. I checked and found that something was very
> wrong with the database. Tech support at the hosting company told me
> that it looked like the drive on the server was going bad, and that I
> would need to restore from backups. I did not have a recent backup, so
> I immediately began downloading files, including the MySQL data files
> (.frm, .myi, .myb).
>
> The hosting company has set up a new server and I am going through the
> process of rebuilding everything. The biggest issue that I've run into
> is with MySQL tables that were set up as InnoDB. MyISAM tables seem to
> be just fine.
>
> When I bring up the database in phpMyAdmin, I can see all of the
> tables, but there are two separate issues with the InnoDB tables.
>
> 1. Some of them are listed as 'in use.' When I try to view the
> structure of the table, I get the following error message:
>
> #1033 - Incorrect information in file: './ahc_db/cd_news_categories.frm'
>
> 2. The rest of the InnoDB tables are apparently 'read only.' This is
> preventing me from repairing them.
>
> Any suggestions?
>
> -- 
> Randal Rust
> R.Squared Communications
> www.r2communications.com
> www.facebook.com/r2communications
> 614-370-0036
> _______________________________________________
> New York PHP Users Group Community Talk Mailing List
> http://lists.nyphp.org/mailman/listinfo/talk
>
> http://www.nyphp.org/Show-Participation
>