Open-AudIT ships with a .sql file used to build the database schema. It is a simple matter to use this and reset the database. 

NOTE - If you have changed the credentials used by Open-AudIT to access MySQL you will need to adjust the below commands. 

Recreate Database

Linux

mysql -u openaudit -popenauditpassword openaudit < /usr/local/open-audit/other/openaudit_mysql.sql

...

Windows

c:\xampplitexampp\mysql\bin\mysql.exe -u openaudit -popenauditpassword openaudit < c:\xampplitexampp\open-audit\other\openaudit_mysql.sql

...

Completely Delete Database and Recreate

If you would like to completely drop the database, before running the above, run the below command then run the abovecommands.

 LinuxLinux

mysql -u root -popenauditrootuserpassword -e "drop database openaudit; create database openaudit;"

...

mysql -u openaudit -popenauditpassword openaudit < /usr/local/open-audit/other/openaudit_mysql.sql

Windows

c:\xampplitexampp\mysql\bin\mysql.exe -u root -popenauditrootuserpassword -e "drop database openaudit; create database openaudit;"
c:\xampp\mysql\bin\mysql.exe -u openaudit -popenauditpassword openaudit < c:\xampp\open-audit\other\openaudit_mysql.sql

Backup the Database

Linux

mysqldump -u openaudit -popenauditpassword --routines --extended-insert=FALSE openaudit > /tmp/openaudit_mysql.sql

Windows

c:\xampp\mysql\bin\mysqldump.exe -u openaudit -popenauditpassword --routines openaudit > c:\temp\openaudit_mysql.sql

Restore the Database

Linux

mysql -u openaudit -popenauditpassword openaudit < /tmp/openaudit_mysql.sql

Windows

c:\xampp\mysql\bin\mysql.exe -u openaudit -popenauditpassword openaudit < c:\temp\openaudit_mysql.sql

NOTE - If you restore the database onto a fresh install or an upgraded install, you will need to update your tasks (if you have any). Run the below.

Windows -

c:\xampp\mysql\bin\mysql.exe -u openaudit -popenauditpassword openaudit -e "UPDATE tasks SET uuid = (SELECT value FROM configuration WHERE name = 'uuid');"

Linux -

mysql -u openaudit -popenauditpassword openaudit -e "UPDATE tasks SET uuid = (SELECT value FROM configuration WHERE name = 'uuid');"

Moving the Database Files on Windows

Sometimes users don't want the actual MySQL data on their C Drive. An example of moving the files to G Drive is below. Note - the MySQL server itself is left on C Drive, only the database data files are moved.

First, open a command prompt with Administrator rights, then run the below commands.

net stop mysql
xcopy C:\xampp\mysql\data G:\data /O /X /E /H /K
move C:\xampp\mysql\data C:\xampp\mysql\data.bak
mklink /J C:\xampp\mysql\data G:\data
net start mysql

NOTE - We have not deleted the files in the original c:\xampp\mysql\data directory. This is left to the user to do when they are happy the files (now on G Drive) are working as intended.

To reverse the procedure (assuming you still have your original files in c:\xampp\mysql\data.bak, again open a command prompt with Administrator rights, then run the below commands.

net stop mysql
rmdir c:\xampp\mysql\data
move c:\xampp\mysql\data.bak c:\xampp\mysql\data
net start mysql

NOTE - the actual data in g:\data has not been deleted and is left for the user to do.

Restoring the database on another machine

First back-up the database as above.

On the new machine, install MySQL.

For all steps, you will need to know the MySQL root users password.

• If MySQL root user has a password, all commands below should use the -p switch so you can input the password.
• If MySQL root user does not have a password, use as below.

Create a new instance of a database

mysql -u root -e "CREATE DATABASE openaudit;"

Create the Open-AudIT user.

mysql -u root -e "CREATE USER openaudit@localhost IDENTIFIED BY 'openauditpassword';"

And allow the user access to the database.

mysql -u root -e "GRANT ALL PRIVILEGES ON openaudit.* TO openaudit@localhost IDENTIFIED BY 'openauditpassword'; FLUSH PRIVILEGES;"

Copy the database backup file to the new machine (in this case to /tmp/openaudit_mysql.sql).

Restore the database, as above.