Restoring from mysql files?

Install / Update
1

I have been running ERPNext for a year as a Docker container. Unfortunately, the container became corrupted, and wouldn’t load any more.
I have re-installed the container but now have trouble accessing the database.
Everything from inside the container has gone, but I still have the sql database files:
root@Tower:~# ls -l /mnt/user/erpdata/
total 176212
drwx------ 1 65534 108 40960 Dec 9 2017 1bd3e0294da19198/
-rw-rw---- 1 65534 108 16384 Oct 2 2017 aria_log.00000001
-rw-rw---- 1 65534 108 52 Oct 2 2017 aria_log_control
-rw-r–r-- 1 root root 0 Oct 2 2017 debian-10.1.flag
-rw-rw---- 1 65534 108 50331648 Oct 30 09:58 ib_logfile0
-rw-rw---- 1 65534 108 50331648 Oct 30 09:58 ib_logfile1
-rw-rw---- 1 65534 108 79691776 Oct 30 09:58 ibdata1
-rw-rw---- 1 65534 108 0 Oct 2 2017 multi-master.info
drwx------ 1 65534 root 4096 Oct 2 2017 mysql/
-rw------- 1 root root 15 Oct 2 2017 mysql_upgrade_info
drwx------ 1 65534 108 27 Oct 2 2017 performance_schema/

I can re-install the docker container but if I point it at the saved database, it fails to start, apparently having trouble with the mysqld daemon which is stopping and starting repeatedly:
2018-10-30 12:44:57,707 INFO spawned: ‘mysqld’ with pid 503
2018-10-30 12:44:59,076 INFO success: mysqld entered RUNNING state, process has stayed up for > than 1 seconds (startsecs)
2018-10-30 12:45:01,757 INFO exited: mysqld (exit status 0; expected)
2018-10-30 12:45:02,408 INFO spawned: ‘mysqld’ with pid 861
2018-10-30 12:45:02,408 INFO success: cron entered RUNNING state, process has stayed up for > than 10 seconds (startsecs)
2018-10-30 12:45:02,408 INFO spawned: ‘mysqld’ with pid 861
2018-10-30 12:45:02,408 INFO success: cron entered RUNNING state, process has stayed up for > than 10 seconds (startsecs)
2018-10-30 12:45:03,835 INFO success: mysqld entered RUNNING state, process has stayed up for > than 1 seconds (startsecs)
2018-10-30 12:45:06,982 INFO exited: mysqld (exit status 0; expected)
2018-10-30 12:45:07,985 INFO spawned: ‘mysqld’ with pid 1219
2018-10-30 12:45:09,280 INFO success: mysqld entered RUNNING state, process has stayed up for > than 1 seconds (startsecs)
2018-10-30 12:45:09,280 INFO success: mysqld entered RUNNING state, process has stayed up for > than 1 seconds (startsecs)
2018-10-30 12:45:11,968 INFO exited: mysqld (exit status 0; expected)
2018-10-30 12:45:12,972 INFO spawned: ‘mysqld’ with pid 1577
2018-10-30 12:45:14,398 INFO success: mysqld entered RUNNING state, process has stayed up for > than 1 seconds (startsecs)
2018-10-30 12:45:16,966 INFO exited: mysqld (exit status 0; expected)
2018-10-30 12:45:17,969 INFO spawned: ‘mysqld’ with pid 1935
2018-10-30 12:45:19,313 INFO success: mysqld entered RUNNING state, process has stayed up for > than 1 seconds (startsecs)
2018-10-30 12:45:22,593 INFO exited: mysqld (exit status 0; expected)
2018-10-30 12:45:22,593 INFO exited: mysqld (exit status 0; expected)

I don’t have a ‘bench backup’.
Is there any way of recovering my data with a new installation of (dockerised) ERPNext?

2

If you have kept the same versions etc, then you can copy/overwrite the test DB with the DB contents from the backup. If you are not familiar with command line methods, you could load a free GUI utility like HeidiSQL to do this.

3

Thanks for your reply. I thought that I had, effectively, been replacing the test DB with the database I last ran with - however, this results in the repeated restarts of the mysqld process.
I believe that I had been running v9.2.23 and that is what I had reinstated. Is there anywhere in the db files where the application version would have been recorded so that I can prove this?
Perhaps it is the MariaDB version which is incorrect?
The MariaDB monitor in my newly installed docker reports : Server version: 10.0.33-MariaDB-1~xenial
but my saved mysql_upgrade_info file contains : 10.1.28-MariaDB
Could this be the source of my problem?

4

As far as I know, they are compatible - but - I don’t know/think you can just copy/overwrite the data files directly.
If unsure, try something along these lines…

  • MAKE BACKUPS!
  • Check that you have a backup
    • now it’s sort of safe to start
  • create a VM which will be just a DB server - using the same version of mysql as you did on your old system. Let’s call it VM(old)
  • create a VM with same version as your new system, or open your new system up (with DB access to the mysql instance).
  • copy the specific DB from the VM(old) → VM(new)
    • You might need to do this for each/all tables, depending on your setups

After getting the DB across, and assuming that mysqld stays up, you would then just need to run a bench update --patch or a bench migrate to ensure that everything lines up

5

If you are looking for restoring database and you already have .sql backup files then
try this command inside frappe-bench:

mysql -u 'your dbname' -p'yourpassword' "again your dbname" <path upto the file where your backup .sql file is stored.
before this command check your dbname and password in site_config.json file.