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?
ok that seemed to have worked, I updated the file name to the one you originally posted and that seems to work without a problem. Any reason why I can’t just use this bat file instead of the original one you sent? I know the quick and lock tables parameters are there, are they that important to use?
Hey, I have just ran all the steps here on my local build running xampp this time instead of MySQL, everything goes well if you have assigned the correct permissions to the new user.
Pretty much the important part is
GRANT PROCESS ON *.* TO `newuser`@`localhost`;
GRANT SELECT ON `yourdatabase`.* TO `newuser`@`localhost`;
Where you change newuser and yourdatabase to actual names you’re going to use.
Any reason why I can’t just use this bat file instead of the original one you sent? I know the quick and lock tables parameters are there, are they that important to use?
Depends under which user you ran the dump, if you ran it as root then it would have succeeded with or without additional parameters, why the –single-transaction parameter is important is because if you don’t assign the “LOCK” permission, you’re gong to get an error.
You can use –quick if you’re exporting large databases, when it comes to active servers their database can be huge. This method will save information row by row instead of retrieving the whole content of the table.
–lock-tables is disabled when saving on a running server to avoid errors and ESX crapping its pants when it encounters a locked table. You can remove this parameter if you’re running a small server without a lot of SQL requests but I don’t recommend it.
By default, single-transaction will export only the snapshot of the InnoDB memory, meaning all additional changes will not be included in the dump.
Appreciate the details! As of right now it seems like it works for the time being. I’ll try re-adding a user with your instructions again and test it and get back to you with an update