{"id":786,"date":"2013-04-04T10:27:11","date_gmt":"2013-04-04T09:27:11","guid":{"rendered":"http:\/\/blogs.silicontechnix.com\/?p=786"},"modified":"2013-04-04T10:45:39","modified_gmt":"2013-04-04T09:45:39","slug":"mysql-replication-how-to","status":"publish","type":"post","link":"https:\/\/blogs.silicontechnix.com\/?p=786","title":{"rendered":"MySQL replication – How-to"},"content":{"rendered":"

MySQL replication is quite straight forward. Here are the setup steps:<\/p>\n

1. Create a user for replication at master.<\/p>\n

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'%' identified by '';<\/pre>\n
2. Copy master data directory to slave.<\/p>\n
For Innodb, use mysqldump with option \u2013single-transaction to get a consistent dump (you can get the log file and position in the dumpfile), or shutdown the source DB to get a cold consistent copy.<\/p>\n
For all other DB engine, following these steps:
\n1) Lock the tables at master<\/p>\n
mysql> FLUSH TABLES WITH READ LOCK;\r\nQuery OK, 0 rows affected (0.00 sec)\r\n\r\nmysql> show master status;\r\n+----------------+----------+--------------+------------------+\r\n| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |\r\n+----------------+----------+--------------+------------------+\r\n| bin-log.000074 | 915 | | |\r\n+----------------+----------+--------------+------------------+\r\n1 row in set (0.00 sec)<\/pre>\n
\u2013Copy the log file name and position number, it will be used later<\/p>\n
2) Copy the data over<\/p>\n
$ rsync -avz \/mysql\/data :\/mysql\/data<\/pre>\n
Remove file master.info and relay-log.info if you build the slave from Master.<\/p>\n
3) After the copy is done, unlock tables at Master:<\/p>\n
mysql> unlock tables;\r\nQuery OK, 0 rows affected (0.00 sec)<\/pre>\n
or you can exist the session which will release the lock as well.<\/p>\n
3. Setup slave<\/p>\n
If slave binary is not setup, you can also copy them from master.<\/p>\n
1) Startup mysql
\na. Remember change the server_id to a different value in my.cnf
\nb. Binary logging is required for replication. And relay-log is recommended.<\/p>\n
log-bin=mysql-bin\r\nrelay-log = relay-log\r\nrelay-log-index = relay-log.index<\/pre>\n
2) Set master<\/p>\n
mysql> change master to\r\n-> master_host='blogs.silicontechnix.com',\r\n-> master_user='repluser',\r\n-> master_password='',\r\n-> master_port = 3306,\r\n-> master_log_file='bin-log.000074',\r\n-> master_log_pos=915;\r\nQuery OK, 0 rows affected (0.00 sec)\r\n--use the log file name and postion we got previously<\/pre>\n
mysql> start slave;\r\nQuery OK, 0 rows affected (0.00 sec)<\/pre>\n
mysql> show slave status\\G;\r\n*************************** 1. row ***************************\r\nSlave_IO_State: Waiting for master to send event\r\nMaster_Host: blogs.silicontechnix.com\r\nMaster_User: repluser\r\nMaster_Port: 3306\r\nConnect_Retry: 60\r\nMaster_Log_File: bin-log.000074\r\nRead_Master_Log_Pos: 1199\r\nRelay_Log_File: relay-log.000003\r\nRelay_Log_Pos: 533\r\nRelay_Master_Log_File: bin-log.000074\r\nSlave_IO_Running: Yes\r\nSlave_SQL_Running: Yes\r\nReplicate_Do_DB:\r\nReplicate_Ignore_DB:\r\nReplicate_Do_Table:\r\nReplicate_Ignore_Table:\r\nReplicate_Wild_Do_Table:\r\nReplicate_Wild_Ignore_Table:\r\nLast_Errno: 0\r\nLast_Error:\r\nSkip_Counter: 0\r\nExec_Master_Log_Pos: 1199\r\nRelay_Log_Space: 682\r\nUntil_Condition: None\r\nUntil_Log_File:\r\nUntil_Log_Pos: 0\r\nMaster_SSL_Allowed: No\r\nMaster_SSL_CA_File:\r\nMaster_SSL_CA_Path:\r\nMaster_SSL_Cert:\r\nMaster_SSL_Cipher:\r\nMaster_SSL_Key:\r\nSeconds_Behind_Master: 0\r\nMaster_SSL_Verify_Server_Cert: No\r\nLast_IO_Errno: 0\r\nLast_IO_Error:\r\nLast_SQL_Errno: 0\r\nLast_SQL_Error:\r\n1 row in set (0.00 sec)<\/pre>\n
Now the Master-Slave replication is setup, is it easy \ud83d\ude42<\/p>\n
In addition,<\/p>\n
A. If you want to setup Master-Master replication, continue the following steps:
\nAt slave :<\/p>\n
mysql> show master status;\r\n+----------------+----------+--------------+------------------+\r\n| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |\r\n+----------------+----------+--------------+------------------+\r\n| bin-log.000077 | 106 | | |\r\n+----------------+----------+--------------+------------------+\r\n1 row in set (0.00 sec)<\/pre>\n
At master:<\/p>\n
mysql> change master to\r\n-> master_host='blogs.silicontechnix.com',\r\n-> master_user='repluser',\r\n-> master_password='',\r\n-> master_port = 3306,\r\n-> master_log_file='bin-log.000077',\r\n-> master_log_pos=106;\r\nQuery OK, 0 rows affected (0.03 sec)<\/pre>\n
mysql> start slave;\r\nQuery OK, 0 rows affected (0.00 sec)<\/pre>\n
B. If you want to setup a slave from an existing slave, using the following steps:<\/p>\n
* Shutdown the existing slave db<\/p>\n
* Copy the data to new slave:<\/p>\n
$ rsync -avz \/mysql\/data <target_host>:\/mysql\/data<\/pre>\n
–Keep file master.info and relay-log.info if you build the slave from another slave.<\/p>\n
* Startup mysql at new slave.
\nmake sure it uses a different server_id in my.cnf<\/p>\n
(cd $MYSQL_BASE; .\/bin\/mysqld_safe &)\r\nstart slave;<\/pre>\n
–Do NOT need to run “change master to …” statement as it already in master.info and relay-log.info<\/p>\n","protected":false},"excerpt":{"rendered":"
MySQL replication is quite straight forward. Here are the setup steps:<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_s2mail":"no","jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","enabled":false},"version":2}},"categories":[95],"tags":[318,348,350,18,347,345,346,349],"class_list":["post-786","post","type-post","status-publish","format-standard","hentry","category-system-administration","tag-backup","tag-master","tag-my-cnf","tag-mysql","tag-mysqldump","tag-replication","tag-rsync","tag-slave"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p12j6H-cG","_links":{"self":[{"href":"https:\/\/blogs.silicontechnix.com\/index.php?rest_route=\/wp\/v2\/posts\/786","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blogs.silicontechnix.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blogs.silicontechnix.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blogs.silicontechnix.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blogs.silicontechnix.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=786"}],"version-history":[{"count":8,"href":"https:\/\/blogs.silicontechnix.com\/index.php?rest_route=\/wp\/v2\/posts\/786\/revisions"}],"predecessor-version":[{"id":795,"href":"https:\/\/blogs.silicontechnix.com\/index.php?rest_route=\/wp\/v2\/posts\/786\/revisions\/795"}],"wp:attachment":[{"href":"https:\/\/blogs.silicontechnix.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=786"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blogs.silicontechnix.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=786"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blogs.silicontechnix.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=786"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}