{"id":5782,"date":"2017-06-03T12:17:54","date_gmt":"2017-06-03T04:17:54","guid":{"rendered":"http:\/\/inertz.com\/blog\/?p=5782"},"modified":"2020-02-24T11:06:53","modified_gmt":"2020-02-24T03:06:53","slug":"shell-script-untuk-backup-mysql-dan-innodb-bagi-cpanel-server","status":"publish","type":"post","link":"https:\/\/inertz.com\/blog\/shell-script-untuk-backup-mysql-dan-innodb-bagi-cpanel-server\/","title":{"rendered":"Shell script untuk backup MySQL dan InnoDB bagi Cpanel server"},"content":{"rendered":"

\"mysql\"<\/a>
\nShell script <\/a>yang aku biasa pakai untuk backup MySQL database ataupun InnoDB database untuk kegunaan sendiri ataupun sesiapa yang memerlukan.  Kalau setakat nak dump 1-2 database command ‘mysqldump’ dah cukup. Tapi macam mana pulak yang ada beratus database dalam 1 server. Penat la nak dump semua kalau tak ada script yang berguna macam ni.<\/p>\n

Since aku dah biasa pakai script ni memang memudahkan kerja untuk dump semua database sekaligus. Database akan disimpan dalam folder yang kita define sendiri dalam bentuk *.sql.<\/p>\n

Disertakan juga script untuk backup database InnoDB SAHAJA. Ianya berguna jikalau InnoDB crash dah menyebabkan MySQL database server down.  Ianya boleh digunakan kemudiannya untuk recreate balik ibdata dengan ib_logfile. Turut disertakan script untuk restore database. Script ini perlu diletak dalam folder output(dimana database backup diletak). Pastikan juga folder backup tersebut dibuat dahulu. Pastikan juga bash script ada executable permission.<\/p>\n

Script juga boleh pakai untuk server yang tak ada Cpanel.<\/p>\n

[bash]
\n#Script to backup whole MySQL database. Remove comment if not root and need to define password.
\n#!\/bin\/bash<\/p>\n

#USER=”root”
\n#PASSWORD=”n8=?JBw*oJqlG,DH”
\nOUTPUT=”\/mysqlbackup\/”<\/p>\n

rm “$OUTPUT\/*sql” > \/dev\/null 2>&1<\/p>\n

#databases=`mysql -u $USER -p$PASSWORD -e “SHOW DATABASES;” | tr -d “| ” | grep -v Database`
\ndatabases=`mysql -e “SHOW DATABASES;” | tr -d “| ” | grep -v Database`<\/p>\n

for db in $databases; do
\nif [[ “$db” != “information_schema” ]] && [[ “$db” != _* ]] ; then
\necho “Dumping database: $db”
\n# mysqldump –user=$USER –password=$PASSWORD –databases $db > $OUTPUT\/$db.sql
\nmysqldump –databases $db > $OUTPUT\/$db.sql<\/p>\n

echo “Sleep for 10 seconds”
\nsleep 10
\necho “wakeup and run again”
\nfi
\ndone
\n[\/bash]<\/p>\n

Script untuk backup database InnoDB<\/p>\n

[bash]
\n#Script to backup InnoDB database. Remove comment if not root and need to define password.
\n#!\/bin\/bash<\/p>\n

#USER=”root”
\n#PASSWORD=”07d9ukwxbiq7″
\nOUTPUT=”\/mysqlinnodb\/”<\/p>\n

rm “$OUTPUT\/*sql” > \/dev\/null 2>&1<\/p>\n

databases=`mysql -N mysql -e “SELECT table_schema, table_name FROM INFORMATION_SCHEMA.TABLES WHERE engine = ‘innodb’;” | awk ‘{print $1}’ | sort | uniq`<\/p>\n

for db in $databases; do
\nif [[ “$db” != “information_schema” ]] && [[ “$db” != _* ]] ; then
\necho “Dumping database: $db”
\n# mysqldump –user=$USER –password=$PASSWORD –databases $db > $OUTPUT\/$db.sql
\nmysqldump –databases $db > $OUTPUT\/$db.sql
\necho “Sleep for 10 seconds”
\nsleep 10
\necho “wakeup and run again”
\nfi
\ndone
\n[\/bash]<\/p>\n

Script untuk restore perlu diletak dalam folder dimana database backup diletak.<\/p>\n

[bash]
\n#Script to restore database. Remove comment if not root and need to define password.
\n#!\/bin\/bash<\/p>\n

# USER=”root”
\n# PASSWORD=”07d9ukwxbiq7″<\/p>\n

echo “Restoring Databases….”<\/p>\n

for SQL in *.sql
\ndo
\nDB=${SQL\/\\.sql\/}
\necho restoring $DB
\n# mysql -u $USER -p$PASSWORD $DB < $SQL
\nmysql $DB < $SQL<\/p>\n

echo Done $DB >> Databaserestore.txt
\nsleep 5
\ndone
\necho “……Complete ALL DB!”
\n[\/bash]<\/p>\n","protected":false},"excerpt":{"rendered":"

Shell script yang aku biasa pakai untuk backup MySQL database ataupun InnoDB database untuk kegunaan sendiri ataupun sesiapa yang memerlukan. Kalau setakat nak dump 1-2 database command \u2018mysqldump\u2019 dah cukup. Tapi macam mana pulak yang ada beratus database dalam 1 server. Penat la nak dump semua kalau tak ada script yang berguna macam ni.
\nSince aku dah biasa pakai script ni memang memudahkan kerja untuk dump<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9,18],"tags":[4476,14,4477,4270,4478],"class_list":["post-5782","post","type-post","status-publish","format-standard","hentry","category-linux","category-server","tag-backup","tag-bash","tag-innodb","tag-mysql","tag-shell-script"],"yoast_head":"\nShell script untuk backup MySQL dan InnoDB bagi Cpanel server - https:\/\/inertz.com\/blog<\/title>\n<meta name=\"description\" content=\"Shell script yang aku biasa pakai untuk backup MySQL database ataupun InnoDB database untuk kegunaan sendiri ataupun sesiapa yang memerlukan.\u00a0 Kalau setakat\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/inertz.com\/blog\/shell-script-untuk-backup-mysql-dan-innodb-bagi-cpanel-server\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Shell script untuk backup MySQL dan InnoDB bagi Cpanel server - https:\/\/inertz.com\/blog\" \/>\n<meta property=\"og:description\" content=\"Shell script yang aku biasa pakai untuk backup MySQL database ataupun InnoDB database untuk kegunaan sendiri ataupun sesiapa yang memerlukan.\u00a0 Kalau setakat\" \/>\n<meta property=\"og:url\" content=\"https:\/\/inertz.com\/blog\/shell-script-untuk-backup-mysql-dan-innodb-bagi-cpanel-server\/\" \/>\n<meta property=\"og:site_name\" content=\"https:\/\/inertz.com\/blog\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/facebook.com\/inertz\" \/>\n<meta property=\"article:author\" content=\"https:\/\/web.facebook.com\/inertz\" \/>\n<meta property=\"article:published_time\" content=\"2017-06-03T04:17:54+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2020-02-24T03:06:53+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/inertz.com\/blog\/wp-content\/uploads\/2016\/11\/mysql.jpg\" \/>\n<meta name=\"author\" content=\"admin\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@https:\/\/twitter.com\/inertz\" \/>\n<meta name=\"twitter:site\" content=\"@inertz\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"admin\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Shell script untuk backup MySQL dan InnoDB bagi Cpanel server - https:\/\/inertz.com\/blog","description":"Shell script yang aku biasa pakai untuk backup MySQL database ataupun InnoDB database untuk kegunaan sendiri ataupun sesiapa yang memerlukan.\u00a0 Kalau setakat","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/inertz.com\/blog\/shell-script-untuk-backup-mysql-dan-innodb-bagi-cpanel-server\/","og_locale":"en_US","og_type":"article","og_title":"Shell script untuk backup MySQL dan InnoDB bagi Cpanel server - https:\/\/inertz.com\/blog","og_description":"Shell script yang aku biasa pakai untuk backup MySQL database ataupun InnoDB database untuk kegunaan sendiri ataupun sesiapa yang memerlukan.\u00a0 Kalau setakat","og_url":"https:\/\/inertz.com\/blog\/shell-script-untuk-backup-mysql-dan-innodb-bagi-cpanel-server\/","og_site_name":"https:\/\/inertz.com\/blog","article_publisher":"https:\/\/facebook.com\/inertz","article_author":"https:\/\/web.facebook.com\/inertz","article_published_time":"2017-06-03T04:17:54+00:00","article_modified_time":"2020-02-24T03:06:53+00:00","og_image":[{"url":"http:\/\/inertz.com\/blog\/wp-content\/uploads\/2016\/11\/mysql.jpg","type":"","width":"","height":""}],"author":"admin","twitter_card":"summary_large_image","twitter_creator":"@https:\/\/twitter.com\/inertz","twitter_site":"@inertz","twitter_misc":{"Written by":"admin","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/inertz.com\/blog\/shell-script-untuk-backup-mysql-dan-innodb-bagi-cpanel-server\/#article","isPartOf":{"@id":"https:\/\/inertz.com\/blog\/shell-script-untuk-backup-mysql-dan-innodb-bagi-cpanel-server\/"},"author":{"name":"admin","@id":"https:\/\/inertz.com\/blog\/#\/schema\/person\/d27d47b78673d67d4de15a57cd609ed9"},"headline":"Shell script untuk backup MySQL dan InnoDB bagi Cpanel server","datePublished":"2017-06-03T04:17:54+00:00","dateModified":"2020-02-24T03:06:53+00:00","mainEntityOfPage":{"@id":"https:\/\/inertz.com\/blog\/shell-script-untuk-backup-mysql-dan-innodb-bagi-cpanel-server\/"},"wordCount":451,"commentCount":0,"publisher":{"@id":"https:\/\/inertz.com\/blog\/#organization"},"image":{"@id":"https:\/\/inertz.com\/blog\/shell-script-untuk-backup-mysql-dan-innodb-bagi-cpanel-server\/#primaryimage"},"thumbnailUrl":"http:\/\/inertz.com\/blog\/wp-content\/uploads\/2016\/11\/mysql.jpg","keywords":["backup","bash","innodb","mysql","shell script"],"articleSection":["Linux","server"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/inertz.com\/blog\/shell-script-untuk-backup-mysql-dan-innodb-bagi-cpanel-server\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/inertz.com\/blog\/shell-script-untuk-backup-mysql-dan-innodb-bagi-cpanel-server\/","url":"https:\/\/inertz.com\/blog\/shell-script-untuk-backup-mysql-dan-innodb-bagi-cpanel-server\/","name":"Shell script untuk backup MySQL dan InnoDB bagi Cpanel server - https:\/\/inertz.com\/blog","isPartOf":{"@id":"https:\/\/inertz.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/inertz.com\/blog\/shell-script-untuk-backup-mysql-dan-innodb-bagi-cpanel-server\/#primaryimage"},"image":{"@id":"https:\/\/inertz.com\/blog\/shell-script-untuk-backup-mysql-dan-innodb-bagi-cpanel-server\/#primaryimage"},"thumbnailUrl":"http:\/\/inertz.com\/blog\/wp-content\/uploads\/2016\/11\/mysql.jpg","datePublished":"2017-06-03T04:17:54+00:00","dateModified":"2020-02-24T03:06:53+00:00","description":"Shell script yang aku biasa pakai untuk backup MySQL database ataupun InnoDB database untuk kegunaan sendiri ataupun sesiapa yang memerlukan.\u00a0 Kalau setakat","breadcrumb":{"@id":"https:\/\/inertz.com\/blog\/shell-script-untuk-backup-mysql-dan-innodb-bagi-cpanel-server\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/inertz.com\/blog\/shell-script-untuk-backup-mysql-dan-innodb-bagi-cpanel-server\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/inertz.com\/blog\/shell-script-untuk-backup-mysql-dan-innodb-bagi-cpanel-server\/#primaryimage","url":"https:\/\/inertz.com\/blog\/wp-content\/uploads\/2016\/11\/mysql.jpg","contentUrl":"https:\/\/inertz.com\/blog\/wp-content\/uploads\/2016\/11\/mysql.jpg","width":521,"height":524},{"@type":"BreadcrumbList","@id":"https:\/\/inertz.com\/blog\/shell-script-untuk-backup-mysql-dan-innodb-bagi-cpanel-server\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/inertz.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Shell script untuk backup MySQL dan InnoDB bagi Cpanel server"}]},{"@type":"WebSite","@id":"https:\/\/inertz.com\/blog\/#website","url":"https:\/\/inertz.com\/blog\/","name":"https:\/\/inertz.com\/blog","description":"19 tahun blog belum pupus","publisher":{"@id":"https:\/\/inertz.com\/blog\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/inertz.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/inertz.com\/blog\/#organization","name":"https:\/\/inertz.com\/blog","url":"https:\/\/inertz.com\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/inertz.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/inertz.com\/blog\/wp-content\/uploads\/2021\/05\/server.png","contentUrl":"https:\/\/inertz.com\/blog\/wp-content\/uploads\/2021\/05\/server.png","width":512,"height":512,"caption":"https:\/\/inertz.com\/blog"},"image":{"@id":"https:\/\/inertz.com\/blog\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/facebook.com\/inertz","https:\/\/x.com\/inertz"]},{"@type":"Person","@id":"https:\/\/inertz.com\/blog\/#\/schema\/person\/d27d47b78673d67d4de15a57cd609ed9","name":"admin","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/inertz.com\/blog\/wp-content\/litespeed\/avatar\/9cb42e3829b77fd9cc6fefd707ea0f1a.jpg?ver=1780245170","url":"https:\/\/inertz.com\/blog\/wp-content\/litespeed\/avatar\/9cb42e3829b77fd9cc6fefd707ea0f1a.jpg?ver=1780245170","contentUrl":"https:\/\/inertz.com\/blog\/wp-content\/litespeed\/avatar\/9cb42e3829b77fd9cc6fefd707ea0f1a.jpg?ver=1780245170","caption":"admin"},"sameAs":["http:\/\/inertz.com\/blog","https:\/\/web.facebook.com\/inertz","https:\/\/www.instagram.com\/inertz\/","https:\/\/www.linkedin.com\/in\/inertz\/","https:\/\/x.com\/https:\/\/twitter.com\/inertz"],"url":"https:\/\/inertz.com\/blog\/author\/admin\/"}]}},"_links":{"self":[{"href":"https:\/\/inertz.com\/blog\/wp-json\/wp\/v2\/posts\/5782","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/inertz.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/inertz.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/inertz.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/inertz.com\/blog\/wp-json\/wp\/v2\/comments?post=5782"}],"version-history":[{"count":0,"href":"https:\/\/inertz.com\/blog\/wp-json\/wp\/v2\/posts\/5782\/revisions"}],"wp:attachment":[{"href":"https:\/\/inertz.com\/blog\/wp-json\/wp\/v2\/media?parent=5782"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/inertz.com\/blog\/wp-json\/wp\/v2\/categories?post=5782"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/inertz.com\/blog\/wp-json\/wp\/v2\/tags?post=5782"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}