Introduction : –

MySQL Replication allows databases on one MySQL server to be duplicated on another, were introduced in MySQL 3.23.15.

For a description of the syntax of replication-related SQL statements,I suggest that you visit Web site at http://www.mysql.com often and read updates to this chapter. Replication is constantly being improved, and updates the manual frequently with the most current information.

MySQL support for one-way replication. One server acts as the master, while one or more other servers act as slaves. The master server writes updates to its binary log files, and maintains an index of the files to keep track of log rotation. These logs serve as a record of updates to be sent to slave servers. When a slave server connects to the master server, it informs the master of its last position within the logs since the last successfully propagated update. The slave catches up any updates that have occurred since then, and then blocks and waits for the master to notify it of new updates.

A slave server can also serve as a master if you want to set up chained replication servers.

Note that when you are using replication, all updates to the tables that are replicated should be performed on the master server. Otherwise, you must always be careful to avoid conflicts between updates that users make to tables on the master and updates that they make to tables on the slave.

1] One-way replication has benefits for robustness, speed, and system administration –

* Robustness is increased with a master/slave setup. In the event of problems with the master, you can switch to the slave as a backup.
* Better response time for clients can be achieved by splitting the load for processing client queries between the master and slave servers.
* SELECT queries may be sent to the slave to reduce the query processing load of the master. Statements that modify data should still be sent to the master so that the master and slave do not get out of sync. This load-balancing strategy is effective if non-updating queries dominate, but that is the normal case.
* Another benefit of using replication is that you can perform backups using a slave server without disturbing the master. The master continues to process updates while the backup is being made.

2] Replication Implementation Overview –

MySQL replication is based on the master server keeping track of all changes to your databases (updates, deletes, and so on) in the binary logs. Therefore, to use replication, you must enable binary logging on the master server. Each slave server receives binary log from the master. Master saves updates which master has recorded in its binary log, so that the slave can execute the same updates on its copy of the data.

It is very important to realize that the binary log is simply a record starting from the fixed point in time at which you enable binary logging. Any slaves that you set up will need copies of the databases on your master as they existed at the moment you enabled binary logging on the master. If you start your slaves with databases that are not the same as what was on the master when the binary log was started, your slaves may fail.

One way to copy the master’s data to the slave is to use the LOAD DATA FROM MASTER statement. Be aware that LOAD DATA FROM MASTER is available only as of MySQL 4.0.0 and currently works only if all the tables on the master are MyISAM type. Also, this statement acquires a global read lock, so no updates on the master are possible while the tables are being transferred to the slave. When we implement lock-free hot table backup (in MySQL 5.0), this global read lock will no longer be necessary.

Due to these limitations, we recommend that at this point you use LOAD DATA FROM MASTER only if the dataset on the master is relatively small, or if a prolonged read lock on the master is acceptable. While the actual speed of LOAD DATA FROM MASTER may vary from system to system, a good rule of thumb for how long it will take is 1 second per 1MB of data. That is only a rough estimate, but you should get close to it if both master and slave are equivalent to 700MHz Pentium performance and are connected through a 100MBit/s network.

After the slave has been set up with a copy of the master’s data, it will simply connect to the master and wait for updates to process. If the master goes away or the slave loses connectivity with your master, it will keep trying to connect periodically until it is able to reconnect and resume listening for updates. The retry interval is controlled by the –master-connect-retry option. The default is 60 seconds.

Each slave keeps track of where it left off. The master server has no knowledge of how many slaves there are or which ones are up to date at any given time.

3] Replication Implementation Details –

MySQL replication capabilities are implemented using three threads (one on the master server and two on the slave). When START SLAVE is issued, the slave creates an I/O thread. The I/O thread connects to the master and asks it to send the statements recorded in its binary logs. The master creates a thread to send the binary log contents to the slave. This thread can be identified as the Binlog Dump thread in the output of SHOW PROCESSLIST on the master. The slave I/O thread reads what the master Binlog Dump thread sends and simply copies it to some local files in the slave’s data directory called relay logs. The third thread is the SQL thread, which the slave creates to read the relay logs and execute the updates they contain.

In the preceding description, there are three threads per slave. For a master that has multiple slaves, it creates one thread for each currently connected slave, and each slave has its own I/O and SQL threads.

For versions of MySQL before 4.0.2, replication involves only two threads (one on the master and one on the slave). The slave I/O and SQL threads are combined as a single thread, and no relay log files are used.

