1

I have a big problem with a MySQL database stored on my raspberry (InnoDB engine). I'm not very expert in this field but I'll try to explain to you the problem.

MySQL crash everytime that I try to start it and I need to recover the data stored inside the DB. The error is:

error: 'Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock'

I just tried to start MySQL with the innodb_force_recovery flag from 1 to 6 but it still crashing. From the log I understood that the problem is a corruption inside some tables.

I tried to check my database file with https://recovery.twindb.com/ and it shows that some tables inside "mysql" database need to be recovered and the tables inside "production" database (the mine) are ok, indeed I can see all my data on this website. How can easly recover my data? There is some free tools or script that I can use? I have all files as ibdata01, *.frm, *.ibd

UPDATE #1:

/etc/mysql/my.cnf

    #
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
# 
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

/etc/mysql/mysql.conf.d/mysqld.cnf

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
# 
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]
#
# * Basic Settings
#
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address        = 127.0.0.1
#
# * Fine Tuning
#
key_buffer_size     = 16M
max_allowed_packet  = 16M
thread_stack        = 192K
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover-options  = BACKUP
#max_connections        = 100
#table_open_cache       = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit   = 1M
query_cache_size        = 16M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
#slow_query_log     = 1
#slow_query_log_file    = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id      = 1
#log_bin            = /var/log/mysql/mysql-bin.log
expire_logs_days    = 10
max_binlog_size   = 100M
#binlog_do_db       = include_database_name
#binlog_ignore_db   = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem

If I try to start mysql without innodb_force_recovery flag this is the result:

pc@pc-hp:/$ 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.

And this is error.log: https://pastebin.com/eeSuMxBT

  • 1
    This problem is not related to data damage. It is related to MySQL server settings - it seems socket connections are disabled or MySQL is not started at all. Check my.ini and mysql error log. – Akina Sep 12 '18 at 8:10
  • I have updated my question with new details (also the log and the configuration files) – diegocom Sep 12 '18 at 8:45
  • System tablespace (tablespace which contains info about all system and user objects) is corrupted. You may make a copy of all files for all databases, re-create system tablespace (or uninstall and then reinstall MySQL), re-create user databases, and then re-import all user tablespaces. – Akina Sep 12 '18 at 9:40
  • Ok, now I have a clean installation of MySQL. How can I re-import all user tablespaces from the data files? – diegocom Sep 12 '18 at 10:55
  • Now use ALTER TABLE .. IMPORT TABLESPACE. And see Moving Tablespace Files While the Server is Offline and all around. – Akina Sep 12 '18 at 11:31
0

You can use Undrop for InnoDB a tool behind recovery.twindb.com. To recover corrupt database you need to follow steps from https://twindb.com/recover-corrupt-mysql-database/

Parse the ibd file:

# ./stream_parser -f /var/lib/mysql/sakila/actor.ibd

The dump records from the PRIMARY index:

# ./c_parser -6f pages-actor.ibd/FIL_PAGE_INDEX/0000000000000015.page \
    -t sakila/actor.sql \
    > dumps/default/actor 2>&1 dumps/default/actor_load.sql

Then load the dump:

# mysql --local-infile sakila < dumps/default/actor_load.sql
0

SOLUTION 1 (if *.ibd files are good):

  • Backup /var/lib/MySQL folder with all data
  • Remove /var/lib/MySQL and initialize the data folder with mysql_install_db command to not miss the *.cnf file

    OR Fresh install mysql

  • Create new database as that one to restore
  • Create all tables with the correct schemas (you can recover they with the *.frm files of every table, online there is some tools)
  • Remove all tablespace inside mysql with ALTER TABLE "tableName" DISCARD TABLESPACE; (you can do it for every table with a simple bash script)
  • Copy all backed-up *.idb files to /var/lib/mysql/yourdb folder
  • You may have to set the correct permission to files with sudo chown -R mysql:mysql /var/lib/mysql

  • Import all tablespace inside mysql with ALTER TABLE "tableName" IMPORT TABLESPACE;

After this procedure you have all data restored inside your database.

SOLUTION 2 (if *.idb may be corrupted):

  • Download undrop-for-innodb and compile it

  • execute ./stream_parser -f /var/lib/mysql/ibdata1

  • execute for every *.ibd file ./stream_parser -f /var/lib/mysql/yourdatabase/your_ibd.ibd

  • create inside YOUR_DB/YOUR_TABLE.sql the CREATE TABLE statement of every table

  • execute ./c_parser -6f pages-YOUR_TABLE.ibd/FIL_PAGE_INDEX/FIRST_FILE_OF_FOLDER.page \-t YOUR_DB/YOUR_TABLE.sql \ > dumps/default/YOUR_TABLE 2> dumps/default/YOUR_TABLE_load.sql

  • Initialize a new empty database and create all table with the correct schema (you can use YOUR_DB/YOUR_TABLE.sql files)

  • Import all data with /dumps/default/YOUR_TABLE_load.sql files

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.