How to set up automatic database backups

[greenie]

Today I will be showing how to set up automatic database backup without installing additional scripts to your FiveM server.

This method has only been tested on Windows but the thread contains a LINUX CRON job guide, feedback is appreciated.

Required

Server with remote access ( Remote desktop connection for Windows, Putty for Linux )
MySQL [ Windows / Linux ]
SQL client ( HeidiSQL )
Notepad++ or any other editing tool
Used in this example

Windows server 2019
MySQL 8.0
Log in to your SQL server using your SQL client, in this case I will be using HeidiSQL



When Heidi connects to the server, select “Query” tab.
It should be the first tab to the right of the server tab.



Copy&Paste the query below into the text field and then change ‘username’, ‘password’ and ‘yourdatabase’ to values you want.


CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
GRANT PROCESS ON *.* TO `newuser`@`localhost`;
GRANT SELECT ON `yourdatabase`.* TO `newuser`@`localhost`;
Hit F9 or the blue play button to run your query.

Create a new folder called “fiveMbackup” in the root of your C: drive (C:\fiveMbackup) or anywhere you want (you will have to change parameters in code below).

Scroll down if you are using Linux.

Open N++, create a new file, add:

cd C:\Program Files\MySQL\MySQL Server 8.0\bin
mysqldump -uexampleusername -pexamplepassword yourdatabase --single-transaction --quick --lock-tables=false > C:\fiveMbackup\fiveMbackup_%date:~-10,2%-%date:~-7,2%-%date:~-4,4%.sql
QUIT
Do not remove -u and -p from uexampleusername -pexamplepassword!
Change only the text after -u and -p!

Save as .bat inside the folder you created.

Test your .bat file, if everything is working right you should see a new file in the folder called “fiveMbackup_MM_DD_YYYY.sql”.

WINDOWS
Open Task Sheduler and create a new basic task (follow steps on .GIF below)

LINUX (source)

Open terminal and type:

sudo tcsh
nano /etc/crontab
Add the following line will schedule the backup at 1 AM each day, change the parameters to the ones you will be using.

0 1 * * * /usr/local/mysql/bin/mysqldump -uroot -ppassword --opt database > /path/to/directory/filename.sql