Home / server / Questions / 128700
Accepted
RainDoctor
Asked: 2010-04-02 22:24:53 +0800 CST

restoring myisam mysql db on a different machine

  • 772

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?

mysql

3 Answers

  1. Best Answer

    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.

    • 3

  2. 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.

    • 2

  3. 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
    • 1