Master-Slave MySQL 5.0 Replication On Ubuntu Intrepid 8.10

Replication enables data from one MySQL database server (called the master) to be replicated to one or more MySQL database servers (slaves). Replication is asynchronous – your replication slaves do not need to be connected permanently to receive updates from the master, which means that updates can occur over long-distance connections and even temporary solutions such as a dial-up service. Depending on the configuration, you can replicate all databases, selected databases, or even selected tables within a database. This is not a backup policy because an accidentally issued DELETE command will also be carried out on the slave; but replication can help protect against hardware failures though. This setup is for linux boxes since that’s the only boxes I know how to use .

Pre Requisites

Master Server

• OS : Ubuntu Intrepid (but will work for any other linux box without or with a slight of changes)
• MySQL server installed
• Database to be replicated on server

Slave Server

• OS : Ubuntu Intrepid (but will work for any other linux box without or with a slight of changes)
• MySQL server installed
• No replicated database on server

1 Database Transfer

Make a dump of the database to be replicated on the master

time mysqldump -u root -p –-host=localhost --opt salimane_db > /home/salimane/backup/salimane_db_dump.sql

Now transfer this file to your slave server! . You can use scp or any other means. Then on the Slave Server, create a new database (replace salimane_db of course):

mysqladmin -u root -p create salimane_db;

then load the dump file on the slave server

time mysql -u root -p --database=salimane_db < /path/to/file/on/slave/salimane_db_dump.sql

2 Configure The Master

First we have to edit the master mysql configuration /etc/mysql/my.cnf.

gksudo gedit /etc/mysql/my.cnf

We have to enable networking for MySQL, and MySQL should listen on all IP addresses, therefore we comment out these lines (if existant):

#skip-networking
#bind-address = 127.0.0.1

For replication to work, you must enable binary logging on the master. If binary logging is not enabled, replication will not be possible as it is the binary log that is used to exchange data between the master and slaves. Add to your my.cnf, located at /etc/mysql/my.cnf in the default lamp stack in Ubuntu, in the [mysqld] block replacing “salimane_db” with you related database (don’t put it at the end of the file) :

# Replication Block
log-bin = /var/lib/mysql/master-bin.log
log-bin-index = /var/lib/mysql/master-bin.index.log
binlog-do-db = salimane_db
server-id = 1

Note

For the greatest possible durability and consistency in a replication setup using InnoDB with transactions, you should also add :

innodb_flush_log_at_trx_commit=1
sync_binlog=1

Then we restart MySQL:

sudo /etc/init.d/mysql restart

Then we need to create a user on the Master Server that the Slave Server will connect as with replication privileges. So still on the Master Server:

mysql -u root -p

Now on the MySQL shell, type (Replace mysql_slave_user with your settings) :

GRANT REPLICATION SLAVE ON *.* TO 'mysql_slave_user'@'%' IDENTIFIED BY 'mysql_slave_user';

Then to refresh the privileges, type:

FLUSH PRIVILEGES;

Next (still on the MySQL shell) do this:

USE salimane_db;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

The last command will show something like this:

mysql> show master status;

+-------------------+----------+--------------+------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+-------------------+----------+--------------+------------------+

| master-bin.000002 | 98 | salimane_db | |

+-------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

Write down this information, we will need it later on the slave!

2 Configure The Slave

Now we have to tell MySQL on the slave that it is the slave, that the master is 123.456.78.90, and that the Master database to watch is salimane_db. Therefore, we add the following lines to /etc/mysql/my.cnf :

# Replication Settings
read-only
server-id=2
master-connect-retry=60
replicate-do-db=salimane_db

Then we restart MySQL:

/etc/init.d/mysql restart

Now log on the Slave mysql shell :

mysql -u root -p

Next , type :

stop slave;

In the next command (still on the Slave MySQL shell) , you have to replace the values appropriately:

CHANGE MASTER TO MASTER_HOST=''123.456.78.90', MASTER_USER='mysql_slave_user', MASTER_PASSWORD='mysql_slave_user', MASTER_LOG_FILE='master-bin.000002', MASTER_LOG_POS=98;

Now let’s explain a bit :

• MASTER_HOST is the IP address or hostname of the master (in this example it is 123.456.78.90).
• MASTER_USER is the user we granted replication privileges on the master.
• MASTER_PASSWORD is the password of MASTER_USER on the master.
• MASTER_LOG_FILE is the file MySQL gave back when you ran SHOW MASTER STATUS; on the master.
• MASTER_LOG_POS is the position MySQL gave back when you ran SHOW MASTER STATUS; on the master.

Now all that is left to do is start the slave. Still on the MySQL shell we run :

start slave;

That’s it! Now whenever salimane_db is updated on the master, all changes will be replicated to salimane_db on the slave. Test it!