我正在尝试将一台运行 MySQL 4.1 的旧 Windows Server 2003 32 位计算机迁移到一台运行 MySQL 5.1 的新 Windows Server 2008 R2 64 位计算机，并且刚刚进入数据库部分。我在旧服务器上有一个相当重的数据库，在我之前尝试下载表格然后重新上传到新网站，我想确保我不需要先进行转换吗？旧服务器没有运行最新的 MySQL 或 PHP，因此使用 PhpMyAdmin 是不可能的，而且我没有权限更改任何类似的东西，我只有一个垃圾、基本的、有缺陷的 WebFusion 控制面板。
任何帮助我都感激不尽。
答案1
您应该将整个 mysql 数据库转储到文本文件中，因为 mysqldump 是数据的逻辑表示。这可以避免在主要版本之间切换时可能出现的任何问题。有一个主要问题：mysql 模式，
步骤01）MySQL
C:\> mkdir C:\>MySQLData
C:\> mysql -h... -u... -p... --all-databases > C:\MySQLData\MySQLDataDump.sql
步骤 02）在 Win2008 上安装 MySQL 5.5
步骤 04) 将 MySQL 4.1 的 my.ini 复制到 Win2008
步骤 05) 将 C:\MySQLData\MySQLDataDump.sql 重新加载到 MySQL 5.5
mysql < C:\MySQLData\MySQLDataDump.sql
步骤 06) 运行mysql_upgrade.exe
这就是事情可能变得有点疯狂的地方，因为这是 MySQL 4.1.22 中的 mysql 模式
mysql> use mysql
Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| func |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
15 rows in set (0.00 sec)
mysql> desc mysql.user;
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Host | varchar(60) | | PRI | | |
| User | varchar(16) | | PRI | | |
| Password | varchar(41) | | | | |
| Select_priv | enum('N','Y') | | | N | |
| Insert_priv | enum('N','Y') | | | N | |
| Update_priv | enum('N','Y') | | | N | |
| Delete_priv | enum('N','Y') | | | N | |
| Create_priv | enum('N','Y') | | | N | |
| Drop_priv | enum('N','Y') | | | N | |
| Reload_priv | enum('N','Y') | | | N | |
| Shutdown_priv | enum('N','Y') | | | N | |
| Process_priv | enum('N','Y') | | | N | |
| File_priv | enum('N','Y') | | | N | |
| Grant_priv | enum('N','Y') | | | N | |
| References_priv | enum('N','Y') | | | N | |
| Index_priv | enum('N','Y') | | | N | |
| Alter_priv | enum('N','Y') | | | N | |
| Show_db_priv | enum('N','Y') | | | N | |
| Super_priv | enum('N','Y') | | | N | |
| Create_tmp_table_priv | enum('N','Y') | | | N | |
| Lock_tables_priv | enum('N','Y') | | | N | |
| Execute_priv | enum('N','Y') | | | N | |
| Repl_slave_priv | enum('N','Y') | | | N | |
| Repl_client_priv | enum('N','Y') | | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | | | | |
| ssl_cipher | blob | | | | |
| x509_issuer | blob | | | | |
| x509_subject | blob | | | | |
| max_questions | int(11) unsigned | | | 0 | |
| max_updates | int(11) unsigned | | | 0 | |
| max_connections | int(11) unsigned | | | 0 | |
+-----------------------+-----------------------------------+------+-----+---------+-------+
31 rows in set (0.00 sec)
mysql>
以下是 MySQL 5.5 中的 mysql 模式
mysql> use mysql
Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| audit_user_host |
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| servers |
| slow_log |
| slow_queries |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
26 rows in set (0.07 sec)
mysql> desc mysql.user;
+------------------------+-----------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Password | char(41) | NO | | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| Create_tablespace_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) unsigned | NO | | 0 | |
| plugin | char(60) | NO | | | |
| authentication_string | text | NO | | NULL | |
+------------------------+-----------------------------------+------+-----+---------+-------+
42 rows in set (0.03 sec)
mysql>
主要的区别，嗯???这就是为什么无论您复制数据文件夹还是新加载 mysqldump，您都会mysql_upgrade.exe在之后运行以添加适当的列和表。
作为替代方案，您可以单独从 MySQL 4.1 中 mysqldump 每个数据库，然后将每个 mysqldump 导入 MySQL 5.5。那么，GRANTS 怎么办？尝试将 MySQl 4.1 中的 GRANTS 转换为纯 SQL。
MYSQL_USERNAME=...
MYSQL_PASSWORD=...
MYSQL_CONN="-u${MYSQL_USERNAME} -p${MYSQL_PASSWORD}"
mysql ${MYSQL_CONN} --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql ${MYSQL_CONN} --skip-column-names -A > C:\TMP\MySQLUserGrants.sql
这将生成 SQL 命令以将用户授权重现在任何版本的 MySQL 4.1+ 中
在 MySQL 5.5 中导入该文件，一切就绪了。