The advantage of using two slave threads is that statement reading and execution are separated into two independent tasks. The task of reading statements is not slowed down if statement execution is slow. For example, if the slave server has not been running for a while, its I/O thread can quickly fetch all the binary log contents from the master when the slave starts, even if the SQL thread lags far behind and may take hours to catch up. If the slave stops before the SQL thread has executed all the fetched statements, the I/O thread has at least fetched everything so that a safe copy of the statements is locally stored in the slave’s relay logs for execution when next the slave starts. This allows the binary logs to be purged on the master, because it no longer need wait for the slave to fetch their contents.

The SHOW PROCESSLIST statement provides information that tells you what is happening on the master and on the slave regarding replication.

MySQL Master — 192.168.0.8

[root@testserver ~]# cat /etc/my.cnf
[mysqld]
set-variable = max_connections=500
safe-show-database
#log = /var/log/mysql/mysql.log

############################################
##MySQL replication setting

server-id = 1
log-bin = /var/log/mysql/binary/mysql-bin.log
binlog-do-db = student
binlog-ignore-db = mysql
log = /var/log/mysql/mysql.log

[root@testserver ~]#

[root@testserver ~]# mkdir /var/log/mysql/binary/
[root@testserver ~]# ll -d /var/log/mysql/binary/
drwxr-xr-x 2 root root 4096 Jan 30 15:30 /var/log/mysql/binary/
[root@testserver ~]# ll -d /var/log/mysql/
drwxr-xr-x 3 mysql mysql 4096 Jan 30 15:30 /var/log/mysql/
[root@testserver ~]# chown mysql:mysql -R /var/log/mysql/
binary/ mysql.log
[root@testserver ~]# chown mysql:mysql -R /var/log/mysql/binary/
[root@testserver ~]#

[root@testserver ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 950
Server version: 5.0.45-community-log MySQL Community Edition (GPL)

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

mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
+——————–+
9 rows in set (0.00 sec)

mysql> create database student;
Query OK, 1 row affected (0.03 sec)

mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
| student |
+——————–+
10 rows in set (0.00 sec)

mysql> use student;
Database changed
mysql>

mysql> create table class ( rollno INT(5) NOT NULL PRIMARY KEY AUTO_INCREMENT , name VARCHAR(30) );
Query OK, 0 rows affected (0.32 sec)

mysql> DESC class;
+——–+————-+——+—–+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+——–+————-+——+—–+———+—————-+
| rollno | int(5) | NO | PRI | NULL | auto_increment |
| name | varchar(30) | YES | | NULL | |
+——–+————-+——+—–+———+—————-+
2 rows in set (0.03 sec)

mysql>

mysql> INSERT INTO class VALUES (”, ‘Arun Bagul’);
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql>
mysql> SELECT * FROM class;
+——–+————+
| rollno | name |
+——–+————+
| 1 | Arun Bagul |
+——–+————+
1 row in set (0.00 sec)

mysql> INSERT INTO class VALUES (”, ‘Suhail Thakur’);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> INSERT INTO class VALUES (”, ‘Bhavesh Vala’);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> SELECT * FROM class;
+——–+—————+
| rollno | name |
+——–+—————+
| 1 | Arun Bagul |
| 2 | Suhail Thakur |
| 3 | Bhavesh Vala |
+——–+—————+
3 rows in set (0.00 sec)

mysql>
[root@testserver ~]#

[root@testserver ~]# /etc/init.d/mysql restart
Shutting down MySQL… [ OK ]
Starting MySQL [ OK ]
[root@testserver ~]# /etc/init.d/mysql restart
[root@testserver ~]# cd /var/log/mysql/binary/mysql-bin.
mysql-bin.000001 mysql-bin.index
[root@testserver ~]# cd /var/log/mysql/binary/
[root@testserver binary]# ls
mysql-bin.000001 mysql-bin.index
[root@testserver binary]#

[root@testserver binary]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.0.45-community-log MySQL Community Edition (GPL)

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

mysql>
mysql> use student;
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_student |
+——————-+
| class |
+——————-+
1 row in set (0.00 sec)

mysql> SELECT * FROM class;
+——–+—————+
| rollno | name |
+——–+—————+
| 1 | Arun Bagul |
| 2 | Suhail Thakur |
| 3 | Bhavesh Vala |
+——–+—————+
3 rows in set (0.00 sec)

mysql> INSERT INTO class VALUES (”, ‘Nishit Shah’);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> SELECT * FROM class;
+——–+—————+
| rollno | name |
+——–+—————+
| 1 | Arun Bagul |
| 2 | Suhail Thakur |
| 3 | Bhavesh Vala |
| 4 | Nishit Shah |
+——–+—————+
4 rows in set (0.00 sec)

mysql>

4] Create user on master server which has permission to read binary logs –

