LinuxQuestions.org - [SOLVED] Convert MySQL for Windows files to Linux?

- Linux - General (https://www.linuxquestions.org/questions/linux-general-1/)

- - Convert MySQL for Windows files to Linux? (https://www.linuxquestions.org/questions/linux-general-1/convert-mysql-for-windows-files-to-linux-819121/)

Convert MySQL for Windows files to Linux?

I had a Windows XP Home box setup with MySQL, Apache, bind9, phpBB.

Then one evening while preparing a Linux replacement my Windows XP Home tower died. It completely croaked. It won't boot up past POST at all. No beep codes or anything! It just sits there with the power on like a brick. I cannot boot it to grab the database through the webserver with any web based MySQL script system.

I also do not have another Windows box that I can install MySQL on. There is no possible way I have to retrieve any MySQL databases that I had on it except by mounting the hard drive where MySQL stored the databases for Windows on it.

I've Googled for hours on end trying to find a solution but to no avail.

I tried simply copying the contents of the database as-is from where it was on Windows to where it would be on Linux but when I try to browse the phpBB forums I get this error:

Quote:

General Error
SQL ERROR [ mysql4 ]

Table 'phpbbdb.phpbb_config' doesn't exist [1146]

SQL

SELECT config_name, config_value FROM phpbb_config WHERE is_dynamic = 1

BACKTRACE

FILE: includes/db/mysql.php
LINE: 174
CALL: dbal->sql_error()

FILE: includes/cache.php
LINE: 37
CALL: dbal_mysql->sql_query()

FILE: common.php
LINE: 221
CALL: cache->obtain_config()

FILE: index.php
LINE: 20
CALL: include('common.php')

I found this thread: http://www.linuxquestions.org/questi...-linux-404689/ but it does me no good because I can't even start Windows let-alone MySQL on it.

Is there any way I can convert the MySQL database directory from Windows version to Linux?

Sure, it's easy:

1. In general you should backup your MySQL database

MySQL backups are a simple, clear-text "dump" format.
100% portable to any platform - easy to make, easy to restore.

Q: Did you happen to make a backup before you crashed?

2. If not, fear not! It should be easy to recover using your binary database files, too. The easiest way to do this is:

a) get a PC that's working

b) Install the SAME VERSION of Windows

c) Install the SAME VERSION of MySQL for Windows

d) Turn the PC off, open the case, and plug in the hard
drive from your failed PC.

e) Reboot the PC. Make sure the drive mounts in Windows.

f) Mount your files in MySQL

3. Finally, I'm *not* sure you can necessarily mount MySQL datafiles created on a Windows system to a MySQL running on Linux. But you can certainly TRY. This link is EXTREMELY useful:

http://www.kimvette.org/linux/recove...after_recovery

'Hope that helps .. PSM

Quote:

Originally Posted by paulsm4 (Post 4029397)

a) get a PC that's working
b) Install the SAME VERSION of Windows

I do not have another working PC. Nor do I have access to one that I'm allowed to install MySQL on.

All I have is the box I've got Ubuntu Studio Lucid installed on. I've already spent alot of time on it and I really don't want to wipe the harddrive on 4 days+ work on it. :(

Dude -

What ELSE did I suggest? Did you try it? Did you READ it?

And don't whine about "not having another PC". If it's really important enough, perhaps you can GET permission (permission, that is, from whoever won't "allow" you to install MySQL)

PS:
*Did* you read my other suggestions? Did any of them work/are any of them feasible?

Quote:

Originally Posted by paulsm4 (Post 4029435)

Dude -
PS:
Did any of them work/are any of them feasible?

No, they did not.

MySQL said it doesn't know what 'InnoDB' is. I Googled the error for InnoDB and the Google search results gave posts about commenting out the MySQL ini/conf file line for skipping InnoDB support but I couldn't find it in my conf files.

Hi -

From your first post, I understood:

a) You had two systems: Windows XP, and Ubuntu Linux.
b) Your Windows XP system croaked, but your Ubuntu system is alive and well.
c) Your goal is to migrate the MySQL data from the failed Windows system to your working Ubuntu system.
Q: Is this correct?

Q: Do you have a MySQL backup (i.e. clear-text schema and data dump) that you can simply restore to your working system?

