如何恢复/重新创建 mysql 的默认“mysql”数据库

How to recover/recreate mysql's default 'mysql' database

提问by user784637

When I installed mysql it came with two database, mysql and information schema. I accidentally deleted the mysql database. Is there any way I can recreate it?

当我安装 mysql 时，它带有两个数据库，mysql 和信息模式。我不小心删除了mysql数据库。有什么办法可以重新创建它吗？

Also, since it contains a table that contains user information, would there be any way I can view users' information without it?

另外，由于它包含一个包含用户信息的表，如果没有它，我有什么方法可以查看用户的信息吗？

回答by Michael Berkowski

If you are still able to log in (I assume you aren't since there's no user table) and have databases to save, dump them with

如果您仍然能够登录（我假设您不能登录，因为没有用户表）并且有要保存的数据库，请将它们转储

mysqldump --routines databasename > outfile.sql

The MySQL database can be recreated with the command

可以使用以下命令重新创建 MySQL 数据库

# Most MySQL versions
mysql_install_db

# MySQL 5.7 and later
mysqld --initialize

MySQL Documentation here

MySQL 文档在这里

回答by hihu

read more at: https://dev.mysql.com/doc/refman/5.7/en/data-directory-initialization.html

阅读更多信息：https: //dev.mysql.com/doc/refman/5.7/en/data-directory-initialization.html

On Windows, use one of these commands:

在 Windows 上，使用以下命令之一：

C:\> bin\mysqld --initialize
C:\> bin\mysqld --initialize-insecure

回答by Sina

I recently installed mysql 8 and it seemed that there was a problem with mysql default databases. Anyway, this worked for me :

我最近安装了mysql 8，似乎mysql默认数据库有问题。无论如何，这对我有用：

mysql_upgrade -u root -p

回答by Andrew Zhilin

I removed database mysql by accident and mysqld --initializedidnt helped me. No errors showed, even mysql service started OK, but folder mysql wasnt created and mysql wasnt usable - I wasnt able even to create new databases.

我不小心删除了数据库 mysql 并且mysqld --initialize没有帮助我。没有显示错误，即使 mysql 服务启动正常，但没有创建文件夹 mysql 并且 mysql 不可用 - 我什至无法创建新数据库。

After spending a lot of time on trying different options I noticed, that I have deprecated option innodb_additional_mem_pool_size=20M, which crashed mysqld --initialize

在花了很多时间尝试不同的选项后，我注意到，我已经弃用了选项 innodb_additional_mem_pool_size=20M，这会导致 mysqld --initialize 崩溃

After fixing mysql config - it worked well.

修复 mysql 配置后 - 它运行良好。

回答by andrew lorien

I had a similar problem : during a backup-and-restore operation we deleted the mysql and information_schema databases. After restoring our own database, we couldn't create users (or do anything really) because those system tables were not restored. (of course we would never do that in a production environment in a company either heh).

我有一个类似的问题：在备份和恢复操作期间，我们删除了 mysql 和 information_schema 数据库。恢复我们自己的数据库后，我们无法创建用户（或真正做任何事情），因为那些系统表没有恢复。（当然，我们也绝不会在公司的生产环境中这样做，呵呵）。

If you still have a copy of the old mysql_install_db (as we did), don't use it. Since MySQL 5.6.8it's a perl script. Takes similar options to mysqld_safe. worked well for us - ran the InnoDB recovery on our restored db, installed the system databases, got our slave server back into action.

如果您仍然拥有旧 mysql_install_db 的副本（就像我们所做的那样），请不要使用它。从MySQL 5.6.8 开始，它是一个 perl 脚本。采用与 mysqld_safe 类似的选项。对我们来说效果很好 - 在我们恢复的数据库上运行 InnoDB 恢复，安装系统数据库，让我们的从服务器恢复运行。

回答by lama12345

I had this error:

我有这个错误：

root@ubuntu-2gb-nbg1-1 ~ # mysqld --initialize
2019-01-23T15:11:22.428139Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2019-01-23T15:11:22.435428Z 0 [ERROR] --initialize specified but the data directory has files in it. Aborting.
2019-01-23T15:11:22.435653Z 0 [ERROR] Aborting

Solution (this will delete all your databases and tables, make a backup!):

解决方案（这将删除您所有的数据库和表，请进行备份！）：

/etc/init.d/mysql stop
rm /var/lib/mysql -rf
mkdir /var/lib/mysql
chown mysql:mysql /var/lib/mysql
mysqld --initialize

Now add skip-grant-tablesin your /etc/mysql/mysql.conf.d/mysqld.cnf, because the root password is not valid, so we need to login somehow and set a new one.

现在添加skip-grant-tables您的/etc/mysql/mysql.conf.d/mysqld.cnf，因为root密码无效，所以我们需要以某种方式登录并设置一个新密码。

/etc/init.d/mysql start
mysql
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> use mysql;

Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| engine_cost               |
| event                     |
| func                      |
| general_log               |
| gtid_executed             |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| server_cost               |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
31 rows in set (0.00 sec)

So now you have a new mysql database with user table, but the password is "messed up". So you need to set a valid password:

所以现在你有了一个带有用户表的新 mysql 数据库，但是密码“搞砸了”。所以你需要设置一个有效的密码：

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('somepass');
FLUSH PRIVILEGES;
ctrl+d

Now remove the skip-grant-tablesfrom your config, otherwise your setup is insecure.

现在skip-grant-tables从您的配置中删除，否则您的设置是不安全的。

Now you can login "normally": mysql -u root -p(enter somepass)

现在您可以“正常”登录：（mysql -u root -p输入somepass）