AuditWiki

Edit the page and hit Save. See syntax for Wiki syntax. Please edit the page only if you can improve it. If you want to test some things, learn to make your first steps on the playground.

====== 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
<code>
sudo gem install schoefmax-multi_db --source http://gems.github.com
</code>

====== 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
<code>
server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log
binlog_do_db            = crossbow_development
</code>

===== 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.
<code>
server-id               = 2
</code>

===== 3. Create a user for replication on master =====
In the master Database do the following

<code>
CREATE USER 'repl' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO repl;
</code>

===== 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.

===== 5. Create a Data Snapshot Using mysqldump =====

Do following on the console
<code>
- mysqldump -uroot -p crossbow_development --master-data > dbdump.db
</code>

===== 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.
<code>
mysqld --skip-slave-start
</code>

- Create the database and import the db dump taken from master
<code>
mysql> CREATE DATABASE crossbow_development;
mysql> exit
</code>

- Restore the data snapshot that was taken from the master
<code>
root@root# mysql crossbow_development < dbdump.db
</code>

- 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.
<code> 
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;
</code>

====== Issues with broken Master_Slave relationship ======
===== Master ahead of slave @ April 25th, 2014 *COMPLETE, here for future reference*=====

Master:
<code>
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)
</code>

Slave:
<code>
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)

</code>

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 =====
  - <del>stop app servers</del>stopped apache, maintenance page.
  - <del>stop slave on db slave</del> 
<code>stop slave;

mysql> stop slave
    -> ;
Query OK, 0 rows affected (0.80 sec)
</code>
  - <del>Take db backup on master using mysqldump. DB name:cbprod</del> 
<code>
mysqldump -u<user> -p<pass> cbprod > dbbkp_april24.2014.sql
</code>
<code>

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
</code>
  - record show master values;
<code>
mysql> show master status;
+--------------+----------+--------------+------------------+
| File         | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------+----------+--------------+------------------+
| cbprd.000211 |   819653 |              |                  |
+--------------+----------+--------------+------------------+
1 row in set (0.00 sec)
</code>
  - <del>Take db backup on slave using mysqldump</del>
<code>
[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
</code>
  - <del>Restore Master dump on Slave</del>
<code>
#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

</code>
  -<del> change master with values recorded above, emphasis on log and pos. (similar statement as above but with values current values from master)</del>
<code>
mysql> change master to master_log_file='cbprd.000211', master_log_pos=819653;
Query OK, 0 rows affected (0.02 sec)
</code>
  - <del>start slave</del>
<code>
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
</code>
  - <del>remove maint page</del>
  - <del>verify SOLR</del>
  - <del>request Parlee to test</del>

Edit summary

Note: By editing this page you agree to license your content under the following license: CC Attribution-Share Alike 4.0 International

AuditWiki

User Tools

Site Tools

Page Tools