I am not going to discuss the advantages and disadvantages of MySQL replication, for that I will write some other time, but some basics are needed
“MySQL Master” is a MySQL database server which we need to replicate to some other place. (you can say Source database server)
“MySQL Slave” is a MySQL database server on which we will replicate data from Master MySQL Server (you can say destination MySQL database server)
Master Server Configuration
First You need to create a directory for MySQL bin-log, I prefer to create in /var/log
# mkdir /var/log/mysql chmod 777 /var/log/mysql #
edit my.cnf (find you my.cnf)
# nano /etc/my.cnf #
You my.cnf can be very different, i am using “my-small.cnf” as example. see the highlighted lines
[client] port = 3306 socket = /var/lib/mysql/mysql.sock [mysqld] port = 3306 socket = /var/lib/mysql/mysql.sock skip-external-locking key_buffer_size = 16K max_allowed_packet = 1M table_open_cache = 4 sort_buffer_size = 64K read_buffer_size = 256K read_rnd_buffer_size = 256K net_buffer_length = 2K thread_stack = 128K server-id = 1 #log-bin binlog-do-db=MY_DATABASE_TO_REPLICATE,newdatabase binlog-ignore-db=mysql binlog-ignore-db=test log_bin = /var/log/mysql/mysql-bin.log expire_logs_days = 10 max_binlog_size = 100M innodb_flush_log_at_trx_commit =1 sync_binlog =1 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [myisamchk] key_buffer_size = 8M sort_buffer_size = 8M [mysqlhotcopy] interactive-timeout
see the highlighted lines above. lets see what they are
binlog-do-db
the database(s) you want to replicate, you can give a COMA separated databases, multipal databases can also be given by adding binlog-do-db multipal times like
binlog-do-db = database1 binlog-do-db = database2 binlog-do-db = database3 OR binlog-do-db = database1, database2, database3
I am not going to discuss the other parameters used, just google them 😉
save your my.cnf. and restart the MySQL. and login to MySQL to check things
# service mysql restart #
Login to Mysql as root check your master server status.
[root@mysql-master ~]#mysql -uroot -p Enter password:
mysql> mysql> show master status; mysql>
and you will see something like this
mysql> show master status; +------------------+----------+--------------------------------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------------------------------+------------------+ | mysql-bin.000011 | 107 | MY_DATABASE_TO_REPLICATE,newdatabase | mysql,test | +------------------+----------+--------------------------------------+------------------+ 1 row in set (0.00 sec)
Save the master status, we need this information, and will be used while configuring slave
Now Create the MySQL user for replication, do not forget to replace the [SLAVE_SERVER_IP] with actual slave server IP address
# CREATE USER 'replication'@'[SLAVE_SERVER_IP]' IDENTIFIED BY '[PASSWORD]'; grant replication slave on *.* to 'replication'@'[SLAVE_SERVER_IP]'; flush privileges; #
SLAVE Server Configuration
edit the my.cnf for slave server and change the server ID.
nano /etc/my.cnf
find the “server-id=” and change it
server-id=2
save the file and restart the mysql
service mysql restart
Login to MySQL at Slave Server
[root@mysql-slave ~]# service mysql restart Shutting down MySQL.. SUCCESS! Starting MySQL.. SUCCESS! [root@mysql-slave ~]# mysql -uroot -p
once loged in mysql use following query to change the master host and bin-log
CHANGE MASTER TO MASTER_HOST='172.18.5.51', MASTER_USER='replication', MASTER_PASSWORD='[PASSWORD]', MASTER_LOG_FILE='mysql-bin.000011', MASTER_LOG_POS=107;
in above see the highlights lines, “MASTER_LOG_FILE” and MASTER_LOG_POS, Should be the one which we got from Master server status. (i told you to save the info above)
once the query execution is done,
Create databases which you want to replicate on slave server, replication do not automatically create databases on slave server. use mysqldump to copy the databases.
finally start the slave
mysql> mysql> start slave; mysql>
once slave is started, please check the slave status;
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 | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Master_Server_Id | +----------------------------------+-------------+-------------+-------------+---------------+------------------+---------------------+------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+ | Waiting for master to send event | 172.18.5.50 | replication | 3306 | 60 | mysql-bin.000011 | 107 | mysql-slave-relay-bin.000047 | 253 | mysql-bin.000011 | Yes | Yes | | | | | | | 0 | | 0 | 107 | 415 | None | | 0 | No | | | | | | 0 | No | 0 | | 0 | | | 1 | +----------------------------------+-------------+-------------+-------------+---------------+------------------+---------------------+------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+ 1 row in set (0.00 sec)
in above output see these things.
Slave_IO_State = Waiting for master to send event Slave_IO_Running = Yes Slave_SQL_Running = Yes
we are good to go.
add some data in Master server and it should be immediately available in Slave server (depending upon data size and your server health. 🙂 )
next i will write how to do “Master Master replication”
No Comments Yet