MySQL – Installing Default Tables in Windows

installationMySQLwindows

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

Best Answer

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