1 of 3

Database

Create a database

1. Go to your Open Enventory Login page on your web browser

2. On the Login page, fill in the following info: - Database: name for the new database - Username: root - Password: your mysql root password

3. Open Enventory will then create a new empty database

Connect databases

This allows for users in one database to see chemicals in another database and/or vice versa.

Note: in the default setting, OE only allows user in one database to see if other databases have chemical(s) but not revealing the chemical's location.

If you want to let users in one database to be able to see location of chemicals in another database, after connecting the databases, please follow the instruction in this link.

1. Go to your Open Enventory Login page on your web browser

2. Log into your database of interest using root as Username and your mysql root password as Password

3. Go to Settings on Top menus:

4. On the left side menu, choose Manage databases :

5. There are two types of connections:

2-way connections: users in one database can see/search chemicals in another database AND vice versa
1-way connection: users in one database can see/search chemicals in another database BUT NOT vice versa)

choose the desired connection and follow the instruction below.

Step 1: Choose as databases that you want to connect (hold Ctrl while choosing). You can choose more than 2. Click select all if you want to connect all databases

Step 2: Click the button Connect selected databases

Using the rubric table, the databases with name in the bold format and listed horizontally in the first row of the table will be able to see/search chemical in database(s) (list vertically in the first column) that has checked check box

For example: In the picture below, users in romo database will be able to see/search chemicals in romo_test database BUT users in romo_test database will NOT be able to see/search any chemicals in romo database.

Step 1: using the info above, make the connection of interest by checking the check boxes

Done! You can now search chemicals in other database(s) in the search page

Database Backup and Restore

This is procedure to backup Open Enventory database and restore it later on the same server or migrating to a different server

For traditional LAMP stack

This guide is for Linux environment with Apache2, PHP and MariaDB (MySQL) (LAMP) stack:

Save or Backup Database(s)

For chemicals and lab journal databases:

1. In Terminal window, type the following command:

mysqldump -u [user] -p [database] > [Path]/[backup_filename].sql

Replace [italic_text] (including the square bracket) with specific info:

[user]: in most cases, use root (recommended)
[database]: use the name of the database that you want to backup
[Path] and [backup_filename] : any path and filename

If you want to compress the SQL backup files, you can use this command instead:

mysqldump -u [user] -p [database] | xz > [Path]/[backup_filename].sql.xz

| xz was added for compressing SQL file to .xz file
Make sure that you have .xz at the end of the file as above.

.xz files seem to have the best compression vs bzip2 and gzip for SQL files. You can see more info here and here:

For example usage of xz, see this link.

2. Type in password for the user above and then Enter (nothing will show when you are typing the password)

For users info:

1. In Terminal window, type the following command:

mysqldump -u [user] -p mysql > [Path]/[user_backup_filename].sql

Replace [italic_text] (including the square bracket) with specific info:

[user]: in most cases, use root (recommended)
[Path] and [backup_filename] : any path and filename

While this approach might work well in case of same MySQL or MariaDB version, if you migrate or upgrade SQL, you want to follow the instruction here for better users backup:

2. Type in password for the user above and then Enter (nothing will show when you are typing the password)

Restore

Step 1: transfer both files (database and users, if you want to keep the same users and passwords) to the new system/server

Step 2: create the database from OE web login if you have not done so.

Step 3 (optional):

If you have compressed the SQL file as instruction above, you will need to decompress the file first.

This is for compressed file with .xz extension:

unxz -vk [/path/backup-database].sql.xz

-v : verbose, give more info
k : to keep the compressed file, this will generated the uncompressed file .sql

Step 4: use ssh and terminal, login as terminal root and run:

mysql -u root -p [database-name] < [/path/backup-database].sql    #for database restore
mysql -u root -p mysql < [/path/backup-user].sql    #for users restore, this might not work for mariabd 10.4+

Replace [italic_text] (including the square bracket) with specific info:

[database-name]: use the name of the database that you want to backup
[/path/backup-database] and [/path/backup-user]: any path and filename

Step 5: go to OE on web browser and login with mysql root account.

If the mysql root user in the old database has password different than the current mysql root user, after step 3 above, the root user password will be changed to the old password.If you don't know the old mysql root password, follow this tutorial to reset mysql root password.

Step 6: go to Settings and then click on Recreate users. That should reactivate all users and their passwords.

