If you only see the .frm files, then there is a strong likelihood that the storage engine in use was InnoDB and innodb_file_per_table must have been off by default.
If you transferred everything from datadir on the crashed server onto another disk on another machine, you may be able to startup mysql with that folder as is.
For example, suppose ServerA is your crashed server and ServerB is where you want it placed.
- Install MySQL 5.5.8 on ServerB
net stop mysql
on ServerB to make sure mysql is down on ServerB
md C:\MySQLData
on ServerB
- Drop everything from the data folder on ServerA into C:\MySQLData on ServerB
- Make C:\MySQLData the new datadir
Add this to my.ini on ServerB
[mysql]
datadir=C:/MySQLData
C:\> del C:\MySQLData\ib_logfile*
on ServerB
net start mysql
on ServerB
Please try this and tell us what happened
UPDATE 2012-02-03 17:06 EDT
Since you were able to recover everything NOW DO THIS:
mysqldump -u... -p... -A -d --routines --triggers > C:\MySQLSchema.sql
This will give all table structures in the MySQL Instance.
Yes, it will overwrite the data on the Slave. However, you can setup replication to pick up from the point-in-time of the dump and make it roll all changes since the dump was loaded on the Slave
For this Example, let's assume
- IP of the Master is 10.1.1.20
- IP of the Slave is 10.1.1.30
Here is what you do
STEP01 : Activate Binary Logging on the Old Server
Step01-a) Add this to /etc/my.cnf on the Master
[mysqld]
server-id=101120
log-bin=mysql-bin
Step01-b) # service mysql restart
After STEP01, you should see mysql-bin.000001 and mysql-bin.index in /var/lib/mysql
STEP02 : Perform mysqldump
On the Master, you can mysqldump the data and record from what point in time it happened.
# service mysql restart --skip-networking --skip-grants
# mysqldump --single-transaction --master-data=2 --all-databases --routines --triggers > MySQLData.sql
# service mysql restart
What this does is record the Master Log File and Position the moment the mysqldump started as a comment. You can visibly see it when you view line 22:
# head -22 MySQLData.sql | tail -1
STEP03 : Load the mysqldump into the Slave
Execute the mysql client loading the mysqldump into the Slave's mysql instance
# mysql -h10.1.1.30 -uroot -p < MySQLData.sql
STEP04 : Create MySQL Replication User on the Master
# mysql -uroot -p -e"GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replicator'@'10.64.51.%' IDENTIFIED BY 'replpassword'"
STEP05 : Setup the Slave with a Separate Server ID
Add this to /etc/my.cnf on the Slave
[mysqld]
server-id=101130
and # service mysql restart
STEP06 : Setup the Replication on the Slave
Goto the mysql client and run the following command
mysql> CHANGE MASTER TO
MASTER_HOST='10.1.1.20',
MASTER_PORT=3306,
MASTER_USER='replicator',
MASTER_PASSWORD='replpassword',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=1;
STEP07 : Setup the point-in-time Master Log and Position
Back in STEP02, I mentioned viewing the point-in-time position using
# head -22 MySQLData.sql | tail -1
You should see something like this:
#CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000015', MASTER_LOG_POS=122957100;
Run it as command in the mysql client on the Slave
mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000015', MASTER_LOG_POS=122957100;
Afterwards, run this command
mysql> SHOW SLAVE STATUS\G
You should see something like this:
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.64.113.232
Master_User: replicant
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000015
Read_Master_Log_Pos: 122957100
Relay_Log_File: relay-bin.003666
Relay_Log_Pos: 122957100
Relay_Master_Log_File: mysql-bin.000015
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 106
Relay_Log_Space: 106
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)
STEP08 : Launch Replication
Start up replication with this:
mysql> START SLAVE;
Afterwards, run this command again
mysql> SHOW SLAVE STATUS\G
If you see this
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
CONGRATULATIONS, MySQL Replication is Working !!!
Best Answer
I was left wondering because you mentioned SSH which implied connecting from your database server to a different machine (free tip: You use "SSH" to connect to a machine, but the things you type after that are "shell commands", not "SSH commands." I googled that phrase just now and was horrified at how frequently they're improperly called "SSH commands") ... and in Sqlbot's world, a "slave" is a different machine -- a replication slave -- which also was initially confusing.
You mentioned
MYD
andMYI
files but notIBD
files, so either you are using MyISAM and not InnoDB, or you're using a mix but you never setinnodb_file_per_table = 1
.Your path to recovery depends on whether you are using InnoDB.
If you're not using
InnoDB
, then you can -- believe it or not -- just copy the old database directories and files into place and fix the permissions if needed. MySQL is extremely forgiving about this withMyISAM
tables.Connect to your MySQL server and locate the data directory. From an old CentOS machine of mine (probably still the same for you):
Find the comparable directory on your old hard drive, and copy all of the directories -- but not the files, and not the "mysql" directory -- into your datadir (probably /var/lib/mysql).
Then, if the files aren't owned by the "mysql" user,
Then see if it worked.
If you are using
MyISAM
only, your databases and tables should be on the new server, though you will need to doCHECK TABLES
on all of your tables or use themysqlcheck
utility to verify that they are all intact and repair them as needed.If you're using
InnoDB
, the steps will be different, so see if this works for you.