Why is my restored backup not the same as the original

Hey there,

I will recommend first to check whether your mysql daemon and service is running:

$ systemctl status mysqld 
$ systemctl status mysql.service

If it's not running then start it first:

$ systemctl start mysql.service

If that doesn't resolve the issue, we would want to look at the error log to review the scope of the issue. You should be able to locate the error log in the following directory:

/var/log/mysql

You may also want to reference MySQL Support Documentation on How to Determine What Is Causing a Problem.

We recommend using top, df, or a similar program to check whether you are out of memory, disk space, file descriptors, or some other critical resource.

The Backup Service uses a snapshot of your disks to take consistent backups while your Linode is running. This method is very reliable, but can fail to properly back up the data files for database services like MySQL. If the snapshot occurs during a transaction, the database’s files may be backed up in an unclean state. We recommend scheduling routine dumps of your database to a file on the filesystem. The resulting file will then be backed up, allowing you to restore the contents of the database if you need to restore from a backup.

We have a few Community Posts that address MySQL dumps:

How do I get a backup of my MySQL database?

How do I backup my MySQL database?

We hope that helps. Feel free to update this post if you run into errors or find success in using this troubleshooting.

Sincerely,
Tara T
Linode Support Team

Thanks for your reply. I already tried that but the log doesnt make any sense to me.

When I try to start it ::

root@localhost:/var/www# sudo service mysql start
Job for mysql.service failed because the control process exited with error code.
See "systemctl status mysql.service" and "journalctl -xe" for details.
root@localhost:/var/www# systemctl status mysql.service
● mysql.service - MySQL Community Server
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
Active: failed (Result: exit-code) since Thu 2019-07-11 15:12:05 UTC; 14s ago
Process: 4081 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=1/FAILURE)

Jul 11 15:12:05 1-bridge systemd[1]: mysql.service: Control process exited, code=exited status=1
Jul 11 15:12:05 1-bridge systemd[1]: mysql.service: Failed with result 'exit-code'.
Jul 11 15:12:05 1-bridge systemd[1]: Failed to start MySQL Community Server.
Jul 11 15:12:05 1-bridge systemd[1]: mysql.service: Service hold-off time over, scheduling restart.
Jul 11 15:12:05 1-bridge systemd[1]: mysql.service: Scheduled restart job, restart counter is at 5.
Jul 11 15:12:05 1-bridge systemd[1]: Stopped MySQL Community Server.
Jul 11 15:12:05 1-bridge systemd[1]: mysql.service: Start request repeated too quickly.
Jul 11 15:12:05 1-bridge systemd[1]: mysql.service: Failed with result 'exit-code'.
Jul 11 15:12:05 1-bridge systemd[1]: Failed to start MySQL Community Server.

I need to get mysql running and any help is greatly appreciated, but my question is also about the backup not being the same as the original. a zero byte file is not he same as a missing file. And as I said what is the point in doing a backup if the restore is not going to work without a massive fight

Cheers

I thought I would try restoring from an auto backup which happened late at night when my web site was dead.
I have run nothing since restoring.
Again mysql -u root gives me:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
After the last restore i did try to install mysql. This time I did not.
This time I have no /var/run/mysqld folder at all.
I can still see the linode that was backed up and it does have that folder.

HOW CAN IT BE A BACKUP IF THERE ARE MISSING FOLDERS ?
HOW COULD A RESTORE EVER BE USEFUL IF THERE ARE MISSING FOLDERS ?

Can anyone shed light on this.

@worried_of_the_uk -

I totally understand your concern here:

But more to the point, why is my new linode not a copy of my old one

That's a great question. It's possible that you are running into a limitation regarding backing up mySQL, as Tara referenced from our Backups Guide:

This method is very reliable, but can fail to properly back up the data files for database services like MySQL. If the snapshot occurs during a transaction, the database’s files may be backed up in an unclean state. We recommend scheduling routine dumps of your database to a file on the filesystem. The resulting file will then be backed up, allowing you to restore the contents of the database if you need to restore from a backup.

In these situations we'd strongly recommend using mysqldump to ensure you can recover fully in the event of an emergency restore.

I have read the guide and understand, but this is not a database folder or file.
The var/run/mysqld folder which appears to contain config files on my original backed up drive, is completely missing when I restore a backup of that drive.
This appears to make the backup useless and there is no guidance on linode as to how to recover from this situation.
Am I supposed to reinstall mysql ?
What id I originally installed it as part of a lamp install, do I have to reinstall the full lamp ?
I have tried to restore two backups, both showed the same problem, surely I cant be the first to hit this.
I know that it can work because I have actually used a backup to create a perfectly working new linode 6 months ago.
Where do I get help from ?
I didn't cause the problem.

Generally directories or files created in /var/run are created when a service starts, and removed when a service is shut down or your Linode is rebooted. They're intended to be temporary files to be used while the service is running.

The options, configuration, and settings for MySQL are generally located here:

/etc/mysql/my.cnf

Any additional directories with configuration files will be listed after !includedir. Here are some common ones:

/etc/mysql/conf.d/ /etc/mysql/mysql.conf.d/

As to the original problem you ran into there's a Stack Overflow thread with suggestions on how to fix it. I've referenced it myself a few times.

Thank you mjones.
Your description of the run folder makes perfect sense.

Strangely my original has no /etc/mysql/my.cnf and runs fine but the log when i try to start mysql on the new server says to create one. I copied mysql.cnf to my.cnf and it complained about the conf.d files referenced in the !includedir.
I will follow your link and probably get it fixed.
I don't think I will bother with linode backup anymore. Probably better to keep a backup of all the config files I have changed and accept that I start from scratch if there is a major fail.
Thanks again.