[Prof Yaffle]

Mods: I tried to post this to Tips, tricks, and step by step guides, but I don't have permission to post there for some reason. Please feel free to move if necessary, therefore.

-----

Disclaimer/plea for comment: this seemed to work perfectly for me, but I'm happy to get a second opinion from people who understand SQL better than I do. If it's valid, I'll scribble it onto the wiki when I get a chance, so think of this as a draft.

Here was my challenge: moving everything from one server to another, wanting to move my mysql database in the process, while changing some of my paths while I was about it. I know you can export it all to file and re-import it from within XBMC/Kodi, and I know you could just rescrape everything... but this felt like an admission of defeat... there had to be another way. So this is what I did...


1. Install mysql on the new (target) system. This is directly lifted from the wiki MySQL/Setting_up_MySQL (wiki).

Code:

$ sudo apt-get install mysql-server

Create a password when asked

Edit /etc/mysql/my.cnf and change the bind-address directive to the server's IP address to allow remote connections:

from:

Code:

bind-address = 127.0.0.1

to:

Code:

bind-address = your.server's.IP.address

Complete the set up of mysql:

Code:

$ sudo restart mysql

$ mysql -u root -p

msyql> CREATE USER 'xbmc' IDENTIFIED BY 'xbmc';
mysql> GRANT ALL ON *.* TO 'xbmc';
mysql> quit

== > You now have a blank setup with an xbmc user defined.


2. Export the mysql data from the old (source) system

Use 'show databases;' in mysql to see what databases you have defined. Export the one that corresponds to your current XBMC/Kodi version XBMC_databases/Versions (wiki).

Code:

$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7454
Server version: 5.5.40-0ubuntu0.14.04.1 (Ubuntu)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| MyMusic32          |
| MyVideos75         |
| MyMusic46          |
| MyVideos78         |
| MyMusic48          |
| MyVideos88         |
| mysql              |
| performance_schema |
+--------------------+
5 rows in set (0.02 sec)

mysql> quit
Bye

I was exporting from Gotham (you can also see previous Frodo db versions, plus where I'd played with early Helix build), so:

Code:

$ mysqldump -u root -p --opt MyMusic46 > MyMusic46.sql
$ mysqldump -u root -p --opt MyVideos78 > MyVideos78.sql

==> You now have two files that contain all of your library information


3. Edit paths as and if necessary

If your directory structure is at all different on your new system, now's the easy time to adjust for this. You can simply load each file in turn into vi and use a global search-and-replace:

Code:

:%s/search_string/replacement_string/g

So, as an example, if you were moving from nfs://192.168.10.10/export/music to nfs://192.168.10.20/music you'd use:

Code:

$ vi MyMusic46.sql
:%s/192\.168\.10\.10\/export\/music/192\.168\.10\.20\/music/g
:w

Remember to escape any special characters.


4. Import the mysql data into the target system

Copy the files over by scp, sftp, nfs, USB stick, paper tape, morse code, whatever.

Create the new databases:

Code:

$ mysqladmin -u root -p create MyMusic46
$ mysqladmin -u root -p create MyVideos78

==> You now have databases in which the library information will be stored

NB: Do not try to export from one version and import into another - it'll go wrong if there are any changes to the table structure (which there inevitably will be, otherwise they wouldn't have changed the db version number, would they?). If you're changing version, let XBMC/Kodi sort out the upgrade for you - either upgrade the source system before exporting the newer database versions, or port across the current version and then let XBMC/Kodi upgrade everything on the target.

... and populate the databases:

Code:

$ mysql -u root -p MyMusic46 < ./MyMusic46.sql
$ mysql -u root -p MyVideos78 < ./MyVideos78.sql

==> You're done


Start XBMC/Kodi (with the right advancedsettings.xml on every client, of course), and all will be well. If you're replacing a system and have brought the new one up with the IP address of the old one, your clients will never even notice that anything has changed (unless they have local sources.xml, which will need to updated for any changed paths).

Easier than exporting and re-importing? Maybe not, but it appealed to me. Despite the wall of text above, it's actually only six basic commands - export, create, import for each of MyMusic and MyVideos.

[helta]

Nice, will probably use this in the future. Thanks Prof.

[gsasearch]

Thanks Prof Yaffle , this has helped me a lot immensely. You have saved me a lot of my valuable time. +rep for you

_______________________________________________________________
GSA Search Engine Ranker coupon| | GSA captcha breaker coupon

[transcender]

Just did this; seems to have worked perfectly for me migrating mysql from a windows hosted ubuntu VM to my ubuntu hosted storage. I didnt have to do any paths replacement and my main OE instance and raspi OE instance working just fine.
Thanks Prof Yaffle