1

I have all codes that I need to do a backup (lvm snapshot) for our MariaDB Database on Linux. These are my commands in terminal:

mysql
flush tables read lock;                        (in database)
exit
lvc create -l100%free -s -n mdbsnap /dev/vgm/lv
mysql
unlock tables;                                 (in database)
exit

And it works.

But right now I'm trying to automate the processes with Linux bash:

#!/bin/bash
mysql -e "flush tables with read lock"
lvc create -l100%free -s -n mdbsnap /dev/vgm/lv
mysql -e "unlock tables"

and it doesn't work (the locking doesn't work and the database doensn't stay locked). I think this is because I should keep my mysql connection open to force our database stay locked during snapshot. Does anyone have any idea?

4
  • I would not trust it without shutting down mysqld.
    – Rick James
    Jan 27, 2022 at 22:54
  • It is impossible for us to shut the database down.
    – TimLer
    Jan 29, 2022 at 11:12
  • A tornado/flood/earthquake/etc will fix that problem for you. Meanwhile, have you tested the snapshot (on another server) to see that is not corrupted?
    – Rick James
    Jan 29, 2022 at 15:24
  • No. But we wanna do an extra step for test. After snapshot we mount it and zip all mounted data and unmount it again. It can reduce locking time of DB.
    – TimLer
    Jan 31, 2022 at 13:53

1 Answer 1

1

With the help of another stackexchange's question I found the answer:

#!/bin/bash
echo
{
echo "
flush tables with read lock;
system lvccreate -l100%free -s -n mdbsnap /dev/vgm/lv;
"
}  | mysql --unbuffered 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.