Installing default MySQL Tables in Windows (mysql_install_db)

If you have had this strange dilemma in MySQL grant tables for Windows, I recommend the following:

Step 1. Get the ZIP FIle distribution that does not have the installer in it.

Step 2. Unzip it's contents to C:\MySQLZipStuff

Step 3. Look for the folder C:\MySQLZipStuff\data\mysql

Step 4. Copy all the files in C:\MySQLZipStuff\data\mysql over to the mysql folder of your desired data directory.

Step 5. net start mysql

If you have the mysql folder already you can do this instead:

Step 1. Edit my.ini by adding this line in the [mysqld] section

skip-grant-tables

Step 2. net stop mysql

Step 3. net start mysql

At this point, you can just type 'mysql' and you are in. However, you cannot run GRANT commands with grant tables disabled.

Step 4. You will have to enter one superuser manually. Like this

a) INSERT INTO mysql.user SET user='root',host='localhost';

b) SELECT * FROM mysql.user WHERE user='root' AND host='localhost'\G

This will show all the columns in the user table. You will have to then manually change each column like this:

UPDATE mysql.user SET select_priv='Y',insert_priv='Y',... WHERE user='root' AND host='localhost';

c) Setup a password for root@localhost like this:

UPDATE mysql.user SET password=PASSWORD('whateverpasswordyouwant') WHERE user='root' AND host='localhost';

Step 5. Remove 'skip-grant-tables' from my.ini

Step 6. net stop mysql

Step 7. net start mysql

Now you can log into mysql as root using 'whateverpasswordyouwant' as the password.

Give it a Try !!!

I knew I had faced this before and managed to figure it out, so I searched my drives and found a batch file I had written last time.

For anyone else who experiences this issue, what you need to do is to run the server daemon with the --bootstrap parameters.

Here is the script I used to dump all the databases out to an SQL file and re-import them (that is, manually—and inconveniently—implementing a MySQL counterpart to SQLite’s vacuum command).

::Start the server, change to the MySQL bin directory, and dump all databases
net start MySQL
cd       /d "%serverdir%\MySQL\bin"
mysqldump  -uroot -p --all-tablespaces --all-databases --add-drop-database --add-drop-table --add-locks --comments --complete-insert --compress --create-options --events --routines --quick --quote-names --set-charset --triggers > %temp%\all.sql

::Stop the server and rename the data directory (as backup)
net stop mysql
ren         "%datadir%\MySQL"        MySQL_

::Delete data folder in MySQL directory, optionally copy old mysql tables
rd    /s /q "%serverdir%\MySQL\data"
md          "%serverdir%\MySQL\data"
xcopy /s /e "%datadir%\MySQL_\mysql" "%serverdir%\MySQL\data"

::Bootstrap the server (creates grant tables if they don’t exist)
mysqld --bootstrap

::Run server, optionally upgrade tables and move upgrade log to data directory
start mysqld --skip-grant-tables
mysql_upgrade --force
move "%serverdir%\MySQL\bin\mysql_upgrade_info" "%serverdir%\MySQL\data"

::Import all databases, shutdown, delete logs, then move to old data dir
mysql      -uroot -p < %temp%\all.sql
mysqladmin -uroot -p shutdown
md  "%datadir%\MySQL"
del "%serverdir%\MySQL\data\ib_logfile?"
xcopy /s /e "%serverdir%\MySQL\data\*" "%datadir%\MySQL"
rd    /s /q "%serverdir%\MySQL\data"

::Start the server, if it works, then all should be well, so del
net start mysql
rd    /s /q "%datadir%\MySQL_"

For anyone who uses XAMPP and experiences this problem, XAMPP ships with a backup of these default mysql tables.

Copy everything in this folder C:\xampp\mysql\backup\mysql and paste it in C:\xampp\mysql\data\mysql, overwriting everything.

Be warned: data may be lost--I wouldn't know, I had a clean install.