记载一次删除mysql的默认数据库mysql的过程

  1. 云栖社区>
  2. 博客>
  3. 正文

记载一次删除mysql的默认数据库mysql的过程

bboysoul 2017-10-14 16:08:00 浏览1039
展开阅读全文

概述

今天终于尝到了什么叫删库跑路,妈的真的悲剧,早上想在树莓派里安装个wordpress,想放点街舞视频给大家分享一下,突然发现数据库里面还有一个以前的论坛数据库,我想删了把,反正没用,所以
drop database mysql
开始我还没用发现,突然想到,wordpress安装要新建一个数据库的,于是登录数据库里面去,突然发现,我擦里面这个forum数据库怎么还在?我刚才删了什么东西?
翻了执行的命令的记录,,
我日你妈了个狗逼,内心犹如千万只草拟马在奔腾,我靠这怎么办,幸好,我有谷歌

操作

第一种方法

我先把我解决的方法写上来,过程谷歌了一早上
我现在是已经把数据库重新建立好了的,为了操作的真实性,我再执行一次
drop database mysql

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bboysoul           |
| forum              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

没错现在mysql这个数据库已经不在了
之后进入到你数据库的data文件夹内
cd /usr/local/mysql/var

  var ls
bboysoul     ib_logfile1       mysql-bin.000012  mysql-bin.000015  mysql-bin.000018  mysql-bin.000021  mysql-bin.000024  mysql-bin.000027  mysql-bin.index     raspberrypi.pid
forum        ibdata1           mysql-bin.000013  mysql-bin.000016  mysql-bin.000019  mysql-bin.000022  mysql-bin.000025  mysql-bin.000028  performance_schema  sock
ib_logfile0  mysql-bin.000011  mysql-bin.000014  mysql-bin.000017  mysql-bin.000020  mysql-bin.000023  mysql-bin.000026  mysql-bin.000029  raspberrypi.err     test

没错的确没有mysql这个文件夹了,接着把数据库进程全部关了

  var sudo service mysql stop
  var ps aux | grep mysql
root      5778  0.0  0.2   5600  2640 ?        Ss   13:32   0:00 SCREEN -S mysql
root     21674  0.0  0.1   4248  1644 pts/0    S+   15:35   0:00 grep --color=auto --exclude-dir=.bzr --exclude-dir=CVS --exclude-dir=.git --exclude-dir=.hg --exclude-dir=.svn mysql

上面这两个进程虽然有mysql的名字,但是和mysql是没有任何关系的

之后恢复这个mysql数据库有两个方法,一种是复制其他相同版本的mysql数据库的mysql文件夹打包到本地解压,之后你不用建立新的用户,你只要用复制过来的那个数据库的密码登录就好,但是你得注意复制过来的文件夹的权限问题,还有如果出现下面这个问题
The server quit without updating PID file (/usr/local/mysql/var/raspberrypi.pid). ... failed!
可能是你的目录权限的问题,导致不能创建这个pid文件,所以你要把/usr/local/mysql/var/这个文件夹的所有者改为mysql的用户,最好里面的文件所有者全是mysql的用户。
我先在服务器上打包好了mysql数据库的文件夹,现在下载下来
scp root@vultr.bboysoul.com:/usr/local/mysql/var/mysql.zip ./
解压
unzip mysql.zip
启动mysql服务

  var sudo service mysql start
Job for mysql.service failed. See 'systemctl status mysql.service' and 'journalctl -xn' for details.
  var systemctl status mysql.service
● mysql.service - LSB: start and stop MySQL
   Loaded: loaded (/etc/init.d/mysql)
   Active: failed (Result: exit-code) since Sat 2017-10-14 15:45:33 CST; 2s ago
  Process: 21643 ExecStop=/etc/init.d/mysql stop (code=exited, status=0/SUCCESS)
  Process: 21779 ExecStart=/etc/init.d/mysql start (code=exited, status=1/FAILURE)

Oct 14 15:45:30 raspberrypi mysql[21779]: Starting MySQL
Oct 14 15:45:33 raspberrypi mysql[21779]: ...The server quit without updating PID file (/usr/local/mysql/var/raspberrypi.pid). ... failed!
Oct 14 15:45:33 raspberrypi systemd[1]: mysql.service: control process exited, code=exited status=1
Oct 14 15:45:33 raspberrypi systemd[1]: Failed to start LSB: start and stop MySQL.
Oct 14 15:45:33 raspberrypi systemd[1]: Unit mysql.service entered failed state.
  var 

没错,报错了,现在下面修改权限

  var ls -alh
