Installing default MySQL Tables in Windows (mysql_install_db)

Question

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.)

Answer 1

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_"

Answer 2

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 !!!

• Synetech: Thanks for the tips; that’s a lot of steps considering on Linux it only takes a single script. :-(

Answer 3

FYI,

it's --initialize now, instead of the deprecated --bootstrap

---

NB. for the sake of completeness:

To run this on using an unprivileged account:

mysqld.exe --log_syslog=0 --console --standalone --initialize

Then start the server:

mysqld.exe --log_syslog=0 --console --standalone

Answer 4

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.

• Synetech: 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.
• Alec Gorge: 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.
• Synetech: Okay cool. I didn’t realize having to manually reset XAMPP was a common problem.
• Alec Gorge: 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
• Synetech: 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.