Q: Have you tried the procedure I cited above:
http://www.kimvette.org/linux/recove...after_recovery
It's admittedly a different system - and probably a much older version of MySQL than the one you're running now.
But exactly what did you try?
Exactly what didn't work?
Exactly what error message did you encounter?

Unfortunately, "MySQL said it doesn't know what 'InnoDB' is..." is too vague to be useful. Please be more specific about a) what exactly you did (step-by-step), and b) the exact error message (ideally, cut/paste it from your terminal window).

Q: Exactly what version of Ubuntu are you running? Exactly what version of Linux/MySQL? Do you recall your Windows version of MySQL?

Q: How exactly did you try "copying" the data from Windows to Linux?

Q: Have you tried physically mounting the old hard drive in your Linux system, and mounting it as a Windows/CIFS filesystem? Can you see the data from Linux?

Quote:

Originally Posted by paulsm4 (Post 4030464)

Yes.

Quote:

Originally Posted by paulsm4 (Post 4030464)

Q: Do you have a MySQL backup (i.e. clear-text schema and data dump) that you can simply restore to your working system?

No, I was unable to use the mysqldump command on the Windows XP box. If I remember correctly it always complained about some broken "pipe".

Quote:

Originally Posted by paulsm4 (Post 4030464)

Q: Have you tried the procedure I cited above:
http://www.kimvette.org/linux/recove...after_recovery

Yes I have tried it:

Code:

user@HomeServer:~$ su

Password: 

root@HomeServer:/home/user# cd /var/lib/mysql

root@HomeServer:/var/lib/mysql# ls -al

total 16

drwx------  3 mysql mysql 4096 2010-07-13 12:52 .

drwxr-xr-x 69 root  root  4096 2010-07-11 11:06 ..

-rw-rw----  1 mysql mysql    6 2010-07-13 12:52 HomeServer.pid

drwx------  2 mysql root  4096 2010-07-13 12:49 mysql

root@HomeServer:/var/lib/mysql# rm -r ./*

root@HomeServer:/var/lib/mysql# mysql_install_db

Installing MySQL system tables...

OK

Filling help tables...

OK



To start mysqld at boot time you have to copy

support-files/mysql.server to the right place for your system



PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !

To do so, start the server, then issue the following commands:



/usr/bin/mysqladmin -u root password 'new-password'

/usr/bin/mysqladmin -u root -h HomeServer password 'new-password'



Alternatively you can run:

/usr/bin/mysql_secure_installation



which will also give you the option of removing the test

databases and anonymous user created by default.  This is

strongly recommended for production servers.



See the manual for more instructions.



You can start the MySQL daemon with:

cd /usr ; /usr/bin/mysqld_safe &



You can test the MySQL daemon with mysql-test-run.pl

cd /usr/mysql-test ; perl mysql-test-run.pl



Please report any problems with the /usr/scripts/mysqlbug script!



The latest information about MySQL is available at http://www.mysql.com/

Support MySQL by buying support/licenses from http://shop.mysql.com/



root@HomeServer:/var/lib/mysql# /etc/init.d/mysql stop

Rather than invoking init scripts through /etc/init.d, use the service(8)

utility, e.g. service mysql stop



Since the script you are attempting to invoke has been converted to an

Upstart job, you may also use the stop(8) utility, e.g. stop mysql

mysql stop/waiting

root@HomeServer:/var/lib/mysql# cd /var/lib/mysql

root@HomeServer:/var/lib/mysql# cp -R /home/user/mysqlbackup/* .

root@HomeServer:/var/lib/mysql# chown -R mysql:mysql *

root@HomeServer:/var/lib/mysql# /etc/init.d/mysql start

Rather than invoking init scripts through /etc/init.d, use the service(8)

utility, e.g. service mysql start



Since the script you are attempting to invoke has been converted to an

Upstart job, you may also use the start(8) utility, e.g. start mysql

mysql start/running, process 19202

root@HomeServer:/var/lib/mysql#

Quote:

Originally Posted by paulsm4 (Post 4030464)

Exactly what error message did you encounter?

This one is from phpMyAdmin when I try to browse the old db:
Attachment 4103

Quote:

