Sometimes you forget your Magento admin username, password or even registered email for Magento admin user, how do we create another admin user without access to Magento backend?
In this case, Forgot password function of Magento will not work as you forgot admin email, we will need to do something with Magento database.
Step 1
Go to your PHPmyadmin tool in hosting cpanel
Next, login to PHPmyadmin with mysql username and password you are using for Magento site, if you forgot this, go to app/etc/local.xml and find these line
|
1 2 3 |
<host><![CDATA[localhost]]></host> <username><![CDATA[root]]></username> <password><![CDATA[password]]></password |
In this example, “root” is mysql username and “password” is mysql password. Now use these details to login to PHPmyadmin
Step 2
In phpmyadmin, select the database you are using for your site, in this example: magento
Select SQL tab and execute the following SQL command
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
LOCK TABLES `admin_role` WRITE , `admin_user` WRITE; SET @SALT = "ls"; SET @PASS = CONCAT(MD5(CONCAT( @SALT , "adminpassword") ), CONCAT(":", @SALT )); SELECT @EXTRA := MAX(extra) FROM admin_user WHERE extra IS NOT NULL; INSERT INTO `admin_user` (firstname,lastname,email,username,password,created,lognum,reload_acl_flag,is_active,extra,rp_token_created_at) VALUES ('AdminFirstname','AdminLastname','admin@email.com','adminusername',@PASS,NOW(),0,0,1,@EXTRA,NOW()); INSERT INTO `admin_role` (parent_id,tree_level,sort_order,role_type,user_id,role_name) VALUES (1,2,0,'U',(SELECT user_id FROM admin_user WHERE username = 'adminusername'),'AdminFirstname'); UNLOCK TABLES; |
The values you need to replace here are:
- adminpassword: your desired password, contains at least 1 digit, for example: Magentoexplorer1
- AdminFirstname: first name
- AdminLastname: last name
- admin@email.com: admin email for retrieving password
- adminusername: your admin username
Now the Sql command should be like this
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
LOCK TABLES `admin_role` WRITE , `admin_user` WRITE; SET @SALT = "ls"; SET @PASS = CONCAT(MD5(CONCAT( @SALT , "Magento123456@") ), CONCAT(":", @SALT )); SELECT @EXTRA := MAX(extra) FROM admin_user WHERE extra IS NOT NULL; INSERT INTO `admin_user` (firstname,lastname,email,username,password,created,lognum,reload_acl_flag,is_active,extra,rp_token_created_at) VALUES ('Brian','John','brian@magentoexplorer.com','brian',@PASS,NOW(),0,0,1,@EXTRA,NOW()); INSERT INTO `admin_role` (parent_id,tree_level,sort_order,role_type,user_id,role_name) VALUES (1,2,0,'U',(SELECT user_id FROM admin_user WHERE username = 'brian'),'Brian'); UNLOCK TABLES; |
Put this command to SQL and click on go
After the query is executed, you will see this result message
This mean you succeeded to add new Magento admin user
Running SQL command via Magento CLI
If you have shell access to your VPS/server, you can use the following command:
|
1 2 3 4 5 6 7 |
-bash-4.1# mysql -u root -p123456# mysql> use magento //Reading table information for completion of table and column names //You can turn off this feature to get a quicker startup with -A //Database changed mysql> |
Next, execute this command line by line (replace values same as PHPmyadmin)
|
1 2 3 4 5 6 7 8 9 10 |
LOCK TABLES `admin_role` WRITE , `admin_user` WRITE; SET @SALT = "ls"; SET @PASS = CONCAT(MD5(CONCAT( @SALT , "adminpassword") ), CONCAT(":", @SALT )); SELECT @EXTRA := MAX(extra) FROM admin_user WHERE extra IS NOT NULL; INSERT INTO `admin_user` (firstname,lastname,email,username,password,created,lognum,reload_acl_flag,is_active,extra,rp_token_created_at) VALUES (1,2,0,'U',(SELECT user_id FROM admin_user WHERE username = 'adminusername'),'AdminFirstname'); UNLOCK TABLES; |
Step 3
Go to yourmagentosite.com/admin and use the newly created admin account
In this example:
- Username: brian
- Password: Magento123456@