# Database Backup and Restore ## 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: ```bash mysqldump -u [user] -p [database] > [Path]/[backup_filename].sql ``` {% hint style="info" %} 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 {% endhint %} {% hint style="info" %} If you want to compress the SQL backup files, you can use this command instead: ```bash 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 ](https://itsfoss.com/tar-vs-zip-vs-gz/)and [here](https://blog.marceloaltmann.com/backup-compactadoem-mysql-mysqldump-gzip-bzip2/): For example usage of `xz`, see this [link](https://www.rootusers.com/13-simple-xz-examples/). {% endhint %} 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: ```bash mysqldump -u [user] -p mysql > [Path]/[user_backup_filename].sql ``` {% hint style="info" %} 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 {% endhint %} {% hint style="info" %} 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: {% endhint %} 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](https://open-enventory.gitbook.io/user-guides/user-guides/untitled/database#create-a-database) if you have not done so. **Step 3 (optional)**: {% hint style="warning" %} 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: ```bash 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 {% endhint %} **Step 4**: use ssh and terminal, login as terminal root and run: ```bash 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+ ``` {% hint style="info" %} 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 {% endhint %} **Step 5**: go to OE on web browser and login with mysql `root` account. {% hint style="danger" %} 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](https://www.a2hosting.com/kb/developer-corner/mysql/reset-mysql-root-password). {% endhint %} **Step 6**: go to `Settings` and then click on `Recreate users`. That should reactivate all users and their passwords. {% hint style="danger" %} 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](https://mariadb.com/kb/en/library/mysqluser-table/). {% endhint %} ## 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: {% tabs %} {% tab title="Mac OS + XAMPP" %} ### **Save or Backup Database** #### For chemicals and lab journal databases: 1\. In Terminal window, type the following command: ```bash /Applications/XAMPP/xamppfiles/bin/mysqldump -u [user] -p [database] > [Path]/[backup_filename].sql ``` {% hint style="info" %} 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 {% endhint %} 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: ```bash /Applications/XAMPP/xamppfiles/bin/mysqldump -u [user] -p mysql > [Path]/[user_backup_filename].sql ``` {% hint style="info" %} 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 {% endhint %} 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](https://open-enventory.gitbook.io/user-guides/user-guides/untitled/database#create-a-database) if you have not done so. \ **Step 3**: use ssh and terminal, login as terminal root and run: ```bash /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+ ``` {% hint style="info" %} 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 {% endhint %} **Step 4**: go to OE on web browser and login with mysql `root` account. {% hint style="danger" %} 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](https://www.a2hosting.com/kb/developer-corner/mysql/reset-mysql-root-password). {% endhint %} **Step 5**: go to `Settings` and then click on `Recreate users`. That should reactivate all users and their passwords. {% hint style="danger" %} 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](https://mariadb.com/kb/en/library/mysqluser-table/). {% endhint %} {% endtab %} {% tab title="Window 10 + XAMPP" %} ### **Save or Backup Database** #### For chemicals and lab journal databases: 1\. Open a Window Powershell window, type the following command: ```bash c:\Programs\XAMPP\mysql\bin\mysqldump -u [user] -p [database] > [Path]/[backup_filename].sql ``` {% hint style="info" %} 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 {% endhint %} {% hint style="danger" %} 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](https://ourcodeworld.com/articles/read/355/how-to-import-a-mysql-database-using-cmd-prompt-in-xampp-for-windows). {% endhint %} 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: ```bash c:\Programs\XAMPP\mysql\bin\mysqldump -u [user] -p mysql > [Path]/[user_backup_filename].sql ``` {% hint style="info" %} 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 {% endhint %} 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](https://open-enventory.gitbook.io/user-guides/user-guides/untitled/database#create-a-database) if you have not done so. \ **Step 3**: use ssh and terminal, login as terminal root and run: ```bash 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+ ``` {% hint style="info" %} 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 {% endhint %} **Step 4**: go to OE on web browser and login with mysql `root` account. {% hint style="danger" %} 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](https://www.a2hosting.com/kb/developer-corner/mysql/reset-mysql-root-password). {% endhint %} **Step 5**: go to `Settings` and then click on `Recreate users`. That should reactivate all users and their passwords. {% hint style="danger" %} 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](https://mariadb.com/kb/en/library/mysqluser-table/). {% endhint %} ### {% endtab %} {% endtabs %}