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
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' );
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
And now, in yet another terminal window, we dump the
/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
In the MariaDB client we have open, we can now unlock the tables with
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
/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.
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
in each database connection.