Again, if you have migrate from MariaDB < 10.4 to MariaDB 10.4+, it might not work and we have to change the command in step 3 above. The reason is because the mysql.user table is deprecated in MariaDB 10.4. You can read more about it here.

For XAMPP users

While the general steps are similar, the exact paths to the executable command are different for Mac OS and Windows using XAMPP, please follow the corresponding guides below:

Save or Backup Database

For chemicals and lab journal databases:

1. In Terminal window, type the following command:

/Applications/XAMPP/xamppfiles/bin/mysqldump -u [user] -p [database] > [Path]/[backup_filename].sql

Replace [italic_text] (including the square bracket) with specific info:

[user]: in most cases, use root (recommended).
[database]: use the name of the database that you want to backup
[Path] and [backup_filename] : any path and filename

2. Type in password for the user above and then Enter (nothing will show when you are typing the password)

For users info:

1. In Terminal window, type the following command:

/Applications/XAMPP/xamppfiles/bin/mysqldump -u [user] -p mysql > [Path]/[user_backup_filename].sql

Replace [italic_text] (including the square bracket) with specific info:

[user]: in most cases, use root (recommended).
[Path] and [user_backup_filename] : any path and filename

2. Type in password for the user above and then Enter (nothing will show when you are typing the password)

Restore

Step 1: transfer both files (database and users, if you want to keep the same users and passwords) to the new system/server

Step 2: create the database from OE web login if you have not done so.

Step 3: use ssh and terminal, login as terminal root and run:

/Applications/XAMPP/xamppfiles/bin/mysql -u root -p [database-name] < [/path/to/backup-database].sql    #for database restore
/Applications/XAMPP/xamppfiles/bin/mysql -u root -p mysql < [/path/to/backup-user].sql    #for users restore, this might not work for mariabd 10.4+

Replace [italic_text] (including the square bracket) with specific info:

[database-name]: use the name of the database that you want to backup
[/path/to/backup-database]: any path and filename

Step 4: go to OE on web browser and login with mysql root account.

If the mysql root user in the old database has password different than the current mysql root user, after step 3 above, the root user password will be changed to the old password.

If you don't know the old mysql root password, follow this tutorial to reset mysql root password.

Step 5: go to Settings and then click on Recreate users. That should reactivate all users and their passwords.

Save or Backup Database

For chemicals and lab journal databases:

1. Open a Window Powershell window, type the following command:

c:\Programs\XAMPP\mysql\bin\mysqldump -u [user] -p [database] > [Path]/[backup_filename].sql

Replace [italic_text] (including the square bracket) with specific info.

[user]: in most cases, use root (recommended).
[database]: use the name of the database that you want to backup
[Path] and [backup_filename] : any path and filename

In the command above, this part is for the default XAMPP set up. If some error happens, you should find the executable file inside XAMPP folder in your system. You can try to follow this link.

2. Type in password for the user above and then Enter (nothing will show when you are typing the password)

For users info:

1. In Terminal window, type the following command:

c:\Programs\XAMPP\mysql\bin\mysqldump -u [user] -p mysql > [Path]/[user_backup_filename].sql

Replace [italic_text] (including the square bracket) with specific info:

[user]: in most cases, use root (recommended).
[Path] and [user_backup_filename] : any path and filename

2. Type in password for the user above and then Enter (nothing will show when you are typing the password)

Restore

Step 1: transfer both files (database and users, if you want to keep the same users and passwords) to the new system/server

Step 2: create the database from OE web login if you have not done so.

Step 3: use ssh and terminal, login as terminal root and run:

c:\Programs\XAMPP\mysql\bin\mysql -u root -p [database-name] < [/path/to/backup-database].sql    #for database restore
c:\Programs\XAMPP\mysql\bin\mysql -u root -p mysql < [/path/to/backup-user].sql    #for users restore, this might not work for mariabd 10.4+

Replace [italic_text] (including the square bracket) with specific info:

[database-name]: use the name of the database that you want to backup
[/path/to/backup-database]: any path and filename

Step 4: go to OE on web browser and login with mysql root account.

If the mysql root user in the old database has password different than the current mysql root user, after step 3 above, the root user password will be changed to the old password.

If you don't know the old mysql root password, follow this tutorial to reset mysql root password.

Step 5: go to Settings and then click on Recreate users. That should reactivate all users and their passwords.

Best Practice for SQL Users Backup

