一早起来,发现数据目录下的mysql文件全部消失,一声冷汗,怎么回事!!???被黑了,还是因为前两天重启服务器导致mysql数据丢失???这是怎么回事,上网看看怎么解决,没找到解决方案!!!!看看mysql服务是不是开启的,
[root@pacteralinux mysql]# ps -ef|grep mysql
root 5379 4291 0 13:10 pts/0 00:00:00 grep mysql
[root@pacteralinux mysql]# service mysqld start
Starting MySQL..The server quit without updating PID file (/mnt/resource/mysqldate/pacteralinux.pid).[FAILED]
靠,服务关闭,而且启动不了!看日志:131126 13:10:57 mysqld_safe Starting mysqld daemon with databases from /mnt/resource/mysqldate
-11-26 13:10:58 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
-11-26 13:10:58 5650 [Note] Plugin 'FEDERATED' is disabled.
/usr/local/mysql/bin/mysqld: Table 'mysql.plugin' doesn't exist
-11-26 13:10:58 5650 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
-11-26 13:10:58 5650 [Note] InnoDB: The InnoDB memory heap is disabled
-11-26 13:10:58 5650 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
-11-26 13:10:58 5650 [Note] InnoDB: Compressed tables use zlib 1.2.3
-11-26 13:10:58 5650 [Note] InnoDB: Not using CPU crc32 instructions
-11-26 13:10:58 5650 [Note] InnoDB: Initializing buffer pool, size = 128.0M
-11-26 13:10:58 5650 [Note] InnoDB: Completed initialization of buffer pool
-11-26 13:10:58 5650 [Note] InnoDB: The first specified data file ./ibdata1 did not exist: a new database to be created!
-11-26 13:10:58 5650 [Note] InnoDB: Setting file ./ibdata1 size to 12 MB
-11-26 13:10:58 5650 [Note] InnoDB: Database physically writes the file full: wait...
-11-26 13:10:58 5650 [Note] InnoDB: Setting log file ./ib_logfile101 size to 48 MB
-11-26 13:10:58 5650 [Note] InnoDB: Setting log file ./ib_logfile1 size to 48 MB
-11-26 13:10:58 5650 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
-11-26 13:10:58 5650 [Warning] InnoDB: New log files created, LSN=45781
-11-26 13:10:58 5650 [Note] InnoDB: Doublewrite buffer not found: creating new
-11-26 13:10:58 5650 [Note] InnoDB: Doublewrite buffer created
-11-26 13:10:58 5650 [Note] InnoDB: 128 rollback segment(s) are active.
-11-26 13:10:59 5650 [Warning] InnoDB: Creating foreign key constraint system tables.
-11-26 13:10:59 5650 [Note] InnoDB: Foreign key constraint system tables created
-11-26 13:10:59 5650 [Note] InnoDB: Creating tablespace and datafile system tables.
-11-26 13:10:59 5650 [Note] InnoDB: Tablespace and datafile system tables created.
-11-26 13:10:59 5650 [Note] InnoDB: Waiting for purge to start
-11-26 13:10:59 5650 [Note] InnoDB: 5.6.14 started; log sequence number 0
-11-26 13:10:59 5650 [Note] Server hostname (bind-address): '*'; port: 3306
-11-26 13:10:59 5650 [Note] IPv6 is available.
-11-26 13:10:59 5650 [Note] - '::' resolves to '::';
-11-26 13:10:59 5650 [Note] Server socket created on IP: '::'.
-11-26 13:10:59 5650 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist
131126 13:10:59 mysqld_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----. 1 mysql mysql 56 Nov 25 17:17 f
-rw-rw----. 1 mysql mysql 12582912 Nov 26 13:15 ibdata1
-rw-rw----. 1 mysql mysql 50331648 Nov 26 13:15 ib_logfile0
-rw-rw----. 1 mysql mysql 50331648 Nov 26 13:10 ib_logfile1
-rw-rw----. 1 mysql root 39056 Nov 26 13:15 pacteralinux.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 for operation.
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...-11-26 13:20:42 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
-11-26 13:20:42 6036 [Note] InnoDB: The InnoDB memory heap is disabled
-11-26 13:20:42 6036 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
-11-26 13:20:42 6036 [Note] InnoDB: Compressed tables use zlib 1.2.3
-11-26 13:20:42 6036 [Note] InnoDB: Not using CPU crc32 instructions
-11-26 13:20:42 6036 [Note] InnoDB: Initializing buffer pool, size = 128.0M
-11-26 13:20:42 6036 [Note] InnoDB: Completed initialization of buffer pool
-11-26 13:20:42 6036 [Note] InnoDB: Highest supported file format is Barracuda.
-11-26 13:20:42 6036 [Note] InnoDB: 128 rollback segment(s) are active.
-11-26 13:20:42 6036 [Note] InnoDB: Waiting for purge to start
-11-26 13:20:42 6036 [Note] InnoDB: 5.6.14 started; log sequence number 1600607
-11-26 13:20:42 6036 [Warning] InnoDB: Cannot open table mysql/innodb_table_stats from the internal data dictionary of InnoDB though the .frm file for the table exists. See /doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem.
ERROR: 1146 Table 'mysql.innodb_table_stats' doesn't exist
-11-26 13:20:42 6036 [ERROR] Aborting
-11-26 13:20:42 6036 [Note] Binlog end
-11-26 13:20:42 6036 [Note] InnoDB: FTS optimize thread exiting.
-11-26 13:20:42 6036 [Note] InnoDB: Starting shutdown...
-11-26 13:20:44 6036 [Note] InnoDB: Shutdown completed; log sequence number 1600617
-11-26 13:20:44 6036 [Note] ./bin/mysqld: Shutdown complete
初始化不了,,删除之前初始化的文件试试[root@pacteralinux data]# ll
total 12
drwx------. 2 mysql mysql 4096 Sep 25 12:27 mysql
drwx------. 2 mysql mysql 4096 Sep 25 12:27 performance_schema
drwxr-xr-x. 2 mysql mysql 4096 Sep 25 10:28 test
[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...-11-26 13:41:48 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
-11-26 13:41:48 6768 [Note] InnoDB: The InnoDB memory heap is disabled
-11-26 13:41:48 6768 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
-11-26 13:41:48 6768 [Note] InnoDB: Compressed tables use zlib 1.2.3
-11-26 13:41:48 6768 [Note] InnoDB: Not using CPU crc32 instructions
-11-26 13:41:48 6768 [Note] InnoDB: Initializing buffer pool, size = 128.0M
-11-26 13:41:48 6768 [Note] InnoDB: Completed initialization of buffer pool
-11-26 13:41:48 6768 [Note] InnoDB: Highest supported file format is Barracuda.
-11-26 13:41:48 6768 [Note] InnoDB: Log scan progressed past the checkpoint lsn 49463
-11-26 13:41:48 6768 [Note] InnoDB: Database was not shutdown normally!
-11-26 13:41:48 6768 [Note] InnoDB: Starting crash recovery.
-11-26 13:41:48 6768 [Note] InnoDB: Reading tablespace information from the .ibd files...
-11-26 13:41:48 6768 [Note] InnoDB: Restoring possible half-written data pages
-11-26 13:41:48 6768 [Note] InnoDB: from the doublewrite buffer...
InnoDB: Doing recovery: scanned up to log sequence number 1600617
-11-26 13:41:48 6768 [Note] InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
-11-26 13:41:48 6768 [Note] InnoDB: 128 rollback segment(s) are active.
-11-26 13:41:48 6768 [Note] InnoDB: Waiting for purge to start
-11-26 13:41:48 6768 [Note] InnoDB: 5.6.14 started; log sequence number 1600617
-11-26 13:41:54 6768 [Note] Binlog end
-11-26 13:41:54 6768 [Note] InnoDB: FTS optimize thread exiting.
-11-26 13:41:54 6768 [Note] InnoDB: Starting shutdown...
-11-26 13:41:55 6768 [Note] InnoDB: Shutdown completed; log sequence number 1625997
OK
Filling help tables...-11-26 13:41:55 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
-11-26 13:41:55 6793 [Note] InnoDB: The InnoDB memory heap is disabled
-11-26 13:41:55 6793 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
-11-26 13:41:55 6793 [Note] InnoDB: Compressed tables use zlib 1.2.3
-11-26 13:41:55 6793 [Note] InnoDB: Not using CPU crc32 instructions
-11-26 13:41:55 6793 [Note] InnoDB: Initializing buffer pool, size = 128.0M
-11-26 13:41:55 6793 [Note] InnoDB: Completed initialization of buffer pool
-11-26 13:41:55 6793 [Note] InnoDB: Highest supported file format is Barracuda.
-11-26 13:41:55 6793 [Note] InnoDB: 128 rollback segment(s) are active.
-11-26 13:41:55 6793 [Note] InnoDB: Waiting for purge to start
-11-26 13:41:55 6793 [Note] InnoDB: 5.6.14 started; log sequence number 1625997
-11-26 13:41:55 6793 [Note] Binlog end
-11-26 13:41:55 6793 [Note] InnoDB: FTS optimize thread exiting.
-11-26 13:41:55 6793 [Note] InnoDB: Starting shutdown...
-11-26 13:41:57 6793 [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 for your system
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, 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 for production servers.
See the manual for more instructions.
You can start the MySQL daemon with:
cd . ; /usr/local/mysql/bin/mysqld_safe &
You can test the MySQL daemon with mysql-test-run.pl
cd mysql-test ; perl mysql-test-run.pl
Please report any problems with the ./bin/mysqlbug script!
The latest information about MySQL is available on the web at
Support MySQL by buying support/licenses at
WARNING: Found existing config file /usr/local/mysql/f on the system.
Because this file might be in use, it was not replaced,
but was used in bootstrap (unless you used --defaults-file)
and when you later start the server.
The new default config file was created as /usr/local/mysql/my-f,
please compare it with your file and take the changes you need.
WARNING: Default config file /etc/f exists on the system
This file will be read by default by the MySQL server
If you do not want to use this, either remove it, or use the
--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/1 00:00:00 grep msyql
[root@pacteralinux mysql]# ps -ef|grep mysql
root 6838 1 0 13:42 pts/1 00: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/1 00: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/1 00:00:00 grep mysql
可以看到,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 f
-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 >mysqldb1126.sql #数据库比较大,大约半小时
复制到备份服务器:[root@uyhd000225 ~]# scp mysqldb1126.sql root@remoteIP:/mnt/backup/
root@remoteIP's password:
mysqldb1126.sql 3% 153MB 680.6KB/s 1:43:33 ETA
如果觉得《mysql重启后数据库消失_linux重启后mysql数据库文件全部丢失mysql无法启动》对你有帮助,请点赞、收藏,并留下你的观点哦!