I need to backup one of my databases from MySQL (Running MariaDB). The issue is that aside from all of my other databases that backup perfectly fine, this database has issues backing up and crashes the server whenever I try.
mysqldump shadygam_forum -u root -p > dump.sql
Enter password:
mysqldump: Got error: 2013: "Lost connection to MySQL server during query" when using LOCK TABLES
So I look in the error log found at /var/lib/mysql/myerrorlog.err
InnoDB: Error: tried to read 16384 bytes at offset 0 819200.
InnoDB: Was only able to read 0.
141219 4:32:54 InnoDB: Operating system error number 5 in a file operation.
InnoDB: Error number 5 means 'Input/output error'.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/operating-system-error-codes.html
InnoDB: File operation call: 'read'.
InnoDB: Cannot continue operation.
141219 04:32:54 mysqld_safe Number of processes running now: 0
141219 04:32:54 mysqld_safe mysqld restarted
141219 4:32:54 InnoDB: The InnoDB memory heap is disabled
141219 4:32:54 InnoDB: Mutexes and rw_locks use GCC atomic builtins
141219 4:32:54 InnoDB: Compressed tables use zlib 1.2.3
141219 4:32:54 InnoDB: Using Linux native AIO
141219 4:32:54 InnoDB: Initializing buffer pool, size = 2.0G
141219 4:32:54 InnoDB: Completed initialization of buffer pool
141219 4:32:54 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!
141219 4:32:54 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...
141219 4:32:55 InnoDB: Waiting for the background threads to start
141219 4:32:56 Percona XtraDB (http://www.percona.com) 5.5.38-MariaDB-35.2 started; log sequence number 12116884277
141219 4:32:56 [Note] Plugin 'FEEDBACK' is disabled.
141219 4:32:56 [Note] Server socket created on IP: '0.0.0.0'.
141219 4:32:56 [ERROR] Missing system table mysql.proxies_priv; please run mysql_upgrade to create it
141219 4:32:56 [ERROR] Native table 'performance_schema'.'events_waits_current' has the wrong structure
141219 4:32:56 [ERROR] Native table 'performance_schema'.'events_waits_history' has the wrong structure
141219 4:32:56 [ERROR] Native table 'performance_schema'.'events_waits_history_long' has the wrong structure
141219 4:32:56 [ERROR] Native table 'performance_schema'.'setup_consumers' has the wrong structure
141219 4:32:56 [ERROR] Native table 'performance_schema'.'setup_instruments' has the wrong structure
141219 4:32:56 [ERROR] Native table 'performance_schema'.'setup_timers' has the wrong structure
141219 4:32:56 [ERROR] Native table 'performance_schema'.'performance_timers' has the wrong structure
141219 4:32:56 [ERROR] Native table 'performance_schema'.'threads' has the wrong structure
141219 4:32:56 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_thread_by_event_name' has the wrong structure
141219 4:32:56 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_instance' has the wrong structure
141219 4:32:56 [ERROR] Native table 'performance_schema'.'events_waits_summary_global_by_event_name' has the wrong structure
141219 4:32:56 [ERROR] Native table 'performance_schema'.'file_summary_by_event_name' has the wrong structure
141219 4:32:56 [ERROR] Native table 'performance_schema'.'file_summary_by_instance' has the wrong structure
141219 4:32:56 [ERROR] Native table 'performance_schema'.'mutex_instances' has the wrong structure
141219 4:32:56 [ERROR] Native table 'performance_schema'.'rwlock_instances' has the wrong structure
141219 4:32:56 [ERROR] Native table 'performance_schema'.'cond_instances' has the wrong structure
141219 4:32:56 [ERROR] Native table 'performance_schema'.'file_instances' has the wrong structure
141219 4:32:56 [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
141219 4:32:56 [ERROR] Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.
141219 4:32:56 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.39-MariaDB' socket: '/var/lib/mysql/mysql.sock' port: 3306 MariaDB Server
141219 4:32:59 [ERROR] mysqld: Table './shadygam_forum/xf_session' is marked as crashed and should be repaired
141219 4:32:59 [Warning] Checking table: './shadygam_forum/xf_session'
Looking at some of these errors I've tried the following
mysql_upgrade -u root -p
Which goes through all the tables saying OK
until it starts to process the tables of that database and crashes.
.
.
.
phanime_xenForo.xf_user_upgrade_expired OK
phanime_xenForo.xf_user_upgrade_log OK
phanime_xenForo.xf_warning OK
phanime_xenForo.xf_warning_action OK
phanime_xenForo.xf_warning_action_trigger OK
phanime_xenForo.xf_warning_definition OK
phanime_xenForo.xf_widget OK
phanime_xenForo.xf_widgetframework_widget_page OK
shadygam_forum
mysqlcheck: Got error: 2013: Lost connection to MySQL server during query when executing 'CHECK TABLE ... FOR UPGRADE'
FATAL ERROR: Upgrade failed
So I check the error log and it's the exact same error.
So I've also tried doing the command to repair the shadygam_forum.xf_session
table like so
REPAIR TABLE xf_session
while in the shadygam_forum database and that crashes the server as well.
InnoDB: Error: tried to read 16384 bytes at offset 0 819200.
Getting an I/O error on a read suggests a physical disk defect. Can you, from the shell, make a copy of shadygam_forum/xf_session.ibd? If you get an error when you try, then I would suggest that you almost certainly have disk issues.shadygam_forum/xf_session.ibd
file in/var/lib/mysql
, however, I do have axf_session.frm
file. I was able to copy that file without any errors being thrown at me.SELECT @@innodb_file_per_table;
please. Does that return1
or0
?1
. MariaDB [(none)]> SELECT @@innodb_file_per_table; +-------------------------+ | @@innodb_file_per_table | +-------------------------+ | 1 | +-------------------------+ 1 row in set (0.00 sec)