?
Solved

How to move MySQL data to new installation

Posted on 2009-02-18
22
Medium Priority
?
2,146 Views
Last Modified: 2012-05-06
I am trying to follow the instructions regarding "How to recover MySQL data from Innodb...etc" at: http://www.buildcms.com/cms_knowledgebase/how_to_recover_mysql_data_from_innodb
which are written for linux and I am using Windows XP. The first step says:

Restore data into the normal MySQL data directory. I have this folder:
 - C:\Program Files\MySQL\MySQL Server 5.1
which contains a bin folder and a share folder.

So, where do I put:
1. My ibdata1 file (which I was told is the Innodb data file)
2. The ib_logfile0 and ib_logfile1 files
3. The folder "KioskActivity" which I think is the production folder aka /databasename/*.frm (and other files) that was located on the good (old PC) at:
 - C:\Program Files\MySQL\MySQL Server 5.0\KioskActivity

Note: the article referenced above has several steps that I plan to ask for help on with separate Expert Exch questions and pay the max 500 points each step. These are probably easy points for some of you experts out there :)

Here's the article:
How to recover MySQL data from Innodb ?
 
Unfortunately some times you need to recover MySQL data from ibdata1. It's many reasons why your getting corrupted Innodb files that cannot automatically be restored of the mysqld daemon. 
 
Solution:
Scenario
We assume that your scenario are like following: 
 
You got backup of your ibdata1, ib_logfile0 and ib_logfile1 
You also got backup of your database folder with .frm files 
You would like to restore this backup into an MySQL server that's already in production. 
Before we start, just one advice: Stop crying, your data isn't lost. 
 
Restore the data backup you got
First of all restore you data on another MySQL server, to not interrupt the services running in your normal production environment. Restore data into the normal MySQL data directory. For our scenario we use /var/lib/mysql. 
 
Be careful to get the right permissions and owners on all your data files. Your MySQL users should own the files and the group should also be assigned to MySQL. 
 
Before you continue you need to find the size of your Innodb logfiles. Simply run the command ls -l to find this. This will output something like this: 
 
-rw-rw---- 1 mysql mysql 5242880 Jun 25 11:30 ib_logfile0
-rw-rw---- 1 mysql mysql 5242880 Jun 25 11:30 ib_logfile1Start up MySQL in rescue mode
Some simple steps will start up the MySQL daemon in rescue mode for you: 
From your Unix shell su into the mysql user: su mysql 
Start up your mysqld process with the logfile size and innodb_force_recovery as parameters. 
/usr/sbin/mysqld --innodb_log_file_size=5242880 --innodb_force_recovery=6
 
If everything goes fine you should get a output like this:
 
InnoDB: The user has set SRV_FORCE_NO_LOG_REDO on
InnoDB: Skipping log redo
070625 11:59:36 InnoDB: Started; log sequence number 0 0
InnoDB: !!! innodb_force_recovery is set to 6 !!!
070625 11:59:36 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.0.18' socket: '/var/lib/mysql/mysql.sock' port: 3306 SUSE MySQLGet your data
The last simple but most important step is now to get your data. 
 
Open a new shell to the server where your MySQL daemon are running in recovery mode. 
Simply run a normal mysqldump of your database: 
mysqldump -u root -p database > database.sql
 
If you get a message looking like this, you got corrupted Innodb log files:
 
Got error: 1146: Table 'database.table' doesn't exist when using LOCK TABLESWhat you can do to resolve this problem with keeping the ib_logfile0 file from you most current backup while you restore all the other files from a older backup. This isn't a fail-proof solution, but worth a try. 
 
Restore you data
Now you can copy your SQL dump to your production server and simply restore the data from your MySQL dump file like this:
 
mysql -u root -p database < database.sql

Open in new window

0
Comment
Question by:lee88
  • 10
  • 9
  • 3
22 Comments
 
LVL 11

Expert Comment

by:bansidhar
ID: 23678161
If you still have the old mysql server running and you just want to move the database no new server, you can use a simple method.
Use the following command in the old server. Replace <password> with your mysql root password.
mysqldump -uroot -p<password> --create-options --add-drop-database --all-databases > database.sql

This will generate a database.sql file in your current directory. Copy this to the new server and use the command
mysql -u root -p database < database.sql
and enter the root password at the prompt

If you just want to restore one database on the first step use the command
mysqldump -uroot -p<password> --create-options --add-drop-database yourdatabasename > database.sql

check mysqldump --help for more details on the options, it is a big list but will give you better idea on what it can do.
0
 
LVL 26

Expert Comment

by:Umesh
ID: 23678181
Pls can you post your MySQL's config file.. and few values

File size of these

1. My ibdata1 file (which I was told is the Innodb data file)
2. The ib_logfile0 and ib_logfile1 files
0
 
LVL 11

Expert Comment

by:bansidhar
ID: 23678196
also please note, in windows installation the Innodb data folder is set by the user when the configuration wizard is run.
0
[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

 

Author Comment

by:lee88
ID: 23680476
ushastry: Where is my MySQL's config file (or what is the default name)?
bansidhar: The old mysql server is not running.
0
 
LVL 26

Expert Comment

by:Umesh
ID: 23680486
Pls look for my.ini
0
 
LVL 11

Expert Comment

by:bansidhar
ID: 23681553
try running the configuration wizard (cannot tell you exactly about the screens as I don't have windows). There is a page with the Innodb path. Use that path to restore your files
0
 

Author Comment

by:lee88
ID: 23681773
Here's the old my.ini (attached. renamed to old_my_ini.txt)
old-my-ini.txt
0
 
LVL 26

Expert Comment

by:Umesh
ID: 23681806
thanks for my.ini

also file size of these

1. My ibdata1
2. The ib_logfile0 and ib_logfile1 files
0
 

Author Comment

by:lee88
ID: 23681908
As shown by windows explorer:
idbata1 is 10,240 KB
ib_logfile0 is 145,408 KB
ib_logfile1 is also 145,408 KB
0
 

Author Comment

by:lee88
ID: 23681922
I see this in the old Myini file:

#Path to installation directory. All paths are usually resolved relative to this.
basedir="C:/Program Files/MySQL/MySQL Server 5.0/"

#Path to the database root
datadir="C:/Program Files/MySQL/MySQL Server 5.0/Data/"

So, to be consistent, should I create a "Data" folder in the installation directory, and then copy these to tthat Data folder?

1. My ibdata1 file
2. The ib_logfile0 and ib_logfile1 files
3. The folder "KioskActivity" which I think is the production folder aka /databasename/*.frm (and other files)
0
 
LVL 26

Expert Comment

by:Umesh
ID: 23681957
Yes... copy these files to data dir...

once that is done (make sure MySQL is down while copying)...

now start MySQL server from command line



cd C:/Program Files/MySQL/MySQL Server 5.0/Data/

mysqld-nt --innodb_log_file_size=145408 --innodb_force_recovery=6

Keep this windows open and open new window and using mysqldump take dump..


0
 

Author Comment

by:lee88
ID: 23682571
OK. I stopped mysql, copied the files and folder to the data folder, and started mysql. At a CMD prompt,  I changed directories (CD) to the Data directory. I can enter the command:
mysqld-nt --innodb_log_file_size=145408 --innodb_force_recovery=6

But, please clarify "Keep this windows open and open new window and using mysqldump take dump."
1. keep that CMD window open. Right?
2. But also, I need to open a new CMD window and execute mysqldump? (which I see is an EXE in the bin folder)

I appreciate your help on this.
0
 
LVL 26

Expert Comment

by:Umesh
ID: 23682773
But, please clarify "Keep this windows open and open new window and using mysqldump take dump."
1. keep that CMD window open. Right?

Yes... this way MySQL is up and running...

2. But also, I need to open a new CMD window and execute mysqldump? (which I see is an EXE in the bin folder)

Open new command window and cd to mysql/bin

then take a complete dump

mysqldump -uroot -p --opt --triggers --routines --databases DB1 DB2 DB3 > /path/to/dumpfile.sql

Change DB1DB2 etc to actual database names..


0
 

Author Comment

by:lee88
ID: 23683045
when I type mysqld-nt --innodb_log_file_size=145408 --innodb_force_recovery=6
I get this message:
'mysqld-nt' is not recognized as internal or external command.

I do not see mysqld-nt.exe in the bin folder.
I do see mysqld.exe in the bin folder.
Is there supposed to be a space between mysqld and -nt ?
0
 
LVL 26

Expert Comment

by:Umesh
ID: 23683062
Ok.. in 5.1 mysqld-nt seems to be missing
try this..

mysqldt --innodb_log_file_size=145408 --innodb_force_recovery=6
0
 

Author Comment

by:lee88
ID: 23683222
when I type mysqldt --innodb_log_file_size=145408 --innodb_force_recovery=6
I get this message:
'mysqld-nt' is not recognized as internal or external command.

I do not see mysqldt.exe in the bin folder either.
I have attached a screenshot of my bin folder contents.

BIN-folder-contents.jpg
0
 
LVL 26

Expert Comment

by:Umesh
ID: 23683244
my bad... shud have been mysqld but I typed mysqldt.. M sorry

mysqld --innodb_log_file_size=145408 --innodb_force_recovery=6
0
 

Author Comment

by:lee88
ID: 23683541
not a prob. Again, I appreciate you help :)
So, the mysqld... command appears to be running in the first CMD window (no error, and did not come back to C> prompt yet).
And, I am in the 2nd CMD window, trying to enter this command:
mysqldump -uroot -p --opt --triggers --routines --databases DB1 DB2 DB3 > /path/to/dumpfile.sql
1. I replaced "DB1 DB2 DB3" with my single database name.
2. Regarding "/path/to/dumpfile.sql"
2a. are these supposed to be forward slashes? (paths usually ahve back-slashes?)
2b. can I specify any path I want?
2c. is the dumpfile.sql going to be used in another step?
0
 
LVL 26

Expert Comment

by:Umesh
ID: 23683603
2. Regarding "/path/to/dumpfile.sql"
2a. are these supposed to be forward slashes? (paths usually ahve back-slashes?)
2b. can I specify any path I want?
2c. is the dumpfile.sql going to be used in another step?

Give the path as you wish.. I just shown that.. you can use c:/mydump.sql or d:/somefolder/dumpfile.sql etc...

mysqldump -uroot -p --opt --triggers --routines --databases database_name > c:/dumpfile.sql

The above dump file would have your database data... once the dump is over.. you can start mysql without that --innodb_force_recovery=6 ...  and import the dump file.. this way you would have your database back..
0
 
LVL 26

Accepted Solution

by:
Umesh earned 2000 total points
ID: 23683643
Pls note that I'll be signing off now.. would be back tomorrow 10-11am IST time...

Once the dump is over.. make sure to import this o some other server and see all the data is intact or not... once you are sure that your crashed server data is recovered..

Just a note... --innodb_force_recovery=6 would allow you to select but not to write/modify the data so.. which is nothing but a safe_mode... so don't try to experiment on that..

Thanks,
Umesh
0
 

Author Comment

by:lee88
ID: 23683847
OK. When I run the mysqldump... command, I am prompted for a password.
1. If I enter the password (Password1) for the old database, it says:
"mysqldump: Got error: 1045: Access denied for user 'root'@'localhost' (using password: YES) when trying to connect"

2. If I enter the password (Password2) that I specified during the MySQL 5.1 installation, it says:
"mysqldump: Got error: 1049: Unknown database '{my database name}' when selecting the database"

Is there a "password" switch that needs to be associated with the single database that I am trying to recover for either the mysqld... command or the mysqldump... command?
OR, Do I need to change the password that was specified when MySQL 5.1 was installed?
OR, something else?
0
 
LVL 26

Expert Comment

by:Umesh
ID: 23689088
>>2. If I enter the password (Password2) that I specified during the MySQL 5.1 installation, it says:
"mysqldump: Got error: 1049: Unknown database '{my database name}' when selecting the database"

Is there a "password" switch that needs to be associated with the single database that I am trying to recover for either the mysqld... command or the mysqldump... command?
OR, Do I need to change the password that was specified when MySQL 5.1 was installed?
OR, something else?


Do you see the database listed in SHOW DATABASES after starting MySQL.. if no then you can't specify while taking dump.. if you see the database in the listing but can't take dump means the data file and logfile you have got corrupted..
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question