Mysql ibdata1 file recovery

Steps to recover data when ibdata file gets corrupted or removed

ibdata is really important file in Mysql because it contains following data –

data dictionary – metadata for Innodb related objects.

double write buffer – protection against a crash in the middle of writing a block to disk.

rollback segments and MVCC data – Undo logs which are required if any transaction needs to be rolled back and also useful if any other transaction needs to see the original data (consistent reads).

And if we accidentally dropped the ibdata file, we definitely cannot rebuild it but we can safely recover all the data onto a new Mysql instance.

  • First shut down mysql if its already running :
mysqladmin -uroot -p shut
  • Let’s try to start it up to see the error we get

Read More

Mysql innobackupex full and incremental backups

Mysql backup strategy using Innobackupex/Xtrabackup :

If we are planning to automate backup of our Mysql production instance, we can think of two approaches :

  1. A full backup followed by cumulative backups i.e All incremental backups with last full backup as base directory.
  2. A full backup followed by differential backups i.e All incremental backups with last incremental/Full backup as base directory.

Both approaches have their own benefits and drawbacks.

The first approach would need little bit more backup storage since we would always perform incremental backup from the last full backup checkpoint. Also the time required to perform incremental backup would be more compared to the second approach.

The main drawback of having the second approach is that we would be spending more time during backup restore process, mainly we need to to prepare the full backup by applying all the subsequent incrementals to it.

A better approach would be definitely the first one since it saves our time while we need to restore the database to a particular point, we just need to apply required incremental to the base backup.

Below are few innobackupex examples :

Full Backup :

/usr/bin/innobackupex --slave-info --user=${User} --password=${Password} --defaults-file=${DefaultsFile} --compress /u01/mysql/backup/fullbackup/

We are using compress option which would compress our backups and save us atleast 2x times of backup storage. slave-info useful while backing up the slave server, generates xtrabackup_slave_info file with master information.

Incremental Backups : Read More

xtrabackup fails with error number 23

innobackupex fails with below error:

InnoDB: Operating system error number 23 in a file operation.
InnoDB: Error number 23 means 'Too many open files in system'.
InnoDB: Some operating system error numbers are described at
InnoDB: Error: could not open single-table tablespace file

Issue :

Innobackupex is failing due to server restriction on open file descriptors for ‘mysql’ user.

[email protected][/mysql/data] ulimit -Hn

Ulimit hard limit is only set to 32k, we can estimate the ulimit required by Mysql user by looking at the number of partitions/tables we have.

[email protected][/mysql/data] find . -name "*.ibd"|wc -l


mysql> select count(*) from information_schema.partitions;
| count(*) |
|   185433 |
1 row in set (1.68 sec)

Fix :

Read More

Mysql Galera WSREP_SST: [ERROR] xtrabackup_checkpoints missing

Adding new node to galera fails with following error :

WSREP_SST: [INFO] Waiting for SST streaming to complete! (20160607 12:21:20.988)
160607 12:21:23 [Note] WSREP: (3d7453d3, 'tcp://') turning message relay requesting off
WSREP_SST: [ERROR] xtrabackup_checkpoints missing, failed innobackupex/SST on donor (20160607 12:21:30.966)
WSREP_SST: [ERROR] Cleanup after exit with status:2 (20160607 12:21:30.968)
160607 12:21:30 [ERROR] WSREP: Process completed with error: wsrep_sst_xtrabackup-v2 --role 'joiner' --address '' --auth 'sstuser:dev2' --datadir '/var/untd/mysql/data/' --defaults-file '/etc/my.cnf' --parent '4858': 2 (No such file or directory)
160607 12:21:30 [ERROR] WSREP: Failed to read uuid:seqno from joiner script.
160607 12:21:30 [ERROR] WSREP: SST failed: 2 (No such file or directory)
160607 12:21:30 [ERROR] Aborting

160607 12:21:30 [Warning] WSREP: 1 ( State transfer to 0 (node2) failed: -22 (Invalid argument)
160607 12:21:30 [ERROR] WSREP: gcs/src/gcs_group.cpp:long int gcs_group_handle_join_msg(gcs_group_t*, const gcs_recv_msg_t*)():717: Will never receive state. Need to abort.
160607 12:21:30 [Note] WSREP: gcomm: terminating thread
160607 12:21:30 [Note] WSREP: gcomm: joining thread
160607 12:21:30 [Note] WSREP: gcomm: joining thread
160607 12:21:30 [Note] WSREP: gcomm: closing backend
160607 12:21:31 [Note] WSREP: gcomm: closed
160607 12:21:31 [Note] WSREP: /usr/sbin/mysqld: Terminated.
160607 12:21:32 mysqld_safe mysqld from pid file /var/untd/mysql/log/ ended

Fix :

Read More

Mysql Secure Installation for non default socket.


mysql_secure_installation is a script useful to

  • set root password
  • disallowing root login remotely
  • removing anonymous user accounts after first installation
  • removing test database which can be accessed by any users

Above script would work for default socket location i.e /var/lib/mysql/mysql.sock

If the server is started with a non-default socket location, mysql_secure_installation does not consider any socket options you specify and the script would fail (For example -> mysql_secure_installation –socket=/mysql/socket/mysql.sock)

We should also consider this situation in case if we need to install more than one instance of mysql on a single server. Read More

Partition maintainance script for Mysql

A simple way for adding future partitions or purging old partitions is through mysql procedures. Later we can use any scheduler or a cronjob to call this procedure daily.

Here I will provide a sample procedure for maintainance of a daily partitioned table based on a datetime column. Please do look at my previous post on mysql range partitioning in the following post. I have taken the example from this post and modified it based on our requirement.

First we will create a sample table along with a partition for our testing purposes.

mysql> create table test_partitions(id int,name varchar(50),tx_date datetime);
mysql> alter table test_partitions PARTITION BY RANGE (to_seconds(tx_date)) (
partition test_partitions_16092015 values less than ( to_seconds('2015-09-17 00:00:00') ));

Now lets create a table for holding list of tables and number of partitions to preserve/drop.

mysql> create table test.part_tables(dbname varchar(50),tabname varchar(50),numpart int,nextpart int);

Lets go ahead and insert a sample row there :

mysql> insert into test.part_tables values('test','test_partitions',30,7);

So, we will be preserving only last 30 days partitions and make sure we have next 7 days partitions for ‘test_partitions’ table.

Mysql Procedure 1 (part_maint) : This procedure takes in table list from our meta-table i.e part_tables and runs partition maintainance for each table :

Read More

Mysql range partitioning on date column

Here is a simple example for partitioning a mysql table based on datetime column :

  • Creating a test table
mysql> create table test_partitions(id int,name varchar(50),tx_date datetime);
Query OK, 0 rows affected (11.83 sec)
  • Creating a initial partition :
alter table test_partitions PARTITION BY RANGE (to_seconds(tx_date)) (
partition test_partitions_16052016 values less than ( to_seconds('2016-05-17 00:00:00') ));
  • Adding new partition:
mysql> alter table test_partitions add partition ( partition test_partitions_17052016 values less than ( to_seconds('2016-05-18 00:00:00') ));
Query OK, 0 rows affected (10.87 sec)
Records: 0 Duplicates: 0 Warnings: 0
  • Verifying data in specific partition

Read More