Mysql – Some tables not found after moving MySQL database from Linux to Windows

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

You certainly have a problem but that does not look related to enabling general logging. Are you sure it's not that those errors have been filling your error log previously and you only just noticed them after an investigation stemming from enabling general_logging?

Enabling general_logging can and cause your server seemingly hang. General logging will log every single query thrown over the fence to mysql (even one resulting in syntax errors).

This can cause the file size to grow a lot more rapidly than expected. Run

show variables like 'general_log_file';

Then check the disk usage for that path from the shell command line

df -h /var/log/mysql/mysql.log

If that reports 0 available and 100% use then it is very much expected mysql would hang.

It's the defined behavior of mysql when any log (general, binary, error) cannot be further written to because space has been exhausted. The idea is it will just hang out until space is made available.

To remedy you want to either more the log to a filesystem with a filesystem with enough space, or just delete it if you don't care. Then either restart w/ the general_log variable pointing to a file system with enough space for your traffic loads or add more space to the existing file system.