Hello Experts,
I am testing Percona Mysql 5.5 database migration to Percona 5.6 & backup using innobackupex.
I took backup & trying to restore full backup files to Percona 5.6
SERVER A: Centos 6, Percona 5.5
@my.cnf : /etc/my.cnf
innodb_file_per_table=1
I took full backup using innobackupex & check the
$ cat backup-my.cnf
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_files_in_group = 2
innodb_log_file_size = 5242880
innodb_fast_checksum = 0
innodb_page_size = 16384
innodb_log_block_size = 512
SERVER B: Centos,Percona Server 5.6
A fresh install Percona Server 5.6 with Xtrabackup installed.
i) After fresh installation . I stop the server & move the datadir from /var/lib/mysql to /var/lib/mysql-backup
ii) IN mysql.cnf
datadir= /var/lib/mysql
iii) restore the backup using innobackupex
innobackupex --copy-back backupfile-location
iv) chown -R mysql.mysql /var/lib/mysql
After this when i start the db "/etc/init.d/mysql start "
It gives me error
Starting MySQL. ERROR! The server quit without updating PID file
I did googling but following things are not working.
a) Change owner of datadir – done but still got error
b) kill processs — No process found
c) Reset the value in my.cnf "innodb_data_file_path = ibdata1:12M:autoextend "
innodb_data_file_path = ibdata1:10M:autoextend
Please help me to resolve this issue.
What does your error log say? Likely will tell us the problem.
Two usual causes are:
-
SELinux is enabled / set to enforcing. Run sestatus to check, and if it is enabled, either disable it or add in the necessary rules.
-
Inconsistent innodb_log_file_size value. Make the setting on the backup server match.
Hi Scott,
- SELINUX is disable
- Sharing the log file size of PERCONA 5.5 & PERCONA 5.6.
PERCONA 5.5 ( WITH DATA)
mysql> show variables like ‘innodb_log_file_size’;
±---------------------±--------+
| Variable_name | Value |
±---------------------±--------+
| innodb_log_file_size | 5242880 |
±---------------------±--------+
1 row in set (0.01 sec)
mysql> show variables like ‘innodb_data%’;
±----------------------±-----------------------+
| Variable_name | Value |
±----------------------±-----------------------+
| innodb_data_file_path | ibdata1:10M:autoextend |
| innodb_data_home_dir | |
±----------------------±-----------------------+
2 rows in set (0.00 sec)
show variables like ‘datadir’;
±--------------±----------------+
| Variable_name | Value |
±--------------±----------------+
| datadir | /var/lib/mysql/ |
±--------------±----------------+
1 row in set (0.00 sec)
PERCONA 5.6 (FRESH INSTALL)
show variables like ‘innodb_log_file_size’;
±---------------------±---------+
| Variable_name | Value |
±---------------------±---------+
| innodb_log_file_size | 50331648 |
±---------------------±---------+
1 row in set (0.00 sec)
mysql> show variables like ‘innodb_data%’;
±----------------------±-----------------------+
| Variable_name | Value |
±----------------------±-----------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
| innodb_data_home_dir | |
±----------------------±-----------------------+
2 rows in set (0.00 sec)
mysql> show variables like ‘datadir’;
±--------------±----------------+
| Variable_name | Value |
±--------------±----------------+
| datadir | /var/lib/mysql/ |
±--------------±----------------+
1 row in set (0.01 sec)
ERROR LOG PERCONA 5.6
30822 17:56:51 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
2013-08-22 17:58:21 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2013-08-22 17:58:21 2395 [Note] Plugin ‘FEDERATED’ is disabled.
2013-08-22 17:58:21 2395 [Note] InnoDB: The InnoDB memory heap is disabled
2013-08-22 17:58:21 2395 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2013-08-22 17:58:21 2395 [Note] InnoDB: Compressed tables use zlib 1.2.3
2013-08-22 17:58:21 2395 [Note] InnoDB: Using Linux native AIO
2013-08-22 17:58:21 2395 [Note] InnoDB: Not using CPU crc32 instructions
2013-08-22 17:58:21 2395 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2013-08-22 17:58:21 2395 [Note] InnoDB: Completed initialization of buffer pool
2013-08-22 17:58:22 2395 [Note] InnoDB: Highest supported file format is Barracuda.
2013-08-22 17:58:22 2395 [Note] InnoDB: 128 rollback segment(s) are active.
2013-08-22 17:58:22 2395 [Note] InnoDB: Waiting for purge to start
2013-08-22 17:58:22 2395 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.6.12-rc60.4 started; log sequence number 1625987
2013-08-22 17:58:22 2395 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 55a93731-0b26-11e3-8b91-000c29e12d22.
2013-08-22 17:58:22 2395 [Note] RSA private key file not found: /var/lib/mysql//private_key.pem. Some authentication plugins will not work.
2013-08-22 17:58:22 2395 [Note] RSA public key file not found: /var/lib/mysql//public_key.pem. Some authentication plugins will not work.
2013-08-22 17:58:22 2395 [Note] Server hostname (bind-address): ‘*’; port: 3306
2013-08-22 17:58:22 2395 [Note] IPv6 is available.
2013-08-22 17:58:22 2395 [Note] - ‘::’ resolves to ‘::’;
2013-08-22 17:58:22 2395 [Note] Server socket created on IP: ‘::’.
2013-08-22 17:58:22 2395 [Note] Event Scheduler: Loaded 0 events
2013-08-22 17:58:22 2395 [Note] /usr/sbin/mysqld: ready for connections.
Version: ‘5.6.12-56’ socket: ‘/var/lib/mysql/mysql.sock’ port: 3306 Percona Server (GPL), Release rc60.4, Revision 393
2013-08-22 18:23:02 2395 [Note] /usr/sbin/mysqld: Normal shutdown
2013-08-22 18:23:02 2395 [Note] Giving 0 client threads a chance to die gracefully
2013-08-22 18:23:02 2395 [Note] Event Scheduler: Purging the queue. 0 events
2013-08-22 18:23:02 2395 [Note] Shutting down slave threads
2013-08-22 18:23:02 2395 [Note] Forcefully disconnecting 0 remaining clients
2013-08-22 18:23:02 2395 [Note] Binlog end
2013-08-22 18:23:02 2395 [Note] Shutting down plugin ‘partition’
2013-08-22 18:23:02 2395 [Note] Shutting down plugin ‘INNODB_CHANGED_PAGES’
2013-08-22 18:23:02 2395 [Note] Shutting down plugin ‘INNODB_SYS_DATAFILES’
2013-08-22 18:23:02 2395 [Note] Shutting down plugin ‘INNODB_SYS_TABLESPACES’
2013-08-22 18:23:02 2395 [Note] Shutting down plugin ‘INNODB_SYS_FOREIGN_COLS’
2013-08-22 18:23:02 2395 [Note] Shutting down plugin ‘INNODB_SYS_FOREIGN’
2013-08-22 18:23:02 2395 [Note] Shutting down plugin ‘INNODB_SYS_FIELDS’
2013-08-22 18:23:02 2395 [Note] Shutting down plugin ‘INNODB_SYS_COLUMNS’
2013-08-22 18:23:02 2395 [Note] Shutting down plugin ‘INNODB_SYS_INDEXES’
2013-08-22 18:23:02 2395 [Note] Shutting down plugin ‘INNODB_SYS_TABLESTATS’
2013-08-22 18:23:02 2395 [Note] Shutting down plugin ‘INNODB_SYS_TABLES’
2013-08-22 18:23:02 2395 [Note] Shutting down plugin ‘INNODB_FT_INDEX_TABLE’
2013-08-22 18:23:02 2395 [Note] Shutting down plugin ‘INNODB_FT_INDEX_CACHE’
2013-08-22 18:23:02 2395 [Note] Shutting down plugin ‘INNODB_FT_CONFIG’
2013-08-22 18:23:02 2395 [Note] Shutting down plugin ‘INNODB_FT_BEING_DELETED’
2013-08-22 18:23:02 2395 [Note] Shutting down plugin ‘INNODB_FT_DELETED’
2013-08-22 18:23:02 2395 [Note] Shutting down plugin ‘INNODB_FT_DEFAULT_STOPWORD’
2013-08-22 18:23:02 2395 [Note] Shutting down plugin ‘INNODB_METRICS’
2013-08-22 18:23:02 2395 [Note] Shutting down plugin ‘INNODB_BUFFER_POOL_STATS’
2013-08-22 18:23:02 2395 [Note] Shutting down plugin ‘INNODB_BUFFER_PAGE_LRU’
2013-08-22 18:23:02 2395 [Note] Shutting down plugin ‘INNODB_BUFFER_PAGE’
2013-08-22 18:23:02 2395 [Note] Shutting down plugin ‘INNODB_CMP_PER_INDEX_RESET’
2013-08-22 18:23:02 2395 [Note] Shutting down plugin ‘INNODB_CMP_PER_INDEX’
2013-08-22 18:23:02 2395 [Note] Shutting down plugin ‘INNODB_CMPMEM_RESET’
2013-08-22 18:23:02 2395 [Note] Shutting down plugin ‘INNODB_CMPMEM’
2013-08-22 18:23:02 2395 [Note] Shutting down plugin ‘INNODB_CMP_RESET’
2013-08-22 18:23:02 2395 [Note] Shutting down plugin ‘INNODB_CMP’
2013-08-22 18:23:02 2395 [Note] Shutting down plugin ‘INNODB_LOCK_WAITS’
2013-08-22 18:23:02 2395 [Note] Shutting down plugin ‘INNODB_LOCKS’
2013-08-22 18:23:02 2395 [Note] Shutting down plugin ‘INNODB_TRX’
2013-08-22 18:23:02 2395 [Note] Shutting down plugin ‘XTRADB_RSEG’
2013-08-22 18:23:02 2395 [Note] Shutting down plugin ‘XTRADB_INTERNAL_HASH_TABLES’
2013-08-22 18:23:02 2395 [Note] Shutting down plugin ‘XTRADB_READ_VIEW’
2013-08-22 18:23:02 2395 [Note] Shutting down plugin ‘InnoDB’
2013-08-22 18:23:02 2395 [Note] InnoDB: FTS optimize thread exiting.
2013-08-22 18:23:02 2395 [Note] InnoDB: Starting shutdown…
2013-08-22 18:23:08 2395 [Note] InnoDB: Shutdown completed; log sequence number 1625997
2013-08-22 18:23:08 2395 [Note] Shutting down plugin ‘BLACKHOLE’
2013-08-22 18:23:08 2395 [Note] Shutting down plugin ‘PERFORMANCE_SCHEMA’
2013-08-22 18:23:08 2395 [Note] Shutting down plugin ‘ARCHIVE’
2013-08-22 18:23:08 2395 [Note] Shutting down plugin ‘MyISAM’
2013-08-22 18:23:08 2395 [Note] Shutting down plugin ‘MRG_MYISAM’
2013-08-22 18:23:08 2395 [Note] Shutting down plugin ‘MEMORY’
2013-08-22 18:23:08 2395 [Note] Shutting down plugin ‘CSV’
2013-08-22 18:23:08 2395 [Note] Shutting down plugin ‘sha256_password’
2013-08-22 18:23:08 2395 [Note] Shutting down plugin ‘mysql_old_password’
2013-08-22 18:23:08 2395 [Note] Shutting down plugin ‘mysql_native_password’
2013-08-22 18:23:08 2395 [Note] Shutting down plugin ‘binlog’
2013-08-22 18:23:08 2395 [Note] /usr/sbin/mysqld: Shutdown complete
130822 18:23:09 mysqld_safe mysqld from pid file /var/lib/mysql/localhost.localdomain.pid ended
Please tell me how to resolve the inconsistency of innodb_log_file_size .
Note that default value for InnoDB logs has changed in 5.6 from 5MB in previous versions to 48MB. InnoDB will refuse to start if it encounters existing logs size different then default or specified value. So you can just copy responsible setting from 5.5 instance (innodb_log_file_size = 5242880), or just move those files out of datadir and start MySQL. InnoDB will re-create new logs.
However for most production cases, much larger logs are advised, like 1GB+.
As przemek mentioned, the easiest way to solve the innodb_log_file_size issue would be move the ib_logfile* files from the MySQL 5.6 data directory to another location for safe keeping, then start up the MySQL 5.6 instance. That will force MySQL to create new ib_logfile’s with the new size. Keep in mind that you will have to do that anytime you do a binary restore (i.e. with Xtrabackup) from another MySQL server with a different innodb_log_file_size.
Note that the ib_logfile files are important, so under normal circumstances you would not want to mess with them unless you know what you are doing, but it should be safe in this case.
Thanks przemek & scott. Last 2 post worked for me. ERROR got resolved.