Mysql – Restore MySQL DB from files

debianibdataMySQL

I own a raspberry pi 1 running raspbian jessie (SYS_A). Two weeks ago I performed an update of the system (aptitude update && aptitude upgrade, not aptitude dist-upgrade, since I know it exists raspbian stretch) and an undesired situation cropped up.

I'm running a MySQL database, I guess version 5.5. After the upgrade, systemd didn't start the mysqld daemon, whose logs are the following:

user@rpi:~ $ sudo systemctl start mysql
Job for mysql.service failed. See 'systemctl status mysql.service' and     'journalctl -xn' for details.

user@rpi:~ $ systemctl status mysql.service
● mysql.service - LSB: Start and stop the mysql database server daemon
Loaded: loaded (/etc/init.d/mysql)
Active: failed (Result: exit-code) since mar 2017-10-10 10:13:11 CEST; 55s ago
Process: 7815 ExecStart=/etc/init.d/mysql start (code=exited, status=1/FAILURE)

user@rpi:~ $ sudo systemctl status mysql.service
● mysql.service - LSB: Start and stop the mysql database server daemon
Loaded: loaded (/etc/init.d/mysql)
Active: failed (Result: exit-code) since mar 2017-10-10 10:13:11 CEST; 2min 46s ago
Process: 7815 ExecStart=/etc/init.d/mysql start (code=exited, status=1/FAILURE)
oct 10 10:13:10 rpi /etc/init.d/mysql[8535]: 0 processes alive and     '/usr/bin/mysqladmin --defaults-file=/etc/mysql/debian.cnf ping' resulted in
oct 10 10:13:11 rpi mysql[7815]: Starting MySQL database server: mysqld . . .     . . . . . . . . . . . . . . . . . . . . . . . . . . . failed!
oct 10 10:13:11 rpi systemd[1]: mysql.service: control process exited,     code=exited status=1
oct 10 10:13:11 rpi systemd[1]: Failed to start LSB: Start and stop the mysql     database server daemon.
oct 10 10:13:11 rpi systemd[1]: Unit mysql.service entered failed state.

Fortunately, I haven't removed the MySQL's files within /var/lib/mysql, so it seems all the data remain in there.

I've tried to open up the ibdataX files, but seem to be binaries…

What I've also tried is to set up another raspbian jessie (SYS_B) in another raspberry pi, install mysql, stop the service, backup the SYS_B's original /var/lib/mysql folder, replace it with the SYS_A's /var/lib/mysql folder and start again the mysqld daemon. But this resulted into the same startup error as in SYS_A.

I've got a wordpress database which I don't want to lose it. So, is there any way to restore it?

/var/logs/mysql/error.log

171010 10:12:36 [Warning] Using unique option prefix myisam-recover instead     of myisam-recover-options is deprecated and will be removed in a future release.     Please use the full name instead.
171010 10:12:36 [Note] Plugin 'FEDERATED' is disabled.
171010 10:12:36 InnoDB: The InnoDB memory heap is disabled
171010 10:12:36 InnoDB: Mutexes and rw_locks use GCC atomic builtins
171010 10:12:36 InnoDB: Compressed tables use zlib 1.2.8
171010 10:12:36 InnoDB: Using Linux native AIO
171010 10:12:36 InnoDB: Initializing buffer pool, size = 128.0M
171010 10:12:37 InnoDB: Completed initialization of buffer pool
171010 10:12:37 InnoDB: highest supported file format is Barracuda.
171010 10:12:38  InnoDB: Waiting for the background threads to start
171010 10:12:39 InnoDB: 5.5.57 started; log sequence number 28689772
171010 10:12:39 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
171010 10:12:39 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
171010 10:12:39 [Note] Server socket created on IP: '0.0.0.0'.
171010 10:12:39 [ERROR] Fatal error: Can't open and lock privilege tables:     Incorrect file format 'user'

Summing up: I can't start mysqld, so I can't backup my databases, which I don't want to lose them, especially the related to wordpress. I haven't deleted the data placed on /var/lib/mysql, ibd files and so on. Could I restore mysql databases into another installation of mysql?

Best Answer

As provided by the OP:

As stated in here, the way to proceed was to initiate mysql daemon in safe mode, just as follows:

  1. Start server bypassing privilege system $ sudo mysqld_safe --user=root --skip-grant-tables
  2. Just afterwards, you must be able to connect to your mysql instance by console (using mysql command) or by any gui client (e.g. MySQL Workbench).

My guess is that the database system became broken after the latest update (perhaps an unsafe power off, reboot, or so on...). As for now, my MySQL databases will be placed on the NAS, as MySQL won't be updated there. I'll also schedule backup tasks.

Related Question