I'm curious whether downtime will be necessary to grab a SQL dump of my master database.
Right now, I'm in the process of rebuilding my one slave. There is actually only one database from master that is being replicated onto slave. All tables in that database are InnoDB. This is the command I want to run:
mysqldump --master-data --single-transaction --hex-blob dbname | gzip > dbname.sql.gz
I'm running MySQL 5.1 and here is a redacted version of my my.cnf file:
[mysqld]
default-storage-engine=InnoDB
character-set-server=UTF8
lower_case_table_names=1
transaction_isolation=READ-COMMITTED
wait_timeout=86400
interactive_timeout=3600
delayed_insert_timeout=10000
connect_timeout=100000
max_connections=750
max_connect_errors=1000
back_log=50
max_allowed_packet=1G
max_heap_table_size=64M
tmp_table_size=64M
bulk_insert_buffer_size=128M
innodb_buffer_pool_size=10000M
innodb_data_file_path=ibdata1:256M:autoextend
innodb_file_per_table=1
innodb_additional_mem_pool_size=32M
innodb_log_file_size=1G
innodb_log_buffer_size=8M
innodb_flush_method=O_DIRECT
innodb_lock_wait_timeout=240
innodb_flush_log_at_trx_commit=2
innodb_open_files=8192
innodb_support_xa=ON
thread_cache_size=500
expire_logs_days=2
server-id=1
log_bin=1
binlog_format=MIXED
sync_binlog=0
[mysqldump]
max_allowed_packet=128M
Am I good without downtime or not? I'm concerned about a possible read lock being placed on tables.
Best Answer
When seeking a dump which provides binlog position you will instruct MySQL to call Flush Tables With Read Lock. The acquisition this lock can block. This where you could impact the system. If you have long running transactions you may see problems but you can check this before starting the dump. Your command looks fine to me.