{"id":1466,"date":"2018-01-19T19:22:52","date_gmt":"2018-01-19T19:22:52","guid":{"rendered":"http:\/\/goofy-trucks.flywheelsites.com\/using-mysqls-built-in-replication\/"},"modified":"2018-01-19T19:24:48","modified_gmt":"2018-01-19T19:24:48","slug":"using-mysqls-built-in-replication","status":"publish","type":"post","link":"https:\/\/phpbuilder.com\/using-mysqls-built-in-replication\/","title":{"rendered":"Using MySQL’s Built-In Replication"},"content":{"rendered":"
\n
\n
By Michael Tanoviceanu<\/div>\n
on September 12, 2000<\/div>\n<\/p><\/div>\n
\"picture<\/p>\n
\nHere at Soundbreak<\/a> we stream out live audio
\nand video 24 hours a day, so it didn’t take much convincing to be able to run some
\ntesting on MySQL’s new replication feature. We discoverd that we could use it to
\nkeep an up to date standby database server that could handle all of the queries
\nshould the primary fail for any reason. It’s not dificult to configure two servers
\nfor just such a purpose, and I’ll walk through the procedure, as well as a discussion
\nas to how you can use PHP to redirect the queries should the primary server fail.<\/div>\n
\nMySQL’s internal replication is built on a master-slave relationship between two or
\nmore servers, with one acting as the master, and any number acting as slaves. I’ll
\nwalk through configuring two servers as a master slave pair, describing the process
\nas we move through it. I initially performed the procedure outlined below on 3.23.22,
\nand have also tested it on 3.23.23. The MySQL developers recommend that you use the
\nmost recent version, and that both the master and slave use the same version while
\nthe 3.23 series is still in beta as versions may not be backward compatible. I
\ncurrently have not yet used this procedure on a live site for that reason, as one of
\nthe advantages of having fail-over capabilities is to be able to upgrade one server
\nwithout interrupting any of the queries.<\/div>\n

Step One: Configure The Master<\/h2>\n
\nFor the remainder of the article, I’ll refer to two servers, A (10.1.1.1), the primary
\nserver, and B (10.1.1.2), the standby server.<\/div>\n
\nMySQL’s replication is done by having the slave server (B) connect to the master (A)
\nand read the binary update log, incorporating those changes into its own databases.
\nThe slave needs a user account to connect to the master, so on the master (A) create
\nan account with only the FILE privilege with the following:\n<\/div>\n
\n
\nGRANT FILE ON *.* TO replicate@10.1.1.2 IDENTIFIED BY 'password';\n<\/pre>\n<\/div>\n
\nDon’t worry about running ‘FLUSH PRIVILEGES’ on the master to ensure that the slave can connect,
\nas we’ll be stopping the server in the next step.\n<\/div>\n
\nNow we need a snapshot of the codesent data, and to configure the master to start generating binary update logs.
\nFirst edit the ‘my.cnf’ file to enable the binary update log, so somewhere under [mysqld] portion add the line:
\n‘log-bin’.  Now the next time the server starts, we’ll be generating the binary update log (named -bin.
\n).  When you shut down the MySQL server to enable the binary update logging, copy all of the database
\ndirectories of the master to another directory, then restart mysqld  Be sure to get all of the databases, or
\nyou could end up with errors when replicating if a table or database exists on the master but not on the slave.
\nNow you have a snapshot of the data, as well as a binary log of any updates since the snapshot.  Please note
\nthat the MySQL data files (the *.MYD, *.MYI, and *.frm) are file system dependent, so you can’t transfer files
\nfrom Solaris to Linux.  If you are in a heterogeneous server environment you’ll have to use mysqldump or other
\ncustom script to get your data snapshot.
\n<\/rotation><\/hostname><\/div>\n<\/div>\n
<\/p>\n
\n
1<\/div>\n
| <\/div>\n
2<\/a> <\/div>\n
| <\/div>\n
3<\/a> <\/div>\n
| <\/div>\n
4<\/a> <\/div>\n
Next Page \u00bb<\/a><\/div>\n<\/div><\/div>\n","protected":false},"excerpt":{"rendered":"
By Michael Tanoviceanu on September 12, 2000 Here at Soundbreak we stream out live audio and video 24 hours a day, so it didn’t take much convincing to be able to run some testing on MySQL’s new replication feature. We discoverd that we could use it to keep an up… <\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-1466","post","type-post","status-publish","format-standard","hentry","category-tutorials"],"_links":{"self":[{"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/posts\/1466","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/comments?post=1466"}],"version-history":[{"count":1,"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/posts\/1466\/revisions"}],"predecessor-version":[{"id":2245,"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/posts\/1466\/revisions\/2245"}],"wp:attachment":[{"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/media?parent=1466"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/categories?post=1466"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/tags?post=1466"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}