MariaDB Replication on the Same Host for Testing

In this short tutorial, we’ll go through the steps required to set up MariaDB for replication on the same host. We are doing this for development purposes, and only run ephemeral MariaDB servers directly in terminal windows. We don’t edit any config files and instead use command line parameters to set up the server processes.

These instructions go through the motion of creating a database, populating it with some data, dumping it, and configuring replication for it—even though this is strictly not necessary. This is in preparation for a database which already contains some data in it; if you want to configure replication for an entirely fresh installation, you can cut out some steps.

The path to the MariaDB installation reflects MacPorts but apart from that these instructions are entirely independent of any operating system.

Notice. This tutorial does not go over the steps required for securing a MariaDB installation and doesn’t include any reference to passwords nor grant statements.

By the end of the process, you should have at least four open terminal windows, two of them running MariaDB servers, and two connection to each of them.

First, we set up a master by creating foo-master directory, initialize it and then us it to run our database with the --datadir argument;

mkdir foo-master
/opt/local/lib/mariadb-10.2/bin/mysql_install_db --datadir=./foo-master/
/opt/local/lib/mariadb-10.2/bin/mysqld --datadir=./foo-master/ \
    --socket=/tmp/foo-master.sock \
    --bind-address=127.0.0.1 \
    --port=3307 \
    --skip-networking=FALSE  \
    --server-id=1 \
    --log-bin=`pwd`/foo-master/binary.log \
    --binlog-do-db=foo

where we deliberately set the socket file of our choosing, so it won’t interfere with any other MariaDB server running on our host, we also bind to the loopback address and port 3307 and tell it not to skip networking. For replication, we set the server id and path to the binary log. The binary log cannot be a relative path, so we use pwd in the command line to make it absolute; the final .log is actually truncated off and replaced by numbers as you can see when you take look inside the data directory. And we deliberately only allow the foo database to be replicated; this last argument can be repeated for more databases, if desired.

Then, in a different terminal window, we connect to the database with our socket file, as root.

/opt/local/lib/mariadb-10.2/bin/mysql --socket=/tmp/foo-master.sock -uroot

and create and connect to the foo database

create database foo;

use foo;

And we create a test table with some data,

create table t ( c text );

insert into t ( c ) values ( 'foo' ), ( 'bar' );

where t is for table, and c is for column; then we insert the values “foo” and “bar”.

As we are running a brand spanking new database daemon, and are in complete control of the clients connecting to it, we strictly speaking don’t need a lock on the tables. However, we can go through the motions just for completeness sake.

flush tables with read lock;

This locks the table for us so our database dump below will be consistent with our master status.

To see our master status, we do

show master status;

which will show something like

File Position Binlog_Do_DB Binlog_Ignore_DB
binary.000001 771 foo

And now, in yet another terminal window, we dump the foo database.

/opt/local/lib/mariadb-10.2/bin/mysqldump  \
    --socket=/tmp/foo-master.sock \
    -uroot  \
    --master-data=2 \
    foo > foo.sql 

where we use the socket file from before, connect as the user root, and for completeness sake, select the master data as a comment in our database dump; finally we name the database, foo, and put it in a file called foo.sql.

In the MariaDB client we have open, we can now unlock the tables with

unlock tables;

Now, it’s time to set up the slave database. We create and initialize yet another directory for it,

mkdir foo-slave
/opt/local/lib/mariadb-10.2/bin/mysql_install_db --datadir=./foo-slave/
/opt/local/lib/mariadb-10.2/bin/mysqld \
    --datadir=./foo-slave \
    --socket=/tmp/foo-slave.sock \
    --bind-address=127.0.0.1 \
    --port=3308 \
    --skip-networking=FALSE \
    --server-id=2 \
    --log-bin=`pwd`/foo-slave/binary.log \
    --binlog-do-db=foo \
    --relay-log=`pwd`/foo-slave/relay.log

where we specify the data directory again, and another socket file, bind to the loopback address and choose 3308 for the port number; and don’t skip networking (even though it’s probably safe for the slave). For the replication, we choose server id 2, select the binary log again (even though it may not be strictly necessary) and choose the foo database for replication. We also include a relay log for completeness.

Then in yet another terminal window, connect to the slave database as user root

/opt/local/lib/mariadb-10.2/bin/mysql --socket=/tmp/foo-slave.sock -uroot

and create the foo database; then connect to it.

create database foo;

use foo;

We can leave this connection open, and use yet another terminal window to load the database dump from earlier.

/opt/local/lib/mariadb-10.2/bin/mysql --socket=/tmp/foo-slave.sock -uroot foo < foo.sql

Then we set our connection parameters for the master.

change master to master_host='127.0.0.1',
                 master_port=3307,
                 master_user='root',
                 master_log_file='binary.000001', 
                 master_log_pos=771;

The hostname is the loopback address and port 3307, both of which we set when we started the master; the username is root. And we use the file and position we selected earlier on the master; note that the file name is a single quoted string, while the position is a raw number — not in quotes.

And now, we start the slave.

start slave;

At this point we have a fully functional master slave replication system. You can test this by creating tables or inserting data on the master, and see it replicated on the slave.

Finally, to shut down the master and slave, we can do

shutdown;

in each database connection.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: