Wednesday, August 31, 2011

MySQL backup & binlog

- Full backup (dump tables to sql file)

shell> mysqldump --user=xxx --password=xxx --single-transaction --flush-logs --master-data --delete-master-logs --all-databases -r backup_sunday_1_PM.sql
- Differential backup (take binlog file)
shell> mysqladmin flush-logs
MySQL binary logs provide point-in-time recovery. But it may take huge disk space.

flush-logs: close the current binary log file and begin a new one
delete-master-logs: delete binary logs
master-data: write binary log information to dump file, so we know which is the next position in binlog. E.g., in dump file, we see
--
-- Position to start replication or point-in-time recovery from
--
CHANGE MASTER TO MASTER_LOG_FILE='hwm_binlog.000002', MASTER_LOG_POS=98;
And mysqlbinlog can be used to display the content of binlog.
C:\HWM\mysqldb\bin>mysqlbinlog ..\data\hwm_binlog.000002
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
# at 4
#110830 23:16:33 server id 1  end_log_pos 98    Start: binlog v 4, server v 5.0.
24-community-nt-log created 110830 23:16:33
# Warning: this binlog was not closed properly. Most probably mysqld crashed wri
ting it.
# at 98
#110830 23:16:33 server id 1  end_log_pos 198   Query   thread_id=108   exec_tim
e=0     error_code=0
use webnmsdb;
SET TIMESTAMP=1314760593;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.uniq
ue_checks=1;
SET @@session.sql_mode=0;
/*!\C utf8 */;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@sessio
n.collation_server=8;
DELETE FROM `webnmsdb`.`extstats`;
# at 198

Tuesday, August 30, 2011

The recovery of crashed InnoDB

If the database crashes, InnoDB will attempt to fix everything by running the log file from the last timestamp. Unfortunately if InnoDB fails to repair itself, the -entire- database will not start. MySQL will exit with an error message and your entire database will be offline.

Once you have corrupt InnoDB tables that are preventing your database from starting, you should follow this five step process:

Step 1: Add this line to your /etc/my.cnf configuration file:

[mysqld]
innodb_force_recovery = 4

Step 2: Restart MySQL. Your database will now start, but with innodb_force_recovery, all INSERTs and UPDATEs will be ignored.

Step 3: Dump all tables

Step 4: Shutdown database and delete the data directory. Run mysql_install_db to create MySQL default tables

Step 5: Remove the innodb_force_recovery line from your /etc/my.cnf file and restart the database. (It should start normally now)

Step 6: Restore everything from your backup

*** This is copied from How to Fix MySQL Database (MyISAM / InnoDB). The command mysql_install_db is only available since MySQL 5. The steps I took are slightly differently since we are using MySQL 4.
1. Set innod_force_recovery=6
2. Start MySQL, drop databases
3. Stop MySQL, delete ibdata and ib_logfiles, and unset innodb_force_recovery
4. Start MySQL again, the tablespace and log files will be recreated
5. Restore the data with the backup file

Friday, August 26, 2011

Discover runtime type of Generic class

A common misconception about generics in Java 5 is that you can't access them at runtime.

What you can't find out at runtime is which generic type is associated with an instance of an object. However you can use reflection to look at which types have been staticly associated with a member of a class.

public Map perGlobalSettings = new HashMap();
public void test() {
  Field field = Test.class.getField("perGlobalSettings");
  ParameterizedType type0=(ParameterizedType) field.getGenericType();
  System.out.println(type0);  //java.util.Map
  Class clazz=(Class)type0.getActualTypeArguments()[1]; //GlobalSettings
					
  ParameterizedType t=(ParameterizedType)perGlobalSettings.getClass().getGenericSuperclass();
  System.out.println(t);	//java.util.AbstractMap
}
  
References:
Accessing generic type information at runtime
Some notes on discovering your type parameter using the Reflection API