MySQL/Advanced notes

advancedsettings.xml
For a full listing of the advancedsettings.xml parameters, see advancedsettings.xml

MySQL speed-up tweaks
For Kodi/XBMC ≤ v12 (Frodo) and/or MySQL 5.0 or lower: with MyISAM as the default storage engine, the following tweaks are necessary:

MySQL speed-up tweaks for Kodi/XBMC - See Comments for more information.

More MySQL speed-up tweaks for Kodi/XBMC

For MySQL 5.5 or latest: with InnoDB as the default storage engine and/or v13 (Gotham)/Kodi+ only the following two tweaks are necessary.

In advancedsettings.xml:
Use the IP address and NOT the hostname of your MySQL server

eg.: 192.168.1.105 <> instead of \\SERVER

In my.cnf (my.ini for older versions):
The named file is located at /etc/mysql/my.cnf

1. $sudo nano /etc/mysql/my.cnf

2. Scroll down to [mysqld] section and add:


 * 1) skip-name-resolve

3. Press Ctrl+X, type y and hit Enter to validate changes and exit nano

4. Restart your MySQL server. $sudo restart mysql



Windows
This will allow you to make a regular backup of your MySQL database using the schedule of your choosing and saving the backups for however many days you choose. Recommend performing daily backups and saving for a minimum of 7 days. This will give you enough time to discover a problem with the DB and restore to a previous version that did not have problems.

To begin, go ahead and download the command line version of 7zip. We will use this to compress our MySQL dumps. Create a new folder in your C directory named KODISQLBACKUP. So you should have a C:\KODISQLBACKUP\ location now. In this location, create another new folder and name it zip. So you should now have C:\KODISQLBACKUP\zip. Extract the contents of the 7zip download to this location. Once complete you should have: C:\KODISQLBACKUP\zip\7za.exe

C:\KODISQLBACKUP\zip\7-zip.chm

C:\KODISQLBACKUP\zip\license.txt

C:\KODISQLBACKUP\zip\readme.txt

We're going to create a down and dirty batch file to perform the actual backup, then we'll execute the batch script with the task scheduler built into Windows. For the purpose of these instructions, it is assumed that you are running MySQL 5.5 installed to the default location. If not, be sure to adjust your path(s) accordingly. It's also a good idea to save your backups to a network location to protect against local drive failure, so the paths here will reflect this. So here are the batch file contents:

Save this as Kodibackup.bat at C:\KODISQLBACKUP Make sure you include the .bat at the end.

After you have edited the above batch file to reflect your paths and saved it as a batch file, all that is left is scheduling it to run. Daily backups are best, but you can use whatever frequency you are comfortable with. To do this, open up the Windows Task Scheduler.

In the Task Scheduler click on "Create Task...", NOT "Create Basic Task...". In the new task window, in the Name box put Kodi MySQL backup (or whatever name you want).

Then click on the Triggers tab, followed by the New button. Select Daily (or whatever you want) and adjust the time you would like it to run. Click OK.

Click on the Actions tab and click New. In the Program/Script box enter C:\KODISQLBACKUP\Kodibackup.bat (or whatever you named your batch file) and in the start in box enter C:\KODISQLBACKUP

Click ok, then click ok in the task window. In the main task scheduler window, right click on the task you just created and select run. If everything works as it should, you should now have a zipped backup of your database(s) in your backup location. It will now do this for you automatically.

Enjoy!

Linux
AutoMySQLBackup is a script that is designed to keep daily, weekly and monthly backups of your databases. It is very customizable and reliable. There is useful information on installing and configuring the script here: http://www.linux.com/learn/tutorials/659107-how-to-do-painless-mysql-server-backups-with-automysqlbackup.

Multiple profiles with one MySQL database
See: http://forum.kodi.tv/showthread.php?tid=81095&pid=840499#pid840499