Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I recently had a problem with our server. It required us to install a new hard drive, cpanel, and setup the old hard drive as a slave. How can I migrate the mysql database from the slave drive to the master drive? The slave contains the tables with MYD, MYI, and frm files. The database is probably about 20G.

I'm assuming SSH is the best way to do this. I am not very familiar with SSH commands and cannot find out how to do this. I've already setup the new database (on the fresh install on the master drive) with the same name, users, and passwords.

System: Linux, Centos 6.4

share|improve this question

migrated from stackoverflow.com Jul 7 '13 at 23:26

This question came from our site for professional and enthusiast programmers.

    
What does "setup the old hard drive as a slave" mean, exactly? –  Michael - sqlbot Jul 8 '13 at 2:04
    
The slave was originally the master drive. It became somewhat corrupted. A new master drive was installed and the slave (original master drive) was added to the system so files can still be access and transferred to the new master drive. The OS, cpanel, etc were installed fresh on the new master drive. –  Brandon Orth Jul 8 '13 at 21:05

1 Answer 1

up vote 0 down vote accepted

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 and MYI files but not IBD files, so either you are using MyISAM and not InnoDB, or you're using a mix but you never set innodb_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 with MyISAM tables.

Connect to your MySQL server and locate the data directory. From an old CentOS machine of mine (probably still the same for you):

mysql> show variables like 'datadir';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.00 sec)

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,

root@host# chown -R mysql:mysql /var/lib/mysql

Then see if it worked.

mysql> SHOW DATABASES;

If you are using MyISAM only, your databases and tables should be on the new server, though you will need to do CHECK TABLES on all of your tables or use the mysqlcheck 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.

share|improve this answer
    
Thank you Michael. I ended up copying the files over and changing the ownership to the account. Seemed to work. I appreciate the response. –  Brandon Orth Jul 9 '13 at 17:43

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.