How to move MySQL data to new installation

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

lee88Asked:
Who is Participating?
 
UmeshMySQL Principle Technical Support EngineerCommented:
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
 
bansidharCommented:
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
 
UmeshMySQL Principle Technical Support EngineerCommented:
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
bansidharCommented:
also please note, in windows installation the Innodb data folder is set by the user when the configuration wizard is run.
0
 
lee88Author Commented:
ushastry: Where is my MySQL's config file (or what is the default name)?
bansidhar: The old mysql server is not running.
0
 
UmeshMySQL Principle Technical Support EngineerCommented:
Pls look for my.ini
0
 
bansidharCommented:
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
 
lee88Author Commented:
Here's the old my.ini (attached. renamed to old_my_ini.txt)
old-my-ini.txt
0
 
UmeshMySQL Principle Technical Support EngineerCommented:
thanks for my.ini

also file size of these

1. My ibdata1
2. The ib_logfile0 and ib_logfile1 files
0
 
lee88Author Commented:
As shown by windows explorer:
idbata1 is 10,240 KB
ib_logfile0 is 145,408 KB
ib_logfile1 is also 145,408 KB
0
 
lee88Author Commented:
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
 
UmeshMySQL Principle Technical Support EngineerCommented:
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
 
lee88Author Commented:
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
 
UmeshMySQL Principle Technical Support EngineerCommented:
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
 
lee88Author Commented:
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
 
UmeshMySQL Principle Technical Support EngineerCommented:
Ok.. in 5.1 mysqld-nt seems to be missing
try this..

mysqldt --innodb_log_file_size=145408 --innodb_force_recovery=6
0
 
lee88Author Commented:
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
 
UmeshMySQL Principle Technical Support EngineerCommented:
my bad... shud have been mysqld but I typed mysqldt.. M sorry

mysqld --innodb_log_file_size=145408 --innodb_force_recovery=6
0
 
lee88Author Commented:
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
 
UmeshMySQL Principle Technical Support EngineerCommented:
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
 
lee88Author Commented:
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
 
UmeshMySQL Principle Technical Support EngineerCommented:
>>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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.