Configuring MySQL Replication

mysql-repl.md

Configuring MySQL for Replication

Configure Mysql:

Additional mysql options common to all instances is provided below. The following are the necessary options for enabling bin-log format necessary for replication. 'server-id' should be unique across all mysql instances.

server-id=1
log-bin=mysql-bin
binlog-format=mixed  # or row

Optionally, to replicate a single database, use the following on the slave configuration:

replicate-do-db=metastore

Create Replication User:

GRANT REPLICATION SLAVE ON *.* TO `repluser`@`%` IDENTIFIED BY 'repluser_pw';

Dump the Master Database:

> FLUSH TABLES WITH READ LOCK;
> SHOW MASTER STATUS;

$ mysqldump -p --all-databases --lock-all-tables --master-data > mydump.sql

Important note that the mysql client session should be held open after running the flush with read lock to maintain the lock until the slave has been started.

Reset Replication on Slave(s):

The logfile and log position values are taken from the output of the SHOW MASTER STATUS command.

> STOP SLAVE;
> RESET SLAVE;
> CHANGE MASTER TO
   MASTER_HOST='master_hostname',
   MASTER_USER='repluser',
   MASTER_PASSWORD='repluser_pw',
   MASTER_LOG_FILE='mysql-bin.0000003'
   MASTER_LOG_POS=438;
   
$ mysql -p < mydump.sql

> START SLAVE;

Note that the use of '>' denotes a mysql command and the '$' prefix is a shell command.

Sample Full MySQL Configuration:

[mysqld]
key_buffer              = 16M
key_buffer_size         = 32M
max_allowed_packet      = 16M
thread_stack            = 256K
thread_cache_size       = 64
query_cache_limit       = 8M
query_cache_size        = 64M
query_cache_type        = 1
max_connections         = 150
read_buffer_size        = 2M
read_rnd_buffer_size    = 16M
sort_buffer_size        = 8M
join_buffer_size        = 8M

transaction-isolation=READ-COMMITTED
binlog-format=mixed
#  may need row for some instances

innodb_file_per_table           = 1
innodb_file_format              = Barracuda
innodb_file_per_table           = 1
innodb_flush_log_at_trx_commit  = 1
innodb_log_buffer_size          = 64M
innodb_buffer_pool_size         = 2G
innodb_thread_concurrency       = 8
innodb_flush_method             = O_DIRECT
innodb_log_file_size            = 512M
innodb_large_prefix
log_bin_trust_function_creators = 1

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql

# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
symbolic-links=0

log-bin=mysql-bin
max_binlog_size = 600M
expire_logs_days=3
server-id=2

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid