Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have copied the web db directory(/var/lib/mysql/data/web/), when mysql is not running. I transferred this directory to another machine, where mysql is running.

web db's stuff is myisam based.

I am thinking about how to restore this on a different server. The strategy I have on my mind is the following.

  1. copy the data directory of web db to the new server.
  2. on new server, 'create database web', which creates a directory in /var/lib/mysql/data
  3. copy all files from the step to the new directory created in 2.
  4. bounce mysql

My question: how to deal with information_schema for this new db?

share|improve this question
    
Are you running the same version of mysqld on both servers? Any reason doing a mysqldump isn't an option? – andol Apr 2 '10 at 6:30
    
800GB database, thats why. Since I have a copy of that directory, i am trying to find an easy way. – RainDoctor Apr 2 '10 at 8:09
up vote 2 down vote accepted

Six years after this question was asked, I had to copy an 80 million row MYISAM table to a backup database (it was using 80% of the disk space of our whole database).

I took the .MYI, .MYD, and .FRM files from the nightly backup, and copied them to the folder of a new running database. The information_schema was updated almost immediately, and all table operations worked as expected (ie the indexes and stuff were correct).
I'll run CHECK TABLE and OPTIMISE TABLE on them before sending them into a quiet retirement, but this backup method was painless.

share|improve this answer

information_schema is the information database where the information about all the other databases is kept. It is a built-in virtual database with the sole purpose of providing information about the database system itself. The MySQL server automatically populates the tables in the information_schema.

reference: http://kb.siteground.com/article/What_is_the_information_schema_database.html

So if its myisam tables only, you can copy / move database around as long as the mysql versions are same.

share|improve this answer

on your source machine:

mysqldump -q -u username -p database > database.sql

transfer database.sql to the destination machine and do:

mysql -u username -p database < database.sql
share|improve this answer
    
this is 800GB myisam db. So, I copied the directory of all these tables. sqldump is not an option – RainDoctor Apr 2 '10 at 8:06

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.