RSS Feed

Simple MySQL replication with Ubuntu – Master to slave

This howto assumes you have two or more MySQL servers, one of which will act as the master (for writing to) and the others as the slaves (for reading from).


Update sources

First, on each of the MySQL servers update your repository package index files from their sources:

sudo apt-get update


Install MySQL server on each node

Next install MySQL server and client. You will need to enter a root password for each MySQL installation, you will need you remember these passwords for the next steps.

sudo apt-get install mysql-server mysql-client


Configure the master server
Once installed, login to MySQL on the master server as the root user

mysql -u root -p

You now need to allow access to the master server from the slaves for streaming of the binary logs. The following command does this for you.

mysql> GRANT REPLICATION SLAVE ON *.* TO ‘slave’@'%’ IDENTIFIED BY ‘pa55word’;

You can change ‘slave’ and ‘pa55word’ to be any username and password you prefer.

Exit out of mysql

mysql> exit

Then open my.cnf in your favourite editor, in this example I am using vi

sudo vi /etc /mysql/my.cnf

Search for server-id and log-bin. If they already exist, uncomment them, if they don’t, add them. They should look like the following

server-id = 1
log_bin = /var/log/mysql/mysql-bin.log

You will also need to comment bind-address to allow the slave servers to connect to the master:

# bind-address = 127.0.0.1

Save and exit, then move onto the slave servers.


Configure the slave server(s)

On the slave servers you need to uncomment/set the server-id in my.cnf, like the master server, however increment the previously entered id by one. So my first slave will have a server-id of 2, the second would be 3, the third 4 and so on.

Remember to restart your MySQL services in order for the server-ids to take effect


Initial database dump

MySQL replication is a fragile setup, correctly setting up replication without starting the life cycle of the setup without errors requires the slave servers to be in the exact same state as the master server. To get the slave servers to this point make a complete dump of the master databases and import them into the slave servers, like so:

mysqldump -uroot -p –all-databases –lock-all-tables > initialDump.sql

The above command dumps all the MySQL databases and locks all tables while doing so.


Import the database dump on the slave server(s)

Now take the initialDump.sql file and import it into your slave servers. You can do this two ways, you can either give remote access to root on the slave MySQL servers or scp the file across, which is the option I am going to take:

scp ./initialDump.sql username@HOSTNAME:~/

Once the file has been transferred to your slaves, ssh onto your slave server(s) and import the SQL.

mysql -uroot -p < ./initialDump.sql


Get your current log position on the master server
On your master server find the current log position and log file name:

mysql> show master status;
+——————+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+——————+
| mysql-bin.000001 | 106 | | |
+——————+———-+————–+——————+
1 row in set (0.00 sec)


Set slave server(s) as slave

Now on your slave(s) run the following command, replacing FILE with the filename and POSITION with the position output in `show master status`.

CHANGE MASTER TO MASTER_HOST=’MASTER_HOSTNAME’, MASTER_USER=’slave’, MASTER_PASSWORD=’pa55word’, MASTER_LOG_FILE=’FILE’, MASTER_LOG_POS=POSITION;

Example:

CHANGE MASTER TO MASTER_HOST=’192.168.1.11′, MASTER_USER=’slave’, MASTER_PASSWORD=’pa55word’, MASTER_LOG_FILE=’mysql-bin.000001′, MASTER_LOG_POS=106;

This tells the MySQL server that it is now a slave and its master is the specified master hostname/ip, using the credentials setup on the master for slave connectivity.

And now start the slave process:

START SLAVE;


Update debian-sys-maint password on slave(s)

When you dumped the database(s) from the master and imported on the slave(s) you also imported the masters mysql database. This database stores all your MySQL user details, including the user debian-sys-maint.

This user is by default similar to root and is used by the majority of debian distros for running MySQL maintenance scripts.

Each installation of Ubuntu (And other Debian distros) has a unique password for this user stored in /etc /mysql/debian.cnf.

Due to replicating the master server databases to the slave(s), including the mysql table, the debian-sys-maint user password in the mysql.user table will not match that in the configuration file on the slave(s). As a result you will receive an error like the following upon restarting MySQL.

user@hostname:~$ sudo /etc /init.d/mysql restart
* Stopping MySQL database server mysqld
* Starting MySQL database server mysqld
/usr/bin/mysqladmin: connect to server at ‘localhost’ failed
error: ‘Access denied for user ‘debian-sys-maint’@'localhost’ (using password: YES)’

Also, any modifications made to the my.cnf file will not be used.

To fix this issue simply take the password from /etc /mysql/debian.cnf file on the master server and update the passwords (There are two) on the slave server(s).

Example /etc /mysql/debian.cnf file on the slave(s)

# Automatically generated for Debian scripts. DO NOT TOUCH!
[client]
host = localhost
user = debian-sys-maint
password = i1TAMAL9lq6SLvWr
socket = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
user = debian-sys-maint
password = i1TAMAL9lq6SLvWr
socket = /var/run/mysqld/mysqld.sock
basedir = /usr

You can ignore the “DO NOT TOUCH!” comment as without making this modification restarting mysql and maintenance scripts will not work correctly.

Restart mysql and you should no longer receive this error.

sudo /etc /init.d/mysql restart


Check replication connectivity

To check successful connectivity log onto the master as root and run:

SHOW FULL PROCESSLIST;

This command should provide a list of currently running processes within MySQL. In this list you should see a connection from your slave(s) with a state of (or similar to) the following:

Has sent all binlog to slave; waiting for binlog to be updated


Testing for successful replication

To test successful replication it’s ideal that you create a database, grant access onto that database and then drop the database.

On the master server create a test database:

CREATE DATABASE test;

The database should now appear on the slave. Now add a user and grant permission onto that database:

GRANT select ON test.* TO testuser@’%’ IDENTIFIED BY ‘pa55word’;

Login to your slave MySQL server with testuser, yous hould be able to “use” the test database.

OK, now drop the database on the master.

DROP DATABASE test;

The database should now have been removed from the slave also.

Important note: It’s extremely important that you never write to a slave database, this is a sure way to screw up your replication and give you some heavy headaches. Always write to the master and read from the slaves.

That’s it, replication is now setup and running. If you have any difficulties setting this up, please don’t hesitate to post here and I will help you.

Posted in Linux on the 14th October 2010

6 people have spoken their minds!

  1. Robin Corps says:

    A pretty handy and comprehensive guide to setting up MySQL replication. Thanks Steve, might come in handy for me soon!

  2. Mark says:

    I can think of a project this would be perfect for. Thanks!

  3. srcnix says:

    Glad it will come of some use. Let me know if you have any difficulties.

  4. yi fu says:

    This tutorial is extreme handy and I manage to config master/slave within minutes

  5. richbos says:

    Nice, many thanks for posting this, we struggled for a full day using various other How-To’s we found, all of which which were either missing sections, over complex or just didn’t work for one reason or another, however this is great, not only is it simple but it works perfectly and is the only one we’ve found which delivers true replication for newly created DBs, not just tables and data in existing ones. Fab. 

  6. Roddy says:

    Very VEry nice tutorial, but how do I make sure that my applications is only reading from slaves and writing to master? Is that on apache config? Thanks for your time

SPEAK YOUR MIND...

Your email address will not be published. Required fields are marked *

*