« Posts tagged MySQL

Resetting a forgotten MySQL root password

There is a simple solution to this problem, we just need to start MySQL with a flag to tell it to ignore any username/password restrictions which might be in place. Once that is done you can successfully update the stored details.

First of all you will need to ensure that your database is stopped:

# /etc/init.d/mysql stop

Now you should start up the database in the background, via the mysqld_safe command:

# /usr/bin/mysqld_safe –skip-grant-tables &

Now that the server is running with the --skip-grant-tables flag you can connect to it without a password and complete the job:

# mysql –user=root mysql

Now to reset the root password:

mysql> update user set Password=PASSWORD(‘new-password-here’) WHERE User=’root’;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0


mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)


mysql> exit

Now that you’ve done that you just need to stop the server, so that you can go back to running a secure MySQL server with password restrictions in place. First of all bring the server you started into the foreground by typing “fg“, then kill it by pressing “Ctrl+c” afterwards.

This will now allow you to start the server:

# /etc/init.d/mysql start
Starting MySQL database server: mysqld.
Checking for corrupt, not cleanly closed and upgrade needing tables..

Now everything should be done and you should have regained access to your MySQL database(s); you should verify this by connecting with your new password:

# mysql -h localhost -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 5.0.24a-Debian_4-log

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql> exit

Debian Administration

Creating new user and new MySQL Database

If you want to create a new user example with a database that they have control over, we will run the following commands.
Note: That ‘mysql>’ is the command prompt for the client program MySQL.

Connect to the local database server as user root. You will be prompted for a password.

# mysql -h localhost -u root -p

Now we see the ‘mysql>’ prompt and we can run the following to create a new database for Example.

mysql> create database exampledb;
Query OK, 1 row affected (0.00 sec)

Now we create the user Example and give him/her permissions on the new database

mysql> grant DROP,CREATE,INSERT,DELETE,UPDATE,SELECT on exampledb.* to example@localhost;
Query OK, 0 rows affected (0.00 sec)

Next we set a password for this new user

mysql> set password for example = password(‘mysecretpassword’);
Query OK, 0 rows affected (0.00 sec)


mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)


mysql> exit;

Once all this has done you have created a new user with complete control over the contents of the database exampledb – the user can create new tables, delete/drop them and generally use the database.
Note: The new user will have no other access to the server, only to the database that you gave them permissions to.

Debian Administration