While the quick user backup as this one-line commandmysqldump -u [user] -p mysql > [Path]/[user_backup_filename].sql might work when you have same version for backup and restore SQL. In most cases, you want to implement something similar to the following bash script

Backup Users

Step 1: On your server, in terminal, prepare the following bash file using vi (or vim or any text-editor of choice)

vim mariadb_user_backup.sh

Step 2: Paste in the content of the file as follow:

mariadb_user_backup.sh

#!/bin/sh

# This bash script is used to save users and permission from mysql database.
# Ref: https://stackoverflow.com/a/57878326
# explanation: https://wisdmlabs.com/blog/migrate-mysql-users-one-server-another/ 

HOSTNAME="localhost"
logininfo="-u root -pPASSWORD"
outfile="/path/oe_backup-user-$(date +\%Y\%m\%d).sql"

# The line below will save all users except 'root', empty name and specified ones
mysql ${logininfo} -B -N -e "SELECT CONCAT('\'',user,'\'@\'',host,'\'') from user where user != 'root' AND user != '' AND user not like 'auto%'" mysql | \
while read uh
do
   echo "SHOW GRANTS FOR ${uh};"
done | mysql ${logininfo} -B -N | sed -e 's/$/;/' > ${outfile}

PASSWORD : change to your root password or the password of the user that you use for the backup process. If you don't use root then change root on line 8 to your user
path : use your specified path to the output file
oe_backup-user-$(date +\%Y\%m\%d).sql : the output file will have the current date concatenated at the end of the file
This script above also not save oldroot user info or empty users. You can change it by modify the code on line 12

