How to Recover MySQL Database root password

By default, MySQL Server will be installed with root superuser without any password. You can connect to MySQL server as root without requiring password or by keying in blank password. However, if you have set the password for root and forget or unable to recall the password, then you will need to reset the root password for MySQL.



Login as root to the Unix-like (Unix, Linux or BSD) machine with the MySQL server.

Stop the MySQL server by using either of the following command

#/etc/init.d/mysql stop

Now you need to Start MySQL server without password

# mysqld_safe --skip-grant-tables &

Connect to mysql server using mysql client with the following command

# mysql -u root

Now you should be having mysql prompt

mysql>

Now you need to Setup new MySQL root user password

mysql> use mysql;


mysql> update user set password=PASSWORD(“newrootpassword”) where user=’root’;


mysql> flush privileges;


mysql> quit



Note: Replace newrootpassword with the new root password for MySQL server. Flush Privileges is needed to making the password change effect immediately.



Now you need to Stop MySQL Server using the following command

# /etc/init.d/mysql stop

Test Your New Mysql root password

First you need to start mysql server using the following command

# /etc/init.d/mysql start


# mysql -u root -p


Now it will prompt for root password and enter your new root password

Read More

MySQL Database Server Installation and configuration

MySQL is a fast, stable and true multi-user, multi-threaded SQL database server. SQL (Structured Query Language) is the most popular database query language in the world. The main goals of MySQL are speed, robustness and ease of use.




Mysql Database Installation in Debian


If you want to install the Mysql base system as well as a textual client run the following command from your shell

#apt-get install mysql-server-4.1 mysql-client-4.1


Now that MySQL is installed, you may want to know how to configure it.

Configuring Mysql Database

We assume that mysqladmin and mysql, which should have been installed when you got the MySQL packages. First, if you haven’t done this already, set the root password for MySQL. You can do this by typing:


#mysqladmin -u root password ‘passwordyouwant’

Now that the root password is set, connect to your MySQL server:

#mysql -u root -p

It will prompt you for a password. Make sure to enter the one you just/previously set. You should now be left at a prompt which looks like this:


mysql>

At this point, you will create basic permissions for a user and database. For my setup, I want to allow access to localhost to all databases, and a computer which is also on the network, which is referred to as “windowsbox” will have access to all databases.

To access the user, host databases, etc… type this;

mysql> use mysql;

Database changed

mysql>

To give localhost permission to access all databases, enter this:

mysql> insert into
-> host(host,db,Select_priv, Insert_priv, Update_priv,
-> Delete_priv, Create_priv, Drop_priv)
-> values(‘localhost’,'%’,'Y’,'Y’,'Y’,'Y’,'Y’,'Y’);


Note, the ‘%’ can be replaced with a database name. The ‘%’ is a wildcard.

Following the previous format, to allow access from another hostname (in this case “windowsbox”) add this:

mysql> insert into
-> host(host,db,Select_priv, Insert_priv, Update_priv,
-> Delete_priv, Create_priv, Drop_priv)
-> values(‘windowsbox’,'%’,'Y’,'Y’,'Y’,'Y’,'Y’,'Y’);


Again, ‘%’ is used as a Wild-Card.

To create a user ‘djg’ who can access the MySQL server from localhost, type this:


mysql> insert into
-> user (host, user, password)
-> values(‘localhost’,'djg’,password(‘mypassword’));



To give the user access from another hostname, domain, etc… add other entries accordingly. For example,to give user djg access from windowsbox:


mysql> insert into
-> user (host, user, password)
-> values(‘windowsbox’,'djg’,password(‘mypassword’));


Now… to give the user permissions to access a database from localhost, add this entry and change with your appropriate information:


mysql> insert into
-> db (host,db,user,Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv)
-> values (‘localhost’,'mydatabase’,'djg’,'Y’,'Y’,'Y’,'Y’,'Y’,'Y’);


To give the user permissions from windowsbox, add this:

mysql> insert into
-> db (host,db,user,Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv)
-> values (‘windowsbox’,'mydatabase’,'djg’,'Y’,'Y’,'Y’,'Y’,'Y’,'Y’);


Now, type: quit and you will exit mysql.

Finally, create the actual database (in this case, ‘mydatabase’) type this:

#mysqladmin -u root -p create mydatabase

After prompting you for a password, it should create the database. At this point, you must reload MySQL. Type:

#mysqladmin -u root -p reload

After prompting you for a password it should reload MySQL.


Congratulations. If all goes well you have set up a user and database with MySQL. You may now create /edit/delete/etc tables as much as you’d like.

Also, please note that by default, MySQL will open up network port 3306 to allow remote requests.

If you do not want this port open, append “--skip-networking” when running safe_mysqld to start
the daemon. Debian users can edit /etc/init.d/mysqld and change this line:

/usr/bin/safe_mysqld > /www.null 2>&1 &

to this:

/usr/bin/safe_mysqld --skip-networking > /www.null 2>&1 &

Now whenever running /etc/init.d/mysql start, it will not open up port 3306.

If you want o install mysql database in ubuntu you can use the same procedure.

Read More

Mysql Database Server Installation and Configuration in Ubuntu

MySQL is a widely used and fast SQL database server. It is a client/server implementation that consists of a server daemon (mysqld) and many different client programs/libraries.



Installing Mysql database in Ubuntu


#apt-get install mysql-server mysql-client libmysqlclient12-dev


MySQL initially only allows connections from the localhost (127.0.0.1). We’ll need to remove that restriction if you wish to make it accessible to everyone on the internet. Open the file /etc/mysql/my.cnf

#vi /etc/mysql/my.cnf
Find the line bind-address = 127.0.0.1 and comment it out
…
#bind-address = 127.0.0.1
…

You can check your configuration using the following command

#netstat -tap


Output Looks like below


tcp 0 0 *:mysql *:* LISTEN 4997/mysqld



MySQL comes with no root password as default. This is a huge security risk. You’ll need to set one. So that the local computer gets root access as well, you’ll need to set a password for that too. The local-machine-name is the name of the computer you’re working on. For more information see here

#mysqladmin -u root password your-new-password


#mysqladmin -h root@local-machine-name -u root -p password your-new-password


#/etc/init.d/mysql restart

Read More