Backup Database MySQL dengan Python

Melakukan backup database MySQL dapat juga dilakukan dengan menggunakan script Python sehingga untuk melakukan backup secara otomatis lebih mudah dilakukan. 

Script ini dapat di jalankan di dalam task scheduler windows dengan mengarahkan ke file project python dan mengatur waktu kapan script ini dijalankan.

Berikut cara melakukan backup database MySQL dengan menggunakan script python :
Pertama pastikan python kamu memiliki library python seperti time, datetime dan pipes jika belum tersedia silahkan install terlebih dahulu. Setelah dipastikan python memliki library tersebut, kemudian periksa dan pastikan data yang diperlukan seperti host name, user, password, nama database yang akan di backup. Sebagai contoh seperti di bawah ini

Setelah mengetahui data yang di perlukan, selanjutnya buat file project python dan copy script python di bawah ini dan ubah data sesuai dengan pengaturan database anda.

# Import required python libraries</code> <br><code class="language-python">import os</code><br><code class="language-python">import time</code><br><code class="language-python">import datetime</code><br><code class="language-python">import pipes </code><br><code class="language-python"># MySQL database details to which backup to be done. Make sure below user having enough privileges to take databases backup.</code><br><code class="language-python"># To take multiple databases backup,
create any file like /backup/dbnames.txt and put databases names one on each line and assigned to DB_NAME variable. </code><br><code class="language-python">DB_HOST = 'host' </code><br><code class="language-python">DB_USER = 'user'</code><br><code class="language-python">DB_USER_PASSWORD = 'password'</code><br><code class="language-python">DB_NAME = 'db_name'<br></code><code class="language-python">#DB_NAME = 'db_name.txt'</code><br><code class="language-python">BACKUP_PATH = 'backup' </code><br><code class="language-python"># Getting current DateTime to
create the separate backup folder like "20180817-123433".</code><br><code class="language-python"># DATETIME = time.strftime('%Y%m%d-%H%M')</code><br><code class="language-python">DATETIME = time.strftime('%Y%m%d')</code><br><code class="language-python">TODAYBACKUPPATH = BACKUP_PATH + '/' + DATETIME </code><br><code class="language-python"># Checking if backup folder already exists or not. If not exists will
create it.</code><br><code class="language-python">try:</code><br><code class="language-python">os.stat(TODAYBACKUPPATH)</code><br><code class="language-python">except:</code><br><code class="language-python">os.mkdir(TODAYBACKUPPATH) </code><br><code class="language-python"># Code for checking if you want to take single database backup or assinged multiple backups in DB_NAME.</code><br><code class="language-python">print ("checking for databases names file.")</code><br><code class="language-python">if os.path.exists(DB_NAME):</code><br><code class="language-python">file1 = open(DB_NAME)</code><br><code class="language-python">multi = 1</code><br><code class="language-python">print ("Databases file found...")</code><br><code class="language-python">print ("Starting backup of all dbs listed in file " + DB_NAME)</code><br><code class="language-python">else:</code><br><code class="language-python">print ("Databases file not found...")</code><br><code class="language-python">print ("Starting backup of database " + DB_NAME)</code><br><code class="language-python">multi = 0 </code><br><code class="language-python"># Starting actual database backup process.</code><br><code class="language-python">if multi:</code><br><code class="language-python">in_file = open(DB_NAME,"r")</code><br><code class="language-python">flength = len(in_file.readlines())</code><br><code class="language-python">in_file.close()</code><br><code class="language-python">p = 1</code><br><code class="language-python">dbfile = open(DB_NAME,"r") </code><br><code class="language-python">while p db = dbfile.readline() # reading database name
from file</code><br><code class="language-python">db = db[:-1] # deletes extra line</code><br><code class="language-python">print (db)</code><br><code class="language-python">dumpcmd = "c:
xampp
mysql
bin
mysqldump -h " + DB_HOST + " -u " + DB_USER + " -p" + DB_USER_PASSWORD + " " + db + " > " + pipes.quote(TODAYBACKUPPATH) + "/" + db + ".sql"</code><br><code class="language-python">os.systcode(dumpcmd)</code><br><code class="language-python">#gzipcmd = "gzip " + pipes.quote(TODAYBACKUPPATH) + "/" + db + ".sql"</code><br><code class="language-python">#os.systcode(gzipcmd)</code><br><code class="language-python">p = p + 1</code><br><code class="language-python">dbfile.close()</code><br><code class="language-python">else:</code><br><code class="language-python">db = DB_NAME</code><br><code class="language-python">dumpcmd = "c:
xampp
mysql
bin
mysqldump -h " + DB_HOST + " -u " + DB_USER + " -p" + DB_USER_PASSWORD + " " + db + " > " + pipes.quote(TODAYBACKUPPATH) + "/" + db + ".sql"</code><br><code class="language-python">os.systcode(dumpcmd)</code><br><code class="language-python">#gzipcmd = "gzip " + pipes.quote(TODAYBACKUPPATH) + "/" + db + ".sql"</code><br><code class="language-python">#os.systcode(gzipcmd) </code><br><code class="language-python">print ("")</code><br><code class="language-python">print ("Backup script completed")</code><br><code class="language-python">print ("Your backups have been created in '" + TODAYBACKUPPATH + "' directory")

database_pertama</code><br><code class="language-python">database_kedua</code><code class="language-python">