Jump to content

[TUTORIAL] Database Backups


CptGooch
 Share

Recommended Posts

MySQL Auto-Backup

Summary:

This tutorial will show you step by step how to setup automated backups of your MySQL DB every 6 hours on a Windows Server.  I will assume your are using MySQL 5.7x64 and Workbench 6.3, but you can adapt this guide to MariaDB and Heidi with the same code.

 

Tutorial:

  1. Login to your dedicated server.
  2. Download my SQL Backup Zip MySQLBackups.zip
  3. Extract the zip file to C:\
  4. Open MySQL Workbench, and login as root.
  5. Click on users and privileges:sql1.png.afeb925deb3a2aafcc0f07eb0a17f33b.png
  6. Create a new user called backupadmin:sql2.png.72c59c5819cb2f627eb9401bded98fe5.png
  7. Click on administrative roles, and give our newly created backupadmin user the BackupAdmin role:sql3.png.57052461c4b1475cf3613a24e113e845.png
  8. Now open the MySQLBackups folder you extracted to C:\
  9. Edit mysqlbackup.bat in your preferred editing program.
  10. Line 43: Change the password to the password you gave backupadmin.  Save and Close.
  11. Press windows key and R to bring up the run prompt and type Taskschd.msc.
  12. Click create task:sql4.png.00c8ab2769dbd24fa5a0204cfbd82646.png
  13. End a title and for the task:sql5.png.46de1dbe43a5fe83e5af328c76f85011.png
  14. Go to the trigger tab, and select new.  Select a start time of your choosing,and set the repeat to 6 hours:sql6.png.8854ae5cc83af514763b8a0e7d86b602.png.
  15. Go to the actions tab and brows to C:\MySQLBackups\mysqlbackup.bat:sql7.png.90ab08e739d6fcf3af54ff9434ce0531.png
  16. All Done!  Now your DB will backup every 6 hours, and keep those backups for 7 days.
  • Like 4
  • Thanks 1
Link to comment
Share on other sites

  • 1 month later...

Changed the code to:

set year=%date:~-4,4%
set day=%date:~-10,2%
set mnt=%date:~-7,2%
set hr=%time:~0,2%
set min=%time:~3,2%
 
IF %day% LSS 10 SET day=0%day:~1,1%
IF %mnt% LSS 10 SET mnt=0%mnt:~1,1%
IF %hr% LSS 10 SET hr=0%hr:~1,1%
IF %min% LSS 10 SET min=0%min:~1,1%
 
set backuptime=%day%-%mnt%-%year%-%hr%-%min%
echo %backuptime%

working fine now

Image%202017-03-26%20at%207.48.09%20PM.p

Edited by T3RRORTOAST
  • Like 1
Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.