Tuesday, January 20, 2009

How to Recreate MySQL System Tables

You might wonder, why would I need to recreate MySQL system tables. Well, the case is that my WoS folder has gotten pretty big, and I lost my original WoS.zip file. And the website doesn't carry WoS anymore. (now its called MoWes, and for some reason I still trust the old' WoS than the MoWes). I need to copy this to another computer, without the 270 MB-or-so MySQL datafiles. So the solution is-> just copy them, excluding the data folder inside mysql folder. But the newly copied MySQL won't start. It created new datafiles all right, but it still won't start. Examining the log files, it said something about missing grant tables.
So I added this option when starting mysql:
bin\mysqld-nt --skip-grant-tables
This forced it to start even without grant tables. Now, the grant tables still must be created, and after browsing for a while I found out that we could initialize the grant tables using these scripts:
  • mysql_system_tables.sql
  • mysql_system_tables_data.sql
  • fill_help_tables.sql
These files cannot be found in WoS's mysql folder. Fortunately I found them in /usr/share/mysql folder in my Ubuntu box, assuming that these files are compatible with WoS's mysql I begin running the scripts using MySQL Query Browser.
The first run spits errors. Seems that I must manually do these before those scripts:
create database mysql;
use mysql;
Then I retried running mysql_system_tables, then all the other scripts. After restarting the mysql server, all seems to be working correctly.

No comments: