www.simplesharebuttons.comShare it!!! 0010

To explain the replication, we will use the article Installing MySQL 5.5 Part 2 – Multiple instances with mysqld_multi because we will need 2 MySQL’s running on our server

Let’s activate the binary logs on our server, creating a file named my_rep.cnf with the follow content:

[mysqld_multi]
mysqld = /mysql/mysql/bin/mysqld_safe
mysqladmin = /mysql/mysql/bin/mysqladmin
[mysqld1]
port = 3306
datadir = /mysql/3306/data
socket = /mysql/3306/mysql.sock
log-error = /mysql/3306/logs/mysqld.log
pid-file = /mysql/3306/mysqld.pid
server_id = 1
#LOGS
log-bin = /mysql/3306/logs/server1_bin.log
log-bin-index = /mysql/3306/logs/server1_log-bin.index
expire_logs_days = 7
binlog-format = MIXED
[mysqld2]
port = 3307
datadir = /mysql/3307/data
socket = /mysql/3307/mysql.sock
log-error = /mysql/3307/logs/mysqld.log
pid-file = /mysql/3307/mysqld.pid
server_id = 2
#LOGS
relay-log = /mysql/3307/logs/server2.relay_log
relay-log-index = /mysql/3307/logs/server2.relay_log_index
expire_logs_days = 7

Done this, let’s start the MySQL service like our article:

cd /mysql/mysql
mysqld_multi --defaults-file=/mysql/my_rep.cnf start

Now, we will connect to server 3306 and create a new user named ‘replication’ with password ‘replication’ and rights to replication.

mysql -u user -p -h 127.0.0.1
mysql> GRANT REPLICATION SLAVE ON *.* TO replication@127.0.0.1 IDENTIFIED BY 'replication';
mysql> FLUSH PRIVILEGES;

Now we need to connect on our slave server and run a start command to start our replication:

mysql -u usuario -p -h 127.0.0.1 --port 3307
mysql> CHANGE MASTER TO MASTER_HOST = '127.0.0.1', MASTER_USER = 'replication', MASTER_PASSWORD = 'replication', MASTER_PORT = 3306, MASTER_LOG_FILE = 'server1_bin.000001', MASTER_LOG_POS = 0;
mysql> START SLAVE;

Let’s explain the CHANGE MASTER TO command:

MASTER_USER = user which will connect to master server
MASTER_PASSWORD = user password which will connect to master server
MASTER_PORT = the mysql master server port
MASTER_LOG_FILE = binary log file name (we set it to server1_bin.log on .cnf file, like we can see, MySQL create file with numeric index which increase each time which server is restarted or exceed the limit set on our configuration by max_binlog_size key which by default is 1GB
MASTER_LOG_POS = the position which the slave will start to search inside the binlog

And how to monitor the replication??? Simple with the command

SHOW SLAVE STATUS;

The replication works with 2 threads:

Slave_IO = Is the thread responsible to get all sql commands from master and save them at the slave disc
Slave_SQL = Is the thread responsible to run the commands which Slave_IO get from master server

Now, I will explain 5 important status from

SHOW SLAVE STATUS;

Slave_IO_Running = Tell us if the IO thread is running, we need to see a Yes on this line
Slave_SQL_Running = Tell us if the SQL thread is running, we need to see a Yes on this line
Seconds_Behind_Master = Tell us if our slave is delayed in relation to our master, here we need to see a 0(zero)
Last_IO_Error = In case we have a problem with IO thread, this line will show us the problem
Last_SQL_Error = In case we have a problem with SQL thread, this line will show us the problem

And is it, now he have a Master server replication their data to a SLAVE server.

To see more details about replication on MySQL, please read MySQL Documentation

www.simplesharebuttons.comShare it!!! 0010