Troubleshooting MySQL Replication

Here are the tools required to diagnose replication problems along with a few suggestions about how and when to use each:

SHOW MASTER STATUS and SHOW SLAVE STATUS
These SQL commands are your primary tool for diagnosing replication problems. Along with the SHOW PROCESSLIST command, you should execute these commands on the master and then on the slave, then examine the output. The slave command has an extended set of parameters that are invaluable in diagnosing replication problems.

SHOW GRANTS FOR <replication user>
Whenever you encounter slave user access problems, you should first examine the grants for the slave user to ensure they have not changed.

CHANGE MASTER
Sometimes the configuration files have been changed either knowingly or accidentally. Use this SQL command to override the last known connection parameters and to diagnose slave connection problems.

STOP/START SLAVE
Use these SQL commands to start and stop replication. It is sometimes a good idea to stop a slave if it is in an error state.

Examine the configuration files
Sometimes the problem occurs as a result of an unsanctioned or forgotten configuration change. Check your configuration files routinely when diagnosing connection problems.

Examine the server logs
You should make this a habit whenever diagnosing problems. Checking the server logs can sometimes reveal errors that are not visible elsewhere. As cryptic as they can sometimes be, the error and warning messages can be helpful.

SHOW SLAVE HOSTS
Use this command to identify the connected slaves on the master if they use the report-host option.

SHOW PROCESSLIST
When encountering problems, it is always a good idea to see what else is running. This command will tell you the current state of each of the threads involved in replication. Check here first when examining the problem.

SHOW BINLOG EVENTS
This SQL command displays the events in the binary log. If you use statement-based replication, this command will display the changes using SQL statements.

mysqlbinlog
This utility allows you to read events in the binary or relay logs, often indicating when there are corrupt events. Don’t hesitate to use this tool frequently when diagnosing problems related to events and the binary log.

PURGE BINARY LOGS
This SQL command allows you to remove certain events from the binary log, such as those that occur after a specific time or after a given event ID. Your routine maintenance plan should include the use of this command for purging older binary logs that are no longer needed.

Simple Example to create a MySQL database and set privileges to a user

On a default settings, mysql root user do not need a password to authenticate from localhost. In this case, ou can login as root on your mysql server using:

$ mysql -u root

If a password is required, use the extra switch -p:

$ mysql -u root -p
Enter password:

Now that you are logged in, we create a database:

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

We allow user amarokuser to connect to the server from localhost using the password amarokpasswd:

mysql> grant usage on *.* to drupaluser@localhost identified by 'drupalpasswd';
Query OK, 0 rows affected (0.00 sec) 

And finally we grant all privileges on the amarok database to this user:

mysql> grant all privileges on drupaldb.* to drupaluser@localhost ;
Query OK, 0 rows affected (0.00 sec) 

And that's it. You can now check that you can connect to the MySQL server using this command:

$ mysql -u drupaluser -p'drupalpasswd' drupaldb

How to Recover MySQL Root Password

Do you want to recover the MySQL root password. its by no means, easy. But its quite simple if you follow the procedure. You will have to follow this step-by-step processes.

 
Here are the commands you need to type for each step (log in as the root user):
Step 1 : Stop the MySQL service:

# /etc/init.d/mysql stop

Output:

    Stopping MySQL database server: mysqld.

Step 2: Start the MySQL server w/o password:

# mysqld_safe --skip-grant-tables &

Output:

    [1] 5988
    Starting mysqld daemon with databases from /var/lib/mysql
    mysqld_safe[6025]: started

Step 3: Connect to the MySQL server using the MySQL client:

# mysql -u root

Output:

    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 1 to server version: 4.1.15-Debian_1-log

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

    mysql>

Step 4: Set a new MySQL root user password:

mysql> use mysql;
mysql> update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root';
mysql> flush privileges;
mysql> quit
Step 5: Stop the MySQL server:

# /etc/init.d/mysql stop

Output:

    Stopping MySQL database server: mysqld
    STOPPING server from pid file /var/run/mysqld/mysqld.pid
    mysqld_safe[6186]: ended

    [1]+  Done                    mysqld_safe –skip-grant-tables

Now Start the MySQL server and test it:

# /etc/init.d/mysql start
# mysql -u root -p