2012-11-07, 09:49
I recently wanted to make a copy of my XBMC MySQL Music and Video DBs, so that I could have a version for playing around with, etc. However, a straight MySQL copy didn't seem to work and only ended up messing up the data.
As such, I had a quick poke around the XBMC source and found some relevant code in mysqldataset.cpp.
Based on this code (especially the contents of MysqlDatabase::copy), I wrote a quick and dirty Python script to copy the XBMC databases. In case it's useful for someone else, here it is:
Unsurprisingly, I am not responsible if this code ends up breaking your databases or burning your house down.
As such, I had a quick poke around the XBMC source and found some relevant code in mysqldataset.cpp.
Based on this code (especially the contents of MysqlDatabase::copy), I wrote a quick and dirty Python script to copy the XBMC databases. In case it's useful for someone else, here it is:
Code:
import MySQLdb
dbhost = "192.168.1.5"
dbname = "xbmc"
dbpass = "xbmc"
musicSourceDB = 'MyMusic30'
musicDestDB = 'NewMusic30'
videoSourceDB = 'MyVideos72'
videoDestDB = 'NewVideos72'
def copyDB(sourceDB, destDB, postcopysql = ""):
mydb = MySQLdb.connect(host = dbhost, user = dbname, passwd = dbpass, db = sourceDB)
myconn = mydb.cursor(MySQLdb.cursors.DictCursor)
#myconn.execute("DROP DATABASE IF EXISTS %s" % (destDB))
myconn.execute("CREATE DATABASE %s" % (destDB))
myconn.execute("SHOW FULL TABLES WHERE Table_type = 'BASE TABLE'")
sourcerow = 'Tables_in_' + sourceDB
for row in myconn.fetchall():
myconn.execute("CREATE TABLE %s.%s LIKE %s" % (destDB, row[sourcerow], row[sourcerow]))
myconn.execute("INSERT INTO %s.%s SELECT * FROM %s" % (destDB, row[sourcerow], row[sourcerow]))
myconn.execute("SELECT TABLE_NAME, VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA = '%s'" % (sourceDB))
for row in myconn.fetchall():
myconn.execute("CREATE VIEW %s.%s AS %s" % (destDB, row['TABLE_NAME'], row['VIEW_DEFINITION'].replace(sourceDB, destDB)))
myconn.execute("SHOW TRIGGERS")
for row in myconn.fetchall():
myconn.execute("CREATE TRIGGER %s.%s %s %s ON %s FOR EACH ROW %s" % (destDB, row['Trigger'], row['Timing'], row['Event'], row['Table'], row['Statement']))
if postcopysql:
myconn.execute(postcopysql)
copyDB(musicSourceDB, musicDestDB)
copyDB(videoSourceDB, videoDestDB)
#copyDB(musicSourceDB, musicDestDB, "UPDATE %s.song SET iTimesPlayed = NULL, lastplayed = NULL" % musicDestDB)
#copyDB(videoSourceDB, videoDestDB, "UPDATE %s.files SET playCount = NULL, lastPlayed = NULL" % videoDestDB)Unsurprisingly, I am not responsible if this code ends up breaking your databases or burning your house down.
