Running A Backup Before Updating Your Flow Data Server

Owned by Chris Mintz (Unlicensed)
Last updated: Mar 19, 2024 by Jonathan Schmale
4 min read

Before running an update, it is recommended that you pull an update of your database. 

In this section:

Unicode Support Note

For versions 9.6.1.1 and greater when you want full Unicode support you should use a batch file backup option though there is also a workaround for Workbench too.

If you want to know more about Unicode see the notes in Unicode and UTF-8

Batch File Backups

One Time Quick Dump

Use this option for:

  • a quick dump of a database into a single file

  • full Unicode support

You edit this template command to set:

  • password

  • MySQL server folder (5.6 vs 8.0)

  • (others if applicable)

Command Prompt or in batch file
"C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqldump.exe" -u root -p"password" -h localhost --port=3306 --default-character-set=utf8mb4 --single-transaction --quote-names --max_allowed_packet=128MB --log-error="superticker_dumpErrors.txt" --routines -r "superticker_dump.sql" "superticker"

MySQL Dump docs: https://dev.mysql.com/doc/refman/5.6/en/mysqldump.html

NOTES:

  1. It’s currently using MySQL 5.6 path – but you could change that to use it for MySQL 8.

  2. You need to change the text “password” to the actual password (leave the quotes in place).

  3. It will log any errors to the file name superticker_dumpErrors.txt in the current folder.

  4. It will save the dump to the file called:  superticker_dump.sql in the current folder. If you want to have it dump to a specific folder replace the file name by putting the full path inside the quotes.

  5. The final "superticker" at the end is telling it to dump the superticker database.

  6. On machines that do not have the MySQL server installed you can also find the mysqldump and mysql command line utilities in the MySQL Workbench installed files -> like C:\Program Files\MySQL\MySQL Workbench 6.3 CE.

Restore/Import this

To import this you can use a command like this:

"C:\Program Files\MySQL\MySQL Server 8.0\bin\mysql.exe" --user=root --password=your_password --host=localhost --port=3306 "superticker" < "c:\temp\superticker_dump.sql" > "c:\temp\import_results.txt"

You can change change the paths and files to point to your setup. And be sure to change the password to the actual password.

Master Backups for Replication

To create a backup to be used in replication please refer to the notes in Running A Backup Server for Bannister Lake Products with DMan Backup and look for the Create A Master Backup section.

Advanced Backup Options

You might like this option (full Unicode support) if you want to

  • dump each table to a separate file

  • AND / OR

  • setup an automated backup that creates a series of timestamped packages that you can keep on file.

The following is a batch file based process that can successfully backup the database with full Unicode support. A set of template files can be downloaded: https://www.bannisterlake.com/dl/installs/Chameleon/_Utilities/mysql/dump_db_batch_scheduable.zip

This batch file setup uses a specific set of files and folder configuration with a recommended folder location of: D:\Bannister Lake\MySqlBackups

Folder and files are as follows:

📁 zip

📄 7za.exe

📄 dump_db.bat
📄 dump_db_options.cnf
📄 tableList.sql
📄 testDate.bat

When run it will generate the following folders and files

📁backupfiles

📁 superticker
📄 zipped data 1
📄 zipped data 2
...

To configure the batch file you may need to edit the options.cnf file as well as the dump_db.bat file.

Batch File: (look for the folder-path settings in the settings section at the beginning of the batch file)

  • mysqldump.exe path

  • mysql.exe path

  • root backup folder path

  • zip exe path

  • options file path

  • destination backup folder path

    • created when it runs if it doesn't exist already

  • error log path

Options File:

  • specify the user and password to connect with

  • specify the server and port

  • possibly others for your situation

To run this batch file use:

dump_db <database-name>

By default it creates one file per table. To request a single file add the text oneFile to the command following the database name.

The results are put into a zip file and renamed with the date and time.

Examples

MySQL Workbench

The default charset that is used is to export data is utf8. To support full Unicode though we need utf8mb4. To achieve this it's possible to modify Workbench to use utf8mb4 manually.

  • Go to C:\Program Files\MySQL\MySQL Workbench 6.3 CE\modules 

  • open this file wb_admin_export.py. 

  • Create a backup copy

  • Replace all occurrences "default-character-set":"utf8" with "default-character-set":"utf8mb4".

  • Save the file. 

  • Restart Workbench.

The next time you run the export you will see in the log results like this: 

  • Running: mysqldump.exe --defaults-file="c:\users\jonathan\appdata\local\temp\tmpidlh7a.cnf" --host=localhost --protocol=tcp --user=root --allow-keywords=TRUE --port=3306 --default-character-set=utf8mb4 --routines --skip-triggers "superticker"



MySQL Workbench version 6.2

Please refer to this page for details: Data Backup with MySQL Workbench version 6.2 

MySQL Workbench version 5.x

Please refer to this page for details: Data Backup with MySQL Workbench version 5.x

Restoring Backups

Restoring a data backup to it's original location is the simplest scenario.

Importing a backup to a new server

When you move data from one database instance to another - you may have to edit the dumped data to reflect the new database user environment.

Specifically if move data from a localhost setup to a remote database (including an Amazon RDS option) you will need to edit dumped data to remove all references to specific users that may not match. 

Look for statements that include a DEFINER including:

  • views

  • triggers

  • stored procedures and functions