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

Question

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?

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

Pallas

I think you've just put yourself in need of reinstallation of MySQL server. – Madalinemadalyn

Same question, MySQL 5.1, mysqld returns an error. Reinstalling via yum reinstall mysql mysql-server succeeds but does not restore the db. – Odelsting

Answer 1

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

# Most MySQL versions
mysql_install_db

# MySQL 5.7 and later
mysqld --initialize

MySQL Documentation here

Avaavadavat

I was still able to log in. Worked like a charm, thank you kind sir. – Pallas

@LedZeppelin That's great, and surprising. I was sure you'd be dead in the water! – Avaavadavat

Not really, I was just practicing some mysql stuff, I would never do that in a production environment in a company heh. – Pallas

Just a note for future users, it looks like you can continue to log in as long as the server hasn't been restarted. – Furriery

I have a corrupted 5.6 database (on windows 64bit), mysql.proc was damaged by an import. Now I am trying to blow away the whole DB, and recreate. removing and installing didn not help - db still there. On windows, there is no mysql_install_db in mysql bin dir, unfortunately. – Caryl

@JohnLittle Maybe this post on dba.stackexchange.com will be of help – Avaavadavat

If you can't login you can start MySQL with the "skip-security" feature that allows unauthenticated login. To be used with "skip-networking". Just in case... – Faugh

To clarify: The new mysql instance will be empty? – Marpet

@Marpet Yes, this procedure is to recreate it from scratch. It will not restore from a previous backup dump. If you have one, you must restore it after recreating the mysql database, (which means default functionality and permissions have been restored) – Avaavadavat

The --initialize option doesn't exist prior to 5.7.mysqld silently accepts it but complains in error logs – Triplicity

@Triplicity - someone edited that in to replace mysql_install_db. I'll clarify it. – Avaavadavat

Answer 2

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

On Windows, use one of these commands:

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

Coelenteron

Thanks for this post. Unfortunately my mysqld gives me "/usr/sbin/mysqld: unknown option '--initialize'". Which is mystifying - it should be fairly new; it's the newest available with Cygwin. – Kimberleekimberley

Answer 3

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

mysql_upgrade -u root -p

Aleishaalejandra

I got: The mysql_upgrade client is now deprecated. The actions executed by the upgrade client are now done by the server. To upgrade, please start the new MySQL binary with the older data directory. Repairing user tables is done automatically. Restart is not required after upgrade. The upgrade process automatically starts on running a new MySQL binary with an older data directory. To avoid accidental upgrades, please use the --upgrade=NONE option with the MySQL binary. The option --upgrade=FORCE is also provided to run the server upgrade sequence on demand. – Wrightson

Answer 4

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-tables in 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.

/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:

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

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

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

Corsair

mysqld --initialize didn't work for me, I used mysql_install_db instead. – Propulsion

Answer 5

I removed database mysql by accident and mysqld --initialize didnt 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.

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

After fixing mysql config - it worked well.

Coriolanus

Answer 6

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).

If you still have a copy of the old mysql_install_db (as we did), don't use it. Since MySQL 5.6.8 it'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.

Tribal

Answer 7

On macOS

OS: macOS Monterey

Chip: Apple M1

Solution

Your file paths can be different, please review it!

1. Stop mysql service: brew services stop mysql

2. Remove Mysql data directory: rm -r /opt/homebrew/var/mysql

3. Create a new Mysql data directory: mkdir /opt/homebrew/var/mysql

4. Initialize Mysql data directory: mysqld --initialize

5. Add skip-grant-tables to the file /opt/homebrew/etc/my.cnf to allow connect to Mysql server without password.

6. Start Mysql service: brew services start mysql

7. Run Mysql client: mysql

8. On Mysql client run the following commands:

   use mysql; # switch to mysql database

   FLUSH PRIVILEGES;

   SET PASSWORD FOR 'root'@'localhost' = PASSWORD('<new-password>');

   FLUSH PRIVILEGES;

9. Remove skip-grant-tables from the file /opt/homebrew/etc/my.cnf

10. Then restart the service: brew services restart mysql

11. Login into Mysql client with your new password: mysql -u root -p

Command Summary

brew services stop mysql
rm -r /opt/homebrew/var/mysql
mkdir /opt/homebrew/var/mysql
mysqld --initialize

# Add skip-grant-tables to /opt/homebrew/etc/my.cnf
brew services start mysql
mysql

# Run on mysql: use mysql; # switch to mysql database
# Run on mysql: FLUSH PRIVILEGES;
# Run on mysql: SET PASSWORD FOR 'root'@'localhost' = PASSWORD('<new-password>');
# Run on mysql: FLUSH PRIVILEGES;
# Run on mysql: exit;
# Remove `skip-grant-tables` from `/opt/homebrew/etc/my.cnf`

brew services restart mysql
mysql -u root -p

Stave

Answer 8

I run MariaDB on Windows 10 and I had some errors about "Incorrect definition of table mysql.column_stats". So I decided it's time to re-create my MariaDB database:

1. backup/export your data; make notes of users and their assigned privileges
2. stop the database service: net stop mysql
3. cd c:\Program Files\MariaDB 10.8\bin
4. mariadb-install-db.exe --datadir=C:\some\dirs\data\ --password ...
5. don't forget to start the database service again: net start mysql
6. import your data back
7. re-create users and re-assign privileges

Gooseberry

Answer 9

After upgrading from mysql 5.5 to 5.7, and backing up database with percona's xtrabackup, my default mysql database was corrupted on copying over.. made clean install, then copied all default db's to .sql file and then imported them back over, after manually deleting the /var/lib/mysql/mysql database dir.. worked.

mysqldump -u root -p --all-databases > alldb.sql
Look up the documentation for mysqldump. You may want to use some of the options mentioned in comments:

mysqldump -u root -p --opt --all-databases > alldb.sql

Import:
mysql -u root -p < alldb.sql```

Mcandrew