MySql database replication
April 4, 2014 / by Marco / Categories : Technology
This was my first time setting up a MySql Database Replication instance between two servers. The instructions that I used from the mysql.com (How to set up Replication) was straight forward but there were a few gotcha’s which I decided to document in case I forget. If you’re an expert in this area and the instructions are incorrect please contact me so I can amend and update.
On the Master Server
This is the server which will have the master database. Master meaning that the information in this database will be replicated and copied to the slave database.
Create replication user account
Login to phpMyAdmin and click the Replication button at the top menu
Click on Add slave replication user
Enter all details (username, password)
This is the account that will be used on the slaves to connect to the master to authenticate and replicate the tables.
Export Database
According to the instructions from Mysql.com you need to flush the tables with read lock. To do this start mysql and enter:
mysql> FLUSH TABLES WITH READ LOCK;
Now if your database is empty I don’t believe you need to run this step.
Then you need to export the database by using the following command:
shell> mysqldump --all-databases --master-data > dbdump.db
It’s important you use –master-data so that it locks all tables in a seperate session. If you have a large database you can use the command gzip
shell> mysqldump --all-databases --master-data | gzip > dbdump.db
That’s pretty much all you need to do on the master server. You also need to make sure that your firewall allows incoming connection using port 3306.
Once you have configured the slave you can check the status on master to see which clients are replicating by clicking on Show Connected Slaves.
On the slave server
You will need to import the mysql dump. So follow these steps
- Start the slave, using the
--skip-slave-startoption so that replication does not start. - Import the dump file:
shell>
mysql < fulldb.dump
Login to myphpadmin and click the replication button at the top.
Click configure
Enter the details that was used on the master server.
Click Go and check the slave status
Once you have added the master settings you will have the option to check the master status
Add –report-host=host_name in the my.ini (I’m using WAMP). The hostname you can change to a unique word to identify your slaves.
I hope this has helped someone start replicating their mysql database. If there’s anything that I can improve in this post then please leave me a comment as I’m no expert in this area.
NOTE: I was getting some duplicate 1062 errors when Importing the dump file, so I just added this line to the my.ini file.
slave-skip-errors = 1062
Our Sponsors
- Advertise Me Pty Ltd
Digital Signage Solutions
OTHER ARTICLES YOU MAY LIKE
SUPABASE BACKUP TUTORIAL: USE DBEAVER TO EXPORT YOUR DATABASE SAFELY
If you are building anything serious on Supabase, whether that is a client portal, a SaaS app, an internal dashboard, or even a small side project that is starting to get traction, having a reliable database backup process is one of those tasks that quickly moves from optional to essential. Supabase makes a lot of […]
read more
USE FUSEBASE TO AUTOMATE TEAMWORK AND COLLABORATION
If your business is growing, your team is juggling too many moving parts, and your client communication is spread across email threads, chat apps, documents, task boards, and endless follow ups, there is a good chance the real problem is not effort but fragmentation. Most businesses do not lose time because people are lazy. They […]
read more









