MySQL/Backup and restore databases - Revision history

Rainman74: /* The easy way: Windows batch scripts for multi profiles */

2022-06-25T09:37:36Z

The easy way: Windows batch scripts for multi profiles

← Older revision		Revision as of 09:37, 25 June 2022
Line 197:		Line 197:

	mysql -h ''Your NAS Hostname or IP'' -u kodi -p''Your Password'' -e "SHOW DATABASES"		mysql -h ''Your NAS Hostname or IP'' -u kodi -p''Your Password'' -e "SHOW DATABASES"
			</syntaxhighlight>

			==== Synced favorites ====
			Although off topic, but since the frequently occurring question is not answered exactly here on the wiki, a brief explanation.

			With Kodi unfortunately no sync of the favorites via DB is possible. Therefore you have to store the favorites.xml centrally on your (NAS) server and then address them via path substitution.

			'''advancedsettings.xml for your parent profile'''
			<syntaxhighlight lang="dos">
			<advancedsettings version="1.0">
			...
			<pathsubstitution>
			<substitute>
			<from>special://profile/favourites.xml</from>
			<to>smb://''Your NAS Hostname or IP''/Kodi/Parent-Favourites.xml</to>
			</substitute>
			</pathsubstitution>
			...
			</advancedsettings>

			</syntaxhighlight>

			'''advancedsettings.xml for your kids profile'''
			<syntaxhighlight lang="dos">
			<advancedsettings version="1.0">
			...
			<pathsubstitution>
			<substitute>
			<from>special://profile/favourites.xml</from>
			<to>smb://''Your NAS Hostname or IP''/Kodi/Kids-Favourites.xml</to>
			</substitute>
			</pathsubstitution>
			...
			</advancedsettings>
	</syntaxhighlight>		</syntaxhighlight>

Rainman74: /* kodi_db_import */

2022-06-23T00:51:11Z

kodi_db_import

← Older revision		Revision as of 00:51, 23 June 2022
Line 181:		Line 181:
	@echo off		@echo off

	echo Importing Kodi DB from: ~~Kodi_Backup_latest~~.sql...		echo Importing Kodi DB from: Kodi_Backup.sql...

	mysql -h ''Your NAS Hostname or IP'' -u kodi -p''Your Password'' <Kodi_Backup.sql		mysql -h ''Your NAS Hostname or IP'' -u kodi -p''Your Password'' <Kodi_Backup.sql
	</syntaxhighlight>		</syntaxhighlight>

	===== kodi_db_drop (just for testing purpose) =====		===== kodi_db_drop (just for testing purpose) =====
	<syntaxhighlight lang="dos">		<syntaxhighlight lang="dos">

Rainman74: /* The easy way: Windows batch scripts for multi profiles */

2022-06-22T22:56:13Z

The easy way: Windows batch scripts for multi profiles

← Older revision		Revision as of 22:56, 22 June 2022
Line 100:		Line 100:
	<syntaxhighlight>		<syntaxhighlight>
	''Your NAS Hostname or IP'' -> 192.168.178.5		''Your NAS Hostname or IP'' -> 192.168.178.5
	''Your Password'' -> My-Super-Secret-DB-Password-2		''Your Password'' -> My-Super-Secret-DB-Password (for user "kodi")
	</syntaxhighlight>		</syntaxhighlight>

Rainman74: /* Command-line interface */

2022-06-22T22:54:09Z

Command-line interface

