Shopping Cart
11abac85b780a6bfb422bb414511bacab31e72a7

How to set up automatic database backups – Server Tutorials


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


gCNReK0


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


majOzlc

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 FilesMySQLMySQL Server 8.0bin
mysqldump -uexampleusername -pexamplepassword yourdatabase --single-transaction --quick --lock-tables=false > C:fiveMbackupfiveMbackup_%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)

f1JaaXW

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!



Premium ESX Scripts? Click here!

Leave a Reply
FiveM ESX Scripts

The best scripts and maps

Best support

Problems? We are there to help!

Wide range of products

We have a wide range of products

100% Secure Checkout

Easy and encrypted