Thursday, January 6, 2011

Binary and character set safe MySQL dump and restore

To dump some MySQL databases in a binary-safe way:

mysqldump -uroot -p -hdbhost \ 
  --skip-extended-insert \ 
  --default-character-set=binary \ 
  --databases dbone dbtwo dbthree \ 
  --add-drop-database --master-data=2 | gzip > /tmp/dump.sql.gz

Replace --master-data=2 with --lock-all-tables if dumping from a slave, and record the output of SHOW SLAVE STATUS while everything is locked.

To import this dump:

zcat /tmp/dump.sql.gz | mysql -uroot -p -hotherdbhost \ 
   --unbuffered --batch --default-character-set=binary

To set up replication:

Use the Relay_Master_Log_File:Exec_Master_Log_Pos values from SHOW SLAVE STATUS that you recorded. See this page for explanation:

http://www.mysqlperformanceblog.com/2008/07/07/how-show-slave-status-relates-to-change-master-to/

For example:

CHANGE MASTER TO MASTER_HOST = 'abc123.dklfjalddkj.com',
  MASTER_USER = 'rep', MASTER_PASSWORD = 'CHANGEME',
  MASTER_LOG_FILE = 'abc123-bin.003292',
  MASTER_LOG_POS = 283072761;

1 comment:

Ian Kelling said...

This is outdated. Most of those options are default, and reading --help offers important advice about them.