Linux下mysql数据库文件全部丢失mysql无法启动

时间:2014-04-09 17:00 来源:linux.it.net.cn 作者:IT网

一早起来，发现数据目录下的mysql文件全部消失，一声冷汗，怎么回事！！？？？被黑了，还是因为前两天重启服务器导致mysql数据丢失？？？这是怎么回事，上网看看怎么解决，没找到解决方案！！！！看看mysql服务是不是开启的，

[root@pacteralinux mysql]# ps -ef|grep mysql
root      53794291013:10pts/000:00:00grep mysql
[root@pacteralinux mysql]# service mysqld start
Starting MySQL..The server quit without updating PID file (/mnt/resource/mysqldate/pacteralinux.pid).[FAILED]

，服务关闭，而且启动不了！看日志：

13112613:10:57mysqld_safe Starting mysqld daemon withdatabases from /mnt/resource/mysqldate
2013-11-2613:10:580[Warning] TIMESTAMP withimplicit DEFAULT value isdeprecated. Pleaseuse--explicit_defaults_for_timestamp server option (see documentation formore details).
2013-11-2613:10:585650[Note] Plugin 'FEDERATED'isdisabled.
/usr/local/mysql/bin/mysqld: Table 'mysql.plugin'doesn't exist
2013-11-2613:10:585650[ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
2013-11-2613:10:585650[Note] InnoDB: The InnoDB memory heap isdisabled
2013-11-2613:10:585650[Note] InnoDB: Mutexes and rw_locks useGCC atomic builtins
2013-11-2613:10:585650[Note] InnoDB: Compressed tables usezlib 1.2.3
2013-11-2613:10:585650[Note] InnoDB: Not using CPU crc32 instructions
2013-11-2613:10:585650[Note] InnoDB: Initializing buffer pool, size = 128.0M
2013-11-2613:10:585650[Note] InnoDB: Completed initialization of buffer pool
2013-11-2613:10:585650[Note] InnoDB: The first specified data file ./ibdata1 did not exist: a newdatabase to be created!
2013-11-2613:10:585650[Note] InnoDB: Setting file ./ibdata1 size to 12MB
2013-11-2613:10:585650[Note] InnoDB: Database physically writes the file full: wait...
2013-11-2613:10:585650[Note] InnoDB: Setting log file ./ib_logfile101 size to 48MB
2013-11-2613:10:585650[Note] InnoDB: Setting log file ./ib_logfile1 size to 48MB
2013-11-2613:10:585650[Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
2013-11-2613:10:585650[Warning] InnoDB: New log files created, LSN=45781
2013-11-2613:10:585650[Note] InnoDB: Doublewrite buffer not found: creating new
2013-11-2613:10:585650[Note] InnoDB: Doublewrite buffer created
2013-11-2613:10:585650[Note] InnoDB: 128rollback segment(s) are active.
2013-11-2613:10:595650[Warning] InnoDB: Creating foreign key constraint system tables.
2013-11-2613:10:595650[Note] InnoDB: Foreign key constraint system tables created
2013-11-2613:10:595650[Note] InnoDB: Creating tablespace and datafile system tables.
2013-11-2613:10:595650[Note] InnoDB: Tablespace and datafile system tables created.
2013-11-2613:10:595650[Note] InnoDB: Waiting forpurge to start
2013-11-2613:10:595650[Note] InnoDB: 5.6.14started; log sequence number 0
2013-11-2613:10:595650[Note] Server hostname (bind-address): '*'; port: 3306
2013-11-2613:10:595650[Note] IPv6 isavailable.
2013-11-2613:10:595650[Note]   - '::'resolves to '::';
2013-11-2613:10:595650[Note] Server socket created on IP: '::'.
2013-11-2613:10:595650[ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist
13112613:10:59mysqld_safe mysqld from pid file /mnt/resource/mysqldate/pacteralinux.pid ended
[ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist：

表初始化没做好;不管，下意识的删除日志看下能不能重启

		
								[root@pacteralinux mysqldate]# ll

								total 110640

								-rw-rw----. 1mysql mysql       56Nov 2517:17auto.cnf

								-rw-rw----. 1mysql mysql 12582912Nov 2613:15ibdata1

								-rw-rw----. 1mysql mysql 50331648Nov 2613:15ib_logfile0

								-rw-rw----. 1mysql mysql 50331648Nov 2613:10ib_logfile1

								-rw-rw----. 1mysql root     39056Nov 2613:15pacteralinux.err

								[root@pacteralinux mysqldate]# rm ib*

								rm: remove regular file `ibdata1'? y

								rm: remove regular file `ib_logfile0'? y

								rm: remove regular file `ib_logfile1'? y

不行，网上看看能不能恢复数据，无解决方案！！

幸好这时一个mysql从服务器（我做了一个主从mysql热备份）！！！没办法，数据恢复不了就重新在复制一份吧！

上述步骤后还是不能启动数据

试试重新初始化：

[root@pacteralinux mysqldate]# /usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql/data/ --datadir=/mnt/resource/mysqldate/
FATAL ERROR: Could not find ./bin/my_print_defaults
If you compiled from source, you need to run 'make install'to
copy the software into the correct location ready foroperation.
If you are using a binary release, you must either be at the top
level of the extracted archive, or pass the --basedir option
pointing to that location.
[root@pacteralinux mysqldate]# cd /usr/local/mysql/bin/
[root@pacteralinux mysql]# /usr/local/mysql/scripts/mysql_install_db --user=mysql --no-defaults
Installing MySQL system tables...2013-11-2613:20:420[Warning] TIMESTAMP withimplicit DEFAULT value isdeprecated. Please use--explicit_defaults_for_timestamp server option (see documentation formore details).
2013-11-2613:20:426036[Note] InnoDB: The InnoDB memory heap isdisabled
2013-11-2613:20:426036[Note] InnoDB: Mutexes and rw_locks useGCC atomic builtins
2013-11-2613:20:426036[Note] InnoDB: Compressed tables usezlib 1.2.3
2013-11-2613:20:426036[Note] InnoDB: Not using CPU crc32 instructions
2013-11-2613:20:426036[Note] InnoDB: Initializing buffer pool, size = 128.0M
2013-11-2613:20:426036[Note] InnoDB: Completed initialization of buffer pool
2013-11-2613:20:426036[Note] InnoDB: Highest supported file format isBarracuda.
2013-11-2613:20:426036[Note] InnoDB: 128rollback segment(s) are active.
2013-11-2613:20:426036[Note] InnoDB: Waiting forpurge to start
2013-11-2613:20:426036[Note] InnoDB: 5.6.14started; log sequence number 1600607
2013-11-2613:20:426036[Warning] InnoDB: Cannot open table mysql/innodb_table_stats from theinternaldata dictionary of InnoDB though the .frm file forthe table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem.
ERROR: 1146Table 'mysql.innodb_table_stats'doesn't exist
2013-11-2613:20:426036[ERROR] Aborting
2013-11-2613:20:426036[Note] Binlog end
2013-11-2613:20:426036[Note] InnoDB: FTS optimize thread exiting.
2013-11-2613:20:426036[Note] InnoDB: Starting shutdown...
2013-11-2613:20:446036[Note] InnoDB: Shutdown completed; log sequence number 1600617
2013-11-2613:20:446036[Note] ./bin/mysqld: Shutdown complete

初始化不了，，删除之前初始化的文件试试

		
								[root@pacteralinux data]# ll

								total 12

								drwx------. 2mysql mysql 4096Sep 2512:27mysql

								drwx------. 2mysql mysql 4096Sep 2512:27performance_schema

								drwxr-xr-x. 2mysql mysql 4096Sep 2510:28test

								[root@pacteralinux data]# cd ..

								[root@pacteralinux mysql]# rm -rf data/

重新初始化：

		
								[root@pacteralinux mysql]# scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/mnt/resource/mysqldate --user=mysql

								Installing MySQL system tables...2013-11-2613:41:480[Warning] TIMESTAMP withimplicit DEFAULT value isdeprecated. Please use--explicit_defaults_for_timestamp server option (see documentation formore details).

								2013-11-2613:41:486768[Note] InnoDB: The InnoDB memory heap isdisabled

								2013-11-2613:41:486768[Note] InnoDB: Mutexes and rw_locks useGCC atomic builtins

								2013-11-2613:41:486768[Note] InnoDB: Compressed tables usezlib 1.2.3

								2013-11-2613:41:486768[Note] InnoDB: Not using CPU crc32 instructions

								2013-11-2613:41:486768[Note] InnoDB: Initializing buffer pool, size = 128.0M

								2013-11-2613:41:486768[Note] InnoDB: Completed initialization of buffer pool

								2013-11-2613:41:486768[Note] InnoDB: Highest supported file format isBarracuda.

								2013-11-2613:41:486768[Note] InnoDB: Log scan progressed past the checkpoint lsn 49463

								2013-11-2613:41:486768[Note] InnoDB: Database was not shutdown normally!

								2013-11-2613:41:486768[Note] InnoDB: Starting crash recovery.

								2013-11-2613:41:486768[Note] InnoDB: Reading tablespace information from the .ibd files...

								2013-11-2613:41:486768[Note] InnoDB: Restoring possible half-written data pages

								2013-11-2613:41:486768[Note] InnoDB: from the doublewrite buffer...

								InnoDB: Doing recovery: scanned up to log sequence number 1600617

								2013-11-2613:41:486768[Note] InnoDB: Starting an apply batch of log records to the database...

								InnoDB: Progress inpercent: 282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899

								InnoDB: Apply batch completed

								2013-11-2613:41:486768[Note] InnoDB: 128rollback segment(s) are active.

								2013-11-2613:41:486768[Note] InnoDB: Waiting forpurge to start

								2013-11-2613:41:486768[Note] InnoDB: 5.6.14started; log sequence number 1600617

								2013-11-2613:41:546768[Note] Binlog end

								2013-11-2613:41:546768[Note] InnoDB: FTS optimize thread exiting.

								2013-11-2613:41:546768[Note] InnoDB: Starting shutdown...

								2013-11-2613:41:556768[Note] InnoDB: Shutdown completed; log sequence number 1625997

								OK

								Filling help tables...2013-11-2613:41:550[Warning] TIMESTAMP withimplicit DEFAULT value isdeprecated. Please use--explicit_defaults_for_timestamp server option (see documentation formore details).

								2013-11-2613:41:556793[Note] InnoDB: The InnoDB memory heap isdisabled

								2013-11-2613:41:556793[Note] InnoDB: Mutexes and rw_locks useGCC atomic builtins

								2013-11-2613:41:556793[Note] InnoDB: Compressed tables usezlib 1.2.3

								2013-11-2613:41:556793[Note] InnoDB: Not using CPU crc32 instructions

								2013-11-2613:41:556793[Note] InnoDB: Initializing buffer pool, size = 128.0M

								2013-11-2613:41:556793[Note] InnoDB: Completed initialization of buffer pool

								2013-11-2613:41:556793[Note] InnoDB: Highest supported file format isBarracuda.

								2013-11-2613:41:556793[Note] InnoDB: 128rollback segment(s) are active.

								2013-11-2613:41:556793[Note] InnoDB: Waiting forpurge to start

								2013-11-2613:41:556793[Note] InnoDB: 5.6.14started; log sequence number 1625997

								2013-11-2613:41:556793[Note] Binlog end

								2013-11-2613:41:556793[Note] InnoDB: FTS optimize thread exiting.

								2013-11-2613:41:556793[Note] InnoDB: Starting shutdown...

								2013-11-2613:41:576793[Note] InnoDB: Shutdown completed; log sequence number 1626007

								OK

								To start mysqld at boot time you have to copy

								support-files/mysql.server to the right place foryour system

								PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !

								To doso, start the server, then issue the following commands:

								/usr/local/mysql/bin/mysqladmin -u root password 'new-password'

								/usr/local/mysql/bin/mysqladmin -u root -h pacteralinux password 'new-password'

								Alternatively you can run:

								/usr/local/mysql/bin/mysql_secure_installation

								which will also give you the option of removing the test

								databases and anonymous user created by default.  This is

								strongly recommended forproduction servers.

								See the manual formore instructions.

								You can start the MySQL daemon with:

								cd . ; /usr/local/mysql/bin/mysqld_safe &

								You can test the MySQL daemon withmysql-test-run.pl

								cd mysql-test ; perl mysql-test-run.pl

								Please report any problems withthe ./bin/mysqlbug script!

								The latest information about MySQL isavailable on the web at

								http://www.mysql.com

								Support MySQL by buying support/licenses at http://shop.mysql.com

								WARNING: Found existing config file /usr/local/mysql/my.cnf on the system.

								Because thisfile might be inuse, it was not replaced,

								but was used inbootstrap (unless you used --defaults-file)

								and when you later start the server.

								The newdefaultconfig file was created as/usr/local/mysql/my-new.cnf,

								please compare it withyour file and take the changes you need.

								WARNING: Default config file /etc/my.cnf exists on the system

								This file will be read by defaultby the MySQL server

								If you donot want to usethis, either remove it, or usethe

								--defaults-file argument to mysqld_safe when starting the server

重新启动mysql

		
								[root@pacteralinux scripts]# service mysqld start

								Starting MySQL.[  OK  ]

								[root@pacteralinux mysql]# ps -ef|grep msyql

								root      7236  4316  0 14:08 pts/100:00:00 grepmsyql

								[root@pacteralinux mysql]# ps -ef|grep mysql

								root      6838     1  0 13:42 pts/100:00:00 /bin/sh/usr/local/mysql/bin/mysqld_safe--datadir=/mnt/resource/mysqldate--pid-file=/mnt/resource/mysqldate/pacteralinux.pid

								mysql     7091  6838  0 13:42 pts/100:00:00 /usr/local/mysql/bin/mysqld--basedir=/usr/local/mysql--datadir=/mnt/resource/mysqldate--plugin-dir=/usr/local/mysql/lib/plugin--user=mysql --log-error=/mnt/resource/mysqldate/pacteralinux.err --pid-file=/mnt/resource/mysqldate/pacteralinux.pid --socket=/mnt/resource/mysqldate/mysql.sock --port=3306

								root      7238  4316  0 14:08 pts/100:00:00 grepmysql

可以看到，mysql安装路径为--basedir=/usr/local/mysql，初始数据库的存放目录为--datadir=/mnt/resource/mysqldate

		
								-rw-rw----. 1 mysql mysql   2048 Nov 26 13:41 user.MYI

								[root@pacteralinux mysql]# pwd

								/mnt/resource/mysqldate/mysql

								[root@pacteralinux mysql]# cd ..

								[root@pacteralinux mysqldate]# ll

								total 110664

								-rw-rw----. 1 mysql mysql       56 Nov 25 17:17 auto.cnf

								-rw-rw----. 1 mysql mysql 12582912 Nov 26 13:42 ibdata1

								-rw-rw----. 1 mysql mysql 50331648 Nov 26 13:42 ib_logfile0

								-rw-rw----. 1 mysql mysql 50331648 Nov 26 13:39 ib_logfile1

								drwx------. 2 mysql mysql     4096 Nov 26 13:41 mysql

								srwxrwxrwx. 1 mysql mysql        0 Nov 26 13:42 mysql.sock

								-rw-rw----. 1 mysql root     46096 Nov 26 13:42 pacteralinux.err

								-rw-rw----. 1 mysql mysql        5 Nov 26 13:42 pacteralinux.pid

								drwx------. 2 mysql mysql     4096 Nov 26 13:41 performance_schema

								drwx------. 2 mysql mysql     4096 Nov 26 13:41 test

其中，mysql，performance_schema，test为初始化后的文件

下面开始恢复数据库

登录到生产服务器执行备份：

		
								[root@uyhd000225 ~]# mysqldump -u***** -p***** mysqldb >mysqldb20131126.sql  #数据库比较大，大约半小时

复制到备份服务器：

		
								[root@uyhd000225 ~]# scp mysqldb20131126.sql root@remoteIP:/mnt/backup/

								root@remoteIP's password:

								mysqldb20131126.sql                                                                               3%  153MB 680.6KB/s 1:43:33ETA

本文出自 “从运维开始” 博客，请务必保留此出处http://fuwenchao.blog.51cto.com/6008712/1331822

(责任编辑：IT)