CUTCODEDOWN
Minimalist Semantic Markup

Welcome Guest
Please Login or Register

If you have registered but not recieved your activation e-mail in a reasonable amount of time, or have issues with using the registration form, please use our Contact Form for assistance. Include both your username and the e-mail you tried to register with.

Author Topic: MySql backup - Permissions?  (Read 2700 times)

GrumpyYoungMan

  • Hero Member
  • *****
  • Posts: 787
  • Karma: +8/-0
    • Grumpy Young Man
MySql backup - Permissions?
« on: 13 Sep 2020, 05:46:51 am »
I want to create a automatic MySQL database backup, but can’t seem to fine a definite list of required permissions so what permissions are required just to backup all the databases? And what would be the best way to do this on a Windows 10 machine?  I was just going to create a batch file?
Trying to learn a new trick to prove old dogs can learn new ones...

Total Novice have-a go Amateur Programmer - not sure that is the right thing to say... but trying to learn...

GrumpyYoungMan

  • Hero Member
  • *****
  • Posts: 787
  • Karma: +8/-0
    • Grumpy Young Man
Re: MySql backup - Permissions?
« Reply #1 on: 15 Sep 2020, 04:15:50 am »
This is the batch file I created to back up the mySQL databases:
Code: [Select]
@echo off

cls

set CUR_YYYY=%date:~6,4%
set CUR_MM=%date:~3,2%
set CUR_DD=%date:~0,2%

set SUBFILENAME=%CUR_YYYY%%CUR_MM%%CUR_DD%

cd c:\Program Files\MariaDb*\bin

mysqldump -u[USERNAME] -p[PASSWORD] --all-databases > c:\Backups\SQL\db_%SUBFILENAME%.sql

tar -acf c:\Backups\SQL\db_%SUBFILENAME%.zip c:\Backups\SQL\db_%SUBFILENAME%.sql

forfiles -p "c:\Backups\SQL" -s -m *.sql -d 15 -c "cmd /c del *.sql"

move "c:\Backups\SQL\db_%SUBFILENAME%.zip" "[PATH]"

forfiles -p "[PATH]" -s -m *.zip -d 15 -c "cmd /c del *.zip"

Code: [Select]
CREATE USER 'backupuser'@'localhost'
    IDENTIFIED BY 'p455w0rd';
Code: [Select]
GRANT SELECT, SHOW VIEW, LOCK TABLES, RELOAD,
    REPLICATION CLIENT
    ON *.* TO 'backupuser'@'localhost';
Code: [Select]
FLUSH PRIVILEGES;
I followed this guide on how to create a read only mySQL backup user.
« Last Edit: 15 Sep 2020, 08:58:51 am by GrumpyYoungMan »
Trying to learn a new trick to prove old dogs can learn new ones...

Total Novice have-a go Amateur Programmer - not sure that is the right thing to say... but trying to learn...

Jason Knight

  • Administrator
  • Hero Member
  • *****
  • Posts: 1049
  • Karma: +188/-1
    • CutCodeDown -- Minimalist Semantic Markup
Re: MySql backup - Permissions?
« Reply #2 on: 15 Sep 2020, 09:45:41 am »
How are you automating that? Task scheduler?

I would have given you an answer, but it would have been "sh" based setting up a cron job. I've never actually done this from Windows because I've always found using Windows as a server to be as big a steaming pile of manure as trying to use Linux as a desktop OS.

We are all, we are all, we are all FRIENDS! For today we're all brothers, tonight we're all friends. Our moment of peace in a war that never ends.

GrumpyYoungMan

  • Hero Member
  • *****
  • Posts: 787
  • Karma: +8/-0
    • Grumpy Young Man
Re: MySql backup - Permissions?
« Reply #3 on: 15 Sep 2020, 10:00:37 am »
How are you automating that? Task scheduler?

Yes, Task Scheduler! :)

I would have given you an answer, but it would have been "sh" based setting up a cron job. I've never actually done this from Windows because I've always found using Windows as a server to be as big a steaming pile of manure as trying to use Linux as a desktop OS.
Thank you - and I totally agree. But my Linux box died but I actually do need to (re)build a Linux server again
« Last Edit: 15 Sep 2020, 10:23:42 am by GrumpyYoungMan »
Trying to learn a new trick to prove old dogs can learn new ones...