Step 3: make sure you (or the user that run the mariadb_user_backup.sh file has the sufficient permission (i.e. chown) and make the script file executable:

chmod u+x mariadb_user_backup.sh

Step 4: Execute the script file to obtain the user backup file

./mariadb_user_backup.sh

Restore Users

Similar to the guide here.

If your new SQL server is newer than your old one, after importing both database(s) and users, you can run the following command in terminal to upgrade MySQL:

mysql_upgrade    # Upgrade MySQL data
systemctl restart mariadb   # Restart mariadb

Database Backup and Restore

This is procedure to backup Open Enventory database and restore it later on the same server or migrating to a different server

For traditional LAMP stack

This guide is for Linux environment with Apache2, PHP and MariaDB (MySQL) (LAMP) stack:

Save or Backup Database(s)

For chemicals and lab journal databases:

1. In Terminal window, type the following command:

mysqldump -u [user] -p [database] > [Path]/[backup_filename].sql

Replace [italic_text] (including the square bracket) with specific info:

[user]: in most cases, use root (recommended)
[database]: use the name of the database that you want to backup
[Path] and [backup_filename] : any path and filename

If you want to compress the SQL backup files, you can use this command instead:

mysqldump -u [user] -p [database] | xz > [Path]/[backup_filename].sql.xz

| xz was added for compressing SQL file to .xz file
Make sure that you have .xz at the end of the file as above.

.xz files seem to have the best compression vs bzip2 and gzip for SQL files. You can see more info here and here:

For example usage of xz, see this link.

2. Type in password for the user above and then Enter (nothing will show when you are typing the password)

For users info:

1. In Terminal window, type the following command:

mysqldump -u [user] -p mysql > [Path]/[user_backup_filename].sql

Replace [italic_text] (including the square bracket) with specific info:

[user]: in most cases, use root (recommended)
[Path] and [backup_filename] : any path and filename

While this approach might work well in case of same MySQL or MariaDB version, if you migrate or upgrade SQL, you want to follow the instruction here for better users backup:

2. Type in password for the user above and then Enter (nothing will show when you are typing the password)

Restore

Step 1: transfer both files (database and users, if you want to keep the same users and passwords) to the new system/server

Step 2: create the database from OE web login if you have not done so.

Step 3 (optional):

If you have compressed the SQL file as instruction above, you will need to decompress the file first.

This is for compressed file with .xz extension:

unxz -vk [/path/backup-database].sql.xz

-v : verbose, give more info
k : to keep the compressed file, this will generated the uncompressed file .sql

Step 4: use ssh and terminal, login as terminal root and run:

mysql -u root -p [database-name] < [/path/backup-database].sql    #for database restore
mysql -u root -p mysql < [/path/backup-user].sql    #for users restore, this might not work for mariabd 10.4+

Replace [italic_text] (including the square bracket) with specific info:

[database-name]: use the name of the database that you want to backup
[/path/backup-database] and [/path/backup-user]: any path and filename

Step 5: go to OE on web browser and login with mysql root account.

Step 6: go to Settings and then click on Recreate users. That should reactivate all users and their passwords.

For XAMPP users

While the general steps are similar, the exact paths to the executable command are different for Mac OS and Windows using XAMPP, please follow the corresponding guides below:

Save or Backup Database

For chemicals and lab journal databases:

1. In Terminal window, type the following command:

/Applications/XAMPP/xamppfiles/bin/mysqldump -u [user] -p [database] > [Path]/[backup_filename].sql

Replace [italic_text] (including the square bracket) with specific info:

[user]: in most cases, use root (recommended).
[database]: use the name of the database that you want to backup
[Path] and [backup_filename] : any path and filename

2. Type in password for the user above and then Enter (nothing will show when you are typing the password)

For users info:

1. In Terminal window, type the following command:

/Applications/XAMPP/xamppfiles/bin/mysqldump -u [user] -p mysql > [Path]/[user_backup_filename].sql

Replace [italic_text] (including the square bracket) with specific info:

[user]: in most cases, use root (recommended).
[Path] and [user_backup_filename] : any path and filename

2. Type in password for the user above and then Enter (nothing will show when you are typing the password)

Restore

Step 1: transfer both files (database and users, if you want to keep the same users and passwords) to the new system/server

Step 2: create the database from OE web login if you have not done so.

Step 3: use ssh and terminal, login as terminal root and run:

/Applications/XAMPP/xamppfiles/bin/mysql -u root -p [database-name] < [/path/to/backup-database].sql    #for database restore
/Applications/XAMPP/xamppfiles/bin/mysql -u root -p mysql < [/path/to/backup-user].sql    #for users restore, this might not work for mariabd 10.4+

Replace [italic_text] (including the square bracket) with specific info:

[database-name]: use the name of the database that you want to backup
[/path/to/backup-database]: any path and filename

Step 4: go to OE on web browser and login with mysql root account.

If the mysql root user in the old database has password different than the current mysql root user, after step 3 above, the root user password will be changed to the old password.

If you don't know the old mysql root password, follow this tutorial to reset mysql root password.

Step 5: go to Settings and then click on Recreate users. That should reactivate all users and their passwords.

Save or Backup Database

For chemicals and lab journal databases:

1. Open a Window Powershell window, type the following command:

c:\Programs\XAMPP\mysql\bin\mysqldump -u [user] -p [database] > [Path]/[backup_filename].sql

Replace [italic_text] (including the square bracket) with specific info.

[user]: in most cases, use root (recommended).
[database]: use the name of the database that you want to backup
[Path] and [backup_filename] : any path and filename

In the command above, this part is for the default XAMPP set up. If some error happens, you should find the executable file inside XAMPP folder in your system. You can try to follow this link.

2. Type in password for the user above and then Enter (nothing will show when you are typing the password)

For users info:

1. In Terminal window, type the following command:

c:\Programs\XAMPP\mysql\bin\mysqldump -u [user] -p mysql > [Path]/[user_backup_filename].sql

Replace [italic_text] (including the square bracket) with specific info:

[user]: in most cases, use root (recommended).
[Path] and [user_backup_filename] : any path and filename

2. Type in password for the user above and then Enter (nothing will show when you are typing the password)

Restore

Step 1: transfer both files (database and users, if you want to keep the same users and passwords) to the new system/server

Step 2: create the database from OE web login if you have not done so.

Step 3: use ssh and terminal, login as terminal root and run:

c:\Programs\XAMPP\mysql\bin\mysql -u root -p [database-name] < [/path/to/backup-database].sql    #for database restore
c:\Programs\XAMPP\mysql\bin\mysql -u root -p mysql < [/path/to/backup-user].sql    #for users restore, this might not work for mariabd 10.4+

Replace [italic_text] (including the square bracket) with specific info:

[database-name]: use the name of the database that you want to backup
[/path/to/backup-database]: any path and filename

Step 4: go to OE on web browser and login with mysql root account.

If the mysql root user in the old database has password different than the current mysql root user, after step 3 above, the root user password will be changed to the old password.

If you don't know the old mysql root password, follow this tutorial to reset mysql root password.

Step 5: go to Settings and then click on Recreate users. That should reactivate all users and their passwords.