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
report-host=master-is-slave-host
relay-log=myserver-relaylog
replicate-same-server-id=1
binlog-do-db=shop
replicate-rewrite-db=shop->replica
replicate-do-db=replica
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
/etc/init.d/mysql restart
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;
stop slave;
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.
FLUSH 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:
start 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
Master_Host: localhost
Master_User: replica_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000007
Read_Master_Log_Pos: 2300
Relay_Log_File: myserver-relaylog.000002
Relay_Log_Pos: 292
Relay_Master_Log_File: mysql-bin.000007
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: shop,replica
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: 650
Relay_Log_Space: 837
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
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
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!