I expect we have Master and Slave machines having MySQL installed on both with server-id as 1 and 2 on Master and Slave .
Mysql Replication steps:
On Master:stop all transactions.
mysql> FLUSH TABLES WITH READ LOCK;
mysql> show master status ;
+---------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| binlog.000005 | 4913710 | | |
+---------------+----------+--------------+------------------+
take mysql dump of Master
$ mysqldump -u root -p --all-databases --master-data > dbdump.sql
mysql> unlock tables;
transfer dump file to slave host
scp dbdump.sql usr@slave:/tmp/
On Slave:
[usr@slave ~]$ ls -ltr
-rwx------ 1 usr usr 57319214 Nov 6 06:06 dbdump.sql
[usr@slave ~]$ mysql -u root -p
Enter password:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| amon |
| sentry |
| smon |
+--------------------+
14 rows in set (0.00 sec)
drop databases on slave except system tables.
mysql> drop database amon
mysql> drop database smon;
Query OK, 27 rows affected, 2 warnings (0.02 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
mysql> exit;
Bye
[usr@slave ~]$ mysql -uroot -p <dbdump.sql
Enter password:
[usr@slave ~]$ mysql -u root -p
Enter password:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| amon |
| hadoop |
| hive |
| hmon |
| hue |
| mysql |
| nav |
| navms |
| oozie |
| rman |
| scm |
| sentry |
| smon |
+--------------------+
14 rows in set (0.00 sec)
Backup loaded.
mysql> CHANGE MASTER TO MASTER_HOST='master.as.svr.com',MASTER_USER='repl',MASTER_PASSWORD='slavepass', MASTER_LOG_FILE='binlog.000005', MASTER_LOG_POS=4913710;
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: master.as.svr.com
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000005
Read_Master_Log_Pos: 4601382
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 4913710
Relay_Master_Log_File: binlog.000005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 4601382
Relay_Log_Space: 4599820
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)mysql> show processlist;
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------+------------------+
| 24 | root | localhost | NULL | Query | 0 | NULL | show processlist |
| 25 | system user | | NULL | Connect | 52 | Waiting for master to send event | NULL |
| 26 | system user | | NULL | Connect | 4 | Has read all relay log; waiting for the slave I/O thread to update it | NULL |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------+------------------+
3 rows in set (0.00 sec)
mysql>show status like 'Slave%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Slave_open_temp_tables | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | ON |
+----------------------------+-------+
=========================================================================
Replication test :
--------------------------------------------------------------------------
On Master:
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
On Slave:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| amon |
| hadoop |
| smon |
| test | <-- Shows test database
+--------------------+
15 rows in set (0.00 sec)
--------------------------------------------------------------------------
On Mater:
mysql> use test;
Database changed
mysql> create table t1(id int);
Query OK, 0 rows affected (0.00 sec)
On Slave:
mysql> use test;
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
On Slave:
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1 |
+----------------+
1 row in set (0.00 sec)
--------------------------------------------------------------------------
On Mater:
mysql> insert into t1 values(10);
Query OK, 1 row affected (0.00 sec)
On Slave:
mysql> select * from t1;
+------+
| id |
+------+
| 10 |
+------+
1 row in set (0.00 sec)
--------------------------------------------------------------------------
On Mater:
mysql> update t1 set id=20;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
On Slave:
mysql> select * from t1;
+------+
| id |
+------+
| 20 |
+------+
1 row in set (0.00 sec)
--------------------------------------------------------------------------
On Mater:
mysql> update t1 set id=200;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
On Slave:
mysql> select * from t1;
+------+
| id |
+------+
| 200 |
+------+
1 row in set (0.00 sec)
--------------------------------------------------------------------------
On Mater:
mysql> insert into t1 values(10);
Query OK, 1 row affected (0.00 sec)
On Slave:
mysql> select * from t1;
+------+
| id |
+------+
| 200 |
| 10 |
+------+
2 rows in set (0.00 sec)
--------------------------------------------------------------------------
on Master:
mysql> drop database test;
Query OK, 1 row affected (0.00 sec)
on Slave:
mysql> drop database test;
Query OK, 1 row affected (0.00 sec)
--------------------------------------------------------------------------
Done !!!!! now You have My SQL with Master slave replication
---------------------------------------------------------------------------
CRASH TEST just for testing. not on PROD!!!!
====================================================================
crash test 1: drop test database from slave. thn go to master and drop test. Slave will throw error "Drop failed. Database test doesnt exist."
solution: just skip this query from slave .
not recommended. but to skip no. of queries to make slave up n running. try below link.
STOP SLAVE ;SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;start slave;
crash test 2:
stop slave;
set bin log MASTER_LOG_POS=future value. ( if current value is 4998479 then put some value greater than 4998479)
mysql> CHANGE MASTER TO MASTER_HOST='master.as.svr.com',MASTER_USER='repl',MASTER_PASSWORD='slavepass', MASTER_LOG_FILE='binlog.000005', MASTER_LOG_POS=4998479;
start slave;
show slave status \G
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from impossible position'
Note values of master status on master.
Solution:
stop slave
mysql> CHANGE MASTER TO MASTER_HOST='master.as.svr.com',MASTER_USER='repl',MASTER_PASSWORD='slavepass', MASTER_LOG_FILE='binlog.000005', MASTER_LOG_POS=current value;
start slave;
Comments
Post a Comment