mysql> GRANT REPLICATION SLAVE ON *.* TO ‘replication’@’192.168.0.%’ IDENTIFIED BY ‘mypwd’;
Query OK, 0 rows affected (0.01 sec)

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

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

mysql> quit
Bye
[root@testserver binary]#

[root@testserver binary]# mysql -u root -p
Enter password:
\Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 340
Server version: 5.0.45-community-log MySQL Community Edition (GPL)

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

mysql> SHOW MASTER STATUS;
+——————+———-+—————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+—————–+——————+
| mysql-bin.000002 | 98 | student,student | mysql,mysql |
+——————+———-+—————–+——————+
1 row in set (0.02 sec)

mysql>

mysql> SHOW PROCESSLIST;
+—-+——-+———–+——-+———+——+————+——————————————————————————————————+
| Id | User | Host | db | Command | Time | State | Info |
+—-+——-+———–+——-+———+——+————+——————————————————————————————————+
| 31 | root | localhost | NULL | Query | 0 | NULL | SHOW PROCESSLIST |
| 41 | horde | localhost | horde | Sleep | 0 | | NULL |
| 42 | horde | localhost | horde | Query | 1 | statistics | SELECT session_data FROM horde_sessionhandler WHERE session_id = ‘f70a7d64bd353917679814813a513c8f’ |
| 43 | horde | localhost | horde | Query | 1 | statistics | SELECT session_data FROM horde_sessionhandler WHERE session_id = ‘f70a7d64bd353917679814813a513c8f’ |
+—-+——-+———–+——-+———+——+————+——————————————————————————————————+
4 rows in set (0.00 sec)

mysql> SHOW PROCESSLIST\G;
*************************** 1. row ***************************
Id: 31
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: NULL
Info: SHOW PROCESSLIST
1 row in set (0.00 sec)

ERROR:
No query specified

mysql>

=========================================================

MySQL Slave — 192.168.0.191

5] Edit my.cnf file of replication slave and create DB and upload the dump of DB –

[root@arun ~]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

########################################
##slave setting
server-id=4
master-port=3306
master-host=192.168.0.8
master-user=replication
master-password=mypwd
master-connect-retry=60
replicate-do-db=student

#slave setting end

[mysql.server]
user=mysql
#basedir=/var/lib

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[root@arun ~]#

[root@arun ~]# mysqladmin create student -u root -p
Enter password:
[root@arun ~]# mysql -u root -p student < /root/arun.sql
Enter password:
[root@arun ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1020
Server version: 5.0.45-community MySQL Community Edition (GPL)

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

mysql> SHOW DATABASES;
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
| student |
+——————–+
12 rows in set (0.01 sec)

mysql> USE student;
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_student |
+——————-+
| class |
+——————-+
1 row in set (0.00 sec)

mysql> SELECT * FROM class;
+——–+—————+
| rollno | name |
+——–+—————+
| 1 | Arun Bagul |
| 2 | Suhail Thakur |
| 3 | Bhavesh Vala |
| 4 | Nishit Shah |
| 5 | Yogesh Nikam |
| 6 | Deven Jadhav |
+——–+—————+
6 rows in set (0.00 sec)

mysql> quit
Bye
[root@arun ~]# /etc/init.d/mysql restart
Shutting down MySQL. [ OK ]
Starting MySQL [ OK ]
[root@arun ~]#

6] Now login to Master (192.168.0.8) and check Master status —

[root@testserver ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1599
Server version: 5.0.45-community-log MySQL Community Edition (GPL)

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

mysql> SHOW MASTER STATUS;
+——————+———-+—————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+—————–+——————+
| mysql-bin.000001 | 98 | student,student | mysql,mysql |
+——————+———-+—————–+——————+
1 row in set (0.00 sec)

mysql>

mysql> USE student;
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_student |
+——————-+
| class |
+——————-+
1 row in set (0.01 sec)

mysql> SELECT * FROM class;
+——–+—————+
| rollno | name |
+——–+—————+
| 1 | Arun Bagul |
| 2 | Suhail Thakur |
| 3 | Bhavesh Vala |
| 4 | Nishit Shah |
| 5 | Yogesh Nikam |
| 6 | Deven Jadhav |
+——–+—————+
6 rows in set (0.06 sec)

mysql> quit
Bye
[root@testserver ~]#

7] Now login to Slave ie 192.168.0.191 in my case –

