Is this way of MySQL shutdown via bat file sufficient to minimise corruption?

Question

I had memory issues with using 32bit Xampp so I have installed 64bit components myself as a new server.

Although, I want to make sure I am stopping the MySQL database server properly.

Is this a sufficient way to shut it down so that frequently stopping MySQL in the hectic development stage isn't corrupting the database?

The following is contained in a bat file I wrote.

@echo off
"C:\host\MariaDB\bin\mysqladmin.exe"  -u root -prootpass flush-tables
echo Flushing DB, Hopefully.
TIMEOUT 30
echo Flushed DB, I think!

"C:\host\MariaDB\bin\mysqladmin.exe"  -u root -prootpass shutdown
echo Stopped MySQL service.
pause

Again, it works but I want to make sure I am minimising the chance of corruption from stopping the database server.

Thanks in advance.

Answer 1

If you want to shutdown MySQL effectively, you need to run several things

@echo off
rem
rem Flush Everything and its Grandmother
rem
echo "Stopping MySQL Service"
"C:\host\MariaDB\bin\mysql.exe" -u root -prootpass -ANe"SET GLOBAL innodb_fast_shutdown=0; STOP SLAVE; FLUSH BINARY LOGS; FLUSH TABLES"
"C:\host\MariaDB\bin\mysqladmin.exe" -u root -prootpass shutdown
echo "Stopped MySQL Service"
pause

The first line does the following

• STOP SLAVE : If this MySQL Service is a Slave, it will close the IO and SQL Threads and close the file handles opened against the relay logs. If replication is not enabled, it will be just a warning.
• FLUSH BINARY LOGS : If binary logging is enabled, it will close the current binary log (which flushes it), and open a new one (file size of the new binary logs will be less than 200 bytes). If binary logging is disabled, it will be just a warning.
• FLUSH TABLES : This closes any open file handles against all open tables, alerting the OS to perform the disk flush of all table changes in memory.
• SET GLOBAL innodb_fast_shutdown=0 : The default value for innodb_fast_shutdown is 1. This allows InnoDB to suspend its changes in the redo logs and the double write buffer. Thus, changes will be applied the next time MySQL Service is started. This is also known as crash recovery upon startup. By setting innodb_fast_shutdown to 0, this causes InnoDB to apply changes during the shutdown. That will result in the MySQL Service starting faster because it does not need to do crash recovery.

I have written about using innodb_fast_shutdown many times

• Apr 15, 2012 : Mysql Innodb: InnoDB: ERROR: the age of the last checkpoint is InnoDB: which exceeds the log group capacity
• Mar 06, 2013 : How to properly kill MySQL?
• Apr 23, 2013 : Start/Stop MySQL
• May 05, 2013 : Issue after moving the ib_logfile1 and ib_logfile0 files
• Dec 09, 2013 : Purge old records from mysql ibdata1 file
• Jan 07, 2015 : Flush/Clean MySql ib_logfile0 ib_logfile1

GIVE IT A TRY !!!

NOTE : The reason for all the other commands is to close as many different file handles as possible before the actual shutdown process. Each closed file handle will induce a flush of the table if there are any disk changes. This gives the shutdown process less to work on, focusing its attention mainly on rolling forward all changes in the InnoDB Plumbing.

Here is a Pictorial Representation of InnoDB

This picture was made by Percona CTO Vadim Tkachenko

This picture should let you see what moving parts are in motion during the shutdown.

UPDATE 2016-10-13 07:45 EDT

@a_horse_with_no_name commented

Are you saying that a "regular" shutdown does not in fact cleanly shut down the database engine? (especially that it will not flush everything to disk?)

When it comes InnoDB, shutting down MySQL is subject to interpretation. This is why innodb_fast_shutdown exists. The documentation says:

The InnoDB shutdown mode. If the value is 0, InnoDB does a slow shutdown, a full purge and a change buffer merge before shutting down. If the value is 1 (the default), InnoDB skips these operations at shutdown, a process known as a fast shutdown. If the value is 2, InnoDB flushes its logs and shuts down cold, as if MySQL had crashed; no committed transactions are lost, but the crash recovery operation makes the next startup take longer.

The slow shutdown can take minutes, or even hours in extreme cases where substantial amounts of data are still buffered. Use the slow shutdown technique before upgrading or downgrading between MySQL major releases, so that all data files are fully prepared in case the upgrade process updates the file format.

Use innodb_fast_shutdown=2 in emergency or troubleshooting situations, to get the absolute fastest shutdown if data is at risk of corruption.

The result of a shutdown will leave InnoDB in one of these three states, two of them requiring some work during crash recovery at startup.

The reason for the answer I posted is the OS. In my opinion, the Linux OS and the right hardware provide a better environment for disk writing and flushing. I don't have that same confidence with Windows. My suggestions are just to give mysqld additional advantages in Windows.

InnoDB can holistically shutdown. My personal preference is to use innodb_fast_shutdown=0 so I don't worry about the redo logs during maintenenaces or worry about the OS.