How to backup Windows Server Edition of MySQL/MariaDB


Summary

In Windows Editoin of MySQL/MariaDB, it is necessary to stop the service by script and perform offline backup.


More Info

In the Windows editoin of MySQL command, there is a [--execute] option, and it can execute commands by passing arguments.
However, since [quit] is executed immediately after command execution, general queries can be executed, but it cannot use [flush tables with read lock] command to operate.


Inspection result
--- Execute following command in batch file ---

mysql -u root -p<password> -e "flush tables with read lock;"

It [quit] immediately after this command and it is unlocked before executing [unlock tables] and the insert query is committed.
--------------------
19 Query        flush tables with read lock
19 Quit
13 Query        insert into ORDERLIST(ITEM_ID, ORDER_DATE) values (9630, SYSDATE(3))
13 Query        update ORDERCOUNT set ORDER_COUNT = ORDER_COUNT + 1 where ITEM_ID = 9630
13 Query        commit
12 Query        select * from ITEMLIST where CATEGORY = 97
--------------------


--- Execute interactively from the mysql command prompt ---
 
> flush tables with read lock;

Even when an insert query is executed, it will never be committed.
--------------------
21 Query        flush tables with read lock
13 Query        select * from ITEMLIST where CATEGORY = 617
                        :
15 Query        select * from ITEMLIST where CATEGORY = 610
10 Query        insert into ORDERLIST(ITEM_ID, ORDER_DATE) values (8627, SYSDATE(3))
12 Query        select * from ITEMLIST where CATEGORY = 372
                        :
13 Query        select * from ITEMLIST where CATEGORY = 290
15 Query        insert into ORDERLIST(ITEM_ID, ORDER_DATE) values (24, SYSDATE(3))
  9 Query        select * from ITEMLIST where CATEGORY = 839
--------------------


--- Execute interactively from the mysql command prompt ---

> unlock tables;

It immediately unlocks and the update query is committed.
* However, the order is random.
--------------------
21 Query        unlock tables
14 Query        update ORDERCOUNT set ORDER_COUNT = ORDER_COUNT + 1 where ITEM_ID = 48046
11 Query        update ORDERCOUNT set ORDER_COUNT = ORDER_COUNT + 1 where ITEM_ID = 20164
16 Query        update ORDERCOUNT set ORDER_COUNT = ORDER_COUNT + 1 where ITEM_ID = 28458
15 Query        update ORDERCOUNT set ORDER_COUNT = ORDER_COUNT + 1 where ITEM_ID = 24
10 Query        update ORDERCOUNT set ORDER_COUNT = ORDER_COUNT + 1 where ITEM_ID = 8627
13 Query        update ORDERCOUNT set ORDER_COUNT = ORDER_COUNT + 1 where ITEM_ID = 42818
18 Query        update ORDERCOUNT set ORDER_COUNT = ORDER_COUNT + 1 where ITEM_ID = 14144
11 Query        commit
12 Query        update ORDERCOUNT set ORDER_COUNT = ORDER_COUNT + 1 where ITEM_ID = 37162
18 Query        commit
15 Query        commit
12 Query        commit
  9 Query        update ORDERCOUNT set ORDER_COUNT = ORDER_COUNT + 1 where ITEM_ID = 39381
14 Query        commit
--------------------

Therefore, you can not execute [flush tables with read lock] from a batch file and perform online backup.


Resolution

Create a batch file to Stop/Start the service.

- Set the following to the script before the snapshot
----------------sample----------------
@echo off
net stop <MySQL Service Name>

if %ERRORLEVEL% neq 0 (
  echo “Failed to stop MySQL Service”
   exit 1
) else (
   echo “Successful to stop MySQL Service”

---------------------------------------


- Set the following to the script after the snapshot
----------------sample----------------
@echo off
net start <MySQL Service Name>

if %ERRORLEVEL% neq 0 (
  echo “Failed to stop MySQL Service”
   exit 1
) else (
   echo “Successful to stop MySQL Service”

---------------------------------------


Target Products

- ActiveImage Protector 2016 / 2018 Windows

 

Tags: NJKB-123
Last update:
2018-11-05 08:12
Author:
Revision:
1.0
Average rating:0 (0 Votes)

You cannot comment on this entry

Chuck Norris has counted to infinity. Twice.

Records in this category

Tags