How to set up automatic database backups

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

Helpful CRON documentation.


That’s it, you’re done!
You can test if your task works by right clicking the task in Task Scheduler and clicking “Run”.

Thank you for reading, if you need help with setting this up please post a reply in the thread!

3 Likes

do you have a specific reason not to lock the table during the SQL dump?

grtz,
Dutch

Some servers have tables that are constantly being updated (or if dump is ran at high traffic), locking the table during the dump refused some important sql queries while it was running.

Also large database dumps take time. Some may be inconsistent but it’s nothing that can’t be fixed by editing the SQL file.

I’ve been using this method for a while now without ever being unable to retrieve the dumped data.

yes I understand the theory :slight_smile: my question was more related to the FiveM ESX database :slight_smile:
So is it necessary for that specific database?
I am making a backup for my database and the .sql file is almost 4MB (not that big I guess), but I have never experienced an error in the backup, that is why I was curious if you set that parameter based on negative experience.

grtz,
Dutch

I wouldn’t say it’s necessary, it all depends on how your server addons are set up and when you’re executing the dump.

You can lock it but keep in mind most queries sent to the SQL server will be rejected during the dump (INSERT, DELETE, UPDATE statements) if the dump is at that table at the time, I keep it turned off due to economy and discord addons that constantly update a table.

Hi

I just get an empty .sql file

heres my .bat

cd C:\Program Files\MySQL\MySQL Server 8.0\bin
mysqldump -root - rlrp --single-transaction --quick --lock-tables=false > C:\Users\admin\Documents\DBBACKUPS\fiveMbackup_%date:~-10,2%-%date:~-7,2%-%date:~-4,4%.sql
QUIT

hi, thanks for helping.

but there was no password.

setup a new user and tried this, but no joy

cd C:\Program Files\MySQL\MySQL Server 8.0\bin
mysqldump -uBackup -ptesttest rlrp --single-transaction --quick --lock-tables=false > C:\Users\admin\Documents\DBBACKUPS\fiveMbackup_%date:~-10,2%-%date:~-7,2%-%date:~-4,4%.sql
QUIT

rlrp is the database

Please follow the instructions in the original post.

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
GRANT PROCESS ON *.* TO `newuser`@`localhost`;
GRANT SELECT ON `yourdatabase`.* TO `newuser`@`localhost`;

Use that user in your .bat script.

Check this reply, I noticed you’re using root for this, making the “fix” I originally posted obsolete.

No worries,

repeated the steps again

cd C:\Program Files\MySQL\MySQL Server 8.0\bin
mysqldump -udbbackup -ppassword rlrp --single-transaction --quick --lock-tables=false > C:\Users\admin\Documents\DBBACKUPS\fiveMbackup_%date:~-10,2%-%date:~-7,2%-%date:~-4,4%.sql
QUIT

still returns just an empty .sql

image

try adding the parameter --log-error=C:\Users\admin\Documents\DBBACKUPS\error_file.log and see if any errors are logged to that file

Please do what @dutchraf said and post results.

I’m having the same issue as Spooner, I’ve tried logging the error file but that file doesn’t create, so I’m assuming there are no errors? All I’m getting is an empty SQL file.

This is what my .bat file reads;

cd C:\xampp\mysql\bin mysqldump -ubackup -ppassword main --single-transaction --quick --lock-tables=false > C:\FiveMDBBackups\fiveMbackup_%date:~-10,2%-%date:~-7,2%-%date:~-4,4%.sql --log-error=C:\FiveMDBBackups\error_file.log

(obviously the password i have set)

Any ideas?

Can you do this and see if it populates?

cd C:\xampp\mysql\bin
mysqldump -ubackup -ppassword databasename > C:\xampp\BackupTest.sql

I just did it on my local xampp and it works, if you are using xampp on a windows server I recommend changing that asap.

For some reason it’s still not populating. This time it’s not empty, as it shows MariaDP information with commented out time zone settings and such, but still empty. When you say ‘databasename’, in my case my database is called ‘main’, is that what I would enter there? That’s what I have and it’s not populating.

You also mentioned to change it asap, are you referring to not using xampp? I’ve been using MySQL natively for a year and it corrupted my files 2 days ago, so this was the solution I went with instead.

Appreciate your help!

Yeah in your case ‘databasename’ would be ‘main’.

Does the account have rights to the database?

Regarding xampp on server; xampp is not meant to be run in a production environment, now if you know how to set it up correctly and patch all the security flaws you’re good but straight out of the box xampp will expose some aspects of your server.

How has native MySQL broken your files? I’ve been using MySQL 8.0 on all by server builds running fivem and none had issues, I generally try to avoid Microsoft SQL but MySQL has proven good in my case.

I did give my user the proper rights when doing this, but I might have done something wrong. I followed the exact query you posted.

What happened with MySQL is that I had to restart the server because of a change I had to make to the my.ini (lift restrictions for new tables, something I was adding MySQL didn’t like). This was a very simple process, but upon restarting the service, MySQL didn’t start back up. All files from the folders were corrupted, tried recovering in many different ways.

When I tried to completely re-instal MySQL, the installer wasn’t working, wouldn’t want to install the server, so I went with a solution that worked, which was xampp

But back to this issue here, can I use the root account to do this, which would theoretically have all access? Would that work?

You could in theory use root and it would work, yes.

Give it a shot with root and report back.

What happened with MySQL is that I had to restart the server because of a change I had to make to the my.ini

That is some strange behaviour, did you restart the service using Service Manager or cmd?

Was MySQL listed as a service like in the picture above?

I used the Services window as in your screenshot, and I had the exact same thing, MySQL80, I hit restart, and when it tried to boot back up it said it “started and shut down again” due to an error. I’ve been up and down stackoverflow and none of the resolutions worked for me unfortunately

That’s a new one for me, I’m yet to run into an issue with MySQL like that, sorry you had a bad experience with it.

Have you tried running the backup as root?