Ubuntu – How to move a Windows XP MySQL database to Linux

migrationMySQLUbuntuwindows-xp

What is the recommended way of moving an MySQL database from Windows (XP) to Linux (Ubuntu 9.10)?

I am thinking running mysqldump from XP then import on the Linux box – is there a better way?

Has anyone done this before, and if yes, could they please outline the steps required.

[Edit] Should have added this:
I am using mySQL v5.1 and I am using InnoDb format

Best Answer

The mysqldump approach will certainly work, and the result will be reliable. This is the way I would recommend, unless you have very good reasons not to do it like this.

Now, for MyISAM tables you should be able to simply copy the .FRM, .MYI and .MYD files to the appropriate directory corresponding to the target database (MySQL creates a dir corresponding to each database in the data directory)

For InnoDB tables, simply copying the files does not work. I have seen people do it though, for example, see this article: http://www.chriscalender.com/?p=28=1

I Hope this helps.

Related Solutions

MySQL – Export and Import Privileges to a New Server

Do not mess with the mysql db. There is a lot more going on there than just the users table. Your best bet is the "SHOW GRANTS FOR" command. I have a lot of CLI maintenance aliases and functions in my .bashrc (actually my .bash_aliases that I source in my .bashrc). This function:

mygrants()
{
  mysql -B -N $@ -e "SELECT DISTINCT CONCAT(
    'SHOW GRANTS FOR \'', user, '\'@\'', host, '\';'
    ) AS query FROM mysql.user" | \
  mysql $@ | \
  sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/## \1 ##/;/##/{x;p;x;}'
}

The first mysql command uses SQL to generate valid SQL which is piped to the second mysql command. The output is then piped through sed to add pretty comments.

The $@ in the command will allow you to call it as: mygrants --host=prod-db1 --user=admin --password=secret

You can use your full unix tool kit on this like so:

mygrants --host=prod-db1 --user=admin --password=secret | grep rails_admin | mysql --host=staging-db1 --user=admin --password=secret

That is THE right way to move users. Your MySQL ACL is modified with pure SQL.

SSH Tunnel – Cannot SSH Tunnel with PuTTY (Windows Vista) to Connect to MySQL on a Linux Box

It sounds like you've got it "backwards". The destination is not your loopback, it's the remote machine on the other network. That, or you're using Remote instead of Local for the tunnel.

Try flipping the IP addresses around, example:

putty.exe -L 3307:mysql.yourdomain.com:3306

Where mysql.yourdomain.com is the name of the host that has the MySQL service you're trying to contact. When putty pops up the connection dialog, put in the address, connect, and log in. Then connect to localhost:3307 using the Administrator tool.

Best Answer

Related Solutions

MySQL – Export and Import Privileges to a New Server

SSH Tunnel – Cannot SSH Tunnel with PuTTY (Windows Vista) to Connect to MySQL on a Linux Box

Related Topic