Error: page xxxx log sequence number yyyy is in the future

Tested in Debian 8 / MySQL 5.5

If you’re getting this error on mysql error log:

InnoDB: Error: page 4352 log sequence number 12151412585
InnoDB: is in the future! Current system log sequence number 8204.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files

It’s due to a sequence mismatch in the InnoDB log, so it’s probable mysql doesn’t start. One thing you can do is restore a backup but, what happens if there is no backup?… Well, you can try to recover mysql using innodb-force-recovery.

First add this to the [mysqld] section in the my.cnf file:

innodb-force-recovery = 6

And then restart mysql:

~ $ /etc/init.d/mysql start

In the mysql error log you should get something like this:

InnoDB: Waiting for the background threads to start
InnoDB: 5.5.52 started; log sequence number 0
InnoDB: !!! innodb_force_recovery is set to 6 !!!
[Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
[Note]   - '127.0.0.1' resolves to '127.0.0.1';
[Note] Server socket created on IP: '127.0.0.1'.
[Note] Event Scheduler: Loaded 0 events
[Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.52'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306

If not, you are in trouble, look for a backup wherever… If yes, the next step is make a backup of all databases:

~ $ mysqldump -u root --all-databases > dump.sql

If dump ends successfully you are lucky. Now stop mysql:

~ $ /etc/init.d/mysql stop

Comment the innodb-force-recovery line from my.cnf file, and move ibdata*, ib_logfile* and all database folders, except mysql, to a temp dir:

~ $ cd /var/lib/mysql
~ $ mv ibdata* ib_logfile* database1 database2 (...) /tmp/

Start mysql, it will create new ibdata and ib_logfiles:

~ $ /etc/init.d/mysql start

In the error log:

InnoDB: The first specified data file ./ibdata1 did not exist:
InnoDB: a new database to be created!
InnoDB: Setting file ./ibdata1 size to 10 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 5 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 5 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: 127 rollback segment(s) active.
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
InnoDB: Waiting for the background threads to start
InnoDB: 5.5.52 started; log sequence number 0
[Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
[Note]   - '127.0.0.1' resolves to '127.0.0.1';
[Note] Server socket created on IP: '127.0.0.1'.
[Note] Event Scheduler: Loaded 0 events
[Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.52'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306

Mysql is working fine in a clean eviroment, the last step is restore the prior dump:

~ $ mysql -u root < dump.sql

At this time you should have all mysql databases, working like they were before crash.

MySQL load balancing with mysqlnd_ms plugin

Tested on Debian 7 / PHP 5.4

In a mysql replication scenario you can balance sql queries sending write queries to the master server and read-only queries to the slaves using mysqlnd_ms plugin with mysqlnd php extension (mysql native driver). Also is possible in a master-master replication balance write queries between two or more masters.

First install php5-mysqlnd extension if is not installed yet, and other necessary packages.

~ $ apt-get install php5-mysqlnd php5-dev

Now you have to compile mysqlnd-ms plugin for mysqlnd, so download pecl extension from php.net:

~ $ wget http://pecl.php.net/get/mysqlnd_ms-1.5.2.tgz
~ $ tar xvzf mysqlnd_ms-1.5.2.tgz
~ $ cd mysqlnd_ms-1.5.2

Done this, configure:

~ $ phpize
~ $ ./configure --with-php-config=/usr/bin/php-config --enable-mysqlnd-ms

Compile:

~ $ make

And install:

~ $ make install

At last add extension mysqlnd_ms.so at the end of specific ini file, usually /etc/php5/mods-available/mysqlnd.ini:

extension=mysqlnd_ms.so

And enable it:

mysqlnd_ms.enable="on"

Now is time to configure the balancing type, two possible scenarios would be these:

One master, two slaves

Create /opt/php/mysqlnd_ms.json and add this:

{
   "mysqlbalancer": {
     "master": {
       "master1": {
         "host": "MASTER-IP",
         "port": "3306"
       }
     },
     "slave": {
        "slave1": {
         "host": "SLAVE1-IP",
         "port": "3306"
       }, 
	"slave2": {
         "host": "SLAVE2-IP",
         "port": "3306"
       }
     },
     "filters": {
           "random": {
              	"weights": {
                   "slave1":50,
                   "slave2":50
               }
           }
       },
     "master_on_write": 1,
     "failover": { "strategy": "loop_before_master", "remember_failed": true }
   }
 }

Add this to the file /etc/php5/mods-available/mysqlnd.ini:

mysqlnd_ms.config_file="/opt/php/mysqlnd_ms.json"

Here, write queries go to the master server and read queries go to the slaves randomly fifty-fifty (the same weight for both).

Parameter ‘master_on_write’ means that after writing into the master the next read query go to the master too, is more reliable because asynchronous mysql replication has delay.

With the option ‘loop_before_master’, if one slave fails, try another slave before making query into the master, and ‘remember_failed’ remembers if a node has been marked as failed discarding it.

Two or more masters

Create /opt/php/mysqlnd_ms.json and add this:

{
   "mysqlbalancer": {
     "master": {
       "master1": {
         "host": "MASTER1-IP",
         "port": "3306"
       },
       "master2": {
         "host": "MASTER2-IP",
         "port": "3306"
       }
     },
     "slave": { },
     "filters": { "roundrobin": [ ] },
     "failover": { "strategy": "loop_before_master", "remember_failed": true }
   }
 }

Add this to the file /etc/php5/mods-available/mysqlnd.ini:

mysqlnd_ms.config_file="/opt/php/mysqlnd_ms.json"
mysqlnd_ms.multi_master=1
mysqlnd_ms.disable_rw_split=1

Here, write and read queries go to both masters using roundrobin iterating into the master server list.

Option mysqlnd_ms.multi_master=1 enables the plugin to using several masters and mysqlnd_ms.disable_rw_split=1 disables separated read-write sentences because now masters work like a slaves too.

Once done configuration you have to restart services, if you use php-fpm do this:

~ $ /etc/init.d/php-fpm restart

if you use php module for apache then restart apache:

~ $ /etc/init.d/apache2 restart

Now you have to tell the web application that use this configuration, at the mysql connection string indicate the connector name defined above in the json file as HOST, example with pdo:

$conn = new PDO("mysql:host=mysqlbalancer;dbname=dbname", "username", "password");

If you use wordpress you have to modify wp-config.php changing DB_HOST var:

/** MySQL hostname */
define('DB_HOST', 'mysqlbalancer');

Finally, for security reasons you can force all mysql connections to use mysqlnd_ms plugin adding this option to the /opt/php/mysqlnd_ms.json file:

mysqlnd_ms.force_config_usage=1

Now if you try connecting to mysql using as HOST another name or IP address you will get an error like this:

PHP Warning:  PDO::__construct(): (mysqlnd_ms) Exclusive usage of configuration enforced but did not find the correct INI file section (localhost)
Connection failed: SQLSTATE[HY000] [2000] (mysqlnd_ms) Exclusive usage of configuration enforced but did not find the correct INI file section

+ Info: http://php.net/manual/en/mysqlnd-ms.configuration.php

MySQL Master-Master replication

Tested on Ubuntu 14 / MySQL 5.5

We will configure mysql master-master replication on two nodes for all databases:

Node A: 1.1.1.1
Node B: 2.2.2.2

First install mysql in both nodes:

$ ~ apt-get install mysql-server mysql-client

In the node A configure this in my.cnf:

server-id                       = 1
log_bin                         = /var/lib/mysql/mysql-bin.log
log_bin_index                   = /var/lib/mysql/mysql-bin.log.index
relay_log                       = /var/lib/mysql/mysql-relay-bin
relay_log_index                 = /var/lib/mysql/mysql-relay-bin.index
binlog_format                   = ROW
slave_exec_mode                 = IDEMPOTENT
expire_logs_days                = 10
max_binlog_size                 = 100M
auto-increment-increment        = 2
auto-increment-offset           = 1

And this in the node B:

server-id                       = 2
log_bin                         = /var/lib/mysql/mysql-bin.log
log_bin_index                   = /var/lib/mysql/mysql-bin.log.index
relay_log                       = /var/lib/mysql/mysql-relay-bin
relay_log_index                 = /var/lib/mysql/mysql-relay-bin.index
binlog_format                   = ROW
slave_exec_mode                 = IDEMPOTENT
expire_logs_days                = 10
max_binlog_size                 = 100M
auto-increment-increment        = 2
auto-increment-offset           = 2

Explication:

server-id: the server identification, it must be different en each node
log_bin: path to the log binary file
log_bin_index: path to the log binary file index
relay_log: path to the log relay file
relay_log_index: path to the log relay file index
binlog_format: in this case is row based replication (ROW), all changes are replicated so is the safest form of replication. The disadvantage is that generates big update logs and more network traffic too.
slave_exec_mode: IDEMPOTENT is the best method for master-master replication, it causes suppression of duplicate-key and no-key-found errors.
expire_logs_days: log expiration in days
max_binlog_size: max size of binary logs
auto-increment-increment: the amount that the server will increment each time an auto incremented value is generated.
auto-increment-offset: the offset of increment. Example: auto-increment-increment=2 and auto-increment-offset, increment sequence will be 2, 4, 6 …

Restart mysql on both nodes:

$ ~ /etc/init.d/mysql restart

Create the replication user on both nodes:

mysql> CREATE USER 'repuser'@'%' identified by 'password'; 
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repuser'@'%'; 

Now in node A get the master status, remember file and position:

 mysql> SHOW MASTER STATUS;

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      102 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec) 

Go to the node B and configure replication against the node A, using data before:

mysql> STOP SLAVE;
mysql> CHANGE MASTER TO MASTER_HOST='1.1.1.1', MASTER_USER='repuser', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=102, MASTER_PORT=3306;
mysql> START SLAVE;

In this node (B) get the master status, remember file and position:

mysql> SHOW MASTER STATUS;

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |      102 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec) 

And back to the node A configure replication against node B, using data before:

mysql> STOP SLAVE;
mysql> CHANGE MASTER TO MASTER_HOST='2.2.2.2', MASTER_USER='repuser', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=102, MASTER_PORT=3306;
mysql> START SLAVE; 

Check slave status on both nodes:

mysql> SHOW SLAVE STATUS\G

Restore mysql master-master replication

Tested on Ubuntu 14 / MySQL 5.5

After a crash in a mysql master-master replication you can follow these steps.

1.- First identify the failed server, from now we’ll use this nomenclature:

Failed server: FS
Working server: WS

2.- Block queries to the FS for example with iptables if this server receive write queries:

~ $ iptables -I INPUT -s  -p tcp --dport 3306 -j DROP
~ $ iptables -I INPUT -s  -p tcp --dport 3306 -j DROP
...

3.- Stop slave on both servers FS and WS:

mysql> STOP SLAVE;

4.- On FS make a dump with –master-data option to block tables and save master position and binary log:

~ $ mysqldump -h WS-IP -u root -pxxxx --master-data --single-transaction --flush-logs --routines --all-databases > ALL-DBs.sql

5.- And then import:

~ $ mysql -u root -pxxxx < ALL-DBs.sql

6.- Start slave:

mysql> START SLAVE;

7.- Verify the slave status:

mysql> SHOW SLAVE STATUS\G

8.- Lock tables:

mysql> FLUSH TABLES WITH READ LOCK;

9.- Get the master status:

mysql> SHOW MASTER STATUS;

10.- Now on the WS set the replication with the parameters obtained at above step:

mysql> CHANGE MASTER TO MASTER_HOST='FS-IP', MASTER_USER='replication-user', MASTER_PASSWORD='replication-password', MASTER_LOG_FILE='mysql-bin.xxxx', MASTER_LOG_POS=yyyy, MASTER_PORT=3306;

11.- Start slave to synchronize with the master:

mysql> START SLAVE;

12.- Verify the slave status:

mysql> SHOW SLAVE STATUS\G

13.- Unlock tables on FS;

mysql> UNLOCK TABLES;

14.- Replication should be working fine now, if not try again from the step 3.

15.- Finally delete firewall blocking if necessary:

~ $ iptables -D INPUT -s  -p tcp --dport 3306 -j DROP
~ $ iptables -D INPUT -s  -p tcp --dport 3306 -j DROP
...

MySQL innodb checksum mismatch

Tested on Ubuntu 12 and 14 / Debian 7

When you find the error InnoDB: Error: checksum mismatch in data file ./ibdata1 (or ./ibdataX) at the mysql error log, it means that ibdata checksum is not correct and there are transactions that have not been completed and wrote to the ibdata, for example due to a low memory, oom killer, mysql service crash etc.

This could be the error at mysql error log:

...
InnoDB: Error: checksum mismatch in data file ./ibdata1
InnoDB: Could not open or create data files.
InnoDB: If you tried to add new data files, and it failed here,
InnoDB: you should now edit innodb_data_file_path in my.cnf back
InnoDB: to what it was, and remove the new ibdata files InnoDB 
InnoDB: created in this failed attempt. InnoDB only wrote those 
InnoDB: files full of zeros, but did not yet use them in any way.  
InnoDB: But be careful: do not remove old data files which contain 
InnoDB: your precious data!
[ERROR] Plugin 'InnoDB' init function returned error.
[ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
[ERROR] Unknown/unsupported storage engine: InnoDB
[ERROR] Aborting
...

You can control the checksum, certainly it will show one or more errors:

~ $ innochecksum -d ibdata1

To solve it writing all changes to the ibdata file, first start mysql in recovery mode, at level 4. Add this to the my.cnf under [mysqld] tag:

innodb_force_recovery = 4

Start mysql:

~ $ /etc/init.d/mysql start

Now at the error log we can see:

...InnoDB: Log scan progressed past the checkpoint lsn 5294845868
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 
InnoDB: doublewrite buffer...
InnoDB: Warning: database page corruption or a failed
InnoDB: file read of space 0 page 0.
InnoDB: Trying to recover it from the doublewrite buffer.
InnoDB: Recovered the page from the doublewrite buffer.
InnoDB: Warning: database page corruption or a failed
InnoDB: file read of space 0 page 371.
InnoDB: Trying to recover it from the doublewrite buffer.
InnoDB: Recovered the page from the doublewrite buffer.
InnoDB: Warning: database page corruption or a failed
InnoDB: file read of space 0 page 197.
InnoDB: Trying to recover it from the doublewrite buffer.
InnoDB: Recovered the page from the doublewrite buffer.
InnoDB: Doing recovery: scanned up to log sequence number 5294846217
InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 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: Waiting for the background threads to start
InnoDB: 5.5.44 started; log sequence number 5294846217
InnoDB: !!! innodb_force_recovery is set to 4 !!!
...

It will make a data recovery. Now you have to disable innodb_force_recovery at my.cnf and kill mysql:

~ $ killall -9 mysqld

Usually mysql should start after kill, if not you can start it:

~ $ /etc/init.d/mysql start

At this time mysql should be already working fine!

Change mysql root password

Stop service:

~ $ /etc/init.d/mysql stop

Add the next parameters to he my.cnf file config:

skip-grant-tables
skip-networking

The first parameter disables grants on mysql so we can access without password to any database. If mysql was compiled with the option –disable-grant-options (not usually), setting this parameter will not work.

The second one disable network connection, by this way we can connect to mysql only from localhost.

Start mysql:

~ $ /etc/init.d/mysql start

Connect to mysql without password:

~ $ mysql -u root

Update the pasword:

mysql> use mysql;
mysql> UPDATE user SET Password=PASSWORD('new_pass') WHERE User='root';
mysql> FLUSH PRIVILEGES;
mysql> exit

And restart mysql:

~ $ /etc/init.d/mysql restart