Total Novice have-a go Amateur Programmer - not sure that is the right thing to say... but trying to learn...

GrumpyYoungMan

  • Hero Member
  • *****
  • Posts: 787
  • Karma: +8/-0
    • Grumpy Young Man
Re: MySql backup - Permissions?
« Reply #4 on: 16 Sep 2020, 05:24:20 am »
Would you recommend staying away from CentOs, Jason?

Trying to learn a new trick to prove old dogs can learn new ones...

Total Novice have-a go Amateur Programmer - not sure that is the right thing to say... but trying to learn...

Jason Knight

  • Administrator
  • Hero Member
  • *****
  • Posts: 1049
  • Karma: +188/-1
    • CutCodeDown -- Minimalist Semantic Markup
Re: MySql backup - Permissions?
« Reply #5 on: 16 Sep 2020, 09:33:04 am »
Would you recommend staying away from CentOs, Jason?
I'm not a fan. It's built on Red Hat legacy which, well... doesn't put many of the files where they belong in a *nix-like. Red Hat and their 'enterprise" versions were always a bit of a scam, and I distrust it.

RPM's suck compared to DEB's, YUM sucks compared to APT, etc, etc.

It's why I went with Debian for so long, and only switched this hosting over to Ubuntu because I wanted more recent packages such as PHP 7 "out of the box". What with Debian still only coming with PHP 5.6 and making you compile your own to get PHP 7 working. Admittedly, still compiling my own for testing and using FPM makes that a lot simpler to switch between, but it's still nice to know that things like the back-end panel are at least on a version of PHP that's still getting security updates.

Which is stupid, Debian is supposed to be focused on security and stability, but they're leaned so hard on the latter it's sacrificing the former.

For most of my hosting I just follow one of howtoforge's tutorials. Right now I'd suggest Ubuntu 18.04 with ISPConfig since the latter doesn't support  the newer Ubuntu flavor yet unless you want to run the Beta. Whilst I like having bleeding edge, don't deploy beta's!

Besides when it's ready, it's not like you can't upgrade-in-place.

https://www.howtoforge.com/tutorial/perfect-server-ubuntu-18.04-with-apache-php-myqsl-pureftpd-bind-postfix-doveot-and-ispconfig/2/

Oh, you mentioned building a linux box... if this is for doing web stuff and hosting, just get a $10 VPS. Might I suggest:

https://afterburst.com/unmetered-vps

Even the $10 plan is overkill for most people. This site is on the $30 plan which is batshit over-the-top but leaves me room to tinker.

Just beware such plans are unmanaged. Their back end will bring the OS you choose up to JUST the point you can SSH into it. From there configuration and installation is up to you.
We are all, we are all, we are all FRIENDS! For today we're all brothers, tonight we're all friends. Our moment of peace in a war that never ends.

benanamen

  • Full Member
  • ***
  • Posts: 188
  • Karma: +18/-0
Re: MySql backup - Permissions?
« Reply #6 on: 16 Sep 2020, 11:55:03 am »
Quote
Would you recommend staying away from CentOs

I have only ever had problems with CentOs. Aside from that, the "current versions" are always several years behind the real current versions of things such as Php.
To save time, let's just assume I am never wrong.

GrumpyYoungMan

  • Hero Member
  • *****
  • Posts: 787
  • Karma: +8/-0
    • Grumpy Young Man
Re: MySql backup - Permissions?
« Reply #7 on: 16 Sep 2020, 12:31:46 pm »
To be honest I alway got in Okay with CentOs, but what version would you recommend? With or without a GUI?
Trying to learn a new trick to prove old dogs can learn new ones...

Total Novice have-a go Amateur Programmer - not sure that is the right thing to say... but trying to learn...

benanamen

  • Full Member
  • ***
  • Posts: 188
  • Karma: +18/-0
Re: MySql backup - Permissions?
« Reply #8 on: 16 Sep 2020, 02:09:57 pm »
I would suggest you use Debian.
To save time, let's just assume I am never wrong.

