Mysql dump crashed table for repair

MySQL

I have a large table which has crashed however when I try to run a repair on the server I have problems with not enough disk space. I would like to dump this table to my local machine for a repair but mysqldump throws error because of crashed table. Is it possible to dump this table for repair locally?

Best Answer

Is this MyISAM? InnoDB?

If it's MyISAM you could try myisamcheck --safe-recover as the docs say:

"This recovery method also uses much less disk space than --recover. Normally, you should repair first using --recover, and then with --safe-recover only if --recover fails. "

Here is the reference.

Related Solutions

MySQL ‘Zombie’ Table – Cannot Delete – Solution

There's a bug reported here with mysqldump that could be causing this. Create the table definition, and then try your dump again:

CREATE TABLE `general_log` (
      `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      `user_host` mediumtext NOT NULL,
      `thread_id` int(11) NOT NULL,
      `server_id` int(10) unsigned NOT NULL,
      `command_type` varchar(64) NOT NULL,
      `argument` mediumtext NOT NULL
    ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log';

Mysql – Fatal error: Can’t open and lock privilege tables: Table storage engine for ‘user’ doesn’t have this option

Ran into the same problem today. I'm running the MySQL service during docker build for the unit tests and upgrading to MySQL CE 5.7.19 from MariaDB broke the build. What did solve the issue for me was running chown -R mysql:mysql /var/lib/mysql /var/run/mysqld each time before starting the mysql service.

So my Dockerfile looks like this now:

RUN chown -R mysql:mysql /var/lib/mysql /var/run/mysqld && \
    service mysql start && \
    mvn -q verify site

Hope this helps.

Best Answer

Related Solutions

MySQL ‘Zombie’ Table – Cannot Delete – Solution

Mysql – Fatal error: Can’t open and lock privilege tables: Table storage engine for ‘user’ doesn’t have this option

Related Topic