mysql db server replicationmodel ktv proj

Minimal centos6.4 
(First off allow mysql port 3306 via iptables) 

iptables -A INPUT -i eth0 -p tcp -m tcp --dport 3306 -j ACCEPT
OR
iptables -A INPUT -p tcp -s 0/0 --sport 1024:65535 -d 202.54.1.20 --dport 3306 -m state --state NEW,ESTABLISHED -j ACCEPT
iptables -A OUTPUT -p tcp -s 192.168.213.10(put slave ip for slave) --sport 3306 -d 0/0 --dport 1024:65535 -m state --state ESTABLISHED -j ACCEPT
OR
(@Master) 
iptables -A INPUT -p tcp -s 192.168.140.11 --sport 1024:65535 -d 192.168.213.10 --dport 3306 -m state --state NEW,ESTABLISHED -j ACCEPT
iptables -A OUTPUT -p tcp -s 192.168.213.10 --sport 3306 -d 192.168.213.11 --dport 1024:65535 -m state --state ESTABLISHED -j ACCEPT

 
One-server process that runs per slave server connected..shows up as binary log dump process thats usually bin-log dump process.Hence binary logging must be enabled on master server to allow replication.
At slave side.there are two process
  1. I/O Thread(moves data/connects to Master and downloads the updated data to a temp log  file called RELAY LOG file at slave system)
  2. SQL Thread(processes sql statements store in relay log  )
Once replication is up and running
Relay Log contains file
  1. host_name-relay-bin.nnnnn 
  2. host_name-relay-bin.index
 these files are created and removed once updates are commited on slave.
Replication is asynchronous.Good for web apps(read intensive(read are fast than writes))

#yum install mysql-server

----------------------------------------------------------------------------------------------------
vim /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
server-id =10                            ##server-id can be 1 to 2^32 -1
log-bin                             
##enable binary logging ::to give specific name type log-bin = give-name

# 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

#service mysqld restart
-------------------------------------------------------------------------------------------------------
#mysql -p
<password>
NOTE:  i had some problems with syncing the msyqld-bin .xxx and index number so make sure of those with command show master status like below; ( use  command :  reset master ;  //to reset indexes)

mysql> show master status;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| mysqld-bin.000002 |      106 |              |                  |
+-------------------+----------+--------------+------------------+

[root@master ~]# ls /var/lib/mysql/
ibdata1      ib_logfile1        master-bin.000002  mysql              mysqld-bin.000002  mysql.sock
ib_logfile0  master-bin.000001  master-bin.index   mysqld-bin.000001  mysqld-bin.index
---------------------------------------------------------------------------------------------------
mysql> create database contact;
Query OK, 1 row affected (0.00 sec)

mysql> use contact;
Database changed
mysql> create table person(id int NOT NULL AUTO_INCREMENT,name varchar(55),phone int,PRIMARY KEY (id));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into person values (null,'ramesh','9851888899');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert into person values (null,'suresh','9841888899');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from person;
+----+--------+------------+
| id | name   | phone      |
+----+--------+------------+
|  1 | ramesh | 2147483647 |
|  2 | suresh | 2147483647 |
+----+--------+------------+
2 rows in set (0.00 sec)
---------------------------------------------------------------------------------------------------
Create Replication account :
GRANT REPLICATION SLAVE ON   *.*  TO 'user1'@'%' IDENTIFIED by 'p@ssword123';
[[% means user1 can be at any host]]
#for replicating particular db eg. contactdb  use contact.*  instead of  -->*.*  (i,e all db's)

 To make 'user1' load data/table(optional)

GRANT SELECT,SUPER,RELOAD ON *.*  TO 'user1'@'%' IDENTIFIED by 'p@ssword123';
 Check:
mysql> show grants for user1;+-----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for user1@%                                                                                                                      |
+-----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, RELOAD, SUPER, REPLICATION SLAVE ON *.* TO 'user1'@'%' IDENTIFIED BY PASSWORD '*45DE09574DD7395CB49B9E7AE24CC93A18AED04D' |
+-----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Flush tables and block all writes during replication configuratoin:
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)
//Assuming connections are still open to db :
making  a binary backup of database directory;
[root@master ~]# tar -cvf contact.tar /var/lib/mysql/contact





Now copy copy the backup file contact.tar to slave servers:
[root@master ~]# scp contact.tar root@slave1:/var/lib/mysql/
root@slave1's password:*********
contact.tar

At slave1:  untar the contact.tar file located at /var/lib/mysql/contact.tar  
# tar -xvf contact.tar  


Record the master server binary logfile name and offset
 mysql> show master status;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| mysqld-bin.000002 |      968 |              |                  |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
#file name= mysqld-bin.000002
#offset = 968 
mysql>unlock tables;

#service mysqld restart

At slave1
vim /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
server-id =100   #should be unique

# 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

#service mysqld start

in mysql command at slave 1
mysql>CHANGE MASTER TO MASTER_HOST='192.168.213.10',MASTER_USER='user1',MASTER_PASSWORD='p@ssword123',MASTER_LOG_FILE='mysqld-bin.000002',MASTER_LOG_POS=968;

mysql>start slave;
mysql>show slave status;  or show slave status \G;
>show processlist;
Confirm the existence of 'Relay log and index files in data directory :
#ls -ltr /var/lib/mysql/
[root@slave1 ~]# ls -ltr /var/lib/mysql/
total 20512
drwx------. 2 mysql mysql     4096 Apr  9 04:11 mysql
-rw-rw----. 1 mysql mysql  5242880 Apr  9 04:11 ib_logfile1
drwx------. 2 mysql mysql     4096 Apr  9 05:16 contact
-rw-rw----. 1 mysql mysql 10485760 Apr  9 05:47 ibdata1
drwxr-xr-x. 3 root  root      4096 Apr  9 05:48 var
-rw-rw----. 1 mysql mysql  5242880 Apr  9 06:11 ib_logfile0
srwxrwxrwx. 1 mysql mysql        0 Apr  9 06:11 mysql.sock
-rw-rw----. 1 mysql mysql      156 Apr  9 06:25 mysqld-relay-bin.000001
-rw-rw----. 1 mysql mysql       52 Apr  9 06:25 mysqld-relay-bin.index
-rw-rw----. 1 mysql mysql      252 Apr  9 06:25 mysqld-relay-bin.000002
-rw-rw----. 1 mysql mysql       75 Apr  9 06:25 master.info
-rw-rw----. 1 mysql mysql       52 Apr  9 06:25 relay-log.info
mysql-relay-bin.index tells mysql which mysqld-relay-bin-XXX(which accepts updates from master server) is current file..
[root@slave1 mysql]# cat master.info
15
mysqld-bin.000003                    #name of bin file currently in use
246                                          #offset
192.168.213.10    #name of server
user1                   #account
p@ssword123    #password
3306                  #port used
60                  #retrieval interval
0
0
similar info is with relay log ..used in situation like poweroutage etc .. ..
------------------------------------------------------------------------------
When outof sync problem occur to change logfile posiiton or name
--------------------------------------------------------------------------------
mysql> STOP SLAVE; 
Query OK, 0 rows affected (0.00 sec) 

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

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

mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysqld-bin.000001'; 
Query OK, 0 rows affected (0.00 sec) 

mysql> CHANGE MASTER TO MASTER_LOG_POS=98; 
Query OK, 0 rows affected (0.00 sec) 

mysql> start slave; 
Query OK, 0 rows affected (0.00 sec)





Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)