I'm in the process of importing data manually from a plesk install into ISPConfig3. I just imported my mysql databases and locked myself out of mysql, and resetting the mysql root password doesn't seem to to work. I'm on ubuntu 14.04, using the tutorials on this site to build the setup. The problem seems to be that on the previous server, root could not login from localhost, so that grant was never setup. When you skip the grant tables, it won't let you modify them (duh). So loading mysql when skipping them, allows you to change the password, but you still don't have permission to login as root from localhost. I can login as a custom account called admin, but it has no grant permissions. Anyone have an idea of how to modify the grant table without knowing how to login as root? Thanks much!
Please see here: http://www.faqforge.com/linux/controlpanels/ispconfig2/how-to-reset-the-mysql-root-password/
Thanks Till, I've done that, but it does no good. Root does not have access to login from localhost, and I don't know how to see who has grant access. In fact, if I select * from `user`; I don't see root listed at all, but it may be that this admin account can't see it?
When you start mysql with "mysqld_safe –skip-grant-tables &" then there is no authentication or permission checking at all. So you can do evrything in every database on this server. If there is no root user in your server, then just add one as you can do evrything in the db, without limits.
I've already tried that Till, I get the following: mysql> GRANT ALL PRIVILEGES ON *.* TO root@localhost IDENTIFIED BY 'password'; ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement I don't know if Plesk deleted the root account on the old system or the previous administrator, but this is nuts.
Thanks Till, I didn't know the syntax for that insert, so while googling it, I came across this method for changing the root password: SET PASSWORD FOR 'root'@'localhost' = PASSWORD('cleartext password'); Which worked. I thought I had a permissions problem. I can login as root now, but it doesn't have grant permissions. I'm afraid I've hosed up my database and I'm not even done with the migration yet. edit... I've tried the following: mysql> UPDATE mysql.user SET Grant_priv='Y', Super_priv='Y' WHERE User='root'; Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0 But as you can see, it says Changed is 0, not sure where I'm going wrong with this. This will teach me to logout of mysql login before testing my root password (when importing databases, including mysql database).
I haven't solved it yet, but I have more info and learned a few things. My plesk setup is on mysql 5.1, my ispconfig is 5.5. Newer versions of mysql seem to add new columns to the user table. When I imported my full mysqldump database onto my ispconfig, it broke the mysql.user tables. Sigh, fun times!
There might be even more tings broken if you overwrte the ysql setup this way. Why havent you just imported the website databases or at least skipped everything in the "mysql" database? Did you made a backup before you overwrote the mysql 5.5 setup so that you can restore that?
Since I'm still in build mode for the new site, I hadn't done any mysql backups, I know, bad admin and lesson learned. I needed all of the users created for the various databases, thats why I imported all databases. Lesson learned, ugh. I may have to archive the databases, rebuild mysql and figure out a way for mysql to reload the databases, then migrate users in another fashion.
Here are the 3 mysql root user lines from a mysql 5.5 database (debian): Code: INSERT INTO `user` (`Host`, `User`, `Password`, `Select_priv`, `Insert_priv`, `Update_priv`, `Delete_priv`, `Create_priv`, `Drop_priv`, `Reload_priv`, `Shutdown_priv`, `Process_priv`, `File_priv`, `Grant_priv`, `References_priv`, `Index_priv`, `Alter_priv`, `Show_db_priv`, `Super_priv`, `Create_tmp_table_priv`, `Lock_tables_priv`, `Execute_priv`, `Repl_slave_priv`, `Repl_client_priv`, `Create_view_priv`, `Show_view_priv`, `Create_routine_priv`, `Alter_routine_priv`, `Create_user_priv`, `Event_priv`, `Trigger_priv`, `Create_tablespace_priv`, `ssl_type`, `ssl_cipher`, `x509_issuer`, `x509_subject`, `max_questions`, `max_updates`, `max_connections`, `max_user_connections`, `plugin`, `authentication_string`) VALUES ('localhost', 'root', '*5172022923C5A97E5A842DA249B93473314416D5', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', '', '', '', '', 0, 0, 0, 0, '', NULL), ('server1.example.com', 'root', '*5172022923C5A97E5A842DA249B93473314416D5', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', '', '', '', '', 0, 0, 0, 0, '', NULL), ('127.0.0.1', 'root', '*5172022923C5A97E5A842DA249B93473314416D5', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', '', '', '', '', 0, 0, 0, 0, '', NULL); the password is "howtoforge".
Thanks Till. That confirms I goofed up my user table. ERROR 1054 (42S22): Unknown column 'Create_tablespace_priv' in 'field list'. Sadly, I think it's time to rebuild mysql and go back through and fix all issues.