total 34M
drwxr-sr-x  7 mysql mysql 4.0K Oct 14 15:44 .
drwxr-sr-x 14 root  mysql 4.0K Oct  1 17:14 ..
drwx--S---  2 mysql mysql 4.0K Oct 14 11:45 bboysoul
drwx--S---  2 mysql mysql 4.0K Oct 13 17:12 forum
-rw-rw----  1 mysql mysql 5.0M Oct 14 15:35 ib_logfile0
-rw-rw----  1 mysql mysql 5.0M Oct  1 17:14 ib_logfile1
-rw-rw----  1 mysql mysql  18M Oct 14 15:35 ibdata1
drwx--S---  2 root  mysql 4.0K Sep 14 15:03 mysql
-rw-rw----  1 mysql mysql  126 Oct 10 18:04 mysql-bin.000011
-rw-rw----  1 mysql mysql  126 Oct 11 12:21 mysql-bin.000012
-rw-rw----  1 mysql mysql  126 Oct 12 18:32 mysql-bin.000013
-rw-rw----  1 mysql mysql  107 Oct 12 18:32 mysql-bin.000014
-rw-rw----  1 mysql mysql  80K Oct 14 11:54 mysql-bin.000015
-rw-rw----  1 mysql mysql  107 Oct 14 11:54 mysql-bin.000016
-rw-rw----  1 mysql mysql  107 Oct 14 12:06 mysql-bin.000017
-rw-rw----  1 mysql mysql  28K Oct 14 12:09 mysql-bin.000018
-rw-rw----  1 mysql mysql 1.1M Oct 14 12:09 mysql-bin.000019
-rw-rw----  1 mysql mysql  28K Oct 14 12:17 mysql-bin.000020
-rw-rw----  1 mysql mysql 1.1M Oct 14 12:17 mysql-bin.000021
-rw-rw----  1 mysql mysql  28K Oct 14 12:27 mysql-bin.000022
-rw-rw----  1 mysql mysql 1.1M Oct 14 12:27 mysql-bin.000023
-rw-rw----  1 mysql mysql  18K Oct 14 12:36 mysql-bin.000024
-rw-rw----  1 mysql mysql  28K Oct 14 12:36 mysql-bin.000025
-rw-rw----  1 mysql mysql 1.1M Oct 14 12:36 mysql-bin.000026
-rw-rw----  1 mysql mysql  28K Oct 14 12:53 mysql-bin.000027
-rw-rw----  1 mysql mysql 1.1M Oct 14 12:53 mysql-bin.000028
-rw-rw----  1 mysql mysql  209 Oct 14 15:35 mysql-bin.000029
-rw-rw----  1 mysql mysql   19 Oct 14 15:20 mysql-bin.index
-rw-r--r--  1 root  mysql 197K Oct 14 15:43 mysql.zip
drwx--S---  2 mysql mysql 4.0K Oct 14 12:53 performance_schema
-rw-rw----  1 mysql mysql 1.3K Oct 14 15:35 raspberrypi.err
-rw-r--r--  1 mysql mysql  15K Oct 14 12:47 sock
drwx--S---  2 mysql mysql 4.0K Oct 14 12:09 test

chown -Rf mysql:mysql mysql
之后再次启动服务
sudo service mysql start
成功

第二种方法

第一种方法是有局限性的,因为你要一个其他的数据库,那个数据库的版本最好是和你现在的数据库一样的,如果没有怎么办呢,那就自己建立一个呗,现在我还是登录到数据库里面去,把mysql这个数据库删除了,妈的删这个还他妈的删上瘾了,用的密码当然是服务器上的数据库的那个密码

  var mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.56-log Source distribution

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bboysoul           |
| forum              |
| mysql              |
| performance_schema |
| test               |
+--------------------+
6 rows in set (0.00 sec)

mysql> drop database mysql;
Query OK, 24 rows affected, 2 warnings (0.04 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bboysoul           |
| forum              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> 

之后老规矩把服务停了
sudo service mysql stop
接着使用下面这个mysql自带的工具新建这个mysql数据库

  var /usr/local/mysql/scripts/mysql_install_db --user=root --basedir=/usr/local/mysql --datadir=/usr/local/mysql/var
Installing MySQL system tables...
171014 15:52:32 [Note] Ignoring --secure-file-priv value as server is running with --bootstrap.
171014 15:52:32 [Note] /usr/local/mysql/bin/mysqld (mysqld 5.5.56-log) starting as process 22992 ...
OK
Filling help tables...
171014 15:52:32 [Note] Ignoring --secure-file-priv value as server is running with --bootstrap.
171014 15:52:32 [Note] /usr/local/mysql/bin/mysqld (mysqld 5.5.56-log) starting as process 22998 ...
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 raspberrypi 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 ; /usr/local/mysql/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd /usr/local/mysql/mysql-test ; perl mysql-test-run.pl

Please report any problems at http://bugs.mysql.com/

注意,你这样新建完成之后,要把root用户的数据库密码设置好才可以再次登录
下面设置密码
同样的,把var这个文件夹的权限修改了
chown -Rf mysql:mysql var
为什么上面该mysql这个目录就够了,下面要该var这个目录呢,因为执行上面这个命令后,var目录里面部分文件夹的所有者会改变,如果只该mysql这个目录,那么还会报错。
接着启动服务sudo service mysql start
修改密码
/usr/local/mysql/bin/mysqladmin -u root password 'woyaoxuehuilinux'
之后用这个密码登录

  mysql mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.5.56-log Source distribution

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

成功

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bboysoul           |
| forum              |
| mysql              |
| performance_schema |
| test               |
+--------------------+
6 rows in set (0.00 sec)

现在终于可以把forum这个数据库删除了

mysql> drop database forum;
Query OK, 23 rows affected (0.47 sec)

欢迎关注Bboysoul的博客www.bboysoul.com
Have Fun

网友评论

登录后评论
0/500
评论