In this blog we are going to share my recent experience with an issue which we faced during migration between two Ubuntu servers.

The activity was pretty simple, the client had bought a new machine with enhanced memory and faster disk, so the data has to be transferred to the new server and live MySQL replication has to be configured between the old and the new server.

For the hot and live data transfer we used xtrabackup’s stream method, the data transfer happened smoothly without any issue.

Everything was set and we had applied the log and followed the normal procedure to change the ownership of datadir to “mysql” and all the entries in the my.cnf were double checked.

When we intended to start mysql, the service didn’t come up instead we had a weird error as below.

MySQL Error

 
2016-08-31 22:35:27 9938 [ERROR] InnoDB: ./ibdata1 can't be opened in read-write mode
2016-08-31 22:35:27 9938 [ERROR] InnoDB: The system tablespace must be writable!
2016-08-31 22:35:27 9938 [ERROR] Plugin 'InnoDB' init function returned error.
	

e were totally perplexed on seeing the above since we had checked the ownership of the ‘datadir’

Intitally we thought this might be due to the apparmour which is enabled by default in debian based systems similar to selinux in RPM based system, since we had pointed the datadir to new disk which was mounted and named as “/Data”

We first whitelisted the datadir in AppArmour configuration and restarted MySQL but still we faced the same issue, we tried again by disabling the AppArmour but still the same error popped.

Initially we didn’t suspect the disk, since we had directly streamed backup to that mount “/Data”, during random checks here and there ran the fdisk command and found the below issue with the partition table.

Partition Table Error

 
Disk /dev/xvdc: 536.9 GB, 536870912000 bytes
 255 heads, 63 sectors/track, 65270 cylinders, total 1048576000 sectors
 Units = sectors of 1 * 512 = 512 bytes
 Sector size (logical/physical): 512 bytes / 512 bytes
 I/O size (minimum/optimal): 512 bytes / 512 bytes
 Disk identifier: 0x00000000
	

Disk /dev/xvdc doesn’t contain a valid partition table

And also the entry was missing in the “/etc/fstab” for the particular mount. Immediately this was taken to the linux admin and he had corrected it by reformatting the disk and adding entry to the ‘/etc/fstab’.

Once this was done, we re-initiated backup and started MySQL successfully and now is handling 10k queries per second with a limited buffer pool of 4GB and DB size of 120GB.

Optimize performance, enhance reliability, and ensure data security with Mydbops' MySQL InnoDB Cluster Consulting and Support Services. Our team can help you fine-tune temporary tablespace configuration, troubleshoot performance issues, and implement best practices. Contact us today to schedule a free consultation.

{{cta}}