My Notes some of this and some of that

Mysql - rebuild slave from master

Problem: Sometimes mysql master and slave go out of sync, this could happen due to various reasons like master stops writing to binary logs, duplicate entry exists in slave or some other insert/update constraint failure in slave (this could happen when slave database is not read only and some clients connects directly to slave). In such scenarios, the only way to bring back master and slave in sync is to rebuild slave from master. Below are tried and tested steps to rebuild slave from master where the master-slave setup already exists.

Consider this scenario with master and slave server as given below:

master/slave exdap211/exdap212

###Problem on slave

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: exdap211.xyz.in
                  Master_User: slave_sec
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000044
          Read_Master_Log_Pos: 552489846
               Relay_Log_File: mysqld-relay-bin.000041
                Relay_Log_Pos: 390393576
        Relay_Master_Log_File: mysql-bin.000044
             Slave_IO_Running: Yes
            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: 1452
                   Last_Error: Error 'Cannot add or update a child row: a foreign key constraint fails...'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 390393413
              Relay_Log_Space: 552490230
              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: 0
                Last_IO_Error:
               Last_SQL_Errno: 1452
               Last_SQL_Error: Error 'Cannot add or update a child row: a foreign key constraint fails...'
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 100412
                  Master_UUID: f20637be-3530-11e5-a860-005056a05b60
             Master_Info_File: /var/db/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State:
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp: 160322 16:12:05
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
1 row in set (0.00 sec)

###Solution: Rebuild slave

On Master

  • Login to mysql
mysql -u root -p (you will be prompted for password)
  • Check master status
mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000044
         Position: 553540493
     Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
  • Reset master
mysql> reset master;
Query OK, 0 rows affected (1.40 sec)
  • Check master status again
mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 13766
     Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
  • Exit from mysql prompt and start taking dump of master. This will take some time depending on size of database
bring@mysql2qa:~$ mysqldump -uroot -p --all-databases --master-data=1 --single-transaction --quick  > /tmp/dbdump.db
Enter password:
  • Once completed, check the file to be sure
ls -ltrh
-rw-r--r--  1 bring    deploy   2.0G Mar 29 10:03 dbdump.db

###Done with steps on master, now login to slave

  • SCP dump file from master to slave
scp user@exdap211:/var/db/backup/dbdump.db /var/db/backup/dbdump.db
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'exdap211,139.114.173.205' (RSA) to the list of known hosts.
dbdump.db                                                                                                                                       100% 1976MB 164.7MB/s   00:12
  • Check the file to be sure
-rw-r--r--  1 bring    deploy   2.0G Mar 29 10:08 dbdump.db
  • Now login to mysql, stop slave and then reset slave and then exit
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
mysql> reset slave;
Query OK, 0 rows affected (0.25 sec)
mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: exdap211.xyz.in
                  Master_User: slave_sec
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File:
          Read_Master_Log_Pos: 4
               Relay_Log_File: mysqld-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File:
             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: 0
              Relay_Log_Space: 143
              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: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 100412
                  Master_UUID: f20637be-3530-11e5-a860-005056a05b60
             Master_Info_File: /var/db/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State:
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
1 row in set (0.00 sec)
mysql> exit
  • Rebuild slave from dump, this will again take some time
bring@mysql1qa:/tmp$ mysql -uroot -p < /tmp/dbdump.db
Enter password:
  • Now login to mysql again and start slave and check status
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: exdap211.xyz.in
                  Master_User: slave_sec
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 62088
               Relay_Log_File: mysqld-relay-bin.000003
                Relay_Log_Pos: 62251
        Relay_Master_Log_File: mysql-bin.000001
             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: 62088
              Relay_Log_Space: 62425
              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: 100412
                  Master_UUID: f20637be-3530-11e5-a860-005056a05b60
             Master_Info_File: /var/db/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
1 row in set (0.00 sec)

Resolve Mysql Replication Issues - Duplicate Entry

These steps would help in resolving Mysql database replication issues arising out of duplicate entry in slave database.

Step-by-step guide

  • SSH to slave DB system. Example: ssh <user>@<server> (could be different server)

  • Connect to mysql. Example: mysql -u root -p. Note: It will prompt for password.

  • Check slave status. Example: show slave status\G. You might see something like this

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: <master-server.com>
                  Master_User: slave_sec
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000038
          Read_Master_Log_Pos: 865774015
               Relay_Log_File: mysqld-relay-bin.000021
                Relay_Log_Pos: 25092925
        Relay_Master_Log_File: mysql-bin.000038
             Slave_IO_Running: Yes
            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: 1062
                   Last_Error: Error 'Duplicate entry '6213' for key 'PRIMARY'' on query. Default database: 'testdb'. Query: 'insert into orders (created_at, user_id, receiver_id) values ('2016-01-12 08:48:09', null, null)'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 817513358
              Relay_Log_Space: 73353756
              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: 0
                Last_IO_Error:
               Last_SQL_Errno: 1062
               Last_SQL_Error: Error 'Duplicate entry '6213' for key 'PRIMARY'' on query. Default database: 'testdb'. Query: 'insert into orders (created_at, user_id, receiver_id) values ('2016-01-12 08:48:09', null, null)'
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 100412
                  Master_UUID: f20637be-3530-11e5-a860-005056a05b60
             Master_Info_File: /var/db/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State:
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp: 160113 07:58:25
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
1 row in set (0.00 sec)

Note: if slave status returns nothing Empty set (0.00 sec) then you might be on master.

  • Better resolve this and don’t skip as suggested at many places as your slave can go out of sync with master and create problems in future. This might be happening because some application might be pointing to slave DB instead of master, so best practice is to have slave as read only so that applications do not accidentally write to slave DB. Try to locate duplicate entry and delete that entry from slave DB.

  • Once you have deleted the old entry then execute stop slave and then start slave on slave DB.

  • Most probably replication will start again and come back to normal. If it gets stuck again for same error for some other record then repeat same steps. In case there are many such entries then better to reset slave and start replication again. Check slave status again to confirm.

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: <master-server.com>
                  Master_User: slave_sec
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000038
          Read_Master_Log_Pos: 867336894
               Relay_Log_File: mysqld-relay-bin.000021
                Relay_Log_Pos: 57402302
        Relay_Master_Log_File: mysql-bin.000038
             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: 849822735
              Relay_Log_Space: 74916972
              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: 46579
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: 100412
                  Master_UUID: f20637be-3530-11e5-a860-005056a05b60
             Master_Info_File: /var/db/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Reading event from the relay log
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
1 row in set (0.00 sec)

Power of Bower Installer

I like Bower, simply because it does away with visiting individual sites to download latest java scripts. It’s been used mostly for java scripts though it is very generic and can handle any package. Bower is a fine package manager but it has its short comings. The main pain point for me is that it downloads the whole distribution with many files which are not needed for the project. That’s the reason most people dont check-in the bower_components in their repositories.

This is where Bower Installer comes to rescue. I came across this project around a month back and was really excited to use in my projects.

Before Bower Installer, my bower.json used to look like this.

