MySQL replication – Master/Slave

MySQL replication enables one database server (we’ll call it the Master server) to be replicated to one or more database servers (we’ll call those the Slave servers). With MySQL, replication is asynchronous. This means your Slave servers do not need to be connected permanently to receive updates from the Master – for example, you could stop the Slave thread on the Slave server and start it back up at a later time and it would automatically catch itself up to the Master. Pretty cool stuff.

There are many different ways to setup replication. This tutorial will cover a simple setup (single Master server replicating to a single Slave server) that replicates all databases from the Master to the Slave.

For the sake of this article, we’ll assume that your OS is Ubuntu and the mysql and mysql-server packages have already been installed. We’ll also assume you want to configure replication on a brand new set of servers with no data (no databases yet, this is important as any existing data will throw off the replication). Please note that this article could be useful for other flavors of Linux as well. Now let’s get to the fun part.
review for the purpose of this article:

Ubuntu -OS installed
mysql -installed
mysql-devel -installed
mysql-sever -installed

Configure your Servers

Master

You’ll need to edit the my.cnf file on the Master server to enable binary logging and set the server’s id.

[user@mysql-master ~]$ sudo vi /etc/myqsl/my.cnf

Add these lines under the [mysqld] section:

log-bin=mysql-bin
server-id=1

This will enable binary logging and set the Master’s server-id to ’1′. Restart MySQL for the changes to take effect.

[user@mysql-master ~]$ sudo /etc/init.d/mysqld restart

Master my.cnf configuration done.

Slave

You’ll need to edit the my.cnf file on the Slave server to set the server’s id to something different than the master.

[user@mysql-slave ~]$ sudo vi /etc/my.cnf

Add these lines under the [mysqld] section:

server-id=2

This assigns the Slave a unique server-id. Now lets restart MySQL so these changs take effect.

[user@mysql-slave ~]$ sudo /etc/init.d/mysqld restart

Slave my.cnf configuration done.

Creating a Replication User

The Slave server will connect to the Master via a standard MySQL user and password. This user will need to have been granted the ‘REPLICATION SLAVE’ privilege. Let’s create that user on the Master server now.

[user@mysql-master ~]$ sudo mysql

You should be at the mysql prompt on the Master server. Once there, let’s create a new user and grant him the proper privilege.

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.176.41.207' IDENTIFIED BY 'slavepass';

This statement is basically saying grant the repl user replication slave access to any database/table on this host from the Slave server’s private IP. Now tell the server to reload the grant tables with the statement ‘FLUSH PRIVILEGES’.
mysql> FLUSH PRIVILEGES;
At this point, it’s a good idea to make sure you’re able to access the Master from the Slave using the credentials you just created. Alright, now back to the Slave server.

[user@mysql-slave ~]$ mysql -h 10.176.41.72 -u repl -p'slavepass'

Be sure to use your private IP for your Master server. In this example, mine is 10.176.41.72. Do you get a MySQL prompt? If so, you’re successfully logged into your Master server using the new ‘repl’ user you just created. Alright, we’re about half way there.

Grab the Master Information

On the Master server, we’ll need to find out the binary log it is currently using and the position it is at in that binary log. If you haven’t logged out of MySQL from creating that replication user, you should be at the MySQL prompt already. Go ahead and lock the tables so we can safely grab that data.
Log into the master server.

mysql> FLUSH TABLES WITH READ LOCK;

Now we’ll use the ‘SHOW MASTER STATUS’ command to get all the data we need.

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |      239 |              |                  | 
+------------------+----------+--------------+------------------+

Note the current binary log and position. In our example, the Master server is currently on the mysql-bin.00003 binary log and is at position 239. Now let’s remove that lock.

mysql> UNLOCK TABLES;

Final Slave Config

Changing the Master Information on the Slave

Up to this point, you should have collected the following information:
Private IPs
replication username and password
Master server binary log and position.
Now it’s time to use this information to connect the Slave server to the Master server. Let’s start configuring the Slave.

[user@mysql-slave ~]$ sudo mysql

Once at the mysql prompt, we’re going to change the Master information with the ‘CHANGE MASTER TO’ statement (so the Slave server knows where/how it’s connecting).

mysql> CHANGE MASTER TO
    -> MASTER_HOST='10.176.41.72',
    -> MASTER_USER='repl',
    -> MASTER_PASSWORD='slavepass',
    -> MASTER_LOG_FILE='mysql-bin.000003',
    -> MASTER_LOG_POS=239;

Note the values for each field. The MASTER_HOST is the private IP of the Master server, MASTER_USER is the user we created for replication, MASTER_PASSWORD is the password for the replication user, MASTER_LOG_FILE is the binary log that we recorded from the Master server status earlier, and MASTER_LOG_POS is the position the Master was in that we recorded.

Start the Slave Thread

Ok, now start the slave thread on the Slave server.

mysql> START SLAVE;

Lets make sure that replication is working with the ‘SHOW SLAVE STATUS’ statement.

mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 10.176.41.72
                Master_User: repl
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000003
        Read_Master_Log_Pos: 314
             Relay_Log_File: mysqld-relay-bin.000003
              Relay_Log_Pos: 235
      Relay_Master_Log_File: mysql-bin.000003
           Slave_IO_Running: Yes
          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: 314
            Relay_Log_Space: 235
            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: 0

If you see that Slave_IO_State is ‘Waiting for master to send event’ and Seconds_Behind_Master isn’t ‘NULL’, your replication is working! If it does show NULL, go back and double check your ‘CHANGE MASTER TO’ statment to make sure all data is correct. That’s it!
Rejoice

Wasn’t too bad, was it? Now sit back and let your Slave server replicate from the Master. Be sure not to perform any writes to the Slave server as this will break replication! All writes performed on the Master will automatically be sent to the slave via the binary log and replication. For more information on MySQL replication, go here: http://dev.mysql.com/replication

Bookmark It

Hide Sites

Its simple really

tar -cvzpf archive.tgz /home/example/public_html/folder

or

tar -cvzpf /vs/backup/`date +%Y%m%d%H%M%S`.tgz /home/example/public_html/folder

to make a datetime stamped backup

Bookmark It

Hide Sites

To search all files and subdirectories recursively for a phrase:

grep -r -i somethingtosearchfor ./

Bookmark It

Hide Sites