This is a copy and paste of some old notes about MySQL replication. I have never fully reviewed this content, or neither finished with the script. I save this anyway, in case I will need some of this info in the future 😉

MySQL Replication NOTES

Master Setup/etc/my.cnf changes

# The following items need to be set:
log-bin=/var/lib/mysqllogs/ServerID-theServerShortName-binary-log
binlog-format=MIXED
expire_logs_days=7
server-id=<server_number>

# replication user
PASS=$(tr -cd '[:alnum:]' < /dev/urandom | fold -w12 | head -n1)
echo "This is the password (take note): $PASS"
mysql -e "GRANT REPLICATION SLAVE ON *.* to repl_user IDENTIFIED BY '$PASS'"

# Dump and copy across
mysqldump -A --flush-privileges --master-data=1 | gzip -1 > ~myuser/master_data.sql.gz
scp ~myuser/master_data.sql.gz $SLAVEIP:/home/myuser

# Restart Master
service mysqld restart

# === take notes of the following ====
# Get replication POSITION
zgrep -m 1 -P 'CHANGE MASTER' ~myuser/master.sql.gz | sed 's/^.*\(MASTER_LOG_FILE=.*\)$/\1/'

# Get new MySQL password to set on the slave
grep password /root/.my.cnf | awk -F= '{print $2}'

Slave Setup

# Verify timezones match between master and slave!/etc/my.cnf changes

# The following items need to be set:
relay-log=/var/lib/mysqllogs/ServerID-theServerShortName-relay-log
relay-log-space-limit = 16G
read-only=1
server-id=<server_number>
report-host=<server_number> #This allows show slave hosts; to work on the master.

# Import the data
echo "zcat /home/myuser/master.sql.gz | mysql"

# Update /root/.my.cnf with password set in the Master (importing ALL the db will overwrite users and passwords too)

# Restart Slave
service mysqld restart

# Enable repication (replace accordingly with position from latest Master's steps)
mysql
mysql> CHANGE MASTER TO MASTER_HOST = '$MASTERIP', MASTER_PORT = 3306, MASTER_USER = 'repl_user', MASTER_PASSWORD = '$PASS', MASTER_LOG_FILE='752118-Db01A-binary-log.000001', MASTER_LOG_POS=107;
mysql> START SLAVE;
mysql> CHECK SLAVE STATUS\G

==========================================================================
Trying to automate: ****DRAFT*****

#>>> On MASTER <<<#

MASTERIP=""
SLAVEIP=""

# On DEDICATED:
MYHOST=$(hostname -a)
SERVERID=$(echo $MYHOST| awk -F- '{print $1}')

# On CLOUD:
MYHOST=$(hostname)
SERVERID=$(echo $MYHOST| awk -F- '{print $1}')

#>> Create a dump and copy across
mysqldump -A --flush-privileges --master-data=1 | gzip -1 > ~myuser/master.sql.gz
scp ~myuser/master.sql.gz $SLAVEIP:/home/myuser/

#>> Set my.cnf

#> Unset possible pre-sets
for LINE in log-bin binlog-format expire_logs_days server-id ; do sed -i "/^.*$LINE.*=.*$/ s/^/#/" -i /etc/my.cnf ; done

#> Make sure all are commented out
for LINE in log-bin binlog-format expire_logs_days server-id ; do grep $LINE /etc/my.cnf ; done

#> Apply new parameters
PASS=$(tr -cd '[:alnum:]' < /dev/urandom | fold -w12 | head -n1)
sed -i "/\[mysqld\]/a \#REPLICATION\nlog-bin=\/var\/lib\/mysqllogs\/$SERVERID-binary-log\nbinlog-format=MIXED\nexpire_logs_days=7\nserver-id=$SERVERID" /etc/my.cnf

service mysqld restart

#>> Set replication user
mysql -e "GRANT REPLICATION SLAVE ON *.* to repl_user IDENTIFIED BY '$PASS'"

#>> Get output to run on the SLAVE

echo "zcat /home/myuser/master.sql.gz | mysql"

POSITION=zgrep -m 1 -P 'CHANGE MASTER' ~myuser/master.sql.gz | sed 's/^.*\(MASTER_LOG_FILE.*\)$/\1/'
echo "mysql -e \"CHANGE MASTER TO MASTER_HOST = '$MASTERIP', MASTER_PORT = 3306, MASTER_USER = 'repl_user', MASTER_PASSWORD = '$PASS', $POSITION;"

POSITION=zgrep -m 1 -P 'CHANGE MASTER' ~myuser/master.sql.gz | sed 's/^.*\(MASTER_LOG_FILE=.*\)$/\1/'

MASTER_LOG_FILE='752118-Db01A-binary-log.000001', MASTER_LOG_POS=107;

#>>> On SLAVE <<<#
for LINE in relay-log relay-log-space-limit read-only server-id report-host ; do grep $LINE /etc/my.cnf ; done

relay-log=/var/lib/mysqllogs/ServerID-theServerShortName-relay-log
relay-log-space-limit = 16G
read-only=1
server-id=<server_number>

report-host=<server_number> #This allows show slave hosts; to work on the master.