{
  "name": "explore-bower",
  "version": "0.0.1",
  "dependencies": {
    "backbone": "latest",
    "requirejs": "2.1.1",
    "jquery": "2.1.4",
    "bootstrap": "~3.3.5"
  }

After Bower Installer, I had to make some changes to my bower.json

{
  "name": "explore-bower",
  "version": "0.0.1",
  "dependencies": {
    "backbone": "latest",
    "requirejs": "2.1.1",
    "jquery": "2.1.4",
    "bootstrap": "~3.3.5"
  },
  "install": {
    "path" : {
      "css": "src/main/webapp/css",
      "js": "src/main/webapp/js"
    },
    "sources" : {
      "backbone" : [
        "bower_components/backbone/backbone.js",
        "bower_components/backbone/backbone-min.js"
      ],
      "bootstrap" : [
        "bower_components/bootstrap/dist/css/bootstrap.css",
        "bower_components/bootstrap/dist/css/bootstrap.min.css",
        "bower_components/bootstrap/dist/js/bootstrap.js"
      ]
    }
  }
}
  • install is the new section for bower installer
  • with path you can define the destination folder individually for css and js folder.
  • even without sources section, bower installer can move the main files from bower componets to your defined destination folder, but with sources section you get greater control of what and how many files you want to move to your destination folder

Result: I found a very clean way to selectively choose the files that I need and move into my projects.

src
└── main
    └── webapp
        ├── css
        │   └── bootstrap
        │       ├── bootstrap.css
        │       └── bootstrap.min.css
        └── js
            ├── backbone
            │   ├── backbone-min.js
            │   └── backbone.js
            ├── bootstrap
            │   └── bootstrap.js
            ├── jquery
            │   └── jquery.js
            ├── requirejs
            │   └── require.js
            └── underscore
                └── underscore.js

The documentation of this project is very good, for complete reference and usage:

https://github.com/blittle/bower-installer

Liquibase - Brief Intro and best practices

Liquibase is an open source library to track database changes.

What does that mean? We all have worked in projects where the typical database change management process was to write DDL, DML scripts in sql files which were source controlled and database changes were executed by self or by some other dedicated team. But there wasn’t really a nice way to define sequence of these changes, it was followed as best practice in the project. We always had to write sql for rollbacks as well and migration to other database was a painful task.

Liquibase tries to address all these issues in an elegant way. Liquibase scripts are typically written in xml format (may be because xml is more readable), though other formats like json and yaml are also supported. Database changes are defined in change log files as small change sets which are uniquely identified by change set name and author. Sequence is defined in master change log file. Rollbacks are handled in most of the cases by Liquibase itself. And since these scripts are database agnostic, migrations to other databases are relatively smooth. As of now, Liquibase supports most of the popular database including MySQL, Oracle, DB2 and PostgreSQL.

Maven Integration Including Liquibase to Maven projects is pretty easy. Just follow below 2 steps:

Step 1: Add below dependency to include liquibase jar

<dependency>
    <groupId>org.liquibase</groupId>
    <artifactId>liquibase-core</artifactId>
    <version>3.3.2</version>
</dependency>

Step 2: To control liquibase via Maven plugin

<build>
    <plugins>
        <plugin>
            <groupId>org.liquibase</groupId>
            <artifactId>liquibase-maven-plugin</artifactId>
            <version>3.3.2</version>
            <configuration>
                <changeLogFile>
                ${basedir}/src/main/resources/liquibase/master.xml
                </changeLogFile>
                <driver>org.apache.derby.jdbc.ClientDriver</driver>
                <url>jdbc:derby://localhost:1527/message</url>
                <username>app</username>
                <password>app</password>
            </configuration>
        </plugin>
    </plugins>
</build>

changeLogFile element is to define the location of the master change log file which has ordered references of other change log files.

Most frequently used goals:

To execute your database changes: mvn liquibase:update

To rollback changes with count parameter: mvn liquibase:rollback -Dliquibase.rollbackCount=n (rolls back last n change sets)

Project structure example:

alt text

master.xml

<databaseChangeLog>
    <include file="changelog-create-employee-table.xml" 
        relativeToChangelogFile="true"/>
    <include file="changelog-add-employee-data.xml" 
        relativeToChangelogFile="true"/>
</databaseChangeLog>

Change set example (for adding employee table):

<databaseChangeLog>
    <changeSet id="create_employee_table" author="sinhav">
        <createTable tableName="Employee">
            <column name="EmployeeId" type="BIGINT">
                <constraints nullable="false"/>
            </column>
            <column name="FirstName" type="VARCHAR(50)">
                <constraints nullable="true"/>
            </column>
            <column name="LastName" type="varchar(50)">
                <constraints nullable="true"/>
            </column>
            <column name="Phone" type="varchar(50)">
                <constraints nullable="true"/>
            </column>
            <column name="JoiningDate" type="Date">
                <constraints nullable="true"/>
            </column>
        </createTable>
    </changeSet>
    <changeSet id="add_pk_for_employee" author="sinhav">
    <addPrimaryKey columnNames="EmployeeId"
                  constraintName="pk_employee"
                  tableName="Employee" />
    </changeSet>
</databaseChangeLog>

Executing liquibase for the first time will create 2 additional tables in Database: Databasechangelog and Databasechangeloglock

First is to track all executed change sets and second to provide locking mechanism to ensure only one instance of liquibase is running at a time.

Best Practices:

  • Organizing change logs: As shown in the project structure above, select a directory to store all your change log files and define their sequence in master change log file. Liquibase suggests to organize these files by major version but working in a multi developer environment, I would recommend to skip version from file names to avoid conflicts arising to developers choosing same versions. Its better to choose a logical and explanatory name for these change log files which upon first look would give some idea about the type of database changes it contains. E.g. db.changelog-add-queued-messages-table.xml db.changelog-add-retry-column-to-queued-messages-table.xml

  • Change set id and author name: Combination of change set id and author name uniquely identifies a change set. Change set id should be logical and explanatory name and author name should easily identify the developer who created the change set.

  • Pre-condition check for DDL statements: If you are inserting any data which relies on data of some other tables then take care to check it as a pre-condition to ensure your change set is executed successfully across all environments regardless of data discrepancy across environments. E.g.

<changeSet id="insert-subcustomer" author="sinhav">
    <preConditions onFail="MARK_RAN" 
    onFailMessage="There is no customers data">
        <sqlCheck expectedResult="1" >
            select count(1)
            from Customers
            where CustomerNumber = ‘1234567'
        </sqlCheck>
    </preConditions>
    <sql>
        insert into SubCustomers(MasterCustomerId, SubCustomerNumber,
        SupplierName, ApiId) values(
        (select id from Customers where CustomerNumber = ‘1234567'),
        ‘11223344', ’VS Suppliers', ‘TPR871')
    </sql>
    <rollback>
        <delete tableName="SubCustomers">
            <where>SubCustomerNumber='11223344'</where>
        </delete>
    </rollback>
</changeSet>
  • Rollback Mechanism: Liquibase offers rollback for DDL statements but for all DML statement rollback has to be handled explicitly by developer. For example, the above change set of creating employee table will create one record in databasechangelog table for this change set. For rolling back this change, you can execute command mvn liquibase:rollback -Dliquibase.rollbackCount=1, this will delete table and will also remove change set record from databasechangelog table.

If you attempt to rollback a change set which has some DML statement then it will result in liquibase.exception.RollbackImpossibleException so make sure that you handle this rollback for DML statement explicitly in the change sets. Its always a good practice to run liquibase and test that both migration and rollback of your change set works as expected and without any exceptions. You can also have empty rollback blocks in case you don’t want to take any action on rollback.

Example of change set having DML statements along with rollback:

<changeSet id="add_employee_data" author="sinhav">
    <insert tableName="Employee">
        <column name="EmployeeId" type="BIGINT" valueNumeric="10001"/>
        <column name="FirstName">Vishal</column>
        <column name="LastName">Sinha</column>
        <column name="Phone">+47 32324324</column>
        <column name="JoiningDate" valueDate="2004-06-09"/>
    </insert>
    <insert tableName="Employee">
        <column name="EmployeeId" type="BIGINT" valueNumeric="10002"/>
        <column name="FirstName">Nishant</column>
        <column name="LastName">Varshney</column>
        <column name="Phone">+91 9834249399</column>
        <column name="JoiningDate" valueDate="2004-05-11"/>
    </insert>
    <insert tableName="Employee">
        <column name="EmployeeId" type="BIGINT" valueNumeric="10003"/>
        <column name="FirstName">Rajat</column>
        <column name="LastName">Sharma</column>
        <column name="Phone">+91 8342342345</column>
        <column name="JoiningDate" valueDate="2004-07-07"/>
    </insert>
    <rollback>
        <sql>
            delete from Employee
            where EmployeeId in (10001, 10002, 10003)
        </sql>
    </rollback>
</changeSet>

For complete reference:

http://www.liquibase.org/

First Post

Finally, after lot of research, zeroed in on Github Pages for blogging! I wanted something with minimalistic look, high performance and familiarity. Github with Jekyll doing the job in the background seems like a good start. Mapping with custom domain name was super easy and TinyPress for editing post would come handy from time to time.

Github Pages, I have high expectations from you.

Thank you TinyPress for boot strapping this for me.