$\"MySQL$ <\/a>
MySQL Add slave replication user<\/p><\/div>\n
Enter all details (username, password)<\/p>\n
$\"MySQL$ <\/a>
MySQL Add slave replication user details<\/p><\/div>\n
$\"MySQL$ <\/a>
MySQL Add slave replication user list<\/p><\/div>\n
This is the account that will be used on the slaves to connect to the master to authenticate and replicate the tables.<\/p>\n
Export Database<\/em><\/p>\n
According to the instructions from Mysql.com you need to flush the tables with read lock. To do this start mysql and enter:<\/p>\n
mysql> FLUSH TABLES WITH READ LOCK;<\/code><\/strong><\/pre>\nNow if your database is empty I don’t believe you need to run this step.<\/p>\n Then you need to export the database by using the following command:<\/p>\nshell> mysqldump --all-databases --master-data > dbdump.db<\/code><\/strong><\/pre>\nIt’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<\/p>\nshell> mysqldump --all-databases --master-data | gzip > dbdump.db<\/code><\/strong><\/pre>\nThat’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.<\/p>\n 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.<\/p>\n<\/a>MySQL Show Connected slaves<\/p><\/div>\n <\/p>\n <\/strong>On the slave server<\/strong><\/p>\n You will need to import the mysql dump. So follow these steps<\/p>\n\nStart the slave, using the\u00a0--skip-slave-start<\/code><\/a>\u00a0option so that replication does not start.<\/li>\n Import the dump file:\nshell> mysql < fulldb.dump<\/code><\/strong><\/pre>\n<\/li>\n<\/ol>\n <\/p>\n Login to myphpadmin and click the replication button at the top.<\/p>\n Click configure<\/p>\n<\/a>mysql slave replication configure<\/p><\/div>\n Enter the details that was used on the master server.<\/p>\n\n\n<\/a><\/dt>\n mysql slave replication configure settings<\/dd>\n<\/dl>\n<\/div>\nClick Go and check the slave status<\/p>\n\n<\/a>mysql slave replication check slave status<\/p><\/div>\n<\/div>\n Once you have added the master settings you will have the option to check the master status<\/p>\n Add\u00a0\u00a0–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.<\/p>\n 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.<\/p>\n NOTE: I was getting some duplicate 1062 errors when Importing the dump file, so I just added this line to the my.ini file.<\/strong><\/p>\n slave-skip-errors = 1062<\/p>\n <\/p>\n DO YOU LIKE WHAT YOU'VE READ?<\/div>Join our subscription list and receive our content right in your mailbox. If you like to receive some Great deals our Freebies then subscribe now!\r\n\r\n \n\n\nName<\/label>\n<\/div>\nEmail<\/label>\n<\/div>\n\n<\/div>\n<\/form>\n<\/div>\n<\/p>\r\n\r\n<\/div><\/div>\n<\/div>","protected":false},"excerpt":{"rendered":"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 […]<\/p>\n","protected":false},"author":1,"featured_media":2206,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-2177","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-technology"],"_links":{"self":[{"href":"https:\/\/www.businesslegions.com\/blog\/wp-json\/wp\/v2\/posts\/2177","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.businesslegions.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.businesslegions.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.businesslegions.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.businesslegions.com\/blog\/wp-json\/wp\/v2\/comments?post=2177"}],"version-history":[{"count":6,"href":"https:\/\/www.businesslegions.com\/blog\/wp-json\/wp\/v2\/posts\/2177\/revisions"}],"predecessor-version":[{"id":2927,"href":"https:\/\/www.businesslegions.com\/blog\/wp-json\/wp\/v2\/posts\/2177\/revisions\/2927"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.businesslegions.com\/blog\/wp-json\/wp\/v2\/media\/2206"}],"wp:attachment":[{"href":"https:\/\/www.businesslegions.com\/blog\/wp-json\/wp\/v2\/media?parent=2177"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.businesslegions.com\/blog\/wp-json\/wp\/v2\/categories?post=2177"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.businesslegions.com\/blog\/wp-json\/wp\/v2\/tags?post=2177"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}