[root@arun ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.0.45-community MySQL Community Edition (GPL)

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

mysql> SHOW SLAVE STATUS;
+—————-+—————+——————–+——————–+——————–+—————–+——————-+—————-+———————–+
| Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master |
+—————-+—————+——————–+——————–+——————–+—————–+——————-+—————-+———————–+
| Waiting for master to send event | 192.168.0.8 | replication | 3306 | 60 | mysql-bin.000001 | 98 | arun-relay-bin.000002 | 235 | mysql-bin.000001 | Yes | Yes | student,student | | | | | | 0 | | 0 | 98 | 235 | None | | 0 | No | | | | | | 0 |
+—————-+—————+——————–+——————–+——————–+—————–+——————-+—————-+———————–+
1 row in set (0.00 sec)

mysql>

8] Now login to Master Server (192.168.0.8)and check processlist –
mysql> SHOW MASTER STATUS;
+——————+———-+—————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+—————–+——————+
| mysql-bin.000001 | 98 | student,student | mysql,mysql |
+——————+———-+—————–+——————+
1 row in set (0.01 sec)

mysql> SHOW PROCESSLIST;
+——+————-+———————+———–+————-+——+—————————————————————-+——————+
| Id | User | Host | db | Command | Time | State | Info |
+——+————-+———————+———–+————-+——+—————————————————————-+——————+
| 570 | eximstats | localhost | eximstats | Sleep | 401 | | NULL |
| 1578 | replication | 192.168.0.191:38843 | NULL | Binlog Dump | 494 | Has sent all binlog to slave; waiting for binlog to be updated | NULL |
| 1633 | root | localhost | NULL | Query | 0 | NULL | SHOW PROCESSLIST |
+——+————-+———————+———–+————-+——+—————————————————————-+——————+
3 rows in set (0.00 sec)

mysql>

mysql> SHOW PROCESSLIST\G;
*************************** 1. row ***************************
Id: 570
User: eximstats
Host: localhost
db: eximstats
Command: Sleep
Time: 508
State:
Info: NULL
*************************** 2. row ***************************
Id: 1578
User: replication
Host: 192.168.0.191:38843
db: NULL
Command: Binlog Dump
Time: 601
State: Has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
*************************** 3. row ***************************
Id: 1633
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: NULL
Info: SHOW PROCESSLIST
3 rows in set (0.00 sec)

ERROR:
No query specified

mysql>

9] Now login to Slave ie (192.168.0.191) and check the processlist –

mysql> SHOW PROCESSLIST;
+—-+————-+———–+——+———+——+———————————————————————–+——————+
| Id | User | Host | db | Command | Time | State | Info |
+—-+————-+———–+——+———+——+———————————————————————–+——————+
| 1 | system user | | NULL | Connect | 685 | Waiting for master to send event | NULL |
| 2 | system user | | NULL | Connect | 685 | Has read all relay log; waiting for the slave I/O thread to update it | NULL |
| 5 | root | localhost | NULL | Query | 0 | NULL | SHOW PROCESSLIST |
+—-+————-+———–+——+———+——+———————————————————————–+——————+
3 rows in set (0.00 sec)

mysql> SHOW PROCESSLIST\G;
*************************** 1. row ***************************
Id: 1
User: system user
Host:
db: NULL
Command: Connect
Time: 690
State: Waiting for master to send event
Info: NULL
*************************** 2. row ***************************
Id: 2
User: system user
Host:
db: NULL
Command: Connect
Time: 690
State: Has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
*************************** 3. row ***************************
Id: 5
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: NULL
Info: SHOW PROCESSLIST
3 rows in set (0.00 sec)

ERROR:
No query specified

mysql>

10] How to reset Binary log position on the slave –

mysql> CHANGE MASTER TO MASTER_HOST=’192.168.0.8′,MASTER_USER=’replication’,MASTER_PASSWORD=’mypwd’, MASTER_LOG_FILE=’mysql-bin.000004′,MASTER_LOG_POS=412;
Query OK, 0 rows affected (0.01 sec)

11] Now login to Master (192.168.0.8) and add records in “class” table of “student” DB –

