{"id":61,"date":"2017-10-21T18:02:40","date_gmt":"2017-10-21T18:02:40","guid":{"rendered":"https:\/\/standingtech.com\/randomarticles\/?p=61"},"modified":"2017-10-21T18:43:43","modified_gmt":"2017-10-21T18:43:43","slug":"automatically-backup-mysql-databases-on-windows","status":"publish","type":"post","link":"https:\/\/standingtech.com\/randomarticles\/automatically-backup-mysql-databases-on-windows\/","title":{"rendered":"Automatically Backup MySQL Databases on Windows"},"content":{"rendered":"

MySQL Command for backup all databases:<\/h3>\n

mysqldump -uUSERNAME -pPASSWORD –all-databases > “D:\/File_NAME_%date:~-4,4%-%date:~-10,2%-%date:~-7,2%-%time:~0,2%_%time:~3,2%_%time:~6,2%.sql”<\/strong><\/p>\n

Example:<\/strong><\/p>\n

Consider MySQL username = root and password = 123456<\/p>\n

mysqldump -uroot -p123456 –all-databases > “D:\/mysql_backup_%date:~-4,4%-%date:~-10,2%-%date:~-7,2%-%time:~0,2%_%time:~3,2%_%time:~6,2%.sql”<\/strong><\/p>\n

The result will be a file name (mysql_backup_2017-10-21-20_29_54.sql) containing all databases.<\/p>\n

MySQL Command for one database databases:<\/h3>\n

mysqldump -uroot -p123456 –databases mydatabasename<\/em> > “D:\/mysql_backup_%date:~-4,4%-%date:~-10,2%-%date:~-7,2%-%time:~0,2%_%time:~3,2%_%time:~6,2%.sql”<\/strong><\/p>\n

We replaced –all-databases with –databases databasename <\/strong><\/p>\n

Note the date format:<\/strong><\/p>\n

    \n
  • %date:~-4,4%: year 4 digits<\/li>\n
  • %date:~-10,2%: month 2 digits<\/li>\n
  • %date:~-7,2%: day 2 digits<\/li>\n
  • %time:~0,2%: time in 24 hours format<\/li>\n
  • %time:~3,2%: minutes 2 digits<\/li>\n
  • %time:~6,2%: seconds 2 digits<\/li>\n<\/ul>\n

    Now, after you customized the file name and date format, save your last command in file with (.bat) extension for examle:<\/p>\n

    mysqk-backup.bat<\/strong><\/p>\n

    And put the file in local C: drive inside windows or any where in safe place of which normal user will not delete it, and then<\/p>\n

      \n
    1. Press windows key + R to open Run windows and then type taskschd.msc
      \n\"Windows<\/strong><\/li>\n
    2. Once the Task Scheduler opened, Create New Task (located at the right sidebar) or go to Action<\/strong> menu and then Create Task
      \n\"Task<\/li>\n
    3. The task creation menu will open, first in General Tab, Fill name of the task (MySQL Database Backup – Daily)
      \n\"Task<\/li>\n
    4. Then Click on Triggers tab and Then New.. to create new trigger (This is where you set your schedule plan)
      \n\"Task<\/li>\n
    5. Then Click Ok, and now go to Actions tab
      \n\"Task<\/li>\n
    6. Then Click on New.. once the window opened, browser to your file that we mentioned above, in our example we used (mysqk-backup.bat)
      \n<\/strong>\"Task<\/li>\n
    7. Then click ok!, and again Click Ok to save everything.<\/li>\n
    8. You can now right click on this task and click run to test if everything works fine.<\/li>\n<\/ol>\n","protected":false},"excerpt":{"rendered":"

      MySQL Command for backup all databases: mysqldump -uUSERNAME -pPASSWORD –all-databases > “D:\/File_NAME_%date:~-4,4%-%date:~-10,2%-%date:~-7,2%-%time:~0,2%_%time:~3,2%_%time:~6,2%.sql” Example: Consider MySQL username = root and password = 123456 mysqldump -uroot -p123456 –all-databases > “D:\/mysql_backup_%date:~-4,4%-%date:~-10,2%-%date:~-7,2%-%time:~0,2%_%time:~3,2%_%time:~6,2%.sql” The result will be a file name (mysql_backup_2017-10-21-20_29_54.sql) containing all databases. MySQL Command for one database databases: mysqldump -uroot -p123456 –databases mydatabasename > “D:\/mysql_backup_%date:~-4,4%-%date:~-10,2%-%date:~-7,2%-%time:~0,2%_%time:~3,2%_%time:~6,2%.sql” We replaced –all-databases…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[10,5,9],"tags":[17,16,19,20,18],"class_list":["post-61","post","type-post","status-publish","format-standard","hentry","category-database","category-security","category-web-design","tag-backup","tag-mysql","tag-mysql-command","tag-task-scheduler","tag-windows-schedule"],"_links":{"self":[{"href":"https:\/\/standingtech.com\/randomarticles\/wp-json\/wp\/v2\/posts\/61","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/standingtech.com\/randomarticles\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/standingtech.com\/randomarticles\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/standingtech.com\/randomarticles\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/standingtech.com\/randomarticles\/wp-json\/wp\/v2\/comments?post=61"}],"version-history":[{"count":5,"href":"https:\/\/standingtech.com\/randomarticles\/wp-json\/wp\/v2\/posts\/61\/revisions"}],"predecessor-version":[{"id":73,"href":"https:\/\/standingtech.com\/randomarticles\/wp-json\/wp\/v2\/posts\/61\/revisions\/73"}],"wp:attachment":[{"href":"https:\/\/standingtech.com\/randomarticles\/wp-json\/wp\/v2\/media?parent=61"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/standingtech.com\/randomarticles\/wp-json\/wp\/v2\/categories?post=61"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/standingtech.com\/randomarticles\/wp-json\/wp\/v2\/tags?post=61"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}