MySQL, How to reset lost root password

Did you forget the root password of MySQL ? Here is how get it back.

The following steps tells you how to reset the root password in MySQL (this is specific to Linux environment, I understand the same process would also work in Windows)


1. Stop MySQL service:
# service mysqld stop
Stopping mysqld:                                           [  OK  ]

2. Start Mysql server with "--skip-grant-tables" option (user privileges table). You may optionally provide "--skip-networking" which would prohibit anyone from connecting the server from remote place;

Please note that, one need to start the server using "mysqld_safe" command. You need to send the process in the background by either pressing ctrl+z and send it to the background using "bg" or '&' at the end of the command;

# mysqld_safe --skip-grant-tables --skip-networking
140227 18:39:40 mysqld_safe Logging to '/var/log/mysqld.log'.
140227 18:39:40 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
^Z
[1]+  Stopped                 mysqld_safe --skip-grant-tables --skip-networking

3. Login to Mysql without password (this will not prompt you for password);

[root@Fedora-14 ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.60 Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

4. Update the password for "root" user with a new password;

mysql> use mysql;
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> update user set password=PASSWORD("root") where user="root";
Query OK, 0 rows affected (0.01 sec)
Rows matched: 3  Changed: 0  Warnings: 0

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

mysql> quit
Bye

5. Stop the MySQL server;

[root@Fedora-14 ~]# /etc/init.d/mysqld stop
Stopping mysqld:                                           [  OK  ]

6. Start back the MySQL server;

# service mysqld start
Starting mysqld:                                           [  OK  ]

7. Login to the server with root user and the new password;

# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.60 Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

Database replication an effective means of Backup !

What is Database replication?
Database replication is one of most effective means of Backup. This is more effective when you need to handle a huge volume of data in a big organization. It is an online process means the master database is backed up instantaneously as and when there are any changes to the master database. The process of Replication may work in chained fashion. Replication protects against hardware failures on one of the replicated databases but not against user stupidity or maliciousness! If a user deletes a number of records, this process will then be replicated onto the other replicated servers, making replication useless as a reliable means of backup.

How to achieve this? ( I described about MySQL Database only)
Here both the servers are of same versions i.e. version: 4.1.15

The Master server is located in the location
/home/bijit/database/mysql

To start the services:

mysqld_multi start 3
mysqld_multi start 4

To log into mysql:

/home/bijit/database/mysql/bin/mysql -u root -proot --port=3307 --socket=/home/bijit/database/mysql/config/mysql.sock

/home/bijit/database2/mysql/bin/mysql -u root -p --port=3308 --socket=/home/bijit/database2/mysql/var/mysql.sock

The slave server is located in the path
/home/bijit/database2/mysql
Steps:
On the master do the following:
1.
use database mysql;
GRANT REPLICATION SLAVE ON *.* TO replicator@localhost IDENTIFIED BY 'replicator';
FLUSH PRIVILEGES;
2.
Make a copy of tables and data i.e. the entire database of the Master server. In this case copy the data directory located in /home/bijit/database/mysql/var
3.
In the my.cnf of Master server, add the following entries;

log-bin
server-id=1
4. Login to maser mysql server, and note the bin-log file and its position as
mysql> show master status;
+----------------------+----------+--------------+------------------+
File Position Binlog_Do_DB Binlog_Ignore_DB
+----------------------+----------+--------------+------------------+
localhost-bin.000002 79
+----------------------+----------+--------------+------------------+
1 row in set (0.05 sec)

On the slave do the following:

1.
In the file my.cnf, add the following entries (under the slave servers entry),
master-host = localhost
master-user = replicator
master-password = replicator
master-port = 3307
server-id = 2

2. Start the slave server, create a user called replicator as created in the master;
mysql> use mysql;
mysql> grant all privileges on *.* to replicator@localhost identified by 'replicator';
mysql>flush privileges;
mysql> stop slave;
mysql> CHANGE MASTER TO MASTER_HOST='localhost',
MASTER_USER='root',
MASTER_PASSWORD='root',
MASTER_PORT=3307,
MASTER_LOG_FILE='localhost-bin.000002'
MASTER_LOG_POS=79
2.
Copy the data taken from the master onto the slave.

3. Login to slave server as mysql -u root -proot [This is now same as master]
Start the slave server as
mysql> start slave;
mysql> LOAD DATA FROM MASTER;

Now check both master and slave and you will see both servers are in sync.
MySql replication has begun !!!