Jason Knight

  • Administrator
  • Hero Member
  • *****
  • Posts: 1049
  • Karma: +188/-1
    • CutCodeDown -- Minimalist Semantic Markup
Re: MySql backup - Permissions?
« Reply #9 on: 17 Sep 2020, 12:05:08 am »
To be honest I alway got in Okay with CentOs, but what version would you recommend? With or without a GUI?
It's a server, why the hell would you install the bloated train wreck of an X11 implementation and the crippled WM's?
We are all, we are all, we are all FRIENDS! For today we're all brothers, tonight we're all friends. Our moment of peace in a war that never ends.

GrumpyYoungMan

  • Hero Member
  • *****
  • Posts: 787
  • Karma: +8/-0
    • Grumpy Young Man
Re: MySql backup - Permissions?
« Reply #10 on: 17 Sep 2020, 01:04:29 am »
To be honest I alway got in Okay with CentOs, but what version would you recommend? With or without a GUI?
It's a server, why the hell would you install the bloated train wreck of an X11 implementation and the crippled WM's?
I don’t use a GUI... I was asking should I be using one... I have my answer...

Thanks :)

Oh it’s a local offline test environment, I (currently) have my own DS for the live stuff...
Trying to learn a new trick to prove old dogs can learn new ones...

Total Novice have-a go Amateur Programmer - not sure that is the right thing to say... but trying to learn...

GrumpyYoungMan

  • Hero Member
  • *****
  • Posts: 787
  • Karma: +8/-0
    • Grumpy Young Man
Re: MySql backup - Permissions?
« Reply #11 on: 17 Sep 2020, 01:05:31 am »
I would suggest you use Debian.
Not Ubuntu?
Trying to learn a new trick to prove old dogs can learn new ones...

Total Novice have-a go Amateur Programmer - not sure that is the right thing to say... but trying to learn...

Jason Knight

  • Administrator
  • Hero Member
  • *****
  • Posts: 1049
  • Karma: +188/-1
    • CutCodeDown -- Minimalist Semantic Markup
Re: MySql backup - Permissions?
« Reply #12 on: 17 Sep 2020, 03:43:44 am »
Oh it’s a local offline test environment, I (currently) have my own DS for the live stuff...
Rather than build a second box, why not just run Linux in a VM if it's for testing? That's what I do. Lets me make a 1:1 clone of the OS install on my VPS account I can screw with, restore to baseline with no effort, etc.

VirtualBox is your friend!
We are all, we are all, we are all FRIENDS! For today we're all brothers, tonight we're all friends. Our moment of peace in a war that never ends.

Jason Knight

  • Administrator
  • Hero Member
  • *****
  • Posts: 1049
  • Karma: +188/-1
    • CutCodeDown -- Minimalist Semantic Markup
Re: MySql backup - Permissions?
« Reply #13 on: 17 Sep 2020, 03:47:36 am »
I would suggest you use Debian.
I used to say that, but the past three or four years it's really been dropping the ball because their "we only use stable proven packages" has resulted in lagging behind on SECURITY PATCHES. Kind of defeats the point of Debian.

I swear it's been on the rot ever since Ian left us.
We are all, we are all, we are all FRIENDS! For today we're all brothers, tonight we're all friends. Our moment of peace in a war that never ends.

GrumpyYoungMan

  • Hero Member
  • *****
  • Posts: 787
  • Karma: +8/-0
    • Grumpy Young Man
Re: MySql backup - Permissions?
« Reply #14 on: 17 Sep 2020, 07:13:52 am »
I would suggest you use Debian.
I used to say that, but the past three or four years it's really been dropping the ball because their "we only use stable proven packages" has resulted in lagging behind on SECURITY PATCHES. Kind of defeats the point of Debian.

I swear it's been on the rot ever since Ian left us.
I have always got on with CentOS, so what would you suggest? Should I install CentOs 8?
Trying to learn a new trick to prove old dogs can learn new ones...

Total Novice have-a go Amateur Programmer - not sure that is the right thing to say... but trying to learn...

 

SMF spam blocked by CleanTalk

Advertisement