MySql server is not starting after xtrabackup restore using innobackupex tool

[Harsh_Pandit]

Hi

I am a beginner on xtrabackup however, i have successfully backup my whole database and restore the same using innobackupex script. Problem comes when after restore i tried to restart MySQL server. It gave me error like "The server quit without updating PID file (/var/lib/mysql).

Let me tell here that before to restore from xtrabackup, i move /var/lib/mysql to /var/lib/mysql-old and made /var/lib/mysql blank. Also in order to avoid permission issues, I gave mysql user permission to access the above mention folder as “sudo chown -R mysql:mysql /var/lib/mysql/”

[przemek]

What does the MySQL error log say? It’s always the first place you should be looking at.

[eroomydna]

More details about your backup and restore efforts are needed here for anyone to be able to provide help. As Przemek mentioned the error log will usually hold the key to your issues in cases like this.

When creating the backup did you stream to a tar file? Remember you to use the -i argument when unpacking the files from a tar archive built using innobackupex

-i, --ignore-zeros ignore zeroed blocks in archive (means EOF)

[Harsh_Pandit]

Hi przemek

I tried a lot to find the err file however after replacing old datadirectory /var/lib/mysql with the new files generated by xtrabackup --copy-back. Mysql is not generating any error file. In actual datadirectory, i found a file named as [dhcppc1.err], which has server startup errors however they are not related to the new setup.

Please suggest.

[scott.nemes]

Hard to say without seeing an error, but the three usual suspects are:

1. The server you are restoring to is using a different innodb_log_file_size
2. SELinux or AppArmor is complaining about the new files and you need to modify the file contexts
3. Permissions are wrong (but you seem to have covered that already)

[Harsh_Pandit]

Hi

I tried it again and now with proper error log file. Here are the details of error log:
130917 09:09:59 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
2013-09-17 09:09:59 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2013-09-17 09:09:59 2399 [Note] Plugin ‘FEDERATED’ is disabled.
^G/usr/sbin/mysqld: Can’t find file: ‘./mysql/plugin.frm’ (errno: 13 - Permission denied)
2013-09-17 09:09:59 2399 [ERROR] Can’t open the mysql.plugin table. Please run mysql_upgrade to create it.
2013-09-17 09:09:59 2399 [Note] InnoDB: The InnoDB memory heap is disabled
2013-09-17 09:09:59 2399 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2013-09-17 09:09:59 2399 [Note] InnoDB: Compressed tables use zlib 1.2.3
2013-09-17 09:09:59 2399 [Note] InnoDB: Using Linux native AIO
2013-09-17 09:09:59 2399 [Note] InnoDB: Not using CPU crc32 instructions
2013-09-17 09:09:59 2399 [Note] InnoDB: Initializing buffer pool, size = 256.0M
2013-09-17 09:09:59 2399 [Note] InnoDB: Completed initialization of buffer pool
2013-09-17 09:09:59 b77598e0 InnoDB: Operating system error number 13 in a file operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory.
2013-09-17 09:09:59 b77598e0 InnoDB: File name /var/lib/mysql/ibdata1
2013-09-17 09:09:59 b77598e0 InnoDB: File operation call: ‘stat’ returned OS error 113.
2013-09-17 09:09:59 2399 [ERROR] InnoDB: os_file_get_status() failed on ‘/var/lib/mysql/ibdata1’. Can’t determine file permissions
2013-09-17 09:09:59 2399 [ERROR] InnoDB: The system tablespace must be writable!
2013-09-17 09:09:59 2399 [ERROR] Plugin ‘InnoDB’ init function returned error.
2013-09-17 09:09:59 2399 [ERROR] Plugin ‘InnoDB’ registration as a STORAGE ENGINE failed.
2013-09-17 09:09:59 2399 [ERROR] Unknown/unsupported storage engine: InnoDB
2013-09-17 09:09:59 2399 [ERROR] Aborting

As, we can see, there are two issues:

1. plugin.frm is not accessible however, i had given permission to mysql user for this folder using (chown -R mysql:mysql /var/lib/mysql), which is the base directory.
2. mysqld doesn’t have access right to Innodb, Here also i had provided read and write permissions to user and groups using (chmod 661 /var/lib/mysql/ibdata1) and to log files also.

Note: I am using my linux box using root user. Also, i tried using restarting my system.

Please suggest.

[scott.nemes]

Make sure the MySQL user has execute privs for all necessary directories still.

Aside from that, do you have SELinux or AppArmor installed/enabled? That would cause similar access errors.

[Harsh_Pandit]

Hi Scott/Przemek

Thank you so much guys !

I finally got the issue and server started after resolving it. There was a SELinux security issues, which was not allowing mysql to consider new datadirectory.

Best Regards
Harsh