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 
my question was more related to the FiveM ESX database
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
try adding the parameter --log-error=C:\Users\admin\Documents\DBBACKUPS\error_file.log and see if any errors are logged to that file
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?
Yeah, the issue was that there were no .frm files, only .ibd and the main backup files, but MySQL Workbench/HeidiSQL didn’t recognize anything I tried to open, so this time around i’m just trying to take preventable measures.
I see, I was thinking of the batch script I posted in my replies earlier though, has that worked as root?
