Difference between revisions of "MySQL/Backup and restore databases"

From Official Kodi Wiki
Jump to navigation Jump to search
m
(Edit for clarity and ease of navigation)
 
(9 intermediate revisions by one other user not shown)
Line 1: Line 1:
{{incomplete|This page/section is under construction|Incomplete}}
{{Mininav|Databases|MySQL}}
{{Incomplete|This page/section is under construction|Incomplete}}


== Rationale ==
Although a [[wikipedia:SQL|SQL]] [[wikipedia:Database#Database management system|database server]] is typically quite stable in day-to-day operation and not prone to random data loss, it usually manages information carefully gathered over a longer period of time, data which users wish to safeguard from damage and prevent from being compromised in any way. One of the most effective forms of protection against these and other adverse eventualities is having a backup of your database data. There are several ways to make such backups. This page will focus on MySQL server backups, with any deviations for MariaDB mentioned separately.


= MySQL/MariaDB backups =
Files from a MySQL server cannot simply be copied like normal computer files. The combination of files and contents makes it necessary to retrieve the data via specialized tools such as:
* [https://www.phpmyadmin.net/ PHPMyAdmin]
* [https://www.mysql.com/products/workbench MySQL Workbench]
* [https://www.heidisql.com/ HeidiSQL] (MariaDB)


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.
== Connecting to the database server ==
 
You can use the same database user <code>kodi</code> which was initially set up with the [[Databases|Kodi video and music libraries]]. By default, the user <code>kodi</code> was granted full access to the database server to make the creation of new Kodi databases seamless during upgrades.
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.
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.


=GUI applications=
== GUI applications ==
 
=== PHPMyAdmin ===
=== PHPMyAdmin ===
[https://www.phpmyadmin.net/ 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.
PHPMyAdmin is a web interface that runs on an Apache web server and PHP instance that connects directly to your database server by way of your web browser, locally or 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
 
[[File:Phpmyadmin-export-1-method.png|phpMyAdmin - Export - Selection]]
 
 
'''Output:'''
Create your output filename in case you do not want the default created output filename.
 
[[File:Phpmyadmin-export-2-output.png|phpMyAdmin - Export - Output]]


<gallery mode=packed-hover heights=300px caption="phpMyAdmin screenshots">
Phpmyadmin-export-1-method.png|Export ▶ Selection
Phpmyadmin-export-2-output.png|Export ▶ Output
Phpmyadmin-export-3-object-creation.png|Export ▶ Object creation
</gallery>
Open the PHPMyAdmin application and log in with a user with full credentials. The <code>kodi</code> user should have those credentials.
* '''Selection:'''
** Click on the '''<kbd>Export</kbd>''' tab, then select '''''<kbd>Custom - display all possible options</kbd>''''', making sure to have the correct database(s) selected.
* '''Output:'''
** Create your output filename in case you do not want to use the auto-generated default.
* '''Object creation:'''
** Include the <kbd>DROP DATABASE IF EXISTS</kbd> and
** <kbd>DROP TABLE / VIEW / PROCEDURE / FUNCTION / EVENT / TRIGGER</kbd> statements


'''Object creation options:'''
To begin the backup, click the Go button in the bottom left corner. A download link of the export file will be presented to you when the process has completed.
* include Add DROP DATABASE IF EXISTS statement
* include Add DROP TABLE / VIEW / PROCEDURE / FUNCTION / EVENT / TRIGGER statement
 
[[File:Phpmyadmin-export-3-object-creation.png|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 ===
[https://www.mysql.com/products/workbench MySQL Workbench] is a standalone database maintenance application which is available for Windows, Linux and OS X.
[[File:Mysql workbench data export.png|right|frameless|x600px|MySQL Workbench]]
MySQL Workbench is a standalone database maintenance application which is available for Windows, Linux and macOS.


Select from the main menu "Server", then select the submenu item "Export data".
* From the main menu, select '''<kbd>Server</kbd>''', then the submenu item '''''<kbd>Export data…</kbd>'''''.<br />{{Note|To make a database the default selected database in MySQL Workbench for current and future sessions, double-click on the '''<kbd>MyVideos119</kbd>''' schema in the left side bar. The entry will then be displayed in boldface.}}
 
* '''Administration – Data Export:'''
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.
** '''Tables to export:''' Select the schema(s) or database(s) you want to export, their respective data tables will be selected automatically.
 
** '''Export options:'''
Select the schema(s) or database(s) you want to export. Their respective data tables will be selected automatically.
*** Select '''''<kbd>Export to Self-Contained File</kbd>''''', and browse/create to the preferred folder and file name.
 
*** Include '''''<kbd>Create Dump in a Single Transaction</kbd>''''' as well as '''''<kbd>Include Create Schema</kbd>'''''.
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.
 
[[File:Mysql workbench data export.png|MySQL Workbench]]


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


=== HeidiSQL (MariaDB) ===
=== HeidiSQL (MariaDB) ===
[https://www.heidisql.com/ 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.
HeidiSQL is an overall database server tool, able to handle multiple types of database servers, but was primarily intended for MariaDB. It is available for a Windows OS only.
{{Clear|right}}


== Command-line interface ==
=== MySQLdump ===
<tabs>
<tab name="Windows">
MySQLdump script for Windows OSes
{{MySQLdump_Windows}}
</tab>
<tab name="Linux">
MySQLdump script for Linux OSes
{{MySQLdump_Linux}}
</tab>
<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.


=MySQLdump Windows=
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.


'''BACKUP/RESTORE SCRIPT TO FOLLOW'''
===Command line===
The MySQLdump tool is part of the MySQL Workbench installation. When installed, you can find the application in the following location:


#Close out the command line tool
<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".




=MySQLdump Linux=
From the command line in a terminal session, things are just as easy. Enter the following:
<syntaxhighlight lang="dos">
mysqldump -u kodi -p MyVideos119 < /path/to/your/videoexportfile.sql
mysqldump -u kodi -p MyVideos82  < /path/to/your/musicexportfile.sql
</syntaxhighlight>


Using MySQLdump in Linux is also available, and is tpyically provided along with the mysql/mariadb-server installation.
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.


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.
</tab>
 
</tabs>
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:
<syntaxhighlight lang="bash">chmod +x filename.sh</syntaxhighlight>
 
 
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.
<syntaxhighlight lang="bash">
#!/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/user/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
</syntaxhighlight>


<headertabs/>
<headertabs/>

Latest revision as of 21:27, 19 April 2022

Home icon grey.png   ▶ Databases ▶ MySQL ▶ Backup and restore databases
Incomplete.png Incomplete:
This page/section is under construction

1 Rationale

Although a SQL database server is typically quite stable in day-to-day operation and not prone to random data loss, it usually manages information carefully gathered over a longer period of time, data which users wish to safeguard from damage and prevent from being compromised in any way. One of the most effective forms of protection against these and other adverse eventualities is having a backup of your database data. There are several ways to make such backups. This page will focus on MySQL server backups, with any deviations for MariaDB mentioned separately.

Files from a MySQL server cannot simply be copied like normal computer files. The combination of files and contents makes it necessary to retrieve the data via specialized tools such as:

2 Connecting to the database server

You can use the same database user kodi which was initially set up with the Kodi video and music libraries. By default, the user kodi was granted full access to the database server to make the creation of new Kodi databases seamless during upgrades.

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.

3 GUI applications

3.1 PHPMyAdmin

PHPMyAdmin is a web interface that runs on an Apache web server and PHP instance that connects directly to your database server by way of your web browser, locally or 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.

  • Selection:
    • Click on the Export tab, then select Custom - display all possible options, making sure to have the correct database(s) selected.
  • Output:
    • Create your output filename in case you do not want to use the auto-generated default.
  • Object creation:
    • Include the DROP DATABASE IF EXISTS and
    • DROP TABLE / VIEW / PROCEDURE / FUNCTION / EVENT / TRIGGER statements

To begin the backup, click the Go button in the bottom left corner. A download link of the export file will be presented to you when the process has completed.

3.2 MySQL Workbench

MySQL Workbench

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

  • From the main menu, select Server, then the submenu item Export data….
    Note: 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 boldface.
  • Administration – Data Export:
    • Tables to export: Select the schema(s) or database(s) you want to export, their respective data tables will be selected automatically.
    • Export options:
      • Select Export to Self-Contained File, and browse/create to the preferred folder and file name.
      • Include 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 if no errors were found.

3.3 HeidiSQL (MariaDB)

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

4 Command-line interface

4.1 MySQLdump

MySQLdump script for Windows OSes MySQLdump script for Windows OSes using MySQLdump is possible, and the tool is typically provided along with the mysql/mariadb-server installation. This is a script that can be used either manually or be hung into a time-repetitive scheduler. 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 .bat (batch) file into the location+filename of your choosing. You can export database contents for the Kodi video library, music library, and the database user for the Kodi application.

cls
@echo off

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

REM UNTESTED SCRIPT !!
REM TO BE TESTED ON WINDOWS 11 Pro with MYSQL SERVER 5.7.36 ON UBUNTU SERVER 18.04

REM SET FORMATTED TIMESTAMP
SET A/ timestamp=%date:~0,2%_%time:~0,2%_%time:~3,2%_%time:~6,2%

REM EXPORT PATH, ADJUST TO YOUR OWN CONVENIENCE
SET A/ path="/home/user/Dropbox/MySQL/kodidtbs_v19_""%timestamp%""_"

REM FILENAMES
SET A/ video_export=%path% + "video.sql"
SET A/ music_export=%path% + "music.sql"
SET A/ user_export =%path% + "user.sql"

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

REM FIXED PARAMETERS FOR KODI DATABASES
SET A/ params=" --add-drop-database --add-drop-table --add-drop-trigger --routines --triggers "

REM VARIABLE PARAMETERS FOR KODI DATABASES
SET A/ video=" --databases MyVideos119 "
SET A/ music=" --databases MyMusic82 "

REM ---------------------------------------------------------------------------------------

REM CHECK IF USER-GIVEN ARGUMENTS ARE PRESENT
for %%x in (%*) do (
	REM CHECK FOR VIDEO
	if (%%x == "v") OR (%%x == "video") echo "Exporting video to : %video_export%"; mysqldump %user% %srvr% %params% %video% > %video_export%
	if (%%x == "m") OR (%%x == "music") echo "Exporting music to : %music_export%"; mysqldump %user% %srvr% %params% %music% > %music_export%
	if (%%x == "u") OR (%%x == "user")  echo "Exporting user to : "%$user_export%;  mysqldump %user% %srvr% %params% --where="User='kodi'" > %user_export%
)

if (%%x ='')
	echo "EXAMPLE: dtbs-export.bat 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

REM EXIT

MySQLdump script for Linux OSes Using MySQLdump in Linux is also possible, and is typically 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 yourfilename.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/user/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

1 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.

2 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:

mysqldump -u kodi -p MyVideos119 < /path/to/your/videoexportfile.sql
mysqldump -u kodi -p MyVideos82  < /path/to/your/musicexportfile.sql

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.