How To Set Up Database Replication In MySQL On Ubuntu 9.10

Want to support HowtoForge? Become a subscriber!
 
Submitted by falko (Contact Author) (Forums) on Sun, 2010-01-10 18:04. :: Ubuntu | MySQL

How To Set Up Database Replication In MySQL On Ubuntu 9.10

Version 1.0
Author: Falko Timme <ft [at] falkotimme [dot] com>
Follow me on Twitter
Last edited 01/06/2010

This tutorial describes how to set up database replication in MySQL. MySQL replication allows you to have an exact copy of a database from a master server on another server (slave), and all updates to the database on the master server are immediately replicated to the database on the slave server so that both databases are in sync. 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.

I do not issue any guarantee that this will work for you!

 

1 Preliminary Note

In this tutorial I will show how to replicate the database exampledb from the server server1.example.com (master) with the IP address 192.168.0.100 to the server server2.example.com (slave) with the IP address 192.168.0.101. Both systems are running Ubuntu 9.10; however, the configuration should apply to almost all distributions with little or no modifications. The database exampledb with tables and data is already existing on the master, but not on the slave.

I'm running all the steps in this tutorial with root privileges, so make sure you're logged in as root:

sudo su

Please enable the root account on server2 (in case you haven't already done so) so that we can transfer the files from server1 to server2 with scp:

server2:

passwd root

 

2 Installing MySQL 5

If MySQL 5 isn't already installed on server1 and server2, install it now:

server1/server2:

aptitude install mysql-server mysql-client

You will be asked to provide a password for the MySQL root user - this password is valid for the user root@localhost as well as root@server1.example.com / root@server2.example.com, so we don't have to specify a MySQL root password manually later on:

New password for the MySQL "root" user: <-- yourrootsqlpassword
Repeat password for the MySQL "root" user: <-- yourrootsqlpassword

 

3 Configuring The Master

To make sure that the replication can work, we must make MySQL listen on all interfaces on the master (server1), therefore we comment out the line bind-address = 127.0.0.1 in /etc/mysql/my.cnf:

server1:

vi /etc/mysql/my.cnf

[...]
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address           = 127.0.0.1
[...]

Restart MySQL afterwards:

/etc/init.d/mysql restart

Then check with

netstat -tap | grep mysql

that MySQL is really listening on all interfaces on the master:

root@server1:~# netstat -tap | grep mysql
tcp        0      0 *:mysql                 *:*                     LISTEN      2166/mysqld
root@server1:~#

Now we set up a replication user slave_user that can be used by server2 to access the MySQL database on server1:

mysql -u root -p

On the MySQL shell, run the following commands:

GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'slave_password';
FLUSH PRIVILEGES;
quit;

Furthermore we have to tell MySQL for which database it should write logs (these logs are used by the slave to see what has changed on the master), which log file it should use, and we have to specify that this MySQL server is the master. We want to replicate the database exampledb, so we add/enable the following lines in /etc/mysql/my.cnf (in the [mysqld]section):

vi /etc/mysql/my.cnf

[...]
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
binlog_do_db            = exampledb
[...]

Then restart MySQL:

/etc/init.d/mysql restart

Next we lock the exampledb database on server1, find out about the master status of server1, create an SQL dump of exampledb (that we will import into exampledb on server2 so that both databases contain the same data), and unlock the database so that it can be used again:

mysql -u root -p

On the MySQL shell, run the following commands:

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

The last command should show something like this (please write it down, we'll need it later on):

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |    19467 | exampledb    |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql>

Now don't leave the MySQL shell, because if you leave it, the database lock will be removed, and this is not what we want right now because we must create a database dump now. While the MySQL shell is still open, we open a second command line window where we create the SQL dump snapshot.sql and transfer it to server2 (using scp):

server1:

cd /tmp
mysqldump -u root -pyourrootsqlpassword --opt exampledb > snapshot.sql
scp snapshot.sql root@192.168.0.101:/tmp

Afterwards, you can close the second command line window. On the first command line window, we can now unlock the database and leave the MySQL shell:

server1:

UNLOCK TABLES;
quit;


Please do not use the comment function to ask for help! If you need help, please use our forum.
Comments will be published after administrator approval.
Submitted by Anonymous (not registered) on Fri, 2011-02-11 13:31.

Hi

Every one Pls give me the solution,

When i test my slave server using this command,

>show slave status \G;

IT has display all outoput's

But,


Last_IO_Error: error connecting to master user@server.com

Two servers are pinging and Master is perfectly correct

But not working

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: 127.0.0.1
                  Master_User: root
                  Master_Port: 9306
                Connect_Retry: 1
              Master_Log_File:
          Read_Master_Log_Pos: 4
               Relay_Log_File: slave-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File:
             Slave_IO_Running: No
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 0
              Relay_Log_Space: 106
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 2003
                Last_IO_Error: error connecting to master 'root@127.0.0.1:9306' -
retry-time: 1  retries: 10
               Last_SQL_Errno: 0
               Last_SQL_Error:

Submitted by fury (not registered) on Thu, 2011-02-24 22:55.
Submitted by Guy Patterson (not registered) on Sun, 2010-01-10 19:53.

Does the replication provide any sort of encryption? In other words, if DB server1 is in a Texas datacenter, and DB server2 is in Seattle, and replication is occurring over the WAN, is that data transferred in the clear?

 Thanks,

 Guy

www.nullamatix.com

Submitted by LenZ (not registered) on Mon, 2010-01-11 11:51.

Hi Guy,

 yes, the MySQL Client/Server Protocol is not encrypted by default. However, you can configure MySQL to either use SSL (see http://dev.mysql.com/doc/refman/5.1/en/replication-solutions-ssl.html), or you could set up an SSH tunnel or any other encryption tunnel or VPN solution.

 Bye,

    LenZ