[root@testserver ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1642
Server version: 5.0.45-community-log MySQL Community Edition (GPL)

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

mysql> USE student;
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_student |
+——————-+
| class |
+——————-+
1 row in set (0.00 sec)

mysql> SELECT * FROM class;
+——–+—————+
| rollno | name |
+——–+—————+
| 1 | Arun Bagul |
| 2 | Suhail Thakur |
| 3 | Bhavesh Vala |
| 4 | Nishit Shah |
| 5 | Yogesh Nikam |
| 6 | Deven Jadhav |
+——–+—————+
6 rows in set (0.02 sec)

mysql>

mysql> INSERT INTO class VALUES (”, ‘Kaushik Kawa’);
Query OK, 1 row affected, 1 warning (0.02 sec)

mysql> INSERT INTO class VALUES (”, ‘Dipak Jadhav’);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> INSERT INTO class VALUES (”, ‘Binish Philip’);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql>
mysql> SELECT * FROM class;
+——–+—————+
| rollno | name |
+——–+—————+
| 1 | Arun Bagul |
| 2 | Suhail Thakur |
| 3 | Bhavesh Vala |
| 4 | Nishit Shah |
| 5 | Yogesh Nikam |
| 6 | Deven Jadhav |
| 7 | Kaushik Kawa |
| 8 | Dipak Jadhav |
| 9 | Binish Philip |
+——–+—————+
9 rows in set (0.00 sec)

mysql>

mysql> SHOW MASTER STATUS;
+——————+———-+—————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+—————–+——————+
| mysql-bin.000001 | 520 | student,student | mysql,mysql |
+——————+———-+—————–+——————+
1 row in set (0.00 sec)

mysql>

12] Now Login to Slave (192.168.0.191) and520 check if Data is replicated or not –

[root@arun ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.0.45-community MySQL Community Edition (GPL)

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

mysql> USE student;
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_student |
+——————-+
| class |
+——————-+
1 row in set (0.00 sec)

mysql> SELECT * FROM class;
+——–+—————+
| rollno | name |
+——–+—————+
| 1 | Arun Bagul |
| 2 | Suhail Thakur |
| 3 | Bhavesh Vala |
| 4 | Nishit Shah |
| 5 | Yogesh Nikam |
| 6 | Deven Jadhav |
| 7 | Kaushik Kawa |
| 8 | Dipak Jadhav |
| 9 | Binish Philip |
+——–+—————+
9 rows in set (0.00 sec)

mysql>

mysql> SHOW SLAVE STATUS;
+—————–+——————-+—————-+———————–+
| Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master |
+—————–+——————-+—————-+———————–+
| Waiting for master to send event | 192.168.0.8 | replication | 3306 | 60 | mysql-bin.000001 | 520 | arun-relay-bin.000002 | 657 | mysql-bin.000001 | Yes | Yes | student,student | | | | | | 0 | | 0 | 520 | 657 | None | | 0 | No | | | | | | 0 |
+———————————-+————-+————-+————-
1 row in set (0.00 sec)

mysql>

13 ]How to reset MySQL slave –

mysql> STOP SLAVE;
Query OK, 0 rows affected (0.00 sec)

mysql> STOP SLAVE;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> RESET SLAVE;
Query OK, 0 rows affected (0.03 sec)

mysql> SHOW SLAVE STATUS;
+—————-+————-+————-+————-+—————| Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master |
+—————–+——————-+—————-+———————–+
| | 192.168.0.8 | replication | 3306 | 60 | | 4 | arun-relay-bin.000001 | 4 | | No | No | student,student | | | | | | 0 | | 0 | 0 | 117 | None | | 0 | No | | | | | | NULL |
+—————-+————-+————-+————-+—————
1 row in set (0.00 sec)

mysql> CHANGE MASTER TO MASTER_HOST=’192.168.0.8′, MASTER_USER=’replication’, MASTER_PASSWORD=’mypwd’, MASTER_LOG_FILE=’mysql-bin.000001′,MASTER_LOG_POS=520;
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW SLAVE STATUS;
+—————+——————–+——————–+——————–+—————–+——————-+—————-+———————–+
| Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master |
+—————-+————-+————-+————-+—————+——————+———————+———————–+—————+———————–+——————+——————-+—————–+———————+——————–+————————+————————-+—————————–+————+————+————–+———————+—————–+—————–+—————-+—————+——————–+——————–+——————–+—————–+——————-+—————-+———————–+
| | 192.168.0.8 | replication | 3306 | 60 | mysql-bin.000001 | 520 | arun-relay-bin.000001 | 4 | mysql-bin.000001 | No | No | student,student | | | | | | 0 | | 0 | 520 | 98 | None | | 0 | No | | | | | | NULL |
+—————–+——————-+—————-+———————–+
1 row in set (0.00 sec)

mysql>

** you may use this command to start the slave

mysql> START SLAVE;
Query OK, 0 rows affected (0.03 sec)

mysql>quit
Bye
[root@arun ~]#

Regards,

Arun Bagul