Activities

September 2012
M T W T F S S
« Aug   Oct »
 12
3456789
10111213141516
17181920212223
24252627282930

MySQL : Handy Replication steps

Here are the few quick steps to make a Mysql Master-Slave setup. I do not mention anything in the technical aspects or provide an explanatory of any replication variables in detailed over here. I uses mysql hard copy backup and restore mechanism. ie copied the mysql data directory from master to slave after shut down the mysql service in Master.

We need two an identical server loaded with same MySQL version. My server configurations are ,

CPU : Intel(R) Xeon(R) CPU E5620 @ 2.40GHz [16 Core]
RAM : 48GB
DISK : 2TB
MySQL : MySQL 5.5 Community version (64 bit)
MySQL Master IP : 192.168.0.17
MySQL slave host : 192.168.0.18

1. MySQL Master config. file.

Here is the “/etc/my.cnf” configuration for the MySQL Master.

[mysqld]
log-bin=/sqldata/mysql_logs/mysql-bin
server-id=1
innodb_flush_log_at_trx_commit=1
sync_binlog=1
binlog-do-db=db01
binlog-do-db=db02
binlog-do-db=db03
expire_logs_days=7
max_binlog_size=1000M

large-pages
datadir=/sqldata/mysql
socket=/sqldata/mysql/mysql.sock
user=mysql
old_passwords=1
innodb_file_per_table=1
connect_timeout = 7200
net_read_timeout = 7200
net_write_timeout = 7200
query_cache_size = 256M
max_connections = 1000
table_cache = 2048
max_allowed_packet = 600M
net_buffer_length = 1048576
net_retry_count = 40
interactive_timeout = 120000
innodb_buffer_pool_size=32G
innodb_additional_mem_pool_size=80M
default-storage-engine=InnoDB
long_query_time         = 1
log-slow-queries        = /sqldata/mysql_slow/mysql-slow.log
thread_concurrency = 8
skip-federated

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Pls note you may need to add only the below entries at the top of the my.cnf file.

log-bin=/sqldata/mysql_logs/mysql-bin
server-id=1
innodb_flush_log_at_trx_commit=1
sync_binlog=1
binlog-do-db=db01
binlog-do-db=db02
binlog-do-db=db03
expire_logs_days=7
max_binlog_size=1000M

server-id : This is the Mysql Master network identify for a replication slave server.
sync_binlog : This will help to make each changes on binary logs to transfer to slave.
binlog-do-db : lists of databases to be replicated.
expire_logs_days : This will delete the binary log files which is created under “log-bin” folder after the specified period.
max_binlog_size : Maximum size of a binary log file. Mysql will create new file if the size limit has reached ie mysql-bin000001…..

Then restart mysql server

 mysql> show master status\G;
*************************** 1. row ***************************
    File: mysql-bin.0001
    Position: 89
    Binlog_Do_DB: db01,db02,db03
    Binlog_Ignore_DB:
    1 row in set (0.00 sec)
ERROR:
No query specified
mysql>GRANT REPLICATION SLAVE ON *.* TO replicauser@192.168.0.18 IDENTIFIED BY 'password';

2. MySQL slave configuration.

#cat /etc/my.conf
[mysqld]
server-id=2
replicate-do-db=db01
replicate-do-db=db02
replicate-do-db=db03
relay-log = /sqldata/mysql_logs/slave-relay.log
relay-log-info-file = /sqldata/mysql_logs/relay-log.info
relay-log-index = /sqldata/mysql_logs/slave-relay-log.index
#slave-skip-errors=all
#slave-skip-errors=1054
#slave-skip-errors=1051
#slave-skip-errors=1146
expire_logs_days=5
innodb_flush_log_at_trx_commit=1
sync_binlog=1
large-pages
datadir=/sqldata/mysql
socket=/sqldata/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
innodb_file_per_table=1

# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
# symbolic-links=0
connect_timeout = 7200
net_read_timeout = 7200
net_write_timeout = 7200
query_cache_size = 256M
max_connections = 1000
table_cache = 2048
max_allowed_packet = 600M
net_buffer_length = 1048576
net_retry_count = 40
interactive_timeout = 120000
innodb_buffer_pool_size=32G
innodb_additional_mem_pool_size=80M
default-storage-engine=InnoDB
#long_query_time         = 1
#log-slow-queries        = /sqllog/mysql_slow/mysql-slow.log
thread_concurrency = 8
skip-federated
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

You might want to copy only the first 12 lines in to your “/etc/my.cnf” file. Then restart the slave MySQL service.

Unlike MySQL 5.1 version, we may need to define the Master information as sql statement in MySQL 5.5 version. I uses the following statements. But you may need some inputs from mysql Master before preparing this sql statement.

Identify the Mysql Master binary file name and log position.

mysql> show master status\G;
*************************** 1. row ***************************
File: mysql-bin.0001
Position: 98
Binlog_Do_DB: db01,db02,db03
Binlog_Ignore_DB:
1 row in set (0.00 sec)
ERROR:
No query specified
mysql>

Then login to slave mysql console and execute the sql shown below,

mysql> CHANGE MASTER TO MASTER_HOST='192.168.10.17', MASTER_USER='replicauser', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001',MASTER_CONNECT_RETRY=60,MASTER_PORT=3306,MASTER_LOG_POS=98

Variables used :

MASTER_HOST= ‘ Mysql Master IP’
MASTER_USER=’ replication user’
MASTER_PASSWORD=’ replication user password’
MASTER_LOG_FILE=’current binary file name of mysql master’
MASTER_LOG_POS=position of log entry in the file.

Verifying the slave activity

Login to mysql console on slave server.

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.18
                  Master_User: replicauser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000159
          Read_Master_Log_Pos: 192699323
               Relay_Log_File: slave-relay.000454
                Relay_Log_Pos: 192699469
        Relay_Master_Log_File: mysql-bin.000159
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: db01,db02,db03
          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: 192699323
              Relay_Log_Space: 192699664
              Until_Condition: None

Ensure that,

a. “Master_Log_File” and “Relay_Master_Log_File” are showing same
b. Check the “Read_Master_Log_Pos” and “Exec_Master_Log_Pos” is showing the same value. It may take few minutes to sync the changes in slave. So it’s not wrong.
c. Seconds_Behind_Master: Will shows the lagged time of slave.

I set Nagios alert to watch “Seconds_Behind_Master” and binary log position of both master and slave. So that I can get sms/email whenever there is a trouble.

The best way to keep the data integrity is to log all the errors. I used to get the errors “1054 and 1051” sometimes which are not harmful. ie syntax errors caused such errors to happen when someone execute sql directly over the phpmyadmin or the console.
#slave-skip-errors=1054
#slave-skip-errors=1051
#slave-skip-errors=1146

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>