MySQL/Backup and restore databases

Incomplete: This page/section is under construction

MySQL/MariaDB backups

GUI applications

Windows script

Linux script

[edit]

Although an SQL database server is quite stable in day-to-day operations, it usually contains carefully gathered information over a longer period of time. And thus something that users will not like to see damaged or comprimised in any way. So just like any other piece of computer data such as the various office documents or personal files, having a backup of your database data is a necessity of life these days.

There are more ways than one to make such backups. For now, we will focus on MySQL server backups. Any deviations for MariaDB will be mentioned separately.

Files from a MySQL Server cannot be simply copied like normal computer files. The combination of files and contents makes it that the data will need to be retrieved via specialized tools, such as:

• PHPMyAdmin
• MySQL Workbench
• HeidiSQL (MariaDB)

Common connection details You can use the same database user 'kodi' with which you have initially set up the Kodi video and/or music databases. By default, the user 'kodi' was granted full access to the database server to make the creation of new Kodi databases easy during a Kodi upgrade.

If you want to create Kodi databases on a new MySQL/MariaDB server, make sure you create the user 'kodi' first, and grant the user full credentials as per Kodi instructions.

[edit]

PHPMyAdmin PHPMyAdmin is a web-interface running on Apache and PHP that connects directly to your database server, locally or remote outside your own network. It can be installed on Windows, Linux and Mac OS computers, as well as most NAS devices.

Open the PHPMyAdmin application and log in with a user with full credentials. The Kodi user should have those credentials. Select the EXPORT tab. Select Custom - display all possible options Make sure to have the correct database(s) selected phpMyAdmin - Export - Selection

Create your output filename in case you do not want the default created output filename. phpMyAdmin - Export - Output

Object creation options:

• include Add DROP DATABASE IF EXISTS statement
• include Add DROP TABLE / VIEW / PROCEDURE / FUNCTION / EVENT / TRIGGER statement

phpMyAdmin - Export - Object creation

Press on the Go button in the bottom left corner, and the export will start. A download link of the export file will be presented to you when the database export has completed.

MySQL Workbench MySQL Workbench is a standalone database maintenance application which is available for Windows, Linux and OS X.

Select from the main menu "Server", then select the submenu item "Export data".

To make a database the default selected database in MySQL Workbench for current and future sessions, double-click on the MyVideos119 schema in the left side bar. The entry will then be displayed in bold text.

Select the schema(s) or database(s) you want to export. Their respective data tables will be selected automatically.

At the export options, select Export to Self-Contained File, and browse/create to the preferred folder and file name. Also include the options Create Dump in a Single Transaction as well as Include Create Schema.

Finally, press the Start Export button in the bottom right corner to start the database export. Exit the application when no errors were found.

HeidiSQL (MariaDB) HeidiSQL is an overall database server tool, able to handle multiple types of database servers, but was primairily intended for MariaDB. It is available for a Windows OS only.

Mysqldump Mysqldump is a commandline tool to export and import SQL data collections via a comand prompt / terminal session. It can be run via scripts and therefore perfectly fit for processing automated backups. The tool is provided via the installation of MySQL/MariaDB.

[edit]

BACKUP/RESTORE SCRIPT TO FOLLOW

1. Close out the command line tool

[edit]

Using MySQLdump in Linux is also available, and is tpyically provided along with the mysql/mariadb-server installation.

This is a script that can be used either manually or be hung into time-repetitive cron jobs. Log files are created with filenames that have a timestamp built-in, so older backups will not be overwritten.

Copy the script below into your favorite text-editor and save it as a .sh (bash) file into the location+filename of your chosing, and make the file executable with:

chmod +x filename.sh

.

You can export database contents for the Kodi video library, music library, and the database user for the Kodi application. You can do so separately or combined with all three parameters at the same time.

#!/usr/bin/env bash
#set -x  # enable in case of trouble

echo ' '
echo 'MYSQLDUMP databases Kodi v19+ video & music'
echo '-------------------------------------------'

# TESTED ON UBUNTU DESKTOP 21.10 with MYSQL SERVER 5.7.36 ON UBUNTU SERVER 18.04

timestamp=$(date +"%Y%m%d_%H%M");

# EXPORT PATH, ADJUST TO YOUR OWN CONVENIENCE
path="/home/klojum/Dropbox/MySQL/kodidtbs_v19_""$timestamp""_"

# FILENAMES
video_export="$path""video.sql"
music_export="$path""music.sql"
user_export="$path""user.sql"

# USER PARAMETERS TO EDIT ACCORDING TO YOUR DATABASE SERVER, CREDENTIALS AND PORT SITUATION
user=" --user=kodi --password=kodi"
srvr=" --host=srvr1 "
port=" --port=3306 "

# FIXED PARAMETERS FOR KODI DATABASES
params=" --add-drop-database --add-drop-table --add-drop-trigger --routines --triggers "

# VARIABLE PARAMETERS FOR KODI DATABASES
video=" --databases MyVideos119 "
music=" --databases MyMusic82 "

# ---------------------------------------------------------------------------------------

# CHECK IF USER-GIVEN PARAMETERS ARE PRESENT
if [ $# -gt 0 ]; then
	echo ' ';
	# LOOP THROUGH PARAMETERS GIVEN
	while test $# -gt 0
	do
		 case "$1" in
			  v|video|all)
				echo "Exporting video to : ""$video_export";
				mysqldump $user $srvr $params $video > $video_export
				;;
			  m|music|all)
				echo "Exporting music to : ""$music_export";
				mysqldump $user $srvr $params $music > $music_export
			  	;;
			  u|user|all)
				params="--databases mysql --tables user --skip-add-drop-table --no-create-info"
				echo "Exporting user to : ""$user_export";
				mysqldump $user $srvr $params --where="User='kodi'" > $user_export
				;;
		 esac
		 # NEXT VARIABLE
		 shift
	done
	echo ' ';
else
	echo 'EXAMPLE: ./dtbs-export video ';
	echo ' ';
	echo 'Possible user parameters:';
	echo '  v or video';
	echo '  m or music';
	echo '  u or user';
	echo '  all';
	echo ' ';
	echo 'Note: for importing full Kodi database exports, you need SQL database root user access.'
	echo ' '
	echo 'For a complete guide, type: "mysqldump --help" or "man mysqldump" ';
	echo ' ';
fi

# EXIT