How to Backup and Restore Time Tracker Database
utility to dump the content of your database to a file. For example, for a database named timetracker
type the following at the command prompt:
# mysqldump -u root -p[root_password] --default-character-set=utf8mb4 timetracker > timetracker.sql
The resulting timetracker.sql file will contain SQL statements required to recreate the database from scratch. If you need to dump a database from a remote server
, use this command:
# mysqldump -u root -p[root_password] -h server_name --default-character-set=utf8mb4 timetracker > timetracker.sql
How to Restore
If you need to restore your database from a dump file use the following command:
# mysql -u root -p[root_password] db_name < dump_file.sql
Do not restore an old dump file into a running production database. The entire content of db_name
will be re-created from dump_file.sql
and your recent changes will get lost.
Resolving Problems with UTF-8 Encoding
After import, you may see corrupted UTF-8 encoded characters (such as ß in German Straße, accented ä and so on). What happens after import is that instead of an ß in "Straße" you may see garbage like "StraÃŸe" on your Time Tracker web pages. And if you examine the dump_file.sql with a binary editor, you may see that the ß, instead of needed UTF-8 2 byte encoding "C3 9F" is encoded with 4 bytes such as "C3 83 C5 B8".
The problem may be difficult to troubleshoot because it's hard to determine where the corruption occurs. For example, you may think it's the import problem, or may be console-input related problem, while the issue is in the corrupted dump file. And your database may be large to further complicate things. We located the problem like this:
- Created a simple database with one table and one varchar(80) field.
- Inserted "Straße" into a row it via a modified Time Tracker web page (to ensure UTF-8 encoding).
- Did a dump as per command above.
- Examined the content of the dump file with a binary editor. At this point it became evident that the ß is encoded by 4-bytes instead of 2 "C3 9F".
It is best to start with the database to see binary codes for Unicode characters in it. For example, register a new account with ß in all fields and then examine the data with a query like this (substitute 10 with a real user id):
select name, hex(name) from tt_users where id = 10;
Normal output is like this for utf8mb4 encoding. hex()
function obtains hexadecimal content of the field.
mysql> select name, hex(name) from tt_users where id = 10;
| name | hex(name) |
| ß | C39F |
If instead you see "C383C5B8" in there (for example, when not using charset=utf8mb4 in your DSN), then encoding in database is wrong and you need to fix it using a solution described below.
The solution consists of 2 steps.
- Call mysqldump with the "--default-character-set=latin1" parameter.
- Edit the dump file and change the encoding in "SET NAMES" instruction in beginning of the file to "utf8".
The resulting mysqldump command:
# mysqldump -u root -p[root_password] --default-character-set=latin1 timetracker > timetracker.sql
This is how the "SET NAMES" line in the dump file should look like after editing.
/*!40101 SET NAMES utf8mb4 */;
There is no change to the restore procedure. Use mysql command as above.
How to Edit Large Files
If your file is large, you may have a problem editing it or even finding a suitable editor to handle it. We used the following script called hfnano
written by user B T at this stackoverflow thread
. It opens a small section of a large file in nano editor, then saves your changes back into a large file:
if [ "$#" -ne 3 ]; then
echo "Usage: $0 hugeFilePath startLine endLine" >&2
sed -n -e $2','$3'p' -e $3'q' $1 > hfnano_temporary_file
(head -n `expr $2 - 1` $1; cat hfnano_temporary_file; sed -e '1,'$3'd' $1) > hfnano_temporary_file2
cat hfnano_temporary_file2 > $1
rm hfnano_temporary_file hfnano_temporary_file2
Related Double-Encoding Problem with Console Output
In some situations there may occur a similar double-encoding problem with mysql console output. For example, Time Tracker application may work just fine but when you select records in mysql console, some characters become double-encoded and printed as garbage. "Straße" prints normally in Time Tracker pages but as "StraÃŸe" in mysql console output. A solution in this case is one of the following:
1) Adjust server settings so that its character_set variables are utf8mb4
and not latin1
. You can examine these variables with a command like this:
mysql> show variables where variable_name like 'character_set_%';
Change server config files, restart it, and make sure you see utf8mb4 in there. For MariaDB
the following settings may work:
2) Try using latin1
character set for mysql client. Either start mysql console as:
or put the following in .my.cnf
Time Tracker FAQ