Server: localhost - Database: phpbbdb - Table: phpbb_acl_groups "Unknown table engine 'InnoDB'"

* Browse
* Structure
* SQL
* Search
* Insert
* Export
* Import
* Operations
* Empty
* Drop

#1286 - Unknown table engine 'InnoDB'
SELECT *
FROM `phpbb_acl_groups`
LIMIT 0 , 30
Profiling
[ Edit ] [ Explain SQL ] [ Create PHP Code ] [ Refresh ]

Error

SQL query: Edit

SHOW FULL FIELDS FROM `phpbb_acl_groups` ;

MySQL said: Documentation
#1286 - Unknown table engine 'InnoDB'
Open new phpMyAdmin windowOpen new phpMyAdmin window

This one is from phpBB:
Attachment 4102

Quote:

General Error
SQL ERROR [ mysql4 ]

Unknown table engine 'InnoDB' [1286]

SQL

SELECT config_name, config_value FROM phpbb_config WHERE is_dynamic = 1

BACKTRACE

FILE: includes/db/mysql.php
LINE: 174
CALL: dbal->sql_error()

FILE: includes/cache.php
LINE: 37
CALL: dbal_mysql->sql_query()

FILE: common.php
LINE: 221
CALL: cache->obtain_config()

FILE: index.php
LINE: 20
CALL: include('common.php')

Quote:

Originally Posted by paulsm4 (Post 4030464)

Q: Exactly what version of Ubuntu are you running? Exactly what version of Linux/MySQL? Do you recall your Windows version of MySQL?

Ubuntu Studio Lucid 10.04 with MySQL installed with "apt-get":

Code:

root@HomeServer:/etc/mysql/conf.d# mysql --version

mysql  Ver 14.14 Distrib 5.1.41, for debian-linux-gnu (i486) using readline 6.1

root@HomeServer:/etc/mysql/conf.d#

This snippet from the ChangeLog file I found in the Docs subfolder of the MySQL installation on the old Windows XP system:

Code:

------------------------------------------------------------

revno: 2852

committer: Jonathan Perkin <jperkin@sun.com>

branch nick: mysql-5.1.34-release

timestamp: Tue 2009-03-31 16:04:02 +0200

message:

  Apply 64-bit fix from Azundris.

------------------------------------------------------------

revno: 2851

tags: clone-5.1.34-build

committer: Georgi Kodinov <joro@sun.com>

branch nick: fix-5.1-pe-stagee

timestamp: Fri 2009-03-27 16:34:48 +0200

message:

  merged tree name change

    ------------------------------------------------------------

    revno: 1810.3868.4

    committer: Georgi Kodinov <joro@sun.com>

    branch nick: fix-5.0-pe-stage

    timestamp: Fri 2009-03-27 16:29:56 +0200

    message:

      tree name changed

...

Quote:

Originally Posted by paulsm4 (Post 4030464)

Q: How exactly did you try "copying" the data from Windows to Linux?
Q: Have you tried physically mounting the old hard drive in your Linux system, and mounting it as a Windows/CIFS filesystem? Can you see the data from Linux?

I connected my old hdd with one of those "IDE/ATAPI to USB adapters." I could see all the files on the old hdd from inside Linux. I copied the db files from there to an intermediary location in my home folder with the default file browser that came with Ubuntu Studio Lucid 10.04. I did that because the path where it is on the old harddrive is really long and contains alot of spaces in the path names which makes it really difficult to work with on a terminal/bash.

