Skip to content

Replication under MySQL

CONFIGURATION STEPS BETWEEN MASTER UND SLAVE
MASTER SERVER= PINK
SLAVE SERVER = PANK

1.) Enable binary-logging on Master

server-id               = 10
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
binlog_do_db            = exampledb

2.) Restart MySQL

3.)mysql -u root -p –> show master status;
PINK:/var/lib/mysql# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 136
Server version: 5.0.67-0.dotdeb.1-log (Dotdeb)

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 | exampledb    |                  |
+——————+———-+————–+——————+
1 row in set (0.00 sec)

4.) check ,if existing this files:

PINK:/var/log/mysql# ls
mysql-bin.000001  mysql-bin.index

5.) Create a replication account

PINK:/var/log/mysql# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 138
Server version: 5.0.67-0.dotdeb.1-log (Dotdeb)

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

mysql> GRANT REPLICATION SLAVE ON *.*TO ‘replica’@’%’ IDENTIFIED by ‘abc123’;
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT,SUPER,RELOAD ON *.*TO ‘replica’@’%’ IDENTIFIED by ‘abc123’;
Query OK, 0 rows affected (0.00 sec)

6.)Check this account mysql> show grants for replica;

+———————————————————————————————————————-+
| Grants for replica@%                                                                                                                      |
+———————————————————————————————————————-+
| GRANT SELECT, RELOAD, SUPER, REPLICATION SLAVE ON *.* TO ‘replica’@’%’ IDENTIFIED BY PASSWORD ‘*6691484EA6B50DDDE1926A220DA01FA9E575C18A’ |
+——————————————————————————————————————-+
1 row in set (0.00 sec)

7.) Enable write protection on Database

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

8.) Create a binary-backup a database exampledb

change to the directory ->  /var/lib/mysql:
PINK:/var/lib/mysql# ls
debian-5.0.flag  ibdata1  ib_logfile0  ib_logfile1  mysql  mysql_upgrade_info  exampledb

# tar -cjvf exampledb.bz2 /var/lib/mysql/exampledb
tar: Removing leading `/’ from member names
/var/lib/mysql/exampledb/
/var/lib/mysql/exampledb/bla.MYI
/var/lib/mysql/exampledb/bla1.MYI
/var/lib/mysql/exampledb/blo.frm
/var/lib/mysql/exampledb/blulu.MYD
….
….

9.) Copy exampledb.bz2 to Slave

scp exampledb.bz2 PANK:/var/lib/mysql

10.)Stop MySQL Server on Slave and change to the directory /var/lib/mysql

extract the file
tar -xjvf exampledb

11.)Notes the name a binary-log file and positions:

mysql> show master status;
+——————+———-+————–+——————+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+——————+
| mysql-bin.000017 |   328493 | |                  |
+——————+———-+————–+——————+
1 row in set (0.00 sec)

12.) Disable write protection on Master

mysql> UNLOCK TABLES

13.) Edit my.cnf for SERVER ID’s on Master and Slave

/etc/mysql/my.cnf for Master
Server id = 10

Restart MySQL Server on Master

/etc/mysql/my.cnf for Slave
server id = 20

Start MySQL Server on Slave

14.)Configure  Replication

On SLAVE SERVER:
mysql> CHANGE MASTER TO MASTER_HOST=’PINK’,MASTER_USER=’replica’,MASTER_PASSWORD=’abc123′,MASTER_LOG_FILE=’mysql-bin.0000017′,MASTER_LOG_POS=328493;

15.) START SLAVE; (on Slave)

16.) you need to use SHOW SLAVE STATUS and SHOW PROCESSLIST on Slave  which provides much more information:

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 | PINK        | replica     |        3306 |            60 | mysql-bin.002974 |            25260722 | relay.000107   |      25260859 | mysql-bin.002974      | Yes | Yes |                 |                     |                    |                        |                         |                             |          0 |            |            0 |            25260722 |        25260859 | None            |                |             0 | No                 |                    |                    |                 |                   |                |                     0 |
+———————————-+————-+————-+————-+—————+——————+———————+—————-+—————+———————–+——————+——————-+—————–+———————+——————–+————————+————————-+—————————–+————+————+————–+———————+—————–+—————–+—————-+—————+——————–+——————–+——————–+—————–+——————-+—————-+———————–+
1 row in set (0.00 sec)
Here is Slave’s parameter.Make sure the status for this parameters is YES
Slave_IO_Running = yes
Slave_SQL_Running = yes

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

17.) Check ,if existing Files Relay-log ,index und info

PANK:/var/log/mysql# ls
relay.000107  relay-log.index  relay-log.info
PANK:/var/log/mysql#

18.) See the Master.info file

drbunsen:/var/lib/mysql# ls
debian-5.0.flag  ibdata1  ib_logfile0  ib_logfile1  master.info  mysql  mysql_upgrade_info  test  exampledb
PANK:/var/lib/mysql#

Contents master.info:
14
mysql-bin.002974
50992588
PINK
replica
password
3306
60

0

19.)PANK> mytop -u root -p xxxx -d exampledb

MySQL on localhost (5.0.67-0.dotdeb.1)                                                                                               up 2+21:51:46 [12:31:07]
Queries: 13.6M  qps:   57 Slow:     0.0         Se/In/Up/De(%):    00/00/00/00
qps now:    1 Slow qps: 0.0  Threads:    1 (   1/   1) 00/00/00/00
Key Efficiency: 99.9%  Bps in/out:   0.0/  0.1   Now in/out:   8.4/ 1.3k

Id      User         Host/IP         DB      Time    Cmd Query or State
—      —-         ——-         —      —-    — ———-
72      root       localhost  exampledb         0  Query show full processlist
41 system us                               245354 Connec Waiting for master to send event
42 system us                            4294966568 Connec Has read all relay log; waiting for the slave I/O thread to update it

#END:-)