MySql database replication
April 4, 2014 / by Marco / Categories : TechnologyThis 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-start
option 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
POWERFUL REFERRAL PROGRAM TOOL
Businesses of all sizes are constantly seeking new and innovative ways to grow their customer base and increase brand awareness. With the rise of social media and the reliance on word-of-mouth marketing, referral programs have become an essential tool for companies looking to expand their reach. However, managing a successful referral program can be a […]
read moreCYBER SECURITY PLATFORM FOR YOUR BUSINESS
Cyber security has become a top priority for individuals and organizations alike. With the ever-evolving threat of cyber attacks, it is imperative to have a strong and reliable security platform in place to protect sensitive information and data. That’s where Bleach Cyber comes in. As a leading cyber security platform, Bleach Cyber is dedicated to […]
read more