How to backup Windows Server Edition of MySQL/MariaDB

Summary

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

More Info

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

Inspection result
— Execute following command in a 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 then 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 script to run 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 script to run 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

?akb&p=2197

Table of Contents