Sometimes you need to use MySQL replication (master-slave) on the same server. Let me tell you how to set this up in 10 minutes or less. :)
First of all, we need to locate MySQL configuration file, containing [mysqld] section. If you don’t know how to do that, you are welcome to see here.
Next we need to set up some things related to master and slave configurations. Let me paste the entire [mysqld] section:
server-id = 1
log-bin = /var/lib/mysql/mysql-bin
In the sample above we are going to replicate shop database to database named replica. We have defined these databases for replication, now we need to restart our MySQL server for the changes to take effect. On Debian it’s
Ok, great job. Now we need to check whether master has started. Log in to mysql as root
mysql -uroot -p and input the following command:
You should see something like this:
MariaDB [(none)]> show master status;
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
| mysql-bin.000007 | 2300 | shop | |
1 row in set (0.00 sec)
Make sure to record File and Position values, we will need them on the next step. At this point I suppose that you have both databases ready, their content should be identical. I prefer to have both of them empty, and on first SQL import you can check whether your desired replication works or not.
Let’s stop slave (it is started automatically unless you have changed that in configuration).
mysql -uroot -p;
If you won’t do that, you will get an error message telling you it’s absolutely necessary to do.
Now we need to grant replication privileges to replica database user (or any user who will do the replication). In my case it’s replica database user.
GRANT REPLICATION SLAVE ON *.* TO 'replica_user'@'localhost' IDENTIFIED BY 'replica_password';
Done, let’s update privileges.
And almost final step – we need to assign replica database user (same user who was granted replication slave) to our master server.
CHANGE MASTER TO MASTER_HOST='localhost', MASTER_USER='replica_user',
MASTER_PASSWORD='replica_password', MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS = 2300;
Make sure to set up MASTER_LOG_FILE and MASTER_LOG_POS. There should be values obtained from master status.
That’s all, starting slave:
Now we need to ensure that everything was set up.
show slave status\G
If it’s fine, you will get something like this:
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
1 row in set (0.00 sec)
That’s all! Now you are welcome to create any query in your shop database, e.g. create a table or update some values.
Your changes should immediately be reflected on your slave database replica.
Make sure you don’t add any values to table replica. I’d suggest you to revoke all privileges from replica_user to avoid stopping replication.
REVOKE INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON replica.* FROM 'replica_user'@'localhost';
We did this! Now your replication should be up and working!