0

I found my mysql.user table is messed up and use drop table if exists mysql.user to drop it, how can I create this special table manually and add my new account to it.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
djy
  • 737
  • 6
  • 14
  • Can you at least show us the structure of your user table ? – Arrabidas92 Nov 19 '17 at 15:51
  • 3
    Dropping a system table such as `mysql.user` is a bad idea. I would suggest that you re-install the database. – Gordon Linoff Nov 19 '17 at 15:52
  • @Arrabidas92 I thought it would be easy to recreate this table before I check the MySQL doc and found it is much complicated(has much columns). My mysql.user table has gone away. I realized that’s a bad idea. – djy Nov 19 '17 at 16:29

2 Answers2

2

NB I have never tried this so I have no idea if it will work at all. YOU HAVE BEEN WARNED!!

Before you do anything, shut down the MySQL service and take a backup of the data folder (copy it elsewhere).

Next, either try creating a new data directory and run mysql_install_db (from the MySQL install directory, scripts/mysql_install_db --datadir=<new data directory>), or use a virtual machine and do the same. Whatever you do, do NOT install into the original folder. Use the same version of MySQL to do this

Whichever method you use, take the resultant files from <mysql data folder>/data (they should be called user.MYD, user MYI and user.frm) and copy them into your broken folder. Ensure that user rights are the same as the rest of the folder (owner and group should be read/write).

Restart your MySQL service and see if you can get into the system (no login information will be required, but you will have to log in to MySQL as root. If you can, recreate the relevant users for application access. If not, you still have a backup of the data folder.

In the event that it doesn't work, and assuming that you have recent backups, re-create the data folder completely using mysql_install_db (ensuring that you still have the copy of the folder you took before you started out), create the users, then restore your individual databases one by one from your last good backup. Do not restore the mysql database and tables - they will be built as you restore the other databases.

Try all this out on a virtual machine first - it'll help you find any problems along the way

Good luck

DaveyBoy
  • 2,928
  • 2
  • 17
  • 27
  • 1
    Great answer - the only thing I would do before shutting down (if the OP hasn't already) is use [pt-show-grants](https://www.percona.com/doc/percona-toolkit/LATEST/pt-show-grants.html) to dump the current users and passwords and privileges, so he can restore them after copying the mysql.user table. But once you shut down mysqld, the in-memory grants are lost. – Bill Karwin Nov 19 '17 at 18:37
2

Thanks to @DaveyBoy 's answer. I operated like below on ubuntu 16.04, mysql 5.7.20, and got mysql.user table back:

sudo mysqld --initialize --datadir=~/data_tmp

sudo cp ~/data_tmp/mysql/user.frm /var/lib/mysql/mysql
sudo cp ~/data_tmp/mysql/user.MYD /var/lib/mysql/mysql
sudo cp ~/data_tmp/mysql/user.MYI /var/lib/mysql/mysql

sudo chown mysql:mysql /var/lib/mysql/mysql/user.frm
sudo chown mysql:mysql /var/lib/mysql/mysql/user.MYD
sudo chown mysql:mysql /var/lib/mysql/mysql/user.MYI

In mysql cli select * from mysql.user, table mysql.user should show up now.

djy
  • 737
  • 6
  • 14