Oh my goodness, I think the problem stems from mixing the mysql schema of different mysql versions. First of all, run this query:
desc mysql.user;
For MySQL 8.0, you get 51 columns
mysql> desc mysql.user;
Field |
Type |
Null |
Key |
Default |
Extra |
Host |
char(255) |
NO |
PRI |
|
|
User |
char(32) |
NO |
PRI |
|
|
Select_priv |
enum('N','Y') |
NO |
|
N |
|
Insert_priv |
enum('N','Y') |
NO |
|
N |
|
Update_priv |
enum('N','Y') |
NO |
|
N |
|
Delete_priv |
enum('N','Y') |
NO |
|
N |
|
Create_priv |
enum('N','Y') |
NO |
|
N |
|
Drop_priv |
enum('N','Y') |
NO |
|
N |
|
Reload_priv |
enum('N','Y') |
NO |
|
N |
|
Shutdown_priv |
enum('N','Y') |
NO |
|
N |
|
Process_priv |
enum('N','Y') |
NO |
|
N |
|
File_priv |
enum('N','Y') |
NO |
|
N |
|
Grant_priv |
enum('N','Y') |
NO |
|
N |
|
References_priv |
enum('N','Y') |
NO |
|
N |
|
Index_priv |
enum('N','Y') |
NO |
|
N |
|
Alter_priv |
enum('N','Y') |
NO |
|
N |
|
Show_db_priv |
enum('N','Y') |
NO |
|
N |
|
Super_priv |
enum('N','Y') |
NO |
|
N |
|
Create_tmp_table_priv |
enum('N','Y') |
NO |
|
N |
|
Lock_tables_priv |
enum('N','Y') |
NO |
|
N |
|
Execute_priv |
enum('N','Y') |
NO |
|
N |
|
Repl_slave_priv |
enum('N','Y') |
NO |
|
N |
|
Repl_client_priv |
enum('N','Y') |
NO |
|
N |
|
Create_view_priv |
enum('N','Y') |
NO |
|
N |
|
Show_view_priv |
enum('N','Y') |
NO |
|
N |
|
Create_routine_priv |
enum('N','Y') |
NO |
|
N |
|
Alter_routine_priv |
enum('N','Y') |
NO |
|
N |
|
Create_user_priv |
enum('N','Y') |
NO |
|
N |
|
Event_priv |
enum('N','Y') |
NO |
|
N |
|
Trigger_priv |
enum('N','Y') |
NO |
|
N |
|
Create_tablespace_priv |
enum('N','Y') |
NO |
|
N |
|
ssl_type |
enum('','ANY','X509','SPECIFIED') |
NO |
|
|
|
ssl_cipher |
blob |
NO |
|
NULL |
|
x509_issuer |
blob |
NO |
|
NULL |
|
x509_subject |
blob |
NO |
|
NULL |
|
max_questions |
int unsigned |
NO |
|
0 |
|
max_updates |
int unsigned |
NO |
|
0 |
|
max_connections |
int unsigned |
NO |
|
0 |
|
max_user_connections |
int unsigned |
NO |
|
0 |
|
plugin |
char(64) |
NO |
|
caching_sha2_password |
|
authentication_string |
text |
YES |
|
NULL |
|
password_expired |
enum('N','Y') |
NO |
|
N |
|
password_last_changed |
timestamp |
YES |
|
NULL |
|
password_lifetime |
smallint unsigned |
YES |
|
NULL |
|
account_locked |
enum('N','Y') |
NO |
|
N |
|
Create_role_priv |
enum('N','Y') |
NO |
|
N |
|
Drop_role_priv |
enum('N','Y') |
NO |
|
N |
|
Password_reuse_history |
smallint unsigned |
YES |
|
NULL |
|
Password_reuse_time |
smallint unsigned |
YES |
|
NULL |
|
Password_require_current |
enum('N','Y') |
YES |
|
NULL |
|
User_attributes |
json |
YES |
|
NULL |
|
51 rows in set (0.00 sec)
For MySQL 5.6, you get 43 columns
mysql> desc mysql.user;
+------------------------+-----------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Password | char(41) | NO | | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| Create_tablespace_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) unsigned | NO | | 0 | |
| plugin | char(64) | YES | | | |
| authentication_string | text | YES | | NULL | |
| password_expired | enum('N','Y') | NO | | N | |
+------------------------+-----------------------------------+------+-----+---------+-------+
43 rows in set (0.15 sec)
For MySQL 5.5, you get 42 columns
mysql> desc mysql.user;
+------------------------+-----------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Password | char(41) | NO | | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| Create_tablespace_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) unsigned | NO | | 0 | |
| plugin | char(64) | YES | | | |
| authentication_string | text | YES | | NULL | |
+------------------------+-----------------------------------+------+-----+---------+-------+
42 rows in set (0.01 sec)
mysql>
For MySQL 5.1, you get 39 columns
mysql> desc mysql.user;
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Password | char(41) | NO | | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) unsigned | NO | | 0 | |
+-----------------------+-----------------------------------+------+-----+---------+-------+
39 rows in set (0.00 sec)
mysql>
For MySQL 5.0, you get 37 columns
mysql> desc mysql.user;
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Password | char(41) | NO | | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) unsigned | NO | | 0 | |
+-----------------------+-----------------------------------+------+-----+---------+-------+
37 rows in set (0.01 sec)
mysql>
For MySQL 4.x, you get 31 columns
mysql> desc mysql.user;
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Host | varchar(60) binary | | PRI | | |
| User | varchar(16) binary | | PRI | | |
| Password | varchar(16) binary | | | | |
| Select_priv | enum('N','Y') | | | N | |
| Insert_priv | enum('N','Y') | | | N | |
| Update_priv | enum('N','Y') | | | N | |
| Delete_priv | enum('N','Y') | | | N | |
| Create_priv | enum('N','Y') | | | N | |
| Drop_priv | enum('N','Y') | | | N | |
| Reload_priv | enum('N','Y') | | | N | |
| Shutdown_priv | enum('N','Y') | | | N | |
| Process_priv | enum('N','Y') | | | N | |
| File_priv | enum('N','Y') | | | N | |
| Grant_priv | enum('N','Y') | | | N | |
| References_priv | enum('N','Y') | | | N | |
| Index_priv | enum('N','Y') | | | N | |
| Alter_priv | enum('N','Y') | | | N | |
| Show_db_priv | enum('N','Y') | | | N | |
| Super_priv | enum('N','Y') | | | N | |
| Create_tmp_table_priv | enum('N','Y') | | | N | |
| Lock_tables_priv | enum('N','Y') | | | N | |
| Execute_priv | enum('N','Y') | | | N | |
| Repl_slave_priv | enum('N','Y') | | | N | |
| Repl_client_priv | enum('N','Y') | | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | | | | |
| ssl_cipher | blob | | | | |
| x509_issuer | blob | | | | |
| x509_subject | blob | | | | |
| max_questions | int(11) unsigned | | | 0 | |
| max_updates | int(11) unsigned | | | 0 | |
| max_connections | int(11) unsigned | | | 0 | |
+-----------------------+-----------------------------------+------+-----+---------+-------+
31 rows in set (0.01 sec)
Picture this scenario:
- You install 5.0
- You uninstall 5.0
- You forgot to move /var/lib/mysql out of the way
- You install 5.1
The user permissions start getting shifted. Whenever you run SHOW GRANTS;
, it is hardwired to expect columns in specfic places in mysql.user (given the fact that mysql.user is a MyISAM and its ROW_FORMAT is Dynamic (Default)). It is very easy to see a user suddenly lose permissions when you reload a MySQL 5.0 version of mysql.user into a MySQL 5.1 instance.
In the future, should you ever decide to reload users into MySQL, try to dump the users to a text file using pt-show-grants rather than mysqldump.
I actually wrote my own version of pt-show-grants as follows:
mysql -hhostaddr -umyuserid -pmypassword --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql -hhostaddr -umyuserid -pmypassword --skip-column-names -A | sed 's/$/;/g' > MySQLUserGrants.sql
This will produce all user grants as SQL, which is far more portable than standard mysql techniques. Simply reload the SQL script and the GRANTS are executed. Of course, such reloading is only forward compatible, never backward.
Best Answer
When you're installing MySQL you're asked to enter the
root
password that you want to set for the database. In the status code you've listed above you're logging in ascharles@localhost
and notroot
. That's the reason why it's not asking for the password you set during installation.If you're logging in to MySQL using the
root
account then you can do all the things you want.Simply type:
and it'll ask you for the root password. Now you're "in" the system with full access. You can then create databases using:
If you have a user that you want to give access to this new database you can type:
When this is done you should be able to login as charles and also have full access to the newly created database.