MySQL replication – Master – Slave

Send to Kindle

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

Send to Kindle
This entry was posted in binlog, MySQL, replication. Bookmark the permalink.

7 Responses to MySQL replication – Master – Slave

  1. riggnsRobson says:

    Boa tarde, eu fiz o que dizia o seu tutorial e deu certo, porém estou com uma realidade aqui onde trabalho um pouco diferente, eu preciso de replicar varios masters em um slave, isso é possível?? tem como?

  2. Walfredo says:

    Isto funciona em hospedagem compartilhada? Tipo… meu servidor 1 é webfullhost e o servidor 2 é hostgator. Quero fazer o master webfullhost e o slave hostgator. É possível? Vou precisar de acesso root ou dá pra fazer em hospedagem compartilhada?

  3. Danilo says:

    Estou tentando realizar a replicação on windows 7. Sempre que executo o show slave status\G, mostra que o Slave_IO_Running: NO. Não sei o que pode estar acontecendo. Não consigo deixar minha máquina como escrava de um outro computador master. Mas, eu consigo ser o master para outro computador. não sei que pode estar havendo. Modifiquei o my.ini de acordo com um tutorial (que posso te enviar por email). Não consigo mudar o SLAVE_IO para YES. =/ Como posso fazer?

  4. Valter Costa says:

    Não sei se entendi sua necessidade, mas creio que uma solução seria cria várias instâncias em portas diferentes em um único Hardware e configurar cada um deles para se conectar a um master diferente.
    Assim você terá um servidor ( Hardware ) Slave conectado a vários servidores Masters.

Leave a Reply

Your email address will not be published. Required fields are marked *


9 − seven =

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>