This is required for rails to direct writes to master and reads to slave database. For details refer to https://github.com/schoefmax/multi_db
sudo gem install schoefmax-multi_db --source http://gems.github.com
Assuming that 2 database servers are setup and both the servers should be listening on a network IP. For detailed steps to setup master/slave refer to http://dev.mysql.com/doc/refman/5.1/en/replication-howto.html
Edit my.cnf and add following entries under mysqld section
server-id = 1 log_bin = /var/log/mysql/mysql-bin.log binlog_do_db = crossbow_development
Add following to my.cnf under mysqld section. This can be any unique number, used to identify the slave.
server-id = 2
In the master Database do the following
CREATE USER 'repl' IDENTIFIED BY 'password'; GRANT REPLICATION SLAVE ON *.* TO repl;
- Stop processing statements on the master and lock the tables by issuing FLUSH TABLES WITH READ LOCK; - mysql> FLUSH TABLES WITH READ LOCK; - Leave the client from which you issued the FLUSH TABLES statement running so that the read lock remains in effect. If you exit the client, the lock is released. - Open mysql session in other window and determine current binary log name and position by issuing
mysql> SHOW MASTER STATUS; This should show data similar to this. Note this information as it will be required when setting up the slave.
| File | Position | Binlog_Do_DB | Binlog_Ignore_D |
|---|---|---|---|
| mysql-bin.000006 | 106 | crossbow_dev |
Do following on the console
- mysqldump -uroot -p crossbow_development --master-data > dbdump.db
- Start the mysql with –skip-slave-start option to prevent the replication from starting.
mysqld --skip-slave-start
- Create the database and import the db dump taken from master
mysql> CREATE DATABASE crossbow_development; mysql> exit
- Restore the data snapshot that was taken from the master
root@root# mysql crossbow_development < dbdump.db
- Setup the slave to communicate to master. The MASTER_LOG_FILE and MASTER_LOG_POS should be the values that were returned by Step 4.
mysql>CHANGE MASTER TO MASTER_HOST='192.168.0.201', MASTER_USER='repl', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=106; mysql>START SLAVE;
Master:
mysql> show master status \G;
*************************** 1. row ***************************
File: cbprd.000211
Position: 754221
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)
Slave:
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 10.166.152.12
Master_User: replcbusr
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: cbprd.000210
Read_Master_Log_Pos: 7840772
Relay_Log_File: proddb-slav01-relay-bin.000424
Relay_Log_Pos: 4
Relay_Master_Log_File: cbprd.000210
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 7840772
Relay_Log_Space: 107
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from impossible position'
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec)
The log file size is quite different, as evident from current log pos!
Also, a look at the log file shows that master and slave are working on two different log files, which explains the size difference.
stop slave;
mysql> stop slave
-> ;
Query OK, 0 rows affected (0.80 sec)
mysqldump -u<user> -p<pass> cbprod > dbbkp_april24.2014.sql
UNLOCK TABLES; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -- Dump completed on 2014-04-25 1:30:08
mysql> show master status; +--------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +--------------+----------+--------------+------------------+ | cbprd.000211 | 819653 | | | +--------------+----------+--------------+------------------+ 1 row in set (0.00 sec)
[root@proddb-slav01 ~]# tail ~/dbbkp_slave_april24_2014.sql /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -- Dump completed on 2014-04-25 1:33:52
#step1 [root@proddb-mast01 ~]# scp ~/dbbkp_april24.2014.sql root@10.166.152.13:/tmp root@10.166.152.13's password: dbbkp_april24.2014.sql 100% 111MB 55.3MB/s 00:02 [root@proddb-mast01 ~] #step2 [root@proddb-slav01 tmp]# mysql -u cbpuser -p cbprod < /tmp/dbbkp_april24.2014.sql Enter password: #step3 [root@proddb-slav01 tmp]# mysqlcheck -u cbpuser -p --databases cbprod Enter password: cbprod.activities OK cbprod.ads OK cbprod.albums OK cbprod.annotations OK cbprod.announcements OK cbprod.answers OK cbprod.assets OK cbprod.attendees OK cbprod.audio_annotations OK cbprod.billing_addresses OK cbprod.bookmarks OK cbprod.carts OK cbprod.categories OK cbprod.category_associations OK cbprod.certificate_templates OK cbprod.certificates OK cbprod.certification_questionnaires OK cbprod.certifications OK cbprod.choices OK cbprod.client_applications OK cbprod.clippings OK cbprod.comments OK cbprod.companies OK cbprod.company_email_domains OK cbprod.contact_us OK cbprod.content_availabilities OK cbprod.content_versions OK cbprod.contents OK cbprod.contests OK cbprod.countries OK cbprod.coupon_usages OK cbprod.coupons OK cbprod.course_contents OK cbprod.course_prerequisites OK cbprod.courses OK cbprod.css_rules OK cbprod.delayed_jobs OK cbprod.disable_company_features OK cbprod.disk_usages OK cbprod.drop_box_associations OK cbprod.enrollments OK cbprod.events OK cbprod.favorites OK cbprod.featured_items OK cbprod.folder_items OK cbprod.folders OK cbprod.follows OK cbprod.footer_contents OK cbprod.forums OK cbprod.friendship_statuses OK cbprod.friendships OK cbprod.group_memberships OK cbprod.group_topics OK cbprod.groups OK cbprod.homepage_features OK cbprod.homepage_highlights OK cbprod.in_person_event_instructors OK cbprod.in_person_events OK cbprod.invitations OK cbprod.invites OK cbprod.likes OK cbprod.live_event_associations OK cbprod.live_events OK cbprod.meetings OK cbprod.merchant_accounts OK cbprod.messages OK cbprod.metro_areas OK cbprod.moderatorships OK cbprod.monitorships OK cbprod.oauth_nonces OK cbprod.oauth_tokens OK cbprod.offerings OK cbprod.orders OK cbprod.pages OK cbprod.panel_positions OK cbprod.payment_transactions OK cbprod.payments OK cbprod.permalinks OK cbprod.photos OK cbprod.plugin_schema_migrations OK cbprod.polls OK cbprod.posts OK cbprod.privileges OK cbprod.privileges_roles OK cbprod.product_coupons OK cbprod.product_groups OK cbprod.product_questions OK cbprod.product_subscription_settings OK cbprod.products OK cbprod.progress_breakups OK cbprod.progress_scores OK cbprod.purchases OK cbprod.question_groups OK cbprod.question_pool_questions OK cbprod.question_pools OK cbprod.question_types OK cbprod.questionnaire_questions OK cbprod.questionnaires OK cbprod.questions OK cbprod.rates OK cbprod.ratings OK cbprod.recommendation_users OK cbprod.recommendations OK cbprod.response_sets OK cbprod.responses OK cbprod.roles OK cbprod.rsvps OK cbprod.sb_posts OK cbprod.schema_migrations OK cbprod.scorm_package_details OK cbprod.scorm_packages OK cbprod.scorm_sessions OK cbprod.selections OK cbprod.sessions OK cbprod.settings OK cbprod.site_custom_plans OK cbprod.site_order_items OK cbprod.site_orders OK cbprod.site_payments OK cbprod.site_subscriptions OK cbprod.site_usages OK cbprod.skills OK cbprod.slugs OK cbprod.sso_settings OK cbprod.states OK cbprod.subscription_plans OK cbprod.subscription_settings OK cbprod.subscriptions OK cbprod.taggings OK cbprod.tags OK cbprod.topics OK cbprod.translations OK cbprod.user_invitations OK cbprod.user_removed_announcements OK cbprod.user_subscription_coupon_details OK cbprod.user_subscription_histories OK cbprod.user_subscriptions OK cbprod.users OK cbprod.voice_extensions OK cbprod.votes OK cbprod.web_links OK cbprod.web_texts OK
mysql> change master to master_log_file='cbprd.000211', master_log_pos=819653; Query OK, 0 rows affected (0.02 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.166.152.12
Master_User: replcbusr
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: cbprd.000211
Read_Master_Log_Pos: 819653
Relay_Log_File: proddb-slav01-relay-bin.000002
Relay_Log_Pos: 249
Relay_Master_Log_File: cbprd.000211
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 819653
Relay_Log_Space: 413
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec)
ERROR:
No query specified