0

After restoring a backup the server can't start..

command line

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
/usr/local/mysql/scripts/mysql_install_db --user=mysql --datadir=/var/lib/mysql
service mysql start

error log

131112 02:00:54 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
2013-11-12 02:00:55 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-12 02:00:55 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-12 02:00:55 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2013-11-12 02:00:55 19706 [Warning] Using pre 5.5 semantics to load error messages from /opt/mysql/server-5.6/share/english/.
2013-11-12 02:00:55 19706 [Warning] If this is not intended, refer to the documentation for valid usage of --lc-messages-dir and --language parameters.
2013-11-12 02:00:55 19706 [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-12 02:00:55 19706 [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-12 02:00:55 19706 [Note] Plugin 'FEDERATED' is disabled.
/usr/local/mysql/bin/mysqld: Table 'mysql.plugin' doesn't exist
2013-11-12 02:00:55 19706 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
2013-11-12 02:00:55 19706 [Note] InnoDB: The InnoDB memory heap is disabled
2013-11-12 02:00:55 19706 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2013-11-12 02:00:55 19706 [Note] InnoDB: Compressed tables use zlib 1.2.3
2013-11-12 02:00:55 19706 [Note] InnoDB: Using Linux native AIO
2013-11-12 02:00:55 19706 [Note] InnoDB: Not using CPU crc32 instructions
2013-11-12 02:00:55 19706 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2013-11-12 02:00:55 19706 [Note] InnoDB: Completed initialization of buffer pool
2013-11-12 02:00:55 19706 [Note] InnoDB: Highest supported file format is Barracuda.
2013-11-12 02:00:55 19706 [Note] InnoDB: Log scan progressed past the checkpoint lsn 10944663
2013-11-12 02:00:55 19706 [Note] InnoDB: Database was not shutdown normally!
2013-11-12 02:00:55 19706 [Note] InnoDB: Starting crash recovery.
2013-11-12 02:00:55 19706 [Note] InnoDB: Reading tablespace information from the .ibd files...
2013-11-12 02:00:55 19706 [Note] InnoDB: Restoring possible half-written data pages 
2013-11-12 02:00:55 19706 [Note] InnoDB: from the doublewrite buffer...
InnoDB: Doing recovery: scanned up to log sequence number 10944683
2013-11-12 02:00:55 19706 [Note] InnoDB: 128 rollback segment(s) are active.
2013-11-12 02:00:55 19706 [Note] InnoDB: Waiting for purge to start
2013-11-12 02:00:55 19706 [Note] InnoDB: 5.6.14 started; log sequence number 10944683
2013-11-12 02:00:55 19706 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
2013-11-12 02:00:55 19706 [Note]   - '127.0.0.1' resolves to '127.0.0.1';
2013-11-12 02:00:55 19706 [Note] Server socket created on IP: '127.0.0.1'.
2013-11-12 02:00:55 19706 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist
131112 02:00:55 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
Community
  • 1
clarkk
  • 2,035
  • 8
  • 24
  • 36

1 Answers1

0

First, mysql_upgrade: command not found. This is basic Linux/UNIX PATH behavior. If you run echo $PATH you'll see that neither '/opt/mysql/server-5.6/bin' nor '.' is listed. Unlike Windows, commands in the directory you are in are NOT executed by simply typing the name. You need to specify the full path or add the path to your PATH environment variable. You can test this by going into '/opt/mysql/server-5.6/bin' and running the following command:

PATH=$PATH:/opt/mysql/server-5.6/bin mysql_upgrade and ./mysql_upgrade.

For your actual MySQL problem, the logs clearly indicate that you are missing multiple tables in the mysql schema. Likely you're missing the entire mysql schema due to it not being backed up in the first place. I don't think mysql_upgrade is going to fix it for you in that case, you need to have the schema there for it to be 'upgraded'.

Look for a script named mysql_install_db and run that to create the initial tables and / or make sure you back up that schema as well.

  • the table `information_schema` is not backed up.. I'm running this syntax `innobackupex --user=root --password=pass --stream=tar ./ | gzip -c -1 > /var/bak/db/2013-11-14-2124_mysql.tar.gz` – clarkk Nov 14 '13 at 21:28
  • this command should make a full backup.. – clarkk Nov 14 '13 at 22:56
  • The tables in information_schema aren't real tables. There are no files to backup (go in your data dir and check). If you'd like more info about information schema try [MySQL Information_Schema explained](http://www.askdbexperts.com/2012/10/mysql-informationschema-explained.html). –  Nov 15 '13 at 01:47
  • what do you then mean by "make sure you back up schema"? so I just need to run `mysql_install_db` to make it work? – clarkk Nov 16 '13 at 23:12
  • The mysql schema. The ones created by mysql_install_db. –  Nov 17 '13 at 00:48
  • Even after running `mysql_install_db --user=mysql --datadir=/var/lib/mysql` the mysql server can not start.. have updated my qustion with all command lines – clarkk Nov 17 '13 at 21:40