This section describes how to carry out common maintenance tasks with MySQL – the default database server for CatDV Server. If you are using Oracle or Microsoft SQL Server please refer to the relevant documentation as to how to perform equivalent tasks on those systems.
Backups
To backup the entire CatDV database to a single flat file shut down the CatDV server and use the commands:
mysqldump -u catdvadmin -pcatdv catdv > backupXXXX.sql gzip backupXXXX.sql
where backupXXXX.sql is a filename you choose (perhaps including today’s date), and gzip is used to compress the file, which would otherwise be quite large (you can use other compression tools or omit this step if you prefer).
The CatDV Control Panel has a convenient command to simplify exporting the CatDV database using the mysqldump program.
MySQL gives you other options for backing up the database files as well. You can directly backup the MySQL data files (in /usr/local/mysql/data/catdv, /var/mysql/catdv or C:mysqldatacatdv or a similar location) using your regular backup utility, or consult the MySQL documentation for details of other backup strategies (such as writing a transaction log to a remote file system).
IMPORTANT: Because it needs to fit in with your existing backup strategy please note that the CatDV server does not perform any backups automatically by itself. It is your responsibility to ensure that you create regular and adequate backups that meet your requirements. You should also test that your strategy works by performing a restore before you rely on it.
Performing a daily backup using cron (on Mac OS X or Linux)
Using a text editor, create a shell script to do the backup, eg. create a file backup.sh in /usr/local/catdvServer containing:
#!/bin/sh /usr/local/mysql/bin/mysqldump -u catdvadmin -pcatdv catdv > /XXX/backup-`date +%Y-%m-%d`.sql
Replace XXX with wherever you want the backups to go, for example /Volumes/CATDV/Backups. The file will then be named something like “backup-2011-07-22.sql”.
You can put the script somewhere else if you prefer, but assuming it’s named as above make it executable by launching a Terminal prompt and typing:
chmod +x /usr/local/catdvServer
Finally, create a cron job by editing your crontab table by typing
crontab -e
in a Terminal window, then adding a line
30 3 * * * /usr/local/catdvServer/backup.sh
The crontab -e command will use your default command line, which may well be vi, in which case beware this is not a normal “just type stuff in” editor!! It has modes and you type single letter commands: type i to enter insert mode, enter the line above, then press Esc to leave insert mode, then ZZ to save and exit. Case is significant!
The first five fields in the crontab line say when you want the command to be performed: minutes (00-59), hours (00-23), day of month (1-31), month (1-12), day of week (0-6, where 0=Sunday), with a * to mean any value. The line above thus tells it to run the job at 3.30am every day but you could also choose to run the script once per week say.
Restoring database or moving MySQL to another machine
If you need to migrate the CatDV server and database to another machine install MySQL and the CatDV Server on the new machine then shut down MySQL on both machines and copy the MySQL data files from one machine to the other. The data files are typically in the directory in /usr/local/mysql/data/catdv, /var/mysql/catdv or C:mysqldatacatdv or a similar location, depending on where you installed MySQL. Never simply overwrite an existing catdv data directory, always rename it first (eg. to catdv_old) in case you make a mistake!
You can also copy the catdv database to another machine by loading a backup file. The instructions are the same if you want to restore a database from a backup.
First, shutdown the MySQL server, then locate any existing catdv data files (in /var/mysql/catdv /usr/local/mysql/data/catdv, or C:mysqldatacatdv or a similar location) and rename the existing directory if it exists to catdv_old.
Next, create an empty ‘catdv’ database:
mysql –u root –psecret CREATE DATABASE catdv ; QUIT
You can then apply the backup script to load the data into the new catdv database:
mysql –u root –psecret catdv < backupXXX.sql
where backupXXXX.sql is the name of your backup file and secret is the MySQL root password if any. Warning: restoring from a backup script will silently delete any existing catdv database at the destination, so you should always backup the catdv data directory first!
If you are moving the data to a new machine or renamed the old catdv database you will need to create an empty ‘catdv’ database first before loading the script. Also, if you are moving from a non-case preserving system to a case-sensitive file system (eg. from Windows to Unix) you might need to manually rename all the tables with mixed case names (RENAME TABLE sourcemedia TO sourceMedia; etc.) otherwise the CatDV server will fail with errors because it thinks the tables it needs are missing.
You can also use the new Import Database command from the control panel Tools menu.
Resetting the administrator password
If a normal user forgets his or her password then a CatDV user with systems administrator privileges can reset that user’s password using the user admin page in the CatDV Pro client.
If you forget the system administrator’s password, however, then you need direct access to the database via the command line to reset the password:
mysql –u root –psecret
You may need to enter the full path to the mysql command, and will need to type in your MySQL root password (not the same as the operating system administrator password) if you have one. For example, on Mac OS X (not Mac OS X Server) you will probably need to type:
/usr/local/mysql/bin/mysql –u root
Once you get the mysql> prompt, type the following (including the semicolon):
UPDATE catdv.c_user SET password=0 WHERE id=0 ; QUIT
This will reset the Administrator account so it has a blank password.
Recovering a ‘crashed’ table
If you power down the serer machine without shutting down the CatDV and MySQL server processes first it is possible that the database might be left in an inconsistent state and the CatDV doesn’t start up normally.
If a table is marked as crashed you can usually recover it using the ‘myisamchk’ command as follows.
Stop the CatDV Server if it’s running, then if you’re running Mac OS X open a Terminal window and type the following . Enter your administrator password when prompted. This will give you superuser access which you need because the catdv data directory is normally protected.
sudo sh
The prompt will change to ‘#’, then type the following to check the tables and display a message if any tables are corrupt and need repair.
cd /usr/local/mysql/data/catdv /usr/local/mysql/bin/myisamchk -s *.MYI
Assuming the database does need repair, the next step will make a backup of the entire catdv data directory in a compressed archive before you make any changes. This step is optional and will take a few moments but is a good idea in case anything goes wrong during the repair:
cd .. tar czf catdv_backup.tgz catdv cd catdv
Next, perform the actual table repair:
/usr/local/mysql/bin/myisamchk -r *.MYI
Assuming this completes without errors, repeat the earlier check to make sure the database is ok now:
/usr/local/mysql/bin/myisamchk -s *.MYI
Finally, log out of the superuser account by typing ‘exit’ and close the Terminal window. Restart the CatDV Server and it should now start normally.
If you’re using the MySQL built in to Mac OS X Server the data directory is /var/mysql/catdv instead of /usr/local/mysql/data/catdv, and the path to myisamchk is /usr/bin/myisamchk instead of /usr/local/mysql/bin/myisamchk. If you’re on Windows the basic steps are the same but please consult the online MySQL documentation for details on how to run the MYISAMCHK.EXE command.