Java Notes-3


    • You need a boolean in the if condition.
    • An overriding method only needs to declare that it can throw a subset of the exceptions the overriden method can throw. Having no throws clause in the overriding method is OK.
    • Normal execution will not resume if an exception is uncaught by a method. The exception will propagate up the invocation stack until some method handles it. If no one handles it then the that thread will terminate.
    • The expression (a = b) does not compare the variables a and b, but rather assigns the value of b to the variable a. The result of the expression is the value being assigned. Since a and b are boolean variables, the value returned by the expression is also boolean. This allows the expressions to be used as the condition for an if-statement.
    • “if-clause and the else-clause can have empty statements. Empty statement ( ie. just ; ) is a valid statement.
      But this is illegal :
      if (true) else;
      because the if part doesn’t contain any valid statement. ( A statement cannot start with an else!)
      So, the following is valid.
      if(true) if(false);
      because if(false); is a valid statement.

Java™ Application Development on Linux® – Free 599 Page eBook

Enterprise Java Virtualization:

Understanding the TCO Implications

InfoWorld’s Java IDE Comparison Strategy Guide:

Java Essential Training

Apache Jakarta Commons: Reusable Java™ Components

Enabling Rapid ROI: With Java™ – Based Business Intelligence Applications:

  • Read the questions carefully. This is very important. Questions are easy but you need to read them carefully.
  • The type of the switch expression (ie. in switch( i ), the type of i )must be char, byte, short, or int, or a compile-time error occurs.
  • The body of a switch statement must be a block. Any statement immediately contained by the block may be labeled with one or more case or default labels. These labels are said to be associated with the switch statement, as are the values of the constant expressions in the case labels.
  • “Every case constant expression associated with a switch statement must be assignable to the type of the switch
    Expression. ie. if the switch expression is of type byte then all the case constants must fit in a byte.(eg. you can’t you 200 as a case value)”
  • No two of the case constant expressions associated with a switch statement may have the same value.
  • At most, one default label may be associated with the same switch statement.
  • “public class TestClass
    {
    public static void main(String args[])
    {
    Exception e = null;
    throw e;
    }
    }”
    You are throwing an exception and there is no try catch block or a throws clause. So it will not compile. If you do that (ie. either put a try catch block or declare a throws clause) then at run time it will throw a null pointer exception as e is null.
    A method that throws an exception either must declare it in throws clause or put the code that throws the exception in try/catch block.
  • Every case constant expression in a switch block must be assignable to the type of switch expression. Meaning :
    “byte by = 10;
    switch(by)
    {
    200 : //some code;
    300 : //some code;
    }
    This will not compile as 300 is not assignable to ‘by ‘ which can only hold values from -128 to 127.

    The use of break keyword is not mandatory, and without it the control will simply fall through the labels of the switch statement.
  • “A break without a label breaks the current loop (ie. no iterations any more) and a break with a label tries to pass the control to the given label.
    ‘Tries to’ means that if the break is in a try block and the try block has a finally clause associated with it then it will be executed.”
  • U can apply a label to any block but not like loop: int x=0;
  • “This is the hierarchy:
    java.lang.Object
    |
    +–java.lang.Throwable
    |
    +–java.lang.Exception, java.lang.Error
  • So the method can even give Throwable ( but not Object !) in throws statement”
  • With a try, either of (multiple) catch and (only one) finally or both can occur. finally must occur only in the end. A try MUST be followed by atleast a catch or finally.
  • The break statement is to break out of any loop completely. So the current iteration and any other remaining iterations of the loop will not happen. Control is transferred to the first statement below the loop.
  • Name of the file is not passes in the args
  • if finally is throwing an Null pointer exception which is RuntimeException so there is no need to handle it or declare it in the throws clause.
  • It is not possible to break out of an if statement. But if the if statement is placed within a labeled block or a switch statement or a loop construct, the usage of break in option 3 would be valid.
  • for (int i=5; i=0; i–) { }
    uses ‘=’ instead of ‘==’ for condition which is invalid. The loop condition must be of type boolean
  • int j=5;
    for(int i=0, j+=5; i uses ‘j +=5′. Now, this statement is preceeded by ‘int i=0,’ and that means we are trying to declare variable j . But it is already declared before the for loop. If we remove the int in the initialization part and declare i before the loop then it will work.But if we remove the statement int j = 5; it will not work because compiler will try to do j = j+5 and you can’t use the variable before it is initialized. Although the compiler gives a message ‘Invalid declaration’ for j += 5 but it really means the above mentioned thing.
  • “int i, j;
    for (j=10; iThrowable – > Exception -> MyException.”
  • You can declare anything that is a Throwable or a subclass of Throwable, in the throws clause.
  • “while (false) { x=3; } in a compile-time error because the statement x=3; is not reachable;
    but the superficially similar case:
    if (false) { x=3; }
    does not result in a compile-time error. An optimizing compiler may realize that the statement x=3; will never be executed and may choose to omit the code for that statement from the generated class file, but the statement x=3; is not regarded as “”unreachable”" in the technical sense .
    The rationale for this differing treatment is to allow programmers to define “”flag variables”" such as:
  • static final boolean DEBUG = false;
  • and then write code such as:
    if (DEBUG) { x=3; }
    The idea is that it should be possible to change the value of DEBUG from false to true or from true to false and then compile the code correctly with no other changes to the program text. This ability to “”conditionally compile”" has a significant impact on, and relationship to, binary compatibility . If a set of classes that use such a “”flag”" variable are compiled and conditional code is omitted, it does not suffice later to distribute just a new version of the class or interface that contains the definition of the flag. A change to the value of a flag is, therefore, not binary compatible with preexisting binaries.
  • This is valid : for( int i = 0; i< 0; i++) x = 3; but this is not : for( int i = 0; false; i++) x = 3;
  • “The Exception that is thrown in the last, gets thrown by the method.
    So, When no exception or any exception is thrown at line 1, the control goes to finally or some catch block. Now, even if the catch blocks throw some exception, the control goes to finally. The finally block throws CloneNotSupportedException, so the whole method ends up throwing CloneNotSupportedException.”
  • “All the arrays are initialized to contain the default values of their type. This means,
    int[] iA = new int[10]; will contain 10 ints having 0.
    Object[] oA = new Object[10]; will contain 10 object references pointing to null.
    boolean[] bA = new boolean[10] will contain 10 booleans of value ‘false’.
    So, as bA[0] is false, the if condition fails and str remains 111.”
  • “If no args are given a Zero length string array is received in the main method. So, there is no NullPointerException on accessing args even if no args are given.
    Indexing in java starts from zero. so the last element will be at args.length -1.”
  • Runnable r=new Thread(); is allowed since Thread implements runnable interface
  • To make a class abstract, you only need to mark it as abstract as in option4. You don’t neccessorily need to put an abstract method.
  • java and classname are not part of the args array.
  • String is not a keyword , it’s a normal Class
  • “You cannot have two methods with the same name in one class.
    Also, even if you put one sayHello() method in other class which is a subclass of this class, it won’t compile because you cannot override/hide a static method with a non static method and vice versa.”
  • Objects are created from class definitions that implement abstractions. The objects that are created are concrete realizations of those abstractions.
  • “Note that Arrays are Objects (ie. cA instanceof Object is true) so are passed by reference. So in m1() the change in cA[1] done by m2() is reflected everywhere the array is used.
    c is a primitive type and is passed by value. In method m2() the passed parameter c is different than instance variable ‘c’ as local variable hides the instance variable. So instance member ‘c’ keeps it’s default (i.e 0) value.
  • Note that both equals() and hashcode() methods can be overridden by the programmer so you can’t say how they work.
  • “public class TestClass
    {
    public static void main(String args[ ] )
    {
    Object a, b, c ;
    a = new String(“”A”");
    b = new String(“”B”");
    c = a;
    a = b;
    System.out.println(“”"”+c);
    }
    }”
    “The variables a, b and c contain references to actual objects. Assigning to a refrence only changes the refrence value, and not the object pointed to by it. So, when c = a is executed c starts pointing to the string object containing A. and when a = b is executed, a starts pointing to the string object containing B but the object containing A still remains same and c still points to it. So the program prints A and not B.
  • For the class that does not define toString() method, the inherited toString() method ( from java.lang.Object) is called. This method returns a string consisting of the name of the class of which the object is an instance, the at-sign character `@’, and the unsigned hexadecimal representation of the hash code of the object. In other words, this method returns a string equal to the value of :
    getClass().getName() + ‘@’ + Integer.toHexString(hashCode())
  • But in this case, String class overrides the toString() method that returns the actual string.
  • “Theoratically, java supports Pass by Value for everything ( ie. primitives as well as Objects).
    . Primitives are always passed by value.
    . Object “”references”" are passed by value. So it looks like the object is passed by reference but actually it’s the value of the reference that is passed.
  • A crude example:
    Object o1 = new Object(); //say, the object is stored at memory location 15000. So o1 actually contains 15000.
  • now, when you call someMethod(o1); the value 15000 is passed.
  • Inside the method someMethod
    someMethod( Object localVar)
    {
    ……localVar contains 15000 so, whatever method you call or modication you do to this varibale, it is done on the original object. But when you try to change it’s value, for eg. make it null, it then contains 000000 (say). But the original variable o1 still contains 15000 so it still points to the same object.
    }
  • In the above question, the local variable is what is changed the original variable ie.someInt remains unchanged.
  • “The toString method for class Object returns a string consisting of the name of the class of which the object is an instance, the at-sign character `@’, and the unsigned hexadecimal representation of the hash code of the object. In other words, this method returns a string equal to the value of:
  • getClass().getName() + ‘@’ + Integer.toHexString(hashCode())”
  • There no unsigned keyword in java!
  • “Java implements abstractions using classes that have properties and behavior. Behavior is dictated by the operations of the abstraction. In Java the operations are defined in classes using methods.
  • “All member fields (static and non-static) are initialized to their default values.
    Default values are: objects are initilized to null (String is also an object), numeric types to 0 (or 0.0 ) and boolean to false.
  • Note that if no argument is passed the args parameter is NOT null but a valid non-null string array of length zero.
  • “As the question says, “”…an instance of the class is not needed…”", the method has to be static.
    Also, as the question does not say that other packages should not have access to the method so public or protected is also correct. But it is better to choose option 2 as it is most appropriate.
    Also as you are asked to select only 1 option,it is better to select option 2 other wise you can select other options also.”
  • “Non-static inner classes can contain final static fields (but not methods).
    Anonymous classes cannot have explict constructors, since they have no names.
  • A static inner class is also known as A Top Level Nested class. So,there are two types of Top level classes. One, that is a standard class and other an inner class which is static. Eg.
    public class A //This is a standard Top Level class.
    {
  • class X
    {
    static final int j = 10; //compiles fine!
    }
    public static class B //This is also a Top Level class (but nested!)
    {
    }
    }
    You can create objects of B with having objects of A. Eg. A.B b = new A.B();
  • Members in outer instances are directly accessible using simple names. There is no restriction that member variables in inner classes must be final.
    Nested classes define distinct types from the enclosing class, and the instanceof operator does not take of the outer instance into consideration.”
  • Altough nonsensical, an empty file is a valid source file. A source file can contain optional package declaration, any number of import statments and any number of class and interface definitions.(only one class/interface in a file can be public though.)
  • An instance member belongs to a single instance, not the class as a whole. An instance member is a member variable or a member method that belongs to a specific object instance. All non-static members are instance members.
  • “The following is a valid code…
  • abstract class SomeClass { public abstract void m1(); }
    public class TestClass
    {
    public static SomeClass getSomeClass() //note static
    {
    return new SomeClass()
    {
    public void m1() { }
    };
    }
    }
  • Another important point to note here is : Contrary to common belief, Anonymous class declared/defined/instantiated in a static method is NOT static. In fact, you can verify it by doing: javap TestClass$1

    Other interesting FAQs related Posts on Java, you may like:

    Java Notes – 1 (clean-clouds.com)

    Java Notes – 2 (clean-clouds.com)

    Java Notes – 3 (clean-clouds.com)

    Java Notes -4 (clean-clouds.com)

    Java Notes -5 (clean-clouds.com)

    Java Notes -6 (clean-clouds.com)

    Frequently Asked Questions in Java Part- 1 (clean-clouds.com)

    Frequently Asked Questions in Java Part- 2 (clean-clouds.com)

    Frequently Asked Questions in Java Part-3 (clean-clouds.com)

    Frequently Asked Questions in Java Part-4 (clean-clouds.com)

    Frequently Asked Questions in Java Part-5 (clean-clouds.com)

    Frequently Asked Questions in Java Part-6 (clean-clouds.com)

    Frequently Asked Questions in Java Part-7 (clean-clouds.com)

    Frequently Asked Questions in Java Part-8 (clean-clouds.com)

    Frequently Asked Questions in Java Part-9 (clean-clouds.com)

    Frequently Asked Questions in Java Part-10 (clean-clouds.com)

    Frequently Asked Questions in Java Part-11 (clean-clouds.com)

    Frequently Asked Questions in Java Part-12 (clean-clouds.com)

    Frequently Asked Questions in Java Part-13 (clean-clouds.com)

    Frequently Asked Questions in Java Part-14 (clean-clouds.com)

Single Sign-On for Java and Web Applications

Bulletproof Java Code: A Practical Strategy for Developing Functional, Reliable, and Secure Java Code

Transforming a Generic Java IDE to Your Application Specific IDE:

The Java Virtual Appliance—No OS Required

BEA WebLogic® Operations Control: Application Virtualization for Enterprise Java

Enabling Rapid ROI: With Java™ – Based Business Intelligence Applications:

About these ads

Incremental Backup in MySQL


To get a consistent backup, do a LOCK TABLES on the relevant tables, followed by FLUSH TABLES for the tables.

You need only a read lock; this allows other clients to continue to query the tables while you are making a copy of the files in the database directory.

The FLUSH TABLES statement is needed to ensure that the all active index pages are written to disk before you start the backup.

mysqldump is more general because it can back up all kinds of tables.
LOCK TABLES and UNLOCK TABLES Syntax
MySQL enables client sessions to acquire table locks explicitly for the purpose of cooperating with other sessions for access to tables, or to prevent other sessions from modifying tables during periods when a session requires exclusive access to them.

A session can acquire or release locks only for itself. One session cannot acquire locks for another session or release locks held by another session.

LOCK TABLES explicitly acquires non-transactional or transactional table locks for the current client session. Table locks can be acquired for base tables or views. You must have the LOCK TABLES privilege, and the SELECT privilege for each object to be locked.

UNLOCK TABLES explicitly releases non-transactional table locks held by the current session. Transactional table locks are released by ending the current transaction.

A table lock protects only against inappropriate reads or writes by other sessions. The session holding the lock can perform table-level operations such as DROP TABLE.

Rules for acqui sition of non-transactional locks.

MySQL supports non-transactional read and write table locks. These can be acquired in non-transactional contexts (that is, when autocommit is enabled).

READ [LOCAL] lock:

  • The session that holds the lock can read the table (but not write it).
  • Multiple sessions can acquire a READ lock for the table at the same time.
  • Other sessions can read the table without explicitly acquiring a READ lock.
  • The LOCAL modifier enables concurrent inserts by other sessions to proceed while the lock is held. (See Section 7.3.3, “Concurrent Inserts”.)

[LOW_PRIORITY] WRITE lock:

  • The session that holds the lock can read and write the table.
  • Only the session that holds the lock can access the table. No other session can access it until the lock is released.
  • Lock requests for the table by other sessions block while the WRITE lock is held.
  • The LOW_PRIORITY modifier affects lock scheduling if the WRITE lock request must wait, as described later.

A session cannot hold non-transactional locks and use transactions at the same time. Acquisition of a non-transactional lock implicitly commits any active transaction for the current session, and beginning a transaction implicitly releases all locks held by the session.

WRITE locks normally have higher priority than READ locks to ensure that updates are processed as soon as possible. This means that if one session obtains a READ lock and then another session requests a WRITE lock, subsequent READ lock requests wait until the session that requested the WRITE lock has obtained the lock and released it.

A request for a LOW_PRIORITY WRITE lock, by contrast, allows subsequent READ lock requests by other sessions to be satisfied first if they occur while the LOW_PRIORITY WRITE request is waiting.

Rules for acquisition of transactional locks

As of MySQL 6.0.3, MySQL supports transactional shared and exclusive table locks that do not commit transactions automatically.

These locks apply only for transactional storage engines that support them and only during a transaction.

Currently, only InnoDB supports transactional locks. For other transactional storage engines or for non-transactional storage engines, requests for transactional locks are converted to requests for non-transactional locks.

IN SHARE MODE [NOWAIT] lock:

  • The session that holds the lock can read the table, and can also write the table under some circumstances.
  • Multiple sessions can acquire an IN SHARE MODE lock for the table at the same time.
  • Other sessions can read the table without explicitly acquiring an IN SHARE MODE lock.

IN EXCLUSIVE MODE [NOWAIT] lock:

  • The session that holds the lock can read and write the table.
  • Only the session that holds the lock can access the table. No other session can access it until the lock is released.
  • Lock requests for the table by other sessions block while the IN EXCLUSIVE MODE lock is held.

A session that requires transactional locks need not acquire them all in a single LOCK TABLES statement.

A session can acquire transactional locks sequentially with multiple LOCK TABLES statements, each one adding new locks to the current set of locks.

It is even possible to acquire additional transactional locks on a table for which the session already holds transactional locks.

A session that holds transactional locks can access non-locked tables while the locks are held.

Transactional locks are not specific to reading or writing. Both operations are allowed to the holder of the lock, whether it is shared or exclusive, with some restrictions:

  • The holder of an IN EXCLUSIVE MODE lock has exclusive access to read and write the table and no other session can lock the table.
  • The holder of an IN SHARE MODE lock has shared access to read the table. The lock holder can also write the table, as long as no other session also has a shared lock for the table. If a session that holds a shared lock has written to the table, no other session can acquire a lock for the table.

Transactional locks do not apply if a session is not in transactional context; that is, when autocommit mode is enabled because the session has not used START TRANSACTION or SET autocommit = 0.

Rules for Lock Release

When the table locks held by a session are released, they are all released at the same time. A session can release its locks explicitly, or locks may be released implicitly under certain conditions.

Rules for lock release when a session holds non-transactional locks:

  • A session can release its locks explicitly with UNLOCK TABLES.
  • If a session issues a LOCK TABLES statement to acquire a lock while already holding non-transactional locks, its existing locks are released implicitly before the new locks are granted.
  • If a session begins a transaction, an implicit UNLOCK TABLES is performed, which causes existing locks to be released.
  • If the connection for a client session terminates, the server releases the session’s locks.
  • UNLOCK TABLES does not release transactional locks.
  • Ending a transaction explicitly, by either COMMIT or ROLLBACK, releases existing locks.
  • Beginning a transaction implicitly commits the current transaction, which releases existing locks.
  • If the session issues a LOCK TABLES request for a non-transactional lock, that implicitly commits the current transaction, which releases existing locks.
  • Any other statement that causes an implicit commit releases the existing locks. For a list, see Section 12.4.3, “Statements That Cause an Implicit Commit”.
  • If the connection for a client session terminates, the server implicitly rolls back the current transaction and releases the session’s locks.

Rules for lock release when a session holds transactional locks:

FLUSH

The FLUSH statement clears or reloads various internal caches used by MySQL. To execute FLUSH, you must have the RELOAD privilege.

mysqldump — A Database Backup Program

The mysqldump client is a backup program originally written by Igor Romanenko. It can be used to dump a database or a collection of databases for backup

If you are doing a backup on the server and your tables all are MyISAM tables, consider using the mysqlhotcopy instead because it can accomplish faster backups and faster restores.

If you do not name any tables following db_name or if you use the --databases or --all-databases option, entire databases are dumped.

mysqldump does not dump the INFORMATION_SCHEMA database. If you name that database explicitly on the command line, mysqldump silently ignores it.

mysqldump can retrieve and dump table contents row by row, or it can retrieve the entire content from a table and buffer it in memory before dumping it. Buffering in memory can be a problem if you are dumping large tables. To dump tables row by row, use the --quick option (or --opt, which enables --quick). The --opt option (and hence --quick) is enabled by default in MySQL 6.0; to enable memory buffering, use --skip-quick.

The Binary Log

Binary Logging Formats

A number of different logging formats are used to record information in the binary log. The exact format employed depends on the version of MySQL being used. There are three logging formats:

  • Replication capabilities in MySQL originally were based on propagation of SQL statements from master to slave. This is called statement-based logging. You can cause this format to be used by starting the server with --binlog-format=STATEMENT.
  • In row-based logging, the master writes events to the binary log that indicate how individual table rows are affected. You can cause the server to use row-based logging by starting it with --binlog-format=ROW.
  • A third option is also available: mixed logging. With mixed logging, statement-based logging is used by default, but the logging mode switches automatically to row-based in certain cases as described below. You can cause MySQL to use mixed logging explicitly by starting mysqld with the option --binlog-format=MIXED.

Mixed logging is the default logging mode in MySQL 6.0.

You can force the replication format by starting the MySQL server with --binlog-format=type. When set, all replication slaves connecting to the server will read the events according to this setting. The supported values for type are:

  • ROW causes replication to be row-based.
  • STATEMENT causes replication to be statement-based.
  • MIXED causes replication to use mixed format.

The logging format also can be switched at runtime. To specify the format globally for all clients, set the global value of the binlog_format system variable.

To switch to statement-based format, use either of these statements:

mysql> SET GLOBAL binlog_format = 'STATEMENT';
mysql> SET GLOBAL binlog_format = 1;

To switch to row-based format, use either of these statements:

mysql> SET GLOBAL binlog_format = 'ROW';
mysql> SET GLOBAL binlog_format = 2;

To switch to mixed format, use either of these statements:

mysql> SET GLOBAL binlog_format = 'MIXED';
mysql> SET GLOBAL binlog_format = 3;

An individual client can control the logging format for its own statements by setting the session value of binlog_format. For example:

mysql> SET SESSION binlog_format = 'STATEMENT';
mysql> SET SESSION binlog_format = 'ROW';
mysql> SET SESSION binlog_format = 'MIXED';

A slave server may switch the format automatically. This happens when the server is running in either STATEMENT or MIXED format and encounters a row in the binary log that is written in ROW logging format. In that case, the slave switches to row-based replication temporarily for that event, and switches back to the previous format afterwards.

There are two reasons why you might want to set replication logging on a per-connection basis:

  • A thread that makes many small changes to the database might want to use row-based logging. A thread that performs updates that match many rows in the WHERE clause might want to use statement-based logging because it will be more efficient to log a few statements than many rows.
  • Some statements require a lot of execution time on the master, but result in just a few rows being modified. It might therefore be beneficial to replicate them using row-based logging.

There are exceptions when you cannot switch the replication format at runtime:

  • From within a stored function or a trigger.
  • If the NDBCLUSTER storage engine is enabled.
  • If the session is currently in row-based replication mode and has open temporary tables.

Trying to switch the format in any of these cases results in an error.

  • Switching the replication format at runtime is not recommended when any temporary tables exist, because temporary tables are logged only when using statement-based replication, whereas with row-based replication they are not logged. With mixed replication, temporary tables are usually logged; exceptions happen with user-defined functions (UDFs) and with the UUID() function.
  • With the binlog format set to ROW, many changes are written to the binary log using the row-based format. Some changes, however, still use the statement-based format. Examples include all DDL (data definition language) statements such as CREATE TABLE, ALTER TABLE, or DROP TABLE.
  • The --binlog-row-event-max-size option is available for servers that are capable of row-based replication. Rows are stored into the binary log in chunks having a size in bytes not exceeding the value of this option. The value must be a multiple of 256. The default value is 1024.

Using the Binary Log to Enable Incremental Backups

MySQL supports incremental backups: You must start the server with the --log-bin option to enable binary logging;

The binary log files provide you with the information you need to replicate changes to the database that are made subsequent to the point at which you performed a backup.

At the moment you want to make an incremental backup (containing all changes that happened since the last full or incremental backup), you should rotate the binary log by using FLUSH LOGS.

This done, you need to copy to the backup location all binary logs which range from the one of the moment of the last full or incremental backup to the last but one.

These binary logs are the incremental backup;

If a MySQL server was started with the --log-bin option to enable binary logging, you can use the mysqlbinlog utility to recover data from the binary log files, starting from a specified point in time (for example, since your last backup) until the present or another specified point in time.

To restore data from a binary log, you must know the location and name of the current binary log file.

By default, the server creates binary log files in the data directory, but a path name can be specified with the --log-bin option to place the files in a different location. Typically the option is given in an option file (that is, my.cnf or my.ini, depending on your system).

Specifying Times for Recovery

To indicate the start and end times for recovery, specify the --start-datetime and --stop-datetime options for mysqlbinlog, in DATETIME format. As an example, suppose that exactly at 10:00 a.m. on April 20, 2005 an SQL statement was executed that deleted a large table.

shell> mysqlbinlog –stop-datetime=”2005-04-20 9:59:59″ \

/var/log/mysql/bin.123456 | mysql -u root -p

This command recovers all of the data up until the date and time given by the --stop-datetime option.

shell> mysqlbinlog --start-datetime="2005-04-20 10:01:00" \
         /var/log/mysql/bin.123456 | mysql -u root -p

In this command, the SQL statements logged from 10:01 a.m. on will be re-executed. The combination of restoring of the previous night’s dump file and the two mysqlbinlog commands restores everything up until one second before 10:00 a.m. and everything from 10:01 a.m. on.

Specifying Positions for Recovery

Instead of specifying dates and times, the --start-position and --stop-position options for mysqlbinlog can be used for specifying log positions. They work the same as the start and stop date options, except that you specify log position numbers rather than dates.

Using positions may enable you to be more precise about which part of the log to recover, especially if many transactions occurred around the same time as a damaging SQL statement.

To determine the position numbers, run mysqlbinlog for a range of times near the time when the unwanted transaction was executed, but redirect the results to a text file for examination. This can be done like so:

shell> mysqlbinlog --start-datetime="2005-04-20 9:55:00" \
         --stop-datetime="2005-04-20 10:05:00" \
         /var/log/mysql/bin.123456 > /tmp/mysql_restore.sql

This command creates a small text file in the /tmp directory that contains the SQL statements around the time that the deleterious SQL statement was executed. Open this file with a text editor and look for the statement that you don’t want to repeat. Determine the positions in the binary log for stopping and resuming the recovery and make note of them.

Positions are labeled as log_pos followed by a number. After restoring the previous backup file, use the position numbers to process the binary log file.

shell> mysqlbinlog --stop-position="368312" /var/log/mysql/bin.123456 \
         | mysql -u root -pshell> mysqlbinlog --start-position="368315" /var/log/mysql/bin.123456 \
         | mysql -u root -p

The first command recovers all the transactions up until the stop position given. The second command recovers all transactions from the starting position given until the end of the binary log. Because the output of mysqlbinlog includes SET TIMESTAMP statements before each SQL statement recorded, the recovered data and related MySQL logs will reflect the original times at which the transactions were executed.

Example

1. How to enable MySQL binary logging

use the Windows’ services (Start -> Control Panel -> Administrative Tools -> Services), select the properties for the “MySQL” service. This will produce the following window:

MySQL Properties

MySQL Properties

Change the “Start parameters:” setting to read: --log-bin=c:\mysqlbinlog:

MySQL Start Parameters

MySQL Start Parameters

Using the Windows’ Services, start the database service

2. How to enable MySQL binary logging

Add “log-bin=c:/mysqlbinlog/bin” in the C:\Program Files\MySQL\MySQL Server 5.0\my.ini and restart the MYSQL service.

Now, every time the database is updated, binarylog file will be uopdated on the location c:/mysqlbinlog and there will be one .index file as well.

Create Dump

mysqldump -u root -pnirma51 –flush-logs mitesh > c:/dump.sql

Recover Data

mysqlbinlog C:\bin.000005 | mysql -u root -pnirma51
Bibliography

http://dev.mysql.com/doc/refman/6.0/en/lock-tables.html

http://dev.mysql.com/doc/refman/6.0/en/flush.html

http://dev.mysql.com/doc/refman/6.0/en/mysqldump.html

http://dev.mysql.com/doc/refman/6.0/en/backup-strategy-example.html

http://dev.mysql.com/doc/refman/6.0/en/backup-policy.html

http://dev.mysql.com/doc/refman/6.0/en/recovery-from-backups.html

http://dev.mysql.com/doc/refman/6.0/en/backup-strategy-summary.html

http://voluntary-simplicity.org/linux/mysql-backup

http://thunk.org/tytso/blog/2009/01/12/wanted-incremental-backup-solutions-that-use-a-database/

http://voluntary-simplicity.org/linux/mysql-backup