how to create new user for phpmyadmin login

PHPMyAdmin and MySQL server complement each other very well. In most situations you have installed mysql server and phpmyadmin.

By default the mysql root password is blank and this is a big security issue and you have change the mysql password as soon as possible. If that is not done anybody could login with phpmyadmin with username root and password blank. You also must avoid to login phpmyadmin with mysql root password. Instead you must create a new user (to phpmyadmin) with same privileges as mysql root.

First change the mysql root password

mysql > update user set password=password('XXXXX') where user='root';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql > FLUSH PRIVILEGES;

Thats all! you have changed your mysql root password. All you have to do is login to phpmyadmin with username as root and password as new password.

Create a new phpmyadmin user

Now we will create a new administrator login (say sysadmin) for phpmyadmin which will have same privileges as mysql root.
Its just one line command;

mysql> GRANT ALL ON *.* TO 'sysadmin'@'localhost' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)

then

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

Now you can login to phpmyadmin as new administrator and avoid using root logins. You can create databases, change or insert or edit tables or do whatever you want!

Access MySQL Server From The Shell Prompt (Command Line)

How do I access MySQL server from the shell prompt (command line)?

MySQL software includes mysql client. It is a text-based client for mysqld, a SQL-based relational database server. It works interactive and non-interactive mode.

mysql Client Syntax

mysql -u {mysql-user} -p {mysql-password} -h {mysql-server}

Where,

• -u {mysql-user} : Specify MySQL user name. Use root only when connecting to local system.
• -p {mysql-password}: Specify password, Employ the specified password when connecting to the database server. If a password is not supplied, it will be requested interactively.
• -h {mysql-server}: Connect to the specified host (remote or local)

For example remote connect to MySQL server called mysql10.nixcraft.in and user vivek:

$ mysql -u root -h localhost -p

Sample outputs:

Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 4.1.15-Debian_1-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>

You can type an sql statement at mysql> prompt. In this example, you will list tables from the demo database, run;

USE demo;
SHOW TABLES;

Sample session:

mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 31855130
Server version: 5.0.77 Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use nqod;
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> show tables;
+----------------+
| Tables_in_nqod |
+----------------+
| quotes         |
| quotes_meta    |
+----------------+
2 rows in set (0.00 sec)
mysql> \q
Bye

After typing an SQL statement, end it with ";" and press [Enter] key. To exit type quit or \q:

quit

OR

q

Sample session:

 mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 31853999
Server version: 5.0.77 Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> quit
Bye

Batch Mode

You can execute SQL statements in a script file (batch file) as follows:

mysql database_name < input.script.sql > output.file
mysql -u user -p'password' database_name < input.script.sql > output.file

Recommend readings:

Type the following command to mysql command man page:
man mysql

Creating a new user phpmyadmin

Managing User Permissions

Within phpMyAdmin, user management is controlled via the Privileges link from the main page. Users can be created, edited, and removed.

Creating a new user

