====== Install multi_db gem ======
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
====== Setting Master/Slave environment for Mysql ======
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
===== 1. Setting the Replication Master Configuration =====
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
===== 2. Setting the Replication Slave Configuration =====
Add following to my.cnf under mysqld section. This can be any unique number, used to identify the slave.
server-id = 2
===== 3. Create a user for replication on master =====
In the master Database do the following
CREATE USER 'repl' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO repl;
===== 4. Obtaining the Replication Master Binary Log Coordinates =====
- 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 | |
===== 5. Create a Data Snapshot Using mysqldump =====
Do following on the console
- mysqldump -uroot -p crossbow_development --master-data > dbdump.db
===== 6. Perform following steps on machine where the Slave is to be setup =====
- 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;
====== Issues with broken Master_Slave relationship ======
===== Master ahead of slave @ April 25th, 2014 *COMPLETE, here for future reference*=====
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.
===== Recovery =====
- stop app serversstopped apache, maintenance page.
- stop slave on db slave
stop slave;
mysql> stop slave
-> ;
Query OK, 0 rows affected (0.80 sec)
- Take db backup on master using mysqldump. DB name:cbprod
mysqldump -u -p 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
- record show master values;
mysql> show master status;
+--------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------+----------+--------------+------------------+
| cbprd.000211 | 819653 | | |
+--------------+----------+--------------+------------------+
1 row in set (0.00 sec)
- Take db backup on slave using mysqldump
[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
- Restore Master dump on Slave
#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
- change master with values recorded above, emphasis on log and pos. (similar statement as above but with values current values from master)
mysql> change master to master_log_file='cbprd.000211', master_log_pos=819653;
Query OK, 0 rows affected (0.02 sec)
- start slave
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
- remove maint page
- verify SOLR
- request Parlee to test