MYSQL database replication

Change the Parameter on Master side
MASTER:~$ more /etc/mysql/my.cnf

server-id              = 1
binlog-format          = mixed
innodb_flush_log_at_trx_commit=1
sync_binlog            =1
log_bin                = mysql-bin
expire_logs_days        = 10
max_binlog_size         = 100M
binlog_do_db           = test
#binlog_ignore_db       = include_database_name

service mysql restart

mysql -u root -p

CREATE USER replicant@<<slave-server-ip>>;
GRANT REPLICATION SLAVE ON *.* TO replicant@SLAVE_IP IDENTIFIED BY 'replicant';


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

mysql> use test
Database changed
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |      107 | test         |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)



Open new window to take database backup

sharmh04@MASTER:~$ mysqldump -u root@ -p --opt test > test.sql
Enter password:
sharmh04@MASTER:~$ ls -ltr
total 894440
-rwxrwxrwx 1 sharmh04 sharmh04      1592 Oct  7 15:40 restore_root_privileges.sql
-rw-rw-r-- 1 sharmh04 sharmh04         0 Oct  8 15:23 mysql
-rwxr-xr-x 1 sharmh04 sharmh04 915886935 Nov  5 13:30 WEBCALL_no-confidential-data_no-temptable.sql
drwxrwxr-x 2 sharmh04 sharmh04      4096 Nov  7 09:38 old
-rw-rw-r-- 1 sharmh04 sharmh04      1277 Nov  8 11:37 test.sql


Go to Previous Window on MAster

mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

mysql> QUIT;
Bye

 prod L01 [~]
11:34:55 root@MASTER #



Login on Slave
SLAVE

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

mysql>

Move Dump from Master to Slave

sharmh04@MASTER:~$ scp test.sql SLAVE:/home/sharmh04


Import database Dump on Slave server database

sharmh04@SLAVE:~$ mysql -u root -p test < test.sql
Enter password:
sharmh04@SLAVE:~$
Modify the my.conf parameter file

server-id              = 2
log_bin                =  mysql-bin
 relay-log             = mysql-relay-bin
log-slave-updates      = 1
 read-only             = 1
binlog-format          = mixed
expire_logs_days        = 10
max_binlog_size         = 100M
binlog_do_db           = test



11:44:36 root@SLAVE # service mysql restart

11:47:04 root@SLAVE #  mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 203
Server version: 5.5.31-0ubuntu0.12.04.2-log (Ubuntu)

Copyright (c) 2000, 2013, 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> CHANGE MASTER TO MASTER_HOST='MASTER_IP',MASTER_USER='replicant',
       MASTER_PASSWORD='replicant', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=107;
Query OK, 0 rows affected (0.01 sec)

mysql>

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: 10.7.134.1
                  Master_User: replicant
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 107
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 253
        Relay_Master_Log_File: mysql-bin.000002
             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: 107
              Relay_Log_Space: 409
              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:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
1 row in set (0.00 sec)

f there is an issue in connecting, you can try starting slave with a command to skip over it:
STOP SLAVE;SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;START SLAVE;



Add another database for replicaiton .

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

mysql> SHOW SLAVE STATUS\G

 Slave_IO_Running: No
 Slave_SQL_Running: No

12:03:21 root@MASTER # vi /etc/mysql/my.cnf

binlog_do_db           = WEBCALL

12:03:46 root@MASTER # service mysql restart
mysql stop/waiting
mysql start/running, process 3698