← Older revision		Revision as of 22:54, 22 June 2022
Line 90:		Line 90:

	<headertabs/>		<headertabs/>

			=== The easy way: Windows batch scripts for multi profiles ===

			Here are simple batch script examples that will allow you to export the entire Kodi database from your MySQL or MariaDB database and import it back into the same, or another server database. For example as backup or when changing your NAS system.

			The examples assume that you have two profiles, one for the parents and one for your kids, to show how it works with multiple profiles. If you have only one profile, just remove the second database in the examples. If you use more than two profiles, you can of course simply specify these databases additionally.

			Replace as follows as an example and without the two apostrophes:
			<syntaxhighlight>
			''Your NAS Hostname or IP'' -> 192.168.178.5
			''Your Password'' -> My-Super-Secret-DB-Password-2
			</syntaxhighlight>

			Note: The current databases do not allow a simple password like ''"kodi"'' without further modifications, as stated in many other older instructions.

			==== advancedsettings.xml ====
			To start, this is roughly how your two advancedsettings.xml should look if you are using two profiles with an SQL database on your NAS.

			'''advancedsettings.xml for your parent / master profile'''
			<syntaxhighlight lang="dos">
			<advancedsettings version="1.0">
			<videolibrary>
			<exportautothumbs>true</exportautothumbs>
			<importresumepoint>true</importresumepoint>
			<importwatchedstate>true</importwatchedstate>
			</videolibrary>
			<videodatabase>
			<type>mysql</type>
			<name>Parent_Video</name>
			<host>''Your NAS Hostname or IP''</host>
			<port>3306</port>
			<user>kodi</user>
			<pass>''Your Password''</pass>
			</videodatabase>
			<musicdatabase>
			<type>mysql</type>
			<name>Shared_Music</name>
			<host>''Your NAS Hostname or IP''</host>
			<port>3306</port>
			<user>kodi</user>
			<pass>''Your Password''</pass>
			</musicdatabase>
			</advancedsettings>
			</syntaxhighlight>

			'''advancedsettings.xml for your kids profile'''
			<syntaxhighlight lang="dos">
			<advancedsettings version="1.0">
			<videolibrary>
			<exportautothumbs>true</exportautothumbs>
			<importresumepoint>true</importresumepoint>
			<importwatchedstate>true</importwatchedstate>
			</videolibrary>
			<videodatabase>
			<type>mysql</type>
			<name>Kids_Video</name>
			<host>''Your NAS Hostname or IP''</host>
			<port>3306</port>
			<user>kodi</user>
			<pass>''Your Password''</pass>
			</videodatabase>
			<musicdatabase>
			<type>mysql</type>
			<name>Shared_Music</name>
			<host>''Your NAS Hostname or IP''</host>
			<port>3306</port>
			<user>kodi</user>
			<pass>''Your Password''</pass>
			</musicdatabase>
			</advancedsettings>
			</syntaxhighlight>

			==== Batch files ====
			To run these batch files, load the latest Maria DB or MySQL beforehand and install it (MSI file) or unzip the ZIP and use the executables in the BIN folder. These are static builds and do not need any additional DLLs. You can copy them e.g. directly to C:\Windows\ or any folder in your path.
			It is also irrelevant whether the database is running on your Windows, Mac or Linux computer or on a NAS.

			'''Current MariaDB: [https://mariadb.org/download/?t=mariadb&p=mariadb&r=10.8.3&os=windows&cpu=x86_64&pkg=zip mariadb-10.8.3-winx64.zip]'''

			===== kodi_db_export =====
			<syntaxhighlight lang="dos">
			@echo off

			echo Backing up Kodi DB to: Kodi_Backup.sql...

			mysqldump -h ''Your NAS Hostname or IP'' -u kodi -p''Your Password'' --databases Parent_Video119 Kids_Video119 Shared_Music82 --add-drop-database --add-drop-table --add-drop-trigger --routines --triggers >Kodi_Backup.sql
			</syntaxhighlight>

			===== kodi_db_import =====
			<syntaxhighlight lang="dos">
			@echo off

			echo Importing Kodi DB from: Kodi_Backup_latest.sql...

			mysql -h ''Your NAS Hostname or IP'' -u kodi -p''Your Password'' <Kodi_Backup.sql
			</syntaxhighlight>
			===== kodi_db_drop (just for testing purpose) =====
			<syntaxhighlight lang="dos">
			@echo off

			mysql -h ''Your NAS Hostname or IP'' -u kodi -p''Your Password'' -e "SHOW DATABASES"

			mysql -h ''Your NAS Hostname or IP'' -u kodi -p''Your Password'' -e "DROP DATABASE `Parent_Video119`"
			mysql -h ''Your NAS Hostname or IP'' -u kodi -p''Your Password'' -e "DROP DATABASE `Kids_Video119`"
			mysql -h ''Your NAS Hostname or IP'' -u kodi -p''Your Password'' -e "DROP DATABASE `Shared_Music82`"

			mysql -h ''Your NAS Hostname or IP'' -u kodi -p''Your Password'' -e "SHOW DATABASES"
			</syntaxhighlight>

RogueScholar: Edit for clarity and ease of navigation

2022-04-19T21:27:48Z

Edit for clarity and ease of navigation

Show changes

Klojum at 13:42, 13 February 2022

