Mysql master slave replication

Mysql server replication(centos 6.4)

steps for mysql db replication

1)

#vim /etc/my.cnf

[mysqld]

log-bin
binlog-ignore-db=mysql            #ignores the databases selected
binlog-ignore-db=whmxfer
binlog-ignore-db=information_schema
binlog-ignore-db=cphulkd
binlog-ignore-db=eximstats
binlog-ignore-db=horde
binlog-ignore-db=leechprotect
binlog-ignore-db=logaholicDB_web01
binlog-ignore-db=performance_schema
binlog-ignore-db=roundcube

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

user=mysql

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

2)

#service msqld restart

[root@server1 ~]# ls -l /var/lib/mysql/

total 20492

-rw-rw----. 1 mysql mysql 10485760 Jun 11 20:57 ibdata1

-rw-rw----. 1 mysql mysql  5242880 Jun 11 20:57 ib_logfile0

-rw-rw----. 1 mysql mysql  5242880 Jun 11 20:01 ib_logfile1

drwx------. 2 mysql mysql     4096 Jun 11 20:01 mysql

-rw-rw----. 1 mysql mysql      106 Jun 11 20:57 mysqld-bin.000001

-rw-rw----. 1 mysql mysql       20 Jun 11 20:57 mysqld-bin.index

srwxrwxrwx. 1 mysql mysql        0 Jun 11 20:57 mysql.sock

##the index file tells which log file mysql is using and the position  where it left off                   .         

[root@server1 ~]# mysql -uroot -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.1.66-log Source distribution

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

mysql> show master status;

+-------------------+----------+--------------+------------------+

| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+-------------------+----------+--------------+------------------+

| mysqld-bin.000001 |         106   |              |                  |

+-------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

note: the file and position info highlighted green above is very important to be supplied to slave mysql server

3)Creating Replication user

mysql>  GRANT REPLICATION SLAVE ON *.* TO 'replica'@'192.168.213.11' IDENTIFIED BY 'p@ssw0rd';

Query OK, 0 rows affected (0.01 sec) #192.168.213.11 is salve server IP # ip canbe = '%' for all IP

mysql> GRANT SELECT,SUPER,RELOAD ON *.* TO 'replica'@'192.168.213.10' IDENTIFIED by 'p@ssw0rd';

Query OK, 0 rows affected (0.01 sec)

mysql > show grants for replica;   ##to view privileges for user replica

Flush all tables and block writes to restrict changes during replication configuration:

mysql> flush tables with read lock;

To make sure that replica user can access master .. login to slave server

[root@mail ~]# mysql -h 192.168.213.10 -u replica -p'p@ssw0rd'

you get a MySQL prompt? If so, you're successfully logged into your Master server using the new 'repl' user you just created. Alright, we're about half way there.

Now at Master server in mysql command<This is optional> :

mysql> unlock tables;

Query OK, 0 rows affected (0.00 sec)

mysql> CREATE DATABASE my_db;

Query OK, 1 row affected (0.00 sec)

mysql> use my_db;

Database changed

mysql> CREATE TABLE Persons

    -> (

    -> PersonID int,

    -> LastName varchar(255),

    -> FirstName varchar(255),

    -> Address varchar(255),

    -> City varchar(255)

    -> );

Query OK, 0 rows affected (0.01 sec)

mysql> show tables;

+-----------------+

| Tables_in_my_db |

+-----------------+

| Persons         |

+-----------------+

1 row in set (0.00 sec)

mysql> insert into Persons values ('1','johnson','jack','ktm','bagmati');

Query OK, 1 row affected (0.01 sec)

mysql> insert into Persons values ('2','daniel','mohan','pokhara','Testing');

Query OK, 1 row affected (0.00 sec)

mysql>flush tables with read lock;

[root@server1 mysql]# pwd

/var/lib/mysql

[root@server1 mysql]# tar -cjvf my_db.bz2 my_db

my_db/

my_db/Persons.frm

my_db/Persons.MYI

my_db/db.opt

my_db/Persons.MYD

[root@server1 mysql]# scp my_db.bz2 192.168.213.11:/var/lib/mysql/

root@192.168.213.11's password: **********

my_db.bz2                                                                                                       100%

Now at slave

[root@mail ~]# service mysqld stop

Stopping mysqld:                                           [  OK  ]

[root@mail ~]# cd /var/lib/mysql/

[root@mail mysql]# tar xjvf my_db.bz2

my_db/

my_db/Persons.frm

my_db/Persons.MYI

my_db/db.opt

my_db/Persons.MYD

[root@mail mysql]# ll

total 8

drwx------. 2 mysql mysql 4096 Jun 11 21:57 my_db

-rw-r--r--. 1 root  root   713 Jun 11 22:09 my_db.bz2

note: you can remove bz2 file

Now at Master mysql

mysql> unlock tables;

configure server ID for both MASTER and Slave  servers

vim /etc/my.cnf                   #(at master mysql server)

[mysqld]

log-bin

server-id=1

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

user=mysql

                      #master server-id=1 (can be up to 2^32 -1)

#service mysqld restart

At slave server

#vim /etc/my.cnf

[mysqld]

server-id=100

replicate-ignore-db=mysql            #slave ignores DB selected
replicate-ignore-db=information_schema
replicate-ignore-db=cphulkd
replicate-ignore-db=eximstats
replicate-ignore-db=horde
replicate-ignore-db=leechprotect
replicate-ignore-db=logaholicDB_web01
replicate-ignore-db=performance_schema
replicate-ignore-db=roundcube
replicate-ignore-db=whmxfer
 
datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

user=mysql

#service msqld start

mysql> change master to master_host='192.168.213.10',master_user='replica',master_password='p@ssw0rd',master_log_file='mysqld-bin.000002',master_log_pos=106;

the master_host='canbe FQDN '

mysql>start slave;

mysql> show slave status; OR

 mysql> SHOW SLAVE STATUS\G;          

mysql> show processlist;

[root@mail mysql]# ls -ltrh /var/lib/mysql/

total 21M

drwx------. 2 mysql mysql 4.0K Jun 11 21:57 my_db

-rw-r--r--. 1 mysql mysql  713 Jun 11 22:09 my_db.bz2

drwx------. 2 mysql mysql 4.0K Jun 11 22:27 mysql

-rw-rw----. 1 mysql mysql 5.0M Jun 11 22:27 ib_logfile1

-rw-rw----. 1 mysql mysql  10M Jun 11 22:28 ibdata1

-rw-rw----. 1 mysql mysql 5.0M Jun 11 22:28 ib_logfile0

srwxrwxrwx. 1 mysql mysql    0 Jun 11 22:28 mysql.sock

-rw-rw----. 1 mysql mysql  156 Jun 11 22:35 mysqld-relay-bin.000001

-rw-rw----. 1 mysql mysql   52 Jun 11 22:35 mysqld-relay-bin.index

-rw-rw----. 1 mysql mysql  252 Jun 11 22:35 mysqld-relay-bin.000002

-rw-rw----. 1 mysql mysql   74 Jun 11 22:35 master.info

-rw-rw----. 1 mysql mysql   52 Jun 11 22:35 relay-log.info