1. INSTRUCTION
The following post contains that base installation steps for setting up MySql Replication Servers (version 5.0.x) on a new MySql database systems. It makes the assumption that the database system is a new system and there is no existing database running; therefore, there is no need to synchronizing the databases among master and slave before replication.
This document uses three nodes replication systems for demonstration purposes. Please see diagram bellow on how data flow.
The replication will go from mySql-1, the master server, to mySql-2, the first slave, slave-1. The additional slave, slave 2, will replicate from mySql-2; therefore, mySql-2 also acts as a master for mySql-3. Please note that Mysql replication server replicate data using ‘pull’ which means that data are pulled by the slaves from the master.
Note that the hostnames, user ids, passowrds, and installation directories are for demonstration purposes.
2. VARIFY SERVER INSTALLATIONS AND CONFIGURATION
The configurations demo here will replicate all databases.
2.1 Verify Master Server Installation and Configuration
Following are the minimum configuration variable setting. Please refer to MySql documentation on other configuration variable settings.
For master, add the following entries to the [mysqld] section of the my.cnf file:
#make sure that skip-networking is not enable (commented) in my.cnf
#skip-networking
server-id = 1
# log-bin is the path and file name where the binary logs are stored
log-bin=/var/lib/mysqlreplog/mySql-1-log-bin
innodb_flush_log_at_trx_commit=1
sync_binlog=1
2.2 Verify Slave Server Installation and Configuration
2.2.1 Slave Server: mySql-2
Add the following entries to the [mysqld] section of the my.cnf file. Since slave 1, server myMyMySql-2, will act as master server for slave 2, there are entries for both master and slave on the my.cnf file in this node.
[mysqld]
# Replication Configuration
#skip-networking
server-id=2
# Binary log
log-bin=/var/lib/mysqlreplog/myMySql-2-log-bin
log-slave-updates
sync_binlog=1
# Relay-logs
relay-log=/var/lib/mysqlreplog/mySql-2-relay-bin
relay-log-index=/var/lib/mysqlreplog/mySql-2-relay-bin-index
relay-log-info=/var/lib/mysqlreplog/mySql-2-relay-bin-info
master-info-file=/var/lib/mysqlreplog/mySql-2-master-info
max_relay_log_size= 100Mpid file
2.2.2 Slave Server: mySql-3
[mysqld]
# Replication Configuration
#skip-networking
server-id = 3
# Binary log
log-bin=/var/lib/mysqlreplog/mySql-3-log-bin
# Relay-logs
relay-log=/var/lib/mysqlreplog/mySql-3-relay-bin
relay-log-index=/var/lib/mysqlreplog/omySql-3-relay-bin-index
relay-log-info=/var/lib/mysqlreplog/omySql-3-relay-bin-info
master-info-file=/var/lib/mysqlreplog/omySql-3-master-info
max_relay_log_size= 100M
3. CREATE REPLICATION USERS
3.1 Create Replication User for Slave 1, mySql-2
Each master server need to have a user id for a slave to use to pull data from. On mySql-1, create a user for slave 1, server myMySql-2 to pull data from.
3.1.1 Create replication user and grant privileges: (password is a place holder only)
mysql>create user 'repsql2' identified by 'repsql2pass';
3.1.2 Grant Replication Privileges
mysql>grant replication slave on *.* to 'repsql2' @ 'hostname or ip' identified by 'repsql2pass';
Replace ”mySql-2. or ip” with the hostname or ip address of server mySql-2
3.2 Create Replication User for Slave-3, mySql-3
On mySql-2 create a user for slave 2, server mySql-3 to pull data from.
3.2.1 Create replication user and grant privileges: (password is a place holder only)
mysql>create user 'repsql3' identified by 'repsql3pass';
3.2.2 Grant Replication Privileges
mysql>grant replication slave on *.* to
Replace ”mySql-3. or ip” with the hostname or ip address of server mySql-3.
4. SET UP REPLICATION ON MASTER SERVER, mySql-1
1. Stop mysql server: mySql-1
#>/sbin/service mysqld stop
2. check that no mysqlds are running
#>ps –ef |grep mysql
3. Re-start the mysql server to reflect any change that made in section 2.1 Verify Master Server Installation and Configuration
4. Lock the tables to prepare for a backup dump.
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
5. Record the log name and offset. The file column shows the name of the log file and the position shows the offset within the file. Record these values for setting up the slave.
mysql> show master status;
+------------------+----------+--------------+------------------+
File Position Binlog_Do_DB inlog_Ignore_DB
+------------------+----------+--------------+------------------+
mySql-1-log-bin.000001 98
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
6. Shut down mysql server again to create a back up. Make sure there is no mysqld running.
#>/sbin/service mysqld stop
#>ps –ef | grep mysql
7. create a back up of the data files in the mysql data directory: datadir=/var/lib/mysql
#> tar –cvf /u0/prod-mysql-snapshot.tar /var/lib/mysql
8. bring mysql back on line
>/sbin/service mysqld start
9. Unlock the tables
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
5. SETUP REPLICATION ON SLAVE SERVER 1, mySql-2
1. Stop slave server: mySql-2
#>/sbin/service mysql stop
2. Create directory /var/lib/mysql/backup
3. Backup the mysql data file in mySql-2 to the backup directory.
#>/var/lib/mysql>cp –R * backup
4. Apply the backup copy from master (from section 4, step 7.)
Scp the file over and untar it.
#/tar –xvf /u0/mySql-1-mysql-snapshot.tar
5. start the slave server and check the err log to make sure it started nomarly.
#/sbin/service mysql start
6. Update master information
Mysql> change master to master_host =’
-> master_port = 3306,
-> master_user=’repsql2’,
->master_password=’repsql2pass’,
->master_log_file=’’
->master_log_spos=;
6. Check slave status.
Mysql>show slave status\G;
Mysql>show full processlist;
7. logon the master server mySql-1 and check if slave threads are connected:
mysql>show full processlist;
6. SET UP SLAVE SERVER 2: mySql-3
6.1 Get Backup Data Dump From mySql-2
1.Logon to mysql server myMySql-2 and lock the tables.
Mysql> Flush tables with read lock;
2. Record the log name and offset;
mysql> show master status;
+----------------------+----------+--------------+------------------+
File Position Binlog_Do_DB \Binlog_Ignore_DB
+----------------------+----------+--------------+------------------+
mySql-2-log-bin.000007 159616
+----------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
3. Stop mysql server mySql-2
#>/sbin/service mysql stop
4. Make a backup of the data files
#> tar –cvf /u0/mySql-2-mysql-snapshot.tar /var/lib/mysql
5. bring myMySql-2 back online and unlock tables
#>/sbin/service mysql start
Mysql>unlock tables;
6.2 Set Up Slave Server 2, mySql-3
1. Stop mysql server mySql-3
#>/sbin/service mysql stop
2. Apply backup from mySql-2
#>/tar – xvf /u0/myMySql-2-mysql-snapshot-20080626.tar /var/lib/mysql
3. Start mysql server mySql-3
#>/sbin/service mysql start
4. Update master information
Mysql> change master to master_host =’
-> master_port = 3306,
-> master_user=’repsql3’,
->master_password=’resql3pass’,
->master_log_file=’’
->master_log_pos=
8. Check slave status.
Mysql>show slave status\G;
Mysql>show full processlist;
9. Logon the master server sq2-1 and check if slave threads are connected:
mysql>show full processlist;
7. TEST
Create a test table in mySql-1 and make sure that it get replicated to mySql-2 and mySql-3
Mysql> create table reptest (a int,b int,c varchar(100));
Mysql> insert into reptest(a,b,c) values (1,2,’relication test’);
8. STOP AND START SLAVE
To stop a slave, use:
Mysql> slave stop;
To start a slave, use:
Mysql> slave start;
Posts
