CODEWITHSUNDEEP
mysqllinuxinnobackupex
clarkk
clarkk

Reputation: 27689

innobackupex - after restoring a mysql backup the server can not start up

After restoring a backup the mysql server can not start up..

quit without updating PID file

backup command line

innobackupex --user=root --password=pass --databases="the_database" --stream=tar ./ 2> /var/log/innobackupex.log | gzip -c -1 > /var/bak/backup.tar.gz

restore command lines

tar -izxf /var/bak/db/2013-11-16-2300_mysql.tar.gz -C /var/bak/db_import
innobackupex --defaults-file=/var/ini/my.cnf --use-memory=1G --apply-log /var/bak/db_import
service mysql stop
mv /var/lib/mysql /var/lib/mysql-old
mkdir /var/lib/mysql
innobackupex --defaults-file=/var/ini/my.cnf --copy-back /var/bak/db_import
chown -R mysql:mysql /var/lib/mysql
service mysql start

/var/lib/mysql

innodb_index_stats.ibd
innodb_table_stats.ibd
slave_master_info.ibd
slave_relay_log_info.ibd
slave_worker_info.ibd

error log

131121 14:08:12 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
2013-11-21 14:08:13 0 [Warning] Using unique option prefix key_buffer instead of key_buffer_size is deprecated and will be removed in a future release. Please use the full name instead.
2013-11-21 14:08:13 0 [Warning] Using unique option prefix key_buffer instead of key_buffer_size is deprecated and will be removed in a future release. Please use the full name instead.
2013-11-21 14:08:13 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2013-11-21 14:08:13 14010 [Warning] Using pre 5.5 semantics to load error messages from /opt/mysql/server-5.6/share/english/.
2013-11-21 14:08:13 14010 [Warning] If this is not intended, refer to the documentation for valid usage of --lc-messages-dir and --language parameters.
2013-11-21 14:08:13 14010 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
2013-11-21 14:08:13 14010 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
2013-11-21 14:08:13 14010 [Note] Plugin 'FEDERATED' is disabled.
/usr/local/mysql/bin/mysqld: Table 'mysql.plugin' doesn't exist
2013-11-21 14:08:13 14010 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
2013-11-21 14:08:13 14010 [Note] InnoDB: The InnoDB memory heap is disabled
2013-11-21 14:08:13 14010 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2013-11-21 14:08:13 14010 [Note] InnoDB: Compressed tables use zlib 1.2.3
2013-11-21 14:08:13 14010 [Note] InnoDB: Using Linux native AIO
2013-11-21 14:08:13 14010 [Note] InnoDB: Not using CPU crc32 instructions
2013-11-21 14:08:13 14010 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2013-11-21 14:08:13 14010 [Note] InnoDB: Completed initialization of buffer pool
2013-11-21 14:08:13 14010 [Note] InnoDB: Highest supported file format is Barracuda.
2013-11-21 14:08:13 14010 [Note] InnoDB: 128 rollback segment(s) are active.
2013-11-21 14:08:14 14010 [Note] InnoDB: Waiting for purge to start
2013-11-21 14:08:14 14010 [Note] InnoDB: 5.6.14 started; log sequence number 56227635
2013-11-21 14:08:14 14010 [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: fb44ff89-52ad-11e3-9dff-002185624b9b.
2013-11-21 14:08:15 14010 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
2013-11-21 14:08:15 14010 [Note]   - '127.0.0.1' resolves to '127.0.0.1';
2013-11-21 14:08:15 14010 [Note] Server socket created on IP: '127.0.0.1'.
2013-11-21 14:08:15 14010 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist
131121 14:08:15 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

Upvotes: 5

Views: 4077

Answers (3)

matipl
matipl

Reputation: 11

You should backup all data, not only one database.

You need to use the xtrabackup command: 

ex: xtrabackup --prepare --target-dir=/data/backups/mysql/

https://www.percona.com/doc/percona-xtrabackup/2.1/xtrabackup_bin/preparing_the_backup.html

Upvotes: 1

RandomSeed
RandomSeed

Reputation: 29759

You have deleted (moved) the /var/lib/mysql folder, (mv /var/lib/mysql /var/lib/mysql-old) which contains the mysql system database.

Restore this folder to its original location, then load your dump. This dump only contains data from your selected database.

The dump only restores data located in /var/lib/mysql/[your_database]. You should never touch files under /var/lib/mysql/mysql.

For completeness: with default settings, the /var/lib/mysql folder contains the following important system files, which should not be tampered with in a normal situation:

  • /var/lib/mysql/mysql/*: the mysql system database
  • /var/lib/mysql/ib_logfile*: the redo log
  • /var/lib/mysql/ibdata*: the default tablespace for InnoDB tables
  • /var/lib/mysql/mysql-bin.*: the binary log

Do not manipulate these files unless you exactly know what you are doing.

Upvotes: 5

Mika Vatanen
Mika Vatanen

Reputation: 4017

If you do not care about original mysql privilege configurations (mysql database), you can run the install script, which will create the mysql database.

$ mysql_install_db

Upvotes: 1

Related Questions