{"id":213,"date":"2016-07-07T05:03:11","date_gmt":"2016-07-07T05:03:11","guid":{"rendered":"http:\/\/symbioticindia.in\/docu\/?p=213"},"modified":"2016-07-07T05:03:11","modified_gmt":"2016-07-07T05:03:11","slug":"mysql-database-back-on-command-prompt","status":"publish","type":"post","link":"http:\/\/symbioticindia.in\/docu\/2016\/07\/07\/mysql-database-back-on-command-prompt\/","title":{"rendered":"mYsql Database back on command prompt"},"content":{"rendered":"
@<\/span>echo <\/span>off<\/span>\r\nCLS\r\ncd c<\/span>:\\<\/span>temp\r\n<\/span>set<\/span> MYSQLUSER<\/span>=<\/span>root\r\n<\/span>set<\/span> MYSQLPASS<\/span>=<\/span>PassWord\r\n<\/span>set<\/span> BATCHFILE<\/span>=<\/span>c<\/span>:\\<\/span>temp<\/span>\\<\/span>Batch_mysqldump<\/span>.<\/span>bat \r\n<\/span>set<\/span> DUMPPATH<\/span>=<\/span>c<\/span>:\\<\/span>temp\r\n<\/span>SET<\/span> backuptime<\/span>=%<\/span>DATE<\/span>:~<\/span>4<\/span>,<\/span>2<\/span>%-%<\/span>DATE<\/span>:~<\/span>7<\/span>,<\/span>2<\/span>%-%<\/span>DATE<\/span>:~<\/span>10<\/span>,<\/span>4<\/span>%-%<\/span>TIME<\/span>:~<\/span>0<\/span>,<\/span>2<\/span>%-%<\/span>TIME<\/span>:~<\/span>3<\/span>,<\/span>2<\/span>%<\/span>\r\nSET<\/span> backuptimelog<\/span>=%<\/span>DATE<\/span>:~<\/span>4<\/span>,<\/span>2<\/span>%-%<\/span>DATE<\/span>:~<\/span>7<\/span>,<\/span>2<\/span>%-%<\/span>DATE<\/span>:~<\/span>10<\/span>,<\/span>4<\/span>%<\/span> %<\/span>TIME<\/span>:~<\/span>0<\/span>,<\/span>2<\/span>%:%<\/span>TIME<\/span>:~<\/span>3<\/span>,<\/span>2<\/span>%:%<\/span>TIME<\/span>:~<\/span>6<\/span>,<\/span>2<\/span>%<\/span>\r\necho starting MySqlDump at <\/span>%<\/span>backuptime<\/span>%<\/span>\r\necho <\/span>------ starting MySqlDump at %backuptimelog% ------   >> \"Z:\\-=macine backup=-\\sqldump\\sqldump.log\"<\/span>\r\necho Running <\/span>dump<\/span>...<\/span>   \r\nset<\/span> 7<\/span>zip_path<\/span>=<\/span>\r\nmkdir <\/span>\"%backuptime%\"<\/span> >><\/span> \"Z:\\-=macine backup=-\\sqldump\\sqldump.log\"<\/span>\r\ncd <\/span>\"c:\\Program Files\\MySQL\\MySQL Server 5.6\\bin\"<\/span>\r\necho <\/span>@<\/span>echo <\/span>off<\/span> ><\/span> %<\/span>BATCHFILE<\/span>%<\/span> \r\necho cd <\/span>%<\/span>DUMPPATH<\/span>%<\/span> >><\/span> %<\/span>BATCHFILE<\/span>%<\/span> \r\necho copy <\/span>\"C:\\Program Files\\MySQL\\MySQL Server 5.6\\bin\\mysqldump.exe\"<\/span> \"c:\\temp\\%backuptime%\"<\/span> >><\/span> %<\/span>BATCHFILE<\/span>%<\/span> \r\necho cd <\/span>\"%backuptime%\"<\/span> >><\/span> %<\/span>BATCHFILE<\/span>%<\/span> \r\nmysql <\/span>-<\/span>u<\/span>%<\/span>MYSQLUSER<\/span>%<\/span> -<\/span>p<\/span>%<\/span>MYSQLPASS<\/span>%<\/span> -<\/span>AN <\/span>-<\/span>e<\/span>\"SELECT CONCAT('mysqldump -u%MYSQLUSER% -p%MYSQLPASS% ' ,schema_name,' --result-file=',schema_name,'.sql') FROM information_schema.schemata WHERE schema_name NOT IN ('information_schema','performance_schema')\"<\/span> >><\/span> %<\/span>BATCHFILE<\/span>%<\/span> \r\necho <\/span>exit<\/span> >><\/span> %<\/span>BATCHFILE<\/span>%<\/span>\r\nstart<\/span> \/<\/span>wait <\/span>%<\/span>BATCHFILE<\/span>%<\/span> \r\necho Compressing bk_<\/span>%<\/span>backuptime<\/span>%.<\/span>sql<\/span>...<\/span>\r\nSET<\/span> ziptime<\/span>=%<\/span>DATE<\/span>:~<\/span>4<\/span>,<\/span>2<\/span>%-%<\/span>DATE<\/span>:~<\/span>7<\/span>,<\/span>2<\/span>%-%<\/span>DATE<\/span>:~<\/span>10<\/span>,<\/span>4<\/span>%<\/span> %<\/span>TIME<\/span>:~<\/span>0<\/span>,<\/span>2<\/span>%:%<\/span>TIME<\/span>:~<\/span>3<\/span>,<\/span>2<\/span>%:%<\/span>TIME<\/span>:~<\/span>6<\/span>,<\/span>2<\/span>%<\/span>\r\necho starting <\/span>7<\/span>zip compression at <\/span>%<\/span>ziptime<\/span>%<\/span>\r\necho starting <\/span>7<\/span>zip compression at <\/span>%<\/span>ziptime<\/span>%<\/span> >><\/span> \"Z:\\-=macine backup=-\\sqldump\\sqldump.log\"<\/span>\r\n\"C:\\Program Files\\7-Zip\\7z.exe\"<\/span> a <\/span>-<\/span>t7z <\/span>-<\/span>m0<\/span>=<\/span>PPMd <\/span>\"Z:\\-=macine backup=-\\sqldump\\bk_%backuptime%.7z\"<\/span> \"c:\\temp\\%backuptime%\"<\/span> >><\/span> \"Z:\\-=macine backup=-\\sqldump\\sqldump.log\"<\/span>\r\n echo Deleting the SQL <\/span>file<\/span> ...<\/span>   \r\n rmdir <\/span>\/<\/span>s <\/span>\/<\/span>q <\/span>\"c:\\temp\\%backuptime%\"<\/span> >><\/span> \"Z:\\-=macine backup=-\\sqldump\\sqldump.log\"<\/span>\r\n echo deleting files older than <\/span>60<\/span> days\r\n echo deleting files older than <\/span>60<\/span> days <\/span>>><\/span> \"Z:\\-=macine backup=-\\sqldump\\sqldump.log\"<\/span>\r\n forfiles <\/span>-<\/span>p <\/span>\"Z:\\-=macine backup=-\\sqldump\"<\/span> -<\/span>s <\/span>-<\/span>m <\/span>*.*<\/span> \/<\/span>D <\/span>-60<\/span> \/<\/span>C <\/span>\"cmd \/c del @path\"<\/span> >><\/span> \"Z:\\-=macine backup=-\\sqldump\\sqldump.log\"<\/span>\r\n SET<\/span> finishtime<\/span>=%<\/span>DATE<\/span>:~<\/span>4<\/span>,<\/span>2<\/span>%-%<\/span>DATE<\/span>:~<\/span>7<\/span>,<\/span>2<\/span>%-%<\/span>DATE<\/span>:~<\/span>10<\/span>,<\/span>4<\/span>%<\/span> %<\/span>TIME<\/span>:~<\/span>0<\/span>,<\/span>2<\/span>%:%<\/span>TIME<\/span>:~<\/span>3<\/span>,<\/span>2<\/span>%:%<\/span>TIME<\/span>:~<\/span>6<\/span>,<\/span>2<\/span>%<\/span>\r\n echo <\/span>------ Done at %finishtime%! ------ >> \"Z:\\-=macine backup=-\\sqldump\\sqldump.log\"<\/span>\r\n echo Done at <\/span>%<\/span>finishtime<\/span>%!<\/span><\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"
@echo off CLS cd c:\\temp set MYSQLUSER=root set MYSQLPASS=PassWord set BATCHFILE=c:\\temp\\Batch_mysqldump.bat set DUMPPATH=c:\\temp SET backuptime=%DATE:~4,2%-%DATE:~7,2%-%DATE:~10,4%-%TIME:~0,2%-%TIME:~3,2% SET backuptimelog=%DATE:~4,2%-%DATE:~7,2%-%DATE:~10,4% %TIME:~0,2%:%TIME:~3,2%:%TIME:~6,2% echo starting MySqlDump at %backuptime% echo —— starting MySqlDump at\u2026<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[13],"tags":[],"class_list":["post-213","post","type-post","status-publish","format-standard","hentry","category-mysql"],"_links":{"self":[{"href":"http:\/\/symbioticindia.in\/docu\/wp-json\/wp\/v2\/posts\/213","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/symbioticindia.in\/docu\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/symbioticindia.in\/docu\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/symbioticindia.in\/docu\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"http:\/\/symbioticindia.in\/docu\/wp-json\/wp\/v2\/comments?post=213"}],"version-history":[{"count":1,"href":"http:\/\/symbioticindia.in\/docu\/wp-json\/wp\/v2\/posts\/213\/revisions"}],"predecessor-version":[{"id":214,"href":"http:\/\/symbioticindia.in\/docu\/wp-json\/wp\/v2\/posts\/213\/revisions\/214"}],"wp:attachment":[{"href":"http:\/\/symbioticindia.in\/docu\/wp-json\/wp\/v2\/media?parent=213"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/symbioticindia.in\/docu\/wp-json\/wp\/v2\/categories?post=213"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/symbioticindia.in\/docu\/wp-json\/wp\/v2\/tags?post=213"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}