Backing up database problems (mySQL)

[faeronsayn]

I'm trying to export one of my databases from mysql, but for some reason it won't export through mysql dump or anything. However, the forum itself is running without any issues.

I've tried a few things to have it connect

$ mysql -u root -p
$ use my_database
$ show tables

ERROR 2006(HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:1Currentdatabase: my_database

ERROR 2006(HY000): MySQL server has gone away
No connection. Trying to reconnect...
ERROR 2002(HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111)
ERROR:
Can't connect to the server

I really am only trying to get the dump of the database

$ mysqldump -u root -p my_database | gzip > my_database.sql.gz

this gives me the following error

mysqldump: Got error:2013: Lost connection to MySQL server during query whenusing LOCK TABLES

I've also tried

mysqlcheck -u root -p -A

It goes through all the tables until it tries to access the tables on my_database where it fails and throws this error

mysqlcheck: Got error:2013: Lost connection to MySQL server during query when executing 'CHECK TABLE ... '

Looking at the logs, I get the following errors

InnoDB: File operation call: 'read'.
InnoDB: Cannot continue operation.
141019 23:53:43 mysqld_safe Number of processes running now: 0
141019 23:53:43 mysqld_safe mysqld restarted
141019 23:53:43 InnoDB: The InnoDB memory heap is disabled
141019 23:53:43 InnoDB: Mutexes and rw_locks use GCC atomic builtins
141019 23:53:43 InnoDB: Compressed tables use zlib 1.2.3
141019 23:53:43 InnoDB: Using Linux native AIO
141019 23:53:43 InnoDB: Initializing buffer pool, size = 2.0G
141019 23:53:43 InnoDB: Completed initialization of buffer pool
141019 23:53:43 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
141019 23:53:43  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
141019 23:53:43  InnoDB: Waiting for the background threads to start
141019 23:53:44 Percona XtraDB (http://www.percona.com) 5.5.38-MariaDB-35.2 started; log sequence number 10733071689
141019 23:53:44 [Note] Plugin 'FEEDBACK' is disabled.
141019 23:53:44 [Note] Server socket created on IP: '0.0.0.0'.
141019 23:53:44 [ERROR] Missing system table mysql.proxies_priv; please run mysql_upgrade to create it
141019 23:53:44 [ERROR] Native table 'performance_schema'.'events_waits_current' has the wrong structure
141019 23:53:44 [ERROR] Native table 'performance_schema'.'events_waits_history' has the wrong structure
141019 23:53:44 [ERROR] Native table 'performance_schema'.'events_waits_history_long' has the wrong structure
141019 23:53:44 [ERROR] Native table 'performance_schema'.'setup_consumers' has the wrong structure
141019 23:53:44 [ERROR] Native table 'performance_schema'.'setup_instruments' has the wrong structure
141019 23:53:44 [ERROR] Native table 'performance_schema'.'setup_timers' has the wrong structure
141019 23:53:44 [ERROR] Native table 'performance_schema'.'performance_timers' has the wrong structure
141019 23:53:44 [ERROR] Native table 'performance_schema'.'threads' has the wrong structure
141019 23:53:44 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_thread_by_event_name' has the wrong structure
141019 23:53:44 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_instance' has the wrong structure
141019 23:53:44 [ERROR] Native table 'performance_schema'.'events_waits_summary_global_by_event_name' has the wrong structure
141019 23:53:44 [ERROR] Native table 'performance_schema'.'file_summary_by_event_name' has the wrong structure
141019 23:53:44 [ERROR] Native table 'performance_schema'.'file_summary_by_instance' has the wrong structure
141019 23:53:44 [ERROR] Native table 'performance_schema'.'mutex_instances' has the wrong structure
141019 23:53:44 [ERROR] Native table 'performance_schema'.'rwlock_instances' has the wrong structure
141019 23:53:44 [ERROR] Native table 'performance_schema'.'cond_instances' has the wrong structure
141019 23:53:44 [ERROR] Native table 'performance_schema'.'file_instances' has the wrong structure
141019 23:53:44 [ERROR] Incorrect definition of table mysql.event: expected column 'sql_mode' at position 14 to have type set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH'), found type set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_A
141019 23:53:44 [ERROR] Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.
141019 23:53:44 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.39-MariaDB'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MariaDB Server
141019 23:53:46 [ERROR] mysqld: Table './my_database/xf_session' is marked as crashed and should be repaired
141019 23:53:46 [Warning] Checking table:   './my_database/xf_session'
141019 23:56:05 [ERROR] mysqld: Table './asdf/xf_session' is marked as crashed and should be repaired
141019 23:56:05 [Warning] Checking table:   './asdf/xf_session'
141019 23:56:39 [ERROR] mysqld: Table './my_database/xf_search_index' is marked as crashed and should be repaired
141019 23:56:39 [Warning] Checking table:   './my_database/xf_search_index'

Rebuilt the search index on xenForo, and it did it without much issue.

I also tried running the mysql_upgrade command:

$ mysql_upgrade -u root - p

Which goes through all the databases and then crashes right when it gets to my_database.

Here is the error

...
...
...
asdf.xf_warning_action_trigger          OK
asdf.xf_warning_definition              OK
asdf.xf_widget                          OK
asdf.xf_widgetframework_widget_page     OK
my_database
mysqlcheck: Got error: 2013: Lost connection to MySQL server during query when executing 'CHECK TABLE ...  FOR UPGRADE'
FATAL ERROR: Upgrade failed

I cannot figure out what else I can try to do to fix this. If anyone has an idea, it would be great to hear.

 

[MattW]

Did you fix the issue with the table that was throwing the errors when I had a look for you?

Enter password:
mysqldump: Couldn't execute 'show table status like 'EWRporta\_categories'': Lost connection to MySQL server during query (2013)

 

[faeronsayn]

Did you fix the issue with the table that was throwing the errors when I had a look for you?

Enter password:
mysqldump: Couldn't execute 'show table status like 'EWRporta\_categories'': Lost connection to MySQL server during query (2013)

After removing basically all EWR related addons and updating xenForo, i'm not sure it was fixed but I haven't received that error.

Enter password:
mysqldump: Got error: 2013: "Lost connection to MySQL server during query" when using LOCK TABLES

 

[Mike]

You can see errors about tables being crashed in that log file. That would need to be resolved.

Behavior like this does generally indicate data corruption, likely in InnoDB tables. There should be some more info in the error log, though you may need to restart MySQL, which may not come back up. This is where InnoDB recovery may be needed: http://dev.mysql.com/doc/refman/5.0/en/forcing-innodb-recovery.html

 

[faeronsayn]

You can see errors about tables being crashed in that log file. That would need to be resolved.

Behavior like this does generally indicate data corruption, likely in InnoDB tables. There should be some more info in the error log, though you may need to restart MySQL, which may not come back up. This is where InnoDB recovery may be needed: http://dev.mysql.com/doc/refman/5.0/en/forcing-innodb-recovery.html

restarting mysql (mariadb) works without any issues. I added into my.cnf file

[mysqld]
innodb_force_recovery = 1

Which still didn't let me show tables, or select anything (crashes as usual).

So I increased the number to 2, and then mysql(mariadb) wouldn't start.