replication

Master
IP:192.168.100.83
ID:150150
ServerName:db1

Slave
IP:192.168.100.8
ID:232323
ServerName:db2

If running on a live server, you will need to lock the databases and run the commands below on the mysql master server.

FLUSH TABLES WITH READ LOCK;

# after the replication setup is completed run this:
UNLOCK TABLES;

On Master Server:

# create directory mysqllogs if it doesn't exist and set permissions:
mkdir /var/lib/mysqllogs
chown mysql: /var/lib/mysqllogs

# /etc/my.cnf:
log-bin=/var/lib/mysqllogs/150150-db1-binary-log
binlog-format=MIXED
expire-logs-days=7
server-id=150150

# Create the replicant user:
GRANT REPLICATION SLAVE ON *.* to 'replicant'@'192.168.100.8' IDENTIFIED BY 'Ahtak9ohJicheip';

# install pv tool which will show you progress if it's not installed already:
yum install pv -y

# Dump the databases:
mysqldump -A --flush-privileges --master-data=1 | pv | gzip -1 > MasterData.sql.gz

On Slave Server:

# create directory mysqllogs if it doesn't exist and set permissions:
mkdir /var/lib/mysqllogs
chown mysql: /var/lib/mysqllogs

# /etc/my.cnf:
relay-log=/var/lib/mysqllogs/232323-db2-relay-log
relay-log-space-limit = 16G
read-only=1
server-id=232323
report-host=232323 #This allows show slave hosts; to work on the master.

# install pv tool which will show you progress if it's not installed already:
yum install pv -y

# rsync the dump file to the slave server, then import the data:
pv MasterData.sql.gz | zcat | mysql

# run the following to get information:
zgrep -m 1 -P 'CHANGE MASTER' MasterData.sql.gz

# it will output something like this:
# CHANGE MASTER TO MASTER_LOG_FILE = bin-log.001631, MASTER_LOG_POS = 108449231;

# input that info into the command below and run that command on the mysql slave server in mysql:
CHANGE MASTER TO MASTER_HOST='192.168.100.83', MASTER_USER='replicant', MASTER_PASSWORD='Ahtak9ohJicheip', MASTER_LOG_FILE='bin-log.001631', MASTER_LOG_POS=108449231;

# then run:
start slave;

—
FAQs
—
Should I update /root/.my.cnf and /etc/holland/backupsets/default.conf on the slave with the new credentials?
Yes.

Can I have databases on the slave server that aren’t on the master server?
Yes.

Can I sync only certain databases on the master server to the slave server?
—
Yes. There are 3 methods.

1.) The master writes all transactions into its binary log. Set the master to only write transactions from select databases into the binary log.
In the master configuration file add this: binlog-do-db=name_of_db where name_of_db is the db you want replicated.

2.) On the slave server specify the db’s to replicate.
slave config: replicate-do-db=db_name

3.) On the slave server specify the db’s not to replicate.
replicate-ignore-db=db_name

4.) Still running into trouble? Failed relay log error?
Backup master data and import into slave.
run RESET SLAVE; on the mysql slave device.
run this command again: CHANGE MASTER TO MASTER_HOST=…..
run START SLAVE;
SHOW SLAVE STATUS\G
Both of these should be set to YES:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
—

How does MySQL replicate all databases?
—
Master has Binary Logging enabled
Master records completed SQL Statements
Master will inject ‘USE dbname;’ among the SQL Statements it records
Slave connects to Master (IO Thread)
Slave IO Thread requests next SQL statement from the Binary Logs
Slave IO Thread copies it to its Relay Logs
Slave SQL Thread Processes its Relay Log Entries FIFO (Queue)
—

How do I view what’s in a binary log?
mysqlbinlog binarylogname > SQLStatements.sql

How do I see the status of replication?
# On Master:
mysql -e ‘show master status\G’
# On Slave:
mysql -e ‘show slave status\G’