for fun i had a site running on a fedora and win xp ( dual boot) using the SAME database ( weel the same files

I just used a startup script to copy the mysql files to the linux mysql folder set the correct ownership and SE context

just make sure that the linux mysql db is the same exact version that was on windows .

Hi, JohnVV - thanx for the input!

Hi, thinktink -

Have you tried this:

Quote:

http://gala4th.blogspot.com/2009/04/...ne-innodb.html

I was getting the error “ERROR 1286 (42000): Unknown table engine ‘InnoDB’” when trying to create/alter tables with the InnoDB engine. You can check for this warning by issuing the create or alter statements and then running
show warnings;
in the same mysql client session.

Check if you have InnoDB support enabled:
mysql> show variables like ‘have_innodb’; +—————+———-+ | Variable_name | Value | +—————+———-+ | have_innodb | DISABLED | +—————+———-+

The problem is that InnoDB is not enabled by default in Debian distributions of my.cnf. To enable it, simply comment the following lines under the [mysqld] section.

ALSO:
Please try connecting to MySQL directly from the command prompt (e.g. "mysql -uROOT -pROOT_PASSWORD mysql"), and let us know what happens.

Code:

show variables like 'have_innodb';

Doing that from phpMyAdmin does:

Quote:

+ Options
Variable_name Value
have_innodb NO

Doing it from mysql prompt on terminal window with "mysql -uroot -pROOT_PASSWORD mysql" (I think you meant lower-case root not upper-case ROOT because upper-case ROOT gave me access denied) gives me:

Code:

mysql> show variables like 'have_innodb';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| have_innodb  | NO    |

+---------------+-------+

1 row in set (0.00 sec)



mysql>

I serached for "skip-innodb" in the my.cnf file to comment it out but "skip-innodb" did not exist.

Thinktink -

1. Please forget mysqladmin for now: please just use the mysql command line until we're sure "everything's working".

2. Please read the link I posted here. Please edit your my.cnf, reboot your system, and verify that InnoDB is ENABLED. As the link says, it's often NOT enabled by default on Debian.

3. Please re-read the link I posted here. Please see a) do you get past the "Error 1286", and b) if you're able to access the data you copied from Windows (if only from the mysql command prompt).

4. Please post back what you find.

TIA .. PSM

Quote:

Originally Posted by paulsm4 (Post 4033057)

1. Please forget mysqladmin for now: please just use the mysql command line until we're sure "everything's working".

Quote:

Originally Posted by paulsm4 (Post 4033057)

2. Please read the link I posted here. Please edit your my.cnf, reboot your system, and verify that InnoDB is ENABLED. As the link says, it's often NOT enabled by default on Debian.

I did and as I explained in my previous post the option that link talks about does not exist in the my.cnf file at all. It is not there to be commented out.

Code:

root@homeserver:~# cd /etc/mysql

root@homeserver:/etc/mysql# vim my.cnf

root@homeserver:/etc/mysql# grep -Ri skip-innodb .

root@homeserver:/etc/mysql#

Quote:

Originally Posted by paulsm4 (Post 4033057)

3. Please re-read the link I posted here. Please see a) do you get past the "Error 1286", and b) if you're able to access the data you copied from Windows (if only from the mysql command prompt).

a) I have followed the instructions in the link you posted to-the-letter twice now and each time I do it the InnoDB support fails. I posted the terminal results at this post here, it gave no errors. I can see the database from myadmin but when I try to access any of the tables inside it shows the 1286 error and I cannot get passed that error.

b) I don't know much at all about mysql queries or how to try to access databases from the "mysql" command line, that's why I'm using myadmin to test it. I am sorry about that. How do I do that?

Sigh...

