Couldn't access MySQL after a restore

Support
1

Greetings,

I am a general practitioner From Ethiopia. I’ve deployed XAMPP/OpenEMR package 5.0.0-4 on Windows 7 for my clinic. Lately, I’ve encounter an issue I wasn’t able to fix.
As this is our first time using it, we were manually documenting patient’s data in parallel with OpenEMR.

After restoring a backup of a XAMPP folder containing our OpenEMR installationfrom an external drive, the MySQL server was unable to start from the XAMPP Control Panel. It gets stuck on “Attempting to start MySQL service…”

Here is a screenshot of the MySQL error log

image
image732×259 161 KB

After consulting various forums and guides, I took these approaches:
I. Hitting on the start button a dozen times continuously
Surprisingly, MySQL started running. And I was able to login to OpenEMR without any loss of data. But this step should be repeated each time after the machine reboots.
II. Deleting ib_logfile0, ib_logfile1 and ibdata1 files
This enables the MySQL server to start running instantly. However, data is lost and OpenEMR is inaccessible. The files are also re-written again after restart because InnoDB is enabled. The following displays when connecting to localhost\openemr.
image
image717×126 29.9 KB

III. Recovering from corrupted InnoDB tables
I first tried bringing up our database in recovery mode. Then, I changed the port used by MySQL server to another random port (8881) and added “innodb_force_recovery=3” to my.ini in the [mysqld] section.
At this point MySQL’s error log displayed the following
image
image717×192 129 KB

Then MySQL started running (presumably in Recovery Mode) but “Check that mysqld is running.” Message is displayed on localhost\openemr.
image
Then, I tried to identify which tables were corrupted by executing the mysqlcheck --all-databases command. It didn’t return anything.
image
But when executing the mysqlcheck -c -u root -p --all-databases command it displayed all tables located in the OpenEMR, MySQL and PhPmyAdmin and all of their statuses were “OK”.
image
The next step would have been to backup and drop the corrupted tables. In our case, I couldn’t see any table with a status set to “Corrupted”. But I tried backing up a random table from the MySQL DB using the mysqldump mysql time_zone > out.sql command after logging in as “root” to the MySQL DB. And a file “out.sql” was created in the XAAMP folder.
image
The final steps would have been to drop the corrupted tables from the database and restore them with a dump file; which I didn’t proceed because no corrupted tables were found.

Please note that OpenEMR is still inaccessible; http://localhost/openemr is still displaying “Check that mysqld is running.” while MySQL is running.

These are the approaches I’ve taken so far. I don’t have a background in IT. I just tried all possible fixes and I’m not even sure if they’re applicable to this issue.

I haven’t installed 5-0-0-Patch-8.zip in my OpenEMR 5.0.0 XAMPP Package Installation and power outages occur from time to time. I’ve used this method [ Windows Backup And Restore Made Easy ]to backup and restore the XAMPP folder.

If anyone experienced a similar issue in the past, kindly share a possible solution and maybe informs us on what may have caused it to occur.

We would like to be able to confidently test run OpenEMR for a period of time before moving our operations on it.

Thank you

2

hi @dagem, you’re using unsupported software, if you’re uncomfortable with using a linux server suggest trying openemr-devops/packages/appliance at master · openemr/openemr-devops · GitHub

3

Greetings,

As per your suggestion, I’ve installed OpenEMR on Ubuntu 18.04 LTS and everything looks good so far.
But I need some advice regarding the backup and restore process.

I wanted a script which makes daily automated backups on an external hard drive but it seems that the instructions to do this are not featured on the “OpenEMR backup tools” page.

So I was trying to tweak the script provided in the “Automated Backups to an Alternate Server” guide even though I don’t have all the technical expertise it requires.
What I thought of doing is:

  1. create a /root/bin directory and a file /root/bin/backup on the production server rather than on the backup server
  2. Replace TARGET=/root/backups with TARGET=/dev/sda1
  3. Replace FROM=my.production.server with FROM=localhost
  4. Replace cp -a /root/backups/myprod/mysql . with cp -a /dev/sda1/myprod/mysql .
    and cp -a /root/backups/myprod/www . with cp -a /dev/sda1/myprod/www .

I am sure there are a couple of things I missed and I would be glad to hear your advice to get this working.

And could you provide detailed instructions on how “to restore a previous backup” where I would have to “first extract the compressed backup to a scratch directory and copy from there”?

Thanks

4

@dagem, Percona is targeted towards backup of large databases. Restore of percona backups involves several steps that you did not list. My first guess is you restored only openemr database which will not work by itself. Regardless, unless your clinic has 100s of users furiously updating the database all the time, it would be safer to use good old mysqldump. Just have it compress and encrypt the backup into a single file.

To restore, decompress the encrypted file into plain sql output and run it against any Mariadb or mysql machine from command line or phpmyadmin.

If you like to deal with raw files, it is your clinic and your choice.

Best.

5

Hi,
Thank you for your answer.
I wanted to use the script provided in Automated Backups to an Alternate Server - OpenEMR Project Wiki because it makes it possible to make automated backups of both the MySQL DB and the OpenEMR web directories.
And I needed help to make some changes to that script so that it creates backups on an external hard drive rather than on a server.

Best,

6

The script(s) you want to change clearly state the intended target as a backup server. Are you trying to take backup of an backup server to external disk? If not, search and select one of many whole server backup tools for whatever operating system. You started with xampp. In that case use built in windows backup service that will take a snapshot and backup the entire machine. Be sure to then practice restore process on a completely different machine with a blank hard drive .

7

Hi,
Sorry for creating an unclear port.
My ultimate question is: is there a script which makes automated backups of MySQL DB and OpenEMR 5.0.1 web directoies running on Ubuntu 18.04, on an external drive?