Umakanthan's Blog

Export MySQL database in XML format

Feb 11th

This small python program can export mysql database in a xml format

import MySQLdb
 
hostName="hostnamehere"
userName="usernamehere"
passWd="passwordhere"
dbName="dbnamehere"
 
print "Started...."
 
#open file
file = open("out.xml", 'w')
 
file.write("");
file.write("\n\t<" + dbName + ">");
 
print "Connecting to " , hostName , "...";
conn=MySQLdb.connect(host=hostName,user=userName,passwd=passWd,db=dbName)
cursor = conn.cursor ()
 
table_list = []
 
#get server version
cursor.execute ("SELECT VERSION()")
row = cursor.fetchone ()
server_version = row[0];
print "Detected MySQL version ", server_version
 
# get all the tables
print "Getting tables...";
cursor.execute("show tables");
for row in cursor:
table_list.append(row[0])
 
# lets get all the tables values
for table in table_list:
file.write("\n\t<" + table + ">");
print "Processing ", table , " ..."
sql_desc = "desc " + table;
cursor.execute(sql_desc);
type_list = []
for row in cursor:
type_list.append(row[0])
# now data
sql_table_rows = "select * from " + table;
cursor.execute(sql_table_rows);
for row in cursor:
for i in range(len(type_list)):
file.write("\n\t\t<" + type_list[i] + ">" + "%s" %row[i] + "")
file.write("\n\t")
 
file.write("\n\t");
file.close();
print "XML file(out.xml) successfully written"

Share/Save

mysql, Python, utility

Remote MySQL backup – Using Windows Batch Script

Feb 9th

Posted by Umakanthan in tips

No comments

@echo off
PATH=%PATH%;C:\temp\mysql\bin;
cd c:\temp\backup_server\
set datetimef=%date:~-4%_%date:~7,2%_%date:~0,2%_%time:~0,2%_%time:~3,2%_%time:~6,2%
echo Please wait , backup in progress...
mysqldump --host="blahblah.com" --user="user" --password="password" databasename > backup_%datetimef%_.sql
echo Backup is success
pause