Moving MySql from a Windows server to Linux

Question

Migrating from the old Win2003 server to the new VM server (our choice of Win or Linux) if we go Linux, will there be any problems converting the current tables?

Answer 1

Moving MySQL / Windows to the same version of MySQL / Linux

You can mysqldump for all databases as follows:

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

Move MySQLData.sql to the Linux field and start the reboot

 mysql -uroot -p < MySQLData.sql 

Moving MySQL / Windows to a Higher Version of MySQL / Linux

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

• MySQL has grants for the user in the main table named mysql.user .
• For each major version 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 discussed the mysql.user column layout before
  • May 01, 2013 : Can I find out which version of MySQL is from data files?
  • Dec 24, 2012 : Backing up and restoring the mysql database
  • Jun 13, 2012 : The fastest way to move a database from one server to another
  • Feb 08, 2012 : Will replication from server 5.5.20 to 5.0.XX work?

Here is the Windows Script package for mysqldump of 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% 

After creating the mysqldump and the Grants file, simply copy them to Linux Server by running them locally. Run mysqldump first. Then download the grants.

Try it !!!

Answer 2

You will not need to convert tables. A dump of SQL from a Windows server will be easily imported into MySQL on Linux.

Answer 3

No, the tables will be fine. You should also be able to transfer configuration files without problems.

using mysqldump and then just mysql command to restore the backup

Answer 4

You can export your database (data tables). Then you will get sql script file. If you run this script file on your new system, your tables and data will be available on the new system