mjhjmtu

All commands in debian9's interactive non-login shell (graphical mode).

I want to backup mysql before reboot or shutdown.

who 
test tty7 2018-02-01 18:26 (:0)
test@world:~$ pwd
/home/test

Now the following three commands can backup mysql database.

USERNAME="xxxx"
PASSWORD="yyyy"
mysqldump -u root -p$PASSWORD database > /home/test/wp.sql.bak

Create a service running before reboot or shutdown.

vim /etc/systemd/system/test.service 

[Unit]
Description=Run command at shutdown
Before=shutdown.target reboot.target

[Service]
Type=oneshot
RemainAfterExit=true
ExecStart=/bin/bash /home/test/test.sh

[Install]
WantedBy=multi-user.target

The test.sh file .

cat /home/test/test.sh
USERNAME="xxxx"
PASSWORD="yyyy"
mysqldump -u root -p$PASSWORD database > /home/test/wp.sql.bak

Enable the service.

sudo systemctl enable test.service
sudo reboot

Now log into interactive non-login shell (graphical mode).

sudo systemctl enable test.service
-- Logs begin at Thu 2018-02-01 18:26:04 HKT, end at Thu 2018-02-01 18:27:23 HKT. --
Feb 01 18:26:13 world systemd[1]: Starting Run command at shutdown...
Feb 01 18:26:18 world bash[480]: mysqldump: Got error: 2002: "Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2 "No such file or directory")"
Feb 01 18:26:19 world systemd[1]: test.service: Main process exited, code=exited, status=2/INVALIDARGUMENT
Feb 01 18:26:19 world systemd[1]: Failed to start Run command at shutdown.
Feb 01 18:26:19 world systemd[1]: test.service: Unit entered failed state.
Feb 01 18:26:19 world systemd[1]: test.service: Failed with result 'exit-code'

The three commands can run in terminal instead of containing in test.service,how to write a service backuping all mysql datadb when reboot or shutdown?

First, use ExecStop=/bin/bash /home/test/test.sh rather than ExecStart. See How to run a script with systemd right before shutdown? for more information. You appear to be running your script on startup, presumably before mysql is fully started.

Second, consider adding After=mariadb.service to be sure the script is run before mysql is shut down. Remember that, when considering shutdown order, everything is in reverse.

[Unit]
Description=Run command at shutdown
Before=shutdown.target reboot.target 
After=mariadb.service 

[Service]
Type=oneshot
RemainAfterExit=true
ExecStop=/bin/bash /home/test/test.sh

[Install]
WantedBy=multi-user.target

It is mariadb in debian9 instead of mysql.