mariadb recover

After a hardware failure, my mariadb cannot be restarted.

service mariadb restart
Redirecting to /bin/systemctl restart  mariadb.service
Job for mariadb.service failed because the control process exited with error code. See “systemctl status mariadb.service” and “journalctl -xe” for details.
[root@panel mysql]# service mariadb status
Redirecting to /bin/systemctl status  mariadb.service
● mariadb.service – MariaDB database server
Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
Active: failed (Result: exit-code) since Mon 2018-06-22 21:42:42 EDT; 1h 3min ago
Process: 11084 ExecStartPost=/usr/libexec/mariadb-wait-ready $MAINPID (code=exited, status=1/FAILURE)
Process: 11083 ExecStart=/usr/bin/mysqld_safe –basedir=/usr (code=exited, status=0/SUCCESS)
Process: 11055 ExecStartPre=/usr/libexec/mariadb-prepare-db-dir %n (code=exited, status=0/SUCCESS)
Main PID: 11083 (code=exited, status=0/SUCCESS)
Jun 22 21:43:39 domainhostseotool.com systemd[1]: Starting MariaDB databa…
Jun 22 21:43:39 domainhostseotool.com mysqld_safe[11083]: 180625 21:43:39…
Jun 22 21:43:39 domainhostseotool.com mysqld_safe[11083]: 180625 21:43:39…
Jun 22 21:43:41 domainhostseotool.com systemd[1]: mariadb.service: contro…
Jun 22 21:43:41 domainhostseotool.com systemd[1]: Failed to start MariaDB…
Jun 22 21:43:41 domainhostseotool.com systemd[1]: Unit mariadb.service en…
Jun 22 21:43:41 domainhostseotool.com systemd[1]: mariadb.service failed.
Hint: Some lines were ellipsized, use -l to show in full.

The log (/var/log/mariadb/mariadb.log)says:

 

180625 12:35:01  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…
InnoDB: Doing recovery: scanned up to log sequence number 17905880794
InnoDB: 1 transaction(s) which must be rolled back or cleaned up
InnoDB: in total 1 row operations to undo
InnoDB: Trx id counter is 11089100
180625 12:35:01  InnoDB: Starting an apply batch of log records to the database…
InnoDB: Progress in percents: 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
InnoDB: Starting in background the rollback of uncommitted transactions
180625 12:35:02  InnoDB: Rolling back trx with id 1105FDC, 1 rows to undo
180625 12:35:02  InnoDB: Assertion failure in thread 140082533023344 in file fut0lst.ic line 83
InnoDB: Failing assertion: addr.page == FIL_NULL || addr.boffset >= FIL_PAGE_DATA
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.3/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
180625 12:35:02  InnoDB: Waiting for the background threads to start
180625 12:35:02 [ERROR] mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.

To report this bug, see http://kb.askmonty.org/en/reporting-bugs

We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.
Server version: 5.3.52-MariaDB
key_buffer_size=13217728
read_buffer_size=11072
max_used_connections=0
max_threads=152
thread_count=0
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 46712 K  bytes of memory
Hope that’s ok; if not, decrease some variables in the equation.

Thread pointer: 0x0x0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong…
stack_bottom = 0×0 thread_stack

It seems we run out of memory but in fact it is not. Since mariadb can not be started, the command such as mysqlcheck can not run. The first step is to try to start the mariadb daemon, which need some twist on the mariadb configuration file(/etc/my.cnf). I followed this guide to add several lines under the [mysqld] section:

innodb_force_recovery=3
innodb_purge_threads=0

Now mariadb starts without a problem. Then I use the command as described in this guide to dump all the databases and tables.

And use the following command to check if some databases/tables are corrupted.

mysqlcheck --all-databases

Interestingly, all databases/tables are ok. I comment the new lines added in /etc/my.cnf and try to restart mariadb again. The situation keeps the same: mariadb fails to start. Because I have backed up all the databases, I can do something riskily.  I delete the files:ibdata1,ib_logfile0,ib_logfile1, and restart mariadb. This time, mariadb is started successfully. Login mariadb and use “show databases;” and “show tables;” to see all databases and tables are there. But when I query for a table(“select * from tablename), it says “ERROR 1146 (42S02): Table ‘tablename’ doesn’t exist . okay, I’ve the backup of the tables, I’ll import it now. But new error occur:”ERROR 1010 (HY000) at line 2840: Error dropping database (can’t rmdir ‘./xxx’, errno: 39)”. Okay, I delete the folders(database directories) under /var/lib/mysql manually. Now something bad happens, I cannot import the databases at all: root@localhost access denied. It seems I deleted too much including the root account(password). It is very annoying. I think I’d better remove mariadb and re-install it again:

yum erase mariadb

yum install mariadb

After that, I try to restart mariadb:

service mariadb restart

It says:

service mariadb status
Redirecting to /bin/systemctl status  mariadb.service
Unit mariadb.service could not be found.

It turns out that I should not only install mariadb but also install mariadb-server:

yum install mariadb-server

Now I can start mariadb successfully. But I still cannot login mysql using the old root password. I think, the old password for root is lost forever. I have to reset the root password. How to change the root password of mariadb? I talked this issue in one of my post: mysql basic, but it does not work here because we use mariadb instead of mysql on centos7. Even the command /usr/sbin/mysqld  does not exist. Forunately, I find a post for changing the mariadb root password. Basically, you have to run a special version of mariadb daemon. The daemon is neither called mysqld nor mariadbd. There is neither /usr/sbin/mariadb nor /usr/sbin/mariadbd. You should run the following command instead:

mysqld_safe –skip-grant-tables –skip-networking &

Then mysql -u root will run without asking you for a password.

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> SET PASSWORD FOR ‘root’@’localhost’ = PASSWORD(‘gfdgsdthtth’);
Query OK, 0 rows affected (0.00 sec)

Now the root password of mariadb has been set. You can kill the process of this special mariadb daemon. But what is the process id to kill? You can find the pid of the mariadb daemon in the file /var/run/mariadb/mariadb.pid. Just kill that pid and restart mariadb service normally. Now when you try to login mariadb, it asks you for a password. It is time to restore my databases:

mysql -p < mydatabases.sql

The misery is not over yet. If another database user tries to access his database, he will see the error:

Connect failed: Access denied for user ‘someuser@’localhost’ (using password: YES) .

Remember we’ve backed up and restored all databases including the mysql database which contains the user/privilege information. You can use the following command to show databases a user has privileges on:

SELECT * FROM mysql.db WHERE User=”username”;

It shows someuser does have all privileges on expected database.

Use the following command to find  all users of specified database:

select User from mysql.db where Db=’databasename’;

Use the following command to find all users in the system:

select * from mysql.user;

All commands show the user can access the database. So why the access is denied?

The reason is that the privileges have not be propagated yet. We should use the following command to flush the privileges;

FLUSH PRIVILEGES;

 

Posted in tips of hosting