MySQL Replication Via SSH
Myth: Replication in MySQL is just about the most awful computer thing there is. Anyway, it’s impossibly brittle. It’s so painful. It’s so unreliable and totally not worth it.
I know that to it on MySQL 5.0 and even MySQL 5.1 is going to be a big challenge. So I upgraded both of my computers to a more current MySQL version: MySQL 5.5.24 on Ubuntu 12.04 (server-db=1) acting as Master Host and MariaDB 5.5.25 on Windows XP (server-db=2) acting as Slave Host.
The concept of replication on MYSQL is straight forward as all SQL statements that change data are kept in a binary log. Data can be replicated easily and quickly to one or more slave machines.
MySQL replication is asynchronous (one-way trip) in which one server acts as the master, while one or more other servers act as slaves. Both the master and its slave/slaves are autonomous when it comes to data consistency. Asynchronous replication provides optimal performance because a master is free to service other inbound transactional requests after writing updates to its Binlog without waiting to verify that updates have been replicated to at least one slave in the topology. While fast, this comes with a high risk of master/slave data inconsistency or even data loss at recovery if there is a failure on either end.
In Master-Slave Replication, in which a single member of the group is designated as the "master" for a given piece of data and is the only node allowed to modify that data item.
On the scalability front, MySQL scales easily into large, query-heavy databases.
Instead of opening MySQL Port 3306 to the world, I have decided to configure replication via SSH private tunnel. So both computer appears like different node within an internal network or different ports within a computer.
Putty Connection Tunnels
L3307 127.0.0.1:3306
Verify mysql connectivity:
mysql -h 127.0.0.1 -P 3307 -u root -p
A short and east guide for a new MySQL replication setup.
Install a MySQL instance that will serve as a Master and configure the Master my.cnf file (located at /etc/mysql/) with the server id and the log file name:
[mysqld]
server-id = 1
log-bin
expire_logs_days=30
max_binlog_size=100M
# input the database which should be replicated
binlog-do-db=ksdb
# database to be ignore
binlog-ignore-db=mysql
binlog-ignore-db=test
Restart the MySQL daemons to apply the my.cnf changes.
/etc/init.d/mysql restart
On the Master, Provide the slave with replication permissions: Run mysql from command prompt:
grant replication slave on *.* to ’replication’@’127.0.0.1’ identified by ’slavery’;
\q
Install a MySQL instance that will serve as a Slave and configure the Slave my.cnf with the server id, reply logs and key configuration databases.
[mysqld]
server-id = 2
Restart the MySQL daemons to apply the my.cnf changes.
/etc/init.d/mysql restart
If both servers were just installed there is no need to sync their data files (since they should have the same data files).
Get the master location, so we can sync the slave to it:
master> FLUSH TABLES WITH READ LOCK;
master> SHOW MASTER STATUS;
# Please Note Following...
File: mysqld-bin.000247
Position: 52837576
If there are existing data, perform a mysqldump from the master and copy over the database to be replicate over to the slave.
On Master:
mysqldump -u root -pwelcome ksdb > myksdb.sql
master> UNLOCK TABLES;
On Slave:
mysql -u root -p ksdb < my.ksdb.sql
On SLAVE machine, setup the slave using the chosen user/password and the master location we found before:
mysql -u root -p
CHANGE MASTER TO MASTER_LOG_FILE=’mysqld-bin.000247’, MASTER_LOG_POS=52837576, MASTER_HOST=’127.0.0.1’, MASTER_PORT=3307, MASTER_USER=’replication’, MASTER_PASSWORD=’slavery’;
START SLAVE;
show slave status\G
It should display...
***********
Slave_IO_State: Waiting for master to send event
Master_Host: 127.0.0.1
Master_User: replication
Master_Port: 3307
Connect_Retry: 60
Master_Log_File: mysqld-bin.000248
Read_Master_Log_Pos: 44137
Relay_Log_File: HPMINI-relay-bin.000003
Relay_Log_Pos: 44422
Relay_Master_Log_File: mysqld-bin.000248
Slave_IO_Running: Yes <<< look out for these two
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Done!
When the master server should fail, you have a copy available immediately, so you don’t loose data and you can switch your applications to the slave server while bringing back up the master server, so the downtime is small. A drawback of this master-slave replication is that it is a one-way backup. The slave server is a complete copy of the master server : all changes that are done on the master server are applied to the slave server, keeping them identical.
But because it is one-way replication, changes to the slave database server aren’t applied to the master server.
NOTE:
Each slave must connect to the master using a MySQL user name and password, so there must be a user account on the master that the slave can use to connect. Any account can be used for this operation, providing it has been granted the REPLICATION SLAVE privilege. You may wish to create a different account for each slave, or connect to the master using the same account for each slave.
You need not create an account specifically for replication. However, you should be aware that the user name and password will be stored in plain text within the master.info file (see Section 16.2.2.2, "Slave Status Logs"). Therefore, you may want to create a separate account that has privileges only for the replication process, to minimize the possibility of compromise to other accounts.
To create a new account, use CREATE USER. To grant this account the privileges required for replication, use the GRANT statement. If you create an account solely for the purposes of replication, that account needs only the REPLICATION SLAVE privilege. For example, to set up a new user, repl, that can connect for replication from any host within the mydomain.com domain, issue these statements on the master:
Updated On: 12.10.06