2022-02-13T13:42:25Z

← Older revision		Revision as of 13:42, 13 February 2022
Line 97:		Line 97:
	From the command line in a terminal session, things are just as easy. Enter the following:		From the command line in a terminal session, things are just as easy. Enter the following:
	<syntaxhighlight lang="dos">		<syntaxhighlight lang="dos">
	mysqldump -u -p < /path/to/your/~~exportfile~~.sql		mysqldump -u kodi -p MyVideos119 < /path/to/your/videoexportfile.sql
			mysqldump -u kodi -p MyVideos82 < /path/to/your/musicexportfile.sql
	</syntaxhighlight>		</syntaxhighlight>

Klojum at 11:49, 26 January 2022

2022-01-26T11:49:53Z

← Older revision		Revision as of 11:49, 26 January 2022
Line 89:		Line 89:
	===Command line===		===Command line===
	The MySQLdump tool is part of the MySQL Workbench installation. When installed, you can find the application in the following location:		The MySQLdump tool is part of the MySQL Workbench installation. When installed, you can find the application in the following location:
	~~'''~~c:\Program Files\MySQL\MySQL Workbench 8.0\mysqldump.exe~~'''.~~ We recommend that you create a shortcut to this application and place the shortcut in a Windows' path folder, so it can be used during a command prompt session "from anywhere".
			<syntaxhighlight lang="dos">c:\Program Files\MySQL\MySQL Workbench 8.0\mysqldump.exe</syntaxhighlight>

			We recommend that you create a shortcut to this application and place the shortcut in a Windows' path folder, so it can be used during a command prompt session "from anywhere".


	From the command line in a terminal session, things are just as easy. Enter the following:		From the command line in a terminal session, things are just as easy. Enter the following:

Klojum at 11:46, 26 January 2022

2022-01-26T11:46:25Z

← Older revision		Revision as of 11:46, 26 January 2022
Line 88:		Line 88:

	===Command line===		===Command line===
			The MySQLdump tool is part of the MySQL Workbench installation. When installed, you can find the application in the following location:
			'''c:\Program Files\MySQL\MySQL Workbench 8.0\mysqldump.exe'''. We recommend that you create a shortcut to this application and place the shortcut in a Windows' path folder, so it can be used during a command prompt session "from anywhere".

	From the command line in a terminal session, things are just as easy. Enter the following:		From the command line in a terminal session, things are just as easy. Enter the following:
	<syntaxhighlight lang="dos">		<syntaxhighlight lang="dos">

Klojum at 09:45, 26 January 2022

2022-01-26T09:45:43Z

← Older revision		Revision as of 09:45, 26 January 2022
Line 80:		Line 80:
	{{MySQLdump_Linux}}		{{MySQLdump_Linux}}
	</tab>		</tab>
	</~~tabs>~~		<tab name="Importing data">

			===GUI applications===
			All the tools mentioned on this wiki page for exporting data, are also capable (of courses) of importing those exported databases. The positive thing about of importing/restoring the databases and their contents, is that there are not specific settings to set beforehand. All details are in the previously exported .sql files.

			Thus, choose the GUI tool you wish to use, select import function and the .sql file you want to import, and let the tool do its job.

			===Command line===
			From the command line in a terminal session, things are just as easy. Enter the following:
			<syntaxhighlight lang="dos">
			mysqldump -u -p < /path/to/your/exportfile.sql
			</syntaxhighlight>

	~~'''BACKUP/RESTORE SCRIPT TO FOLLOW'''~~		Enter the database user and the appropriate password when asked for, and the import will commence. Depending on the size of the import, it can take a couple of minutes. Should there be errors during the import process, then those will be displayed promptly.

			</tab>
			</tabs>

	<headertabs/>		<headertabs/>

Klojum at 22:37, 25 January 2022

2022-01-25T22:37:45Z

← Older revision		Revision as of 22:37, 25 January 2022
Line 72:		Line 72:

	<tabs>		<tabs>
	<tab name="Windows">MySQLdump script for Windows OSes</tab>		<tab name="Windows">
			MySQLdump script for Windows OSes
			{{MySQLdump_Windows}}
			</tab>
	<tab name="Linux">		<tab name="Linux">
			MySQLdump script for Linux OSes
	{{MySQLdump_Linux}}		{{MySQLdump_Linux}}
	</tab>		</tab>