To create a new user, click the Add a new user link near the bottom of the Privileges page (you must be a "superuser", e.g., user "root"). Use the textboxes and drop-downs to configure the user to your particular needs. You can then select whether to create a database for that user and grant specific global privileges. Once you've created the user (by clicking Go), you can define that user's permissions on a specific database (don't grant global privileges in that case). In general, users do not need any global privileges (other than USAGE), only permissions for their specific database.

Editing an existing user

To edit an existing user, simply click the pencil icon () to the right of that user in the Privileges page. You can then edit their global- and database-specific privileges, change their password, or even copy those privileges to a new user.

Deleting a user

From the Privileges page, check the checkbox for the user you wish to remove, select whether or not to also remove any databases of the same name (if they exist), and click Go.

Assigning privileges to user for a specific database

Users are assigned to databases by editing the user record (from the Privileges link on the home page) not from within the Privileges link under the table. If you are creating a user specifically for a given table you will have to create the user first (with no global privileges) and then go back and edit that user to add the table and privileges for the individual table.

How to change the password for phpMyAdmin?

Can you run queries on your database? IF so you can do it using the built in Mysql commands.

SET PASSWORD FOR 'username'@'%' = PASSWORD('newpass');

You will have to change the username part (to probably pma) and the % part (to probably localhost).

So it becomes:

SET PASSWORD FOR 'pma'@'localhost' = PASSWORD('newpass');

For information on the SET PASSWORD command see: dev.mysql.com/doc/refman/5.0/en/set-password.html

To see exactly what user and domain (the localhost/% part) your account is using do:

SELECT User, Host FROM mysql.user;

That should show you all your mysql user accounts. Look for the one that looks like it's for PHPMyAdmin (probably pma).

PhpMyAdmin - Access denied for user root@localhost

In the PHP programming language, there is an open source tool called PhpMyAdmin that handles theadministration of MySQL. Access can be denied when access to MySQL is attempted through theroot user. Root requires insertion of a password and once that is wrongly entered or is not provided, this issue arises. This can be configured with the help of the PHP tool. The rights of the root user can also be reset at the same time. 

PhpMyAdmin - Access denied for user root@localhost

• To reset the rights of the root user
• Insert the host and password
• See also: Localhost/phpmyadmin

Beginners may face problems accessing MySql by the root user. This happens when the root requires you to enter a password and the user has either entered a the wrong one or has not provided one at all. This can be easily configured using the following steps:

• Step1: Open MySql
• Step2: Through phpMyAdmin/librarires, you should edit config.inc.php
  • Where $cfg['PmaAbsoluteUri'] = ''; is displayed, alter the command to $cfg['PmaAbsoluteUri'] = 'localhost/phpmyadmin/';
• Step 3: Concerning the server commands, change $cfg['Servers'][$i]['host'] = 'localhost'; to$cfg['Servers'][$i]['host'] = 'Localhost';
• Step 4: To enter your new password for the root user, find the command $cfg['Servers'][$i]['password'] = ''; and enter your password in the field provided
  • For example: $cfg['Servers'][$i]['password'] = 'type your password here';
• Step 5: Reboot MySql

To reset the rights of the root user

To be able to reset the rights of the root user, you should enter the following commands 
mysql> GRANT ALL PRIVILEGES ON *.* TO root@localhost IDENTIFIED BY ' your current password ' WITH GRANT 

Insert the host and password

This section will allow you to insert the host's name and user account by default and set them up with the correct password, as allocated to them in the php.ini folder. To do so, use the code below:

• Default host for mysql_connect() (doesn't apply in safe mode):

  • mysqli.default_host ='localhost'    

• Default user for mysql_connect() (doesn't apply in safe mode):

  • mysqli.default_user ='root'    

• Default password for mysqli_connect() (doesn't apply in safe mode):
  • Note: It is generally a bad idea to store passwords in this file as any user with PHP access can view it

  • mysqli.default_pw =' your current password '

How do I reset the administrator password?

Help, I forgot my password!

QUESTION:

How do I reset the administrator password?

ANSWER:

A publisher can reset any user’s password. Provided there’s at least one publisher who can log in, ask them to reset your password for you.

If you’re the only user with publisher privileges (or the only Textpattern user at all), you’ll need to access the database directly to reset your password. Most web hosting accounts provide direct MySQL access using a program called phpMyAdmin, or via the command line. If you’re not sure how to access either of these, ask your hosting company’s tech support.

With phpMyAdmin, or at the MySQL command prompt, run the following query:

update txp_users set pass=password(lower('pass')) where name='user';

..where pass is the new password, and user is the login username.

Forgot phpmyadmin root password or Reset the root password

How to recover phpmyadmin root password ?

For users of the powerful database MySQL, access, as well as right to modify the admin (root) password is important, because both are required when installing and in situations when the master password is lost. The user must be connected to the root password to modify it but for reinitialising the password, this process can be skipped. Resetting the password is possible after connection to theMySQL database with the help of specific commands. In case the password is lost, then it is also possible to get access to the MySQL server through a bypass of the authentication process. To reset the password under the latter condition, the server must be restarted to continue the process. 

[MySQL]Reset the root password

• The root password is lost
• Resetting the password
• See also: Phpmyadmin reset root password

It's important that you are able to access and modify the admin (root) password of MYSQL, either when installing MySQL for the first time or in situations where the master password is lost. 

The root password is lost

To modify the existing root password, you must firstly be connected to it. If you know the root password and just want to reinitialize it, you can skip this part. If you don't have the root password then follow the steps below:

• Stop the MySQL server

  • #/etc/init.d/mysql stop

• Restart MySQL, disabling network communication and skipping the authentication procedure

  • #mysqld --skip-grant-tables --skip-networking &

Resetting the password

• Get connected to the MySQL system database:

  • # mysql mysql -u root

• Type in the following command and reset your password:

  • UPDATE user SET password=PASSWORD('newpassword') WHERE user="root";

• If you didn't go through the first step, to validate the changes simply type:

  • FLUSH PRIVILEGES;

• If you started MySQL manually (bypassing the authentication process), the server must be restarted:

  • #/etc/init.d/mysql restart

Warning: Changing your password can cause connection problems between phpmyadmin and mysql.