0

Ubuntu Server running MySql server.

We have 5 databases on our server. On a recent crash, the server was restored incorrectly and now 1 database (B) has 2 incorrect tables and a 2nd database (A) is completely incorrect.

How do I correctly replace database files?

Is it as simple as 

cp -a /path/to/backup/databaseA/ /var/lib/mysql/databaseA
cp -a /path/to/backup/databaseB/table1.MYD /var/lib/mysql/databaseB
cp -a /path/to/backup/databaseB/table1.MYI /var/lib/mysql/databaseB
cp -a /path/to/backup/databaseB/table2.ibd /var/lib/mysql/databaseB

And for what it is worth, there are mixed MyISAM and InnoDB tables.

Shane Madden
  • 112,982
  • 12
  • 174
  • 248
csi
  • 1,535
  • 7
  • 22
  • 42

1 Answers1

1

With MyISAM - no problem (except metadata), regarding InnoDB: https://dba.stackexchange.com/questions/15531/why-does-innodb-store-all-databases-in-one-file

I think your problem is in innodb_file_per_table - was this option enabled before tables were created on both A & B or not.

If innodb_file_per_table was enabled later - you will have mixed tablespace: some data will be saved in .ibd and some in ibdata1, metadata always stays in ibdata1.

Also, remember that not only database files provide information regarding tables and permissions etc (metadata) - mysql DB information tables and ibdata1 do. So, copying is useful only when there is no change or you don't care about that metadata.

Short answer - no (you need ibdata1 too). it's not that easy, but in some circumstances it might work anyway.

If this isn't answer you are looking for - this information might be very useful for you anyway.

Recovery: you need ibdata1 too, but it will affect other DB's with innodb too.

Community
  • 1
GioMac
  • 4,444
  • 3
  • 24
  • 41
  • Upon checking all Innodb tables are ok. For MyISAM, can a mysql stop followed by copying MYD & MYI files work? Are there other considerations? – csi Sep 22 '13 at 17:26
  • 1
    If you stop mysql, then copy files and then start again - it will work. – GioMac Sep 22 '13 at 17:28