Sorry this is causing you so much grief - it SHOULD ordinarily be a slam dunk. Honest :(

SUGGESTIONS:
1. Search for a duplicate my.cnf:
http://www.experts-exchange.com/Data..._20921250.html
[code]find / -name my.cnf -print > /dev/null[/quote]

2. Your goal, of course, is to get InnoDB ENABLED:

Code:

mysql -uXXX -pYYY mysql



mysql> show variables like 'have_innodb';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| have_innodb  | YES  |

+---------------+-------+

1 row in set (0.00 sec)



mysql> show engines;

+------------+---------+------------------------------------------------------------+

| Engine    | Support | Comment                                                    |

+------------+---------+------------------------------------------------------------+

| MyISAM    | DEFAULT | Default engine as of MySQL 3.23 with great performance    |

| HEAP      | YES    | Alias for MEMORY                                          |

| MEMORY    | YES    | Hash based, stored in memory, useful for temporary tables  |

| MERGE      | YES    | Collection of identical MyISAM tables                      |

| MRG_MYISAM | YES    | Alias for MERGE                                            |

| ISAM      | YES    | Obsolete storage engine, now replaced by MyISAM            |

| MRG_ISAM  | YES    | Obsolete storage engine, now replaced by MERGE            |

| InnoDB    | YES    | Supports transactions, row-level locking, and foreign keys |

| INNOBASE  | YES    | Alias for INNODB                                          |

| BDB        | NO      | Supports transactions and page-level locking              |

| BERKELEYDB | NO      | Alias for BDB                                              |

| NDBCLUSTER | NO      | Clustered, fault-tolerant, memory-based tables            |

| NDB        | NO      | Alias for NDBCLUSTER                                      |

| EXAMPLE    | NO      | Example storage engine                                    |

| ARCHIVE    | NO      | Archive storage engine                                    |

| CSV        | NO      | CSV storage engine                                        |

+------------+---------+------------------------------------------------------------+

16 rows in set (0.00 sec)

3. This link suggests that updating from Ubuntu Gutsy (a Debian Linux) to Ubuntu Hardy (a newer version) fixed the problem:
http://forum.slicehost.com/comments....cussionID=1754

Q: What is your exact distro and version?

4. Here's another good suggestion:
http://www.geedew.com/2008/05/21/how...-mysql-server/

Quote:

After 3 apt-get -f –purge remove mysql-server-5.0 and and upgrade to Ubuntu Hardy, I recovered the answer. You see, InnoDB is very touchy. If it isn’t told what is going on , it just doesn’t start. It will usually throw NO error upon this issue. I’m not an expert as to why it does this, but that seems to be the case. I found that there are files in the /var/lib/mysql folder that were getting pointed to in some tutorials on MySQL.com. These files were the ibdata1 and the ib_logfile0 files. The program is outputting it’s logs to these files. Now, if InnoDB is supposed to be working, but it’s not, and these files are being pointed at by MyISAM… my understanding is there could be an issue with how the files were created. I did this as root (use sudo in front of each command otherwise)..

<= Please read this, and let us know what you find

5. In any case, if you don't find a "rogue my.cnf" on your system, please try Googling for "enable InnoDB" (or something similar) and see if you get other hints.

It SHOULD work. Honest!

'Hope that helps .. PSM

Ok, I have found the solution. It does not match the original question of how to convert a MySQL database from Windows to Linux but I have the ultimate goal of recovering the MySQL data from dead Windows XP box. I have probably read alot of the articles you have read over these past days but I guess it just took a bit of time to piece them all together.

...and this time I took detailed notes!

I sat down and thought about the goal of not converting but mounting the database as is. I realized, of course, that would not work unless the settings for both source and target installs matched (for the most part) exactly.

I then decided to spend some time comparing all the ini files from the source box to the cnf files in the target box. I did a Google search to find a grep command to strip the comment lines out of the config files and redirected the output of each to "oldmysqlserverconf.txt" (source) and "newmysqlserverconf.txt" (target). I then opened both files at the same time in gedit, removed the inactive/sample entries and blank lines, and went to work finding every option in the source configs that either did not match, or did not exist, in the target configs. In my case only one explicitly defined option was different in the target configs, the rest were non-existent in the target configs.

While doing that process I came across an option in the source configs that caught my immediate attention:

Code:

innodb_data_home_dir="I:/Program Files/MySQL/MySQL Server 5.1/bin/InnoDB Tablespace/"

At first when I compared the two data directories I had not noticed that the source data directory was missing the "ibdata1" file that was present in the target data directory. Today I finally did and copied the "ibdata1" file I found in the source innodb_data_home_dir configuration file to the right place in my local backup directory I had made earlier. After that I finished the step-by-step process of notating differences.

When all my notes were complete I first stopped the MySQL daemon, backed up "my.cnf", opened the original and implemented the changes I notated. That part was easy since the output of the grep command I used also included the line numbers of each uncommented setting, plus there was only one to explicitly change, the rest I just copied and pasted.

I then recopied my local database backup (which now included the missing "ibdata1" file) as per the link provided earlier, restarted the MySQL daemon, and checked myPHPAdmin. To my immediate utter consternation it still failed with the same dam'n error code and text [1286]. But about a second later I remembered reading somewhere that InnoDB "log files" (I think it said "log files") that get corrupted will cause the InnoDB engine to fail at startup with no warnings or errors. I looked at the data directory root again and saw "ibdata1", "ib_logfile0", "ib_logfile1", "mysql", "phpbbdb", and "test". I saw that the second and third entries had the word "logfile" in them.

I stopped the MySQL daemon and ran this command line:

Code:

rm ib_*

This time, when I started the MySQL daemon again, it finally worked!

For reference I am posting my notes.

grep comment stripper commands:

Code:

cd /etc/mysql

grep -Rvn "^#" . > /home/user/newmysqlserverconf.txt



cd "/media/7A588AE4588A9F0F/Program Files/MySQL/MySQL Server 5.1"

grep -Rvn "^#" *.ini > /home/user/oldmysqlserverconf.txt

Completed target server configuration change notations (the one line that starts with "-->" was the only explicitly defined configuration that was different. The config options at the end that don't start with [filename]:[linenumber]: were the config options that were not explicitly defined in the target configuration files.)

Code:

./debian.cnf:2:[client]

./debian.cnf:3:host    = localhost

./debian.cnf:4:user    = debian-sys-maint

./debian.cnf:5:password = ****************

./debian.cnf:6:socket  = /var/run/mysqld/mysqld.sock

./debian.cnf:7:[mysql_upgrade]

./debian.cnf:8:host    = localhost

./debian.cnf:9:user    = debian-sys-maint

./debian.cnf:10:password = ****************

./debian.cnf:11:socket  = /var/run/mysqld/mysqld.sock

./debian.cnf:12:basedir  = /usr

./my.cnf:19:[client]

./my.cnf:20:port                = 3306

./my.cnf:21:socket                = /var/run/mysqld/mysqld.sock

./my.cnf:27:[mysqld_safe]

./my.cnf:28:socket                = /var/run/mysqld/mysqld.sock

./my.cnf:29:nice                = 0

./my.cnf:31:[mysqld]

./my.cnf:42:user                = mysql

./my.cnf:43:pid-file        = /var/run/mysqld/mysqld.pid

./my.cnf:44:socket                = /var/run/mysqld/mysqld.sock

./my.cnf:45:port                = 3306

./my.cnf:46:basedir                = /usr

./my.cnf:47:datadir                = /var/lib/mysql

./my.cnf:48:tmpdir                = /tmp

./my.cnf:49:skip-external-locking

./my.cnf:53:bind-address                = 127.0.0.1

./my.cnf:57:key_buffer                = 16M

./my.cnf:58:max_allowed_packet        = 16M

./my.cnf:59:thread_stack                = 192K

./my.cnf:60:thread_cache_size        = 8

./my.cnf:63:myisam-recover        = BACKUP

./my.cnf:70:query_cache_limit        = 1M

-->./my.cnf:71:query_cache_size        = 8M

./my.cnf:93:expire_logs_days        = 10

./my.cnf:94:max_binlog_size        = 100M

./my.cnf:116:[mysqldump]

./my.cnf:117:quick

./my.cnf:118:quote-names

./my.cnf:119:max_allowed_packet        = 16M

./my.cnf:121:[mysql]

./my.cnf:124:[isamchk]

./my.cnf:125:key_buffer                = 16M

./my.cnf:131:!includedir /etc/mysql/conf.d/



default-character-set=latin1

default-storage-engine=INNODB

sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

table_cache=256

tmp_table_size=5M

myisam_max_sort_file_size=100G

myisam_sort_buffer_size=8M

key_buffer_size=8M

read_buffer_size=64K

read_rnd_buffer_size=185K

innodb_data_home_dir=/var/lib/mysql

innodb_additional_mem_pool_size=2M

innodb_flush_log_at_trx_commit=1

innodb_log_buffer_size=1M

innodb_buffer_pool_size=8M

innodb_log_file_size=10M

innodb_thread_concurrency=8

P.S.
The first thing I did after recovering the database was to back it up through MyPHPAdmin, clear the database files in /var/lib/mysql, disable InnoDB support and change the default storage engine to MyISAM, and restore the backup through MyPHPAdmin. Goodbye InnoDB!

I know it's marked as 'solved' and congratulations. Thanks for the extensive feedback, it might help me one day ;)

Just on a side note: you are aware of the differences between MyIsam and InnoDB tables, most notably related to transactions. So there might be scenarios where one needs InnoDB.