Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In Linux, the default MySQL grant tables can be created with the mysql_install_db script, but that does not work on Windows.

How can the default grant tables be installed on Windows?

(No, I’m not looking for the response that Google’s results are packed full of about how they are automatically installed on Windows, because that is only the case with the installer distribution, not the ZIP package. Besides, that does not help after MySQL is installed and the data directory is damaged or is being replaced or such.)

share|improve this question

3 Answers 3

up vote 9 down vote accepted

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_"
share|improve this answer

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

[mysqld]
skip-grant-tables
skip-networking

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 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 and skip-networking 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 !!!

share|improve this answer
    
Thanks for the tips; that’s a lot of steps considering on Linux it only takes a single script. :-( –  Synetech Mar 7 '11 at 0:46

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.

share|improve this answer
    
That’s handy for XAMPP, but it’s a pretty limited use-case scenario since MySQL is a lot more general-purpose than just a single web-server. –  Synetech Aug 11 '11 at 0:21
    
Oh, I know. I just wanted to post that there for anyone who landed on this page after Googling for that error and also uses XAMPP (like me). I don't expect this to be marked as accepted, but to be left as reference for others. –  Alec Gorge Aug 11 '11 at 19:51
    
Okay cool. I didn’t realize having to manually reset XAMPP was a common problem. –  Synetech Aug 12 '11 at 0:10
    
It isn't, I have no clue how this happened and I didn't want to rerun the installer for fear of overwriting carefully manicured configuration files :P –  Alec Gorge Aug 12 '11 at 2:00
    
Oh I know! Since MySQL has no built-in method of shrinking the database file (like the vacuum command of SQLite3, et. al.), I have to manually dump it and create a new one now and then since I don’t have a lot space; but I’m always worried about doing so, so I put it off as long as possible. –  Synetech Aug 12 '11 at 2:28

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.