Moving MySql from windows server to linux

Question

Moving from an old Win2003 server to a new VM server (our choice Win or Linux) if we go Linux would there be any problems converting the current tables?

Answer 1

Moving MySQL/Windows to same version of MySQL/Linux

You can mysqldump all the databases as follows:

C:\> mysqldump -uroot -p --routines --triggers --flush-privileges --all-databases > MySQLData.sql

Move MySQLData.sql to Linux box and run the reload

mysql -uroot -p < MySQLData.sql

Moving MySQL/Windows to higher version of MySQL/Linux

You can mysqldump all the databases EXCEPT THE mysql SCHEMA !!! Why?

• MySQL has the grants for the user in a main table called mysql.user.
• For each major release of MySQL, mysql.user has the following number of columns:
  • 43 columns in MySQL 5.6
  • 42 columns in MySQL 5.5
  • 39 columns in MySQL 5.1
  • 37 columns in MySQL 5.0
  • 31 columns in MySQL 4.0/4.1
• I have discussed mysql.user's column arrangement before
  • May 01, 2013 : Can I find out what version of MySQL from the data files?
  • Dec 24, 2012 : Backup and restore "mysql" database
  • Jun 13, 2012 : Fastest way to move a database from one server to another
  • Feb 08, 2012 : will replication from 5.5.20 to 5.0.XX server work?

Here is a Windows Batch Script to mysqldump all databases except the mysql schema and then dump the mysql schema in pure SQL:

rem
rem Startup Settings
rem
set MYSQL_CONN=-uroot -prootpassword
set MYSQLDUMP_OUTPUT=C:\LocalDump.sql
set MYSQL_USERGRANTS=C:\LocalGrants.sql
set MYSQL_TEMPGRANTS=C:\TempGrants.sql
rem
rem Get MySQL User Data
rem
set MYSQLDUMP_OPTIONS=--routines --triggers --databases
set SQLSTMT=SELECT CONCAT('mysqldump %MYSQL_CONN% %MYSQLDUMP_OPTIONS% ',DBList)
set SQLSTMT=%SQLSTMT% FROM (SELECT GROUP_CONCAT(schema_name SEPARATOR ' ') DBList
set SQLSTMT=%SQLSTMT% FROM information_schema.schemata WHERE schema_name NOT IN
set SQLSTMT=%SQLSTMT% ('information_schema','mysql','performance_schema')) A
echo echo off > C:\RunLocalDump.bat
mysql %MYSQL_CONN% -ANe"%SQLSTMT%" >> C:\RunLocalDump.bat
C:\RunLocalDump.bat > %MYSQLDUMP_OUTPUT%
rem
rem Get MySQL User Grants
rem
set SQLSTMT=SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';')
set SQLSTMT=%SQLSTMT% FROM mysql.user WHERE LENGTH(user)
echo %SQLSTMT%
mysql %MYSQL_CONN% -ANe"%SQLSTMT%" > %MYSQL_TEMPGRANTS%
mysql %MYSQL_CONN% -AN < %MYSQL_TEMPGRANTS% > %MYSQL_USERGRANTS%
del %MYSQL_TEMPGRANTS%

Once you create the mysqldump and the Grants File, simply copy them to the Linux Server execute them locally. Execute the mysqldump first. Then, load the grants.

Give it a Try !!!

Answer 2

You would not need to convert the tables. The SQL dump from the Windows server would be easy to import into MySQL on Linux.