[Newbie] How to do erpnext database replication

1

Hello all
i am a newbie to erpnext system, i have seen how to backup erpnext database Backup & Restore - #11 by revant_one
but when i decided to do database replication using another server i thought about Mariadb.

i use erpnext VM based on ubuntu14.04 but when i do mariadb command the shell raises an error although mariadb is used by mysql !?

frappe@erpnext~$ mariadb
mariadb: command not found

frappe@erpnext:~$ mysql --version
mysql Ver 15.1 Distrib 10.0.32-MariaDB, for debian-linux-gnu (i686) using readline 5.2

frappe@erpnext:~$ mysql -u root -p !@#!@#
MariaDB [(none)]>

Shall i re-install mariadb ?
or shall i use rsync since the backed up databases are already (–prepared ) ?

[ My fear is when there are some transactions taking a place during my backup, that is why i am thinking about using mariadb when doing replication instead of rsync ]

maybe you have a better solution, or i miss understood something
Please advice, your help is highly appreciated.

2

If you want to run MySQL cmd shell:
bench mysql

Make sure you’re at frappe-bench directory.

1 Like
3

@ibrasec

As indicated clearly in the Error above, MariaDB uses the same terminal command as MySQL does, which is mysql command

There is no need to reinstall MariaDB its working fine by the looks of it.

With regards to the backups, you can schedule them to take place when the system is not in use, like 1am and for that you simply change the cron times

Hope this helps

2 Likes
4

Dear @ibrasec for your reference
Bench Commands Cheatsheet

The backup very important

5

Thanks for your replies @saidsl @magic-overflow

I am interrested to backup the databases (locally and remotely) with a minimum SLA of every hour. yes i know about how to use crontab -e to schedual this but the problem with such SLA level is that full backup might not be liked by the user during his working hours, also there might be many transactions happening at the moment of taking the backup… and that led me to think about using mariaDB incremental backup every hour and full backup after the midnight…

what i am trying to do is using such command:
mariabackup --backup --target-dir /var/mariadb/inc1/ --incremental-basedir /var/mariadb/backup/

but it seems that the shell doesnt know where is such command
How could i solve this issue .

6

And do you think you wont have transactions happening during your incremental backup? I would suggest you to first get some data regarding the full backup and its impact from a users perspective instead of assuming he won’t like it. Incremental backups aren’t like full backups, they are very difficult to maintain and restore. If you can’t have a reliable restore mechanism, your backup is of no use.

1 Like
7

@ibrasec

You should find the following guidelines helpful if you wish to do DB replication.

Specifically this one https://mariadb.com/kb/en/library/setting-up-replication/

NOTE: Its still way easier to setup crontab -e as opposed to setting up replication. I also don’t think the impact would that great to the users. (Subject to DB size)

2 Likes
8

Thanks @saidsl
My issue has been fixed simply by using mysqldump

I didn’t use mariadb nor xtrabackup

i used Two erpnext virtual machines, i did rsync to make full backup and restored it into the slave
and did what was necessary for the slave to work independently first.
then i used mysqldump and configured one VM to work as a master and the other as a slave…
i tried to add something on the master ERPNext (via http) and i could found it replicated into the slave immediatly, and that was what i want… scheduling rsync using crontab -e will be used to syncronize the files stored inside the related site.

currently i want to do more tests on this to make sure everything is identical… maybe i will try something else if someone pointed to an issue with this or gave another opinion.

@spoojary
No need to have a tough speech against a [ newbie ] user… i have no offensive against Frappe/ERPNext, i am just willing to give my best.

3 Likes
9

Care to explain what is wrong in the ‘advice’ I gave? You mentioned about the consistency during full backup which is also applicable to incremental backups. How are you going to deal with that using rsync? You might get 100’s of restore working successfully, but it won’t guarantee that the restore that you really need will be successful with rsync approach. For backup, you either use the supported backup utilities from MariaDB or you stop the server and copy the database files. Anything else will have the same consistency issue.