Configuration and Setup

On Master and Slave

edit /etc/mysql/my.cnf
# http://stackoverflow.com/questions/8605318/mysql-replication-slave-server-on-one-database#answer-8606998
# http://forums.mysql.com/read.php?35,369191,370003
# if you only want to replicate a specified database you need to add line to my.cnf
# !!! IMPORTANT in selecting the correct database name or replication will run without errors but nothing will be updated

replicate-do-db=dbname

# name reported to the master during slave registration

report_host=hostname

# give a server id

server-id=1323

# give bin log file location

log_bin=/var/log/mysql/mysql-bin.log

# restart database

$ sudo /etc/init.d/mysql restart

# add the user the slave will use

mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'slavepassword'; 
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

On master

Dump mysql databases WITH compression

# all databases

$ mysqldump -uUSERNAME -pPASSWORD --all-databases --master-data | gzip > master_dbname_dump.sql.gz

# individual database

$ mysqldump -uUSERNAME -pPASSWORD --databases dbname --master-data | gzip > master_dbname_dump.sql.gz

Dump mysql databases WITHOUT compression

# individual database

$ mysqldump -uUSERNAME -pPASSWORD --databases dbname --master-data > master_dbname_dump.sql

# View Master information in the file dumped that will set the slave.

$ grep CHANGE *sql | head -1

# show slaves that are connected

mysql> SHOW SLAVE HOSTS;

# send file to slave server

$ scp master_dbname_dump.sql username@address.com:

On Slave

edit /etc/mysql/my.cnf
the slave server has a ‘read-only’ variable because that is its purpose. (Do not use this in master-master replication)
http://dba.stackexchange.com/questions/30128/should-a-mysql-replication-slave-be-set-to-read-only#answer-30129

read_only=ON

# The purpose of the import is because both the master and slave database must be the equal at point where the binlog started recording the replication.
# import the dump.

$ mysql -uroot < master_dbname_dump.sql

# Set the slave user

mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.3', MASTER_USER='repl', MASTER_PASSWORD='slavepassword';

# Set a user that will only have an read ability.
# This is a safeguard and repetitive because setting the 'read-only' variable in my.cnf would prevent slave writing. Slave should not have writing permissions.

mysql> CREATE USER 'read_user'@'%' IDENTIFIED BY 'password';
mysql> GRANT SELECT ON *.* TO 'read_user'@'%';

# start slave

mysql> START SLAVE;
mysql> SHOW SLAVE STATUS \G 

Notes

The location of either the master or slave logs are located in /var/log/mysql.
Its a binary(.bin) file contain the queries ran to sync the slave.

# view what inside the bin, mysql view queries logs

$ mysqlbinlog /var/log/mysql/mysql-bin.000001

# show the bin name

mysql> SHOW MASTER STATUS;
mysql> SHOW BINARY LOGS;

# dba.stackexchange.com/questions/47046/why-do-the-mysql-bin-log-files-still-exist-after-a-purge-or-flush#answer-47048
# to reset all the queries and purge all binary logs. This will also do the same in the slave.

mysql> RESET MASTER;

# Sometimes there's errors in the 'slave status' you can skip queries but more error may lie ahead. This is not a permanent fix.
# https://www.howtoforge.com/how-to-repair-mysql-replication

mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

# the slave is working when you see this in the status

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

# To detect a Master and Slave being out of sync
# Pick any table and run CHECKSUM TABLE against a table on the Master and the Slave's copy of the same table.
# If the values do not come back the same, then something is out-of-sync.

mysql> CHECKSUM TABLE dbname.table;

Reset everything

On master when changing the replicate user password there was an error on the slave. Then the slave needed to be reset.

On Master

$ mysqldump -uUSERNAME -pPASSWORD --databases dbname --master-data | gzip > master_dbname_dump.sql.gz

# will clear/delete/purge all bin log. Be careful this may cause issues if there's mulitple slave to this master.

mysql> RESET MASTER;

On Slave

mysql> STOP SLAVE;
mysql> RESET SLAVE ALL;

mysql> RESET MASTER;  # will clear/delete/purge all bin log.

if this error 'ERROR 1186 (HY000): Binlog closed, cannot RESET MASTER' is because 'log_bin' was not specified in mysql configuration file my.cnf

# import the dump

$ mysql -uroot < master_dbname_dump.sql

mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.3', MASTER_USER='repl', MASTER_PASSWORD='slavepassword';
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS;

the slave is working when you see in the status

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

Master to master replication

# simply make master a slave as well.
# https://www.digitalocean.com/community/tutorials/how-to-set-up-mysql-master-master-replication

mysql> CHANGE MASTER TO MASTER_HOST='hostname', MASTER_USER='repl', MASTER_PASSWORD='slavepassword';

# values need to be equal where the colors match

Miscellaneous

# http://stackoverflow.com/questions/2366018/how-to-re-sync-the-mysql-db-if-master-and-slave-have-different-database-incase-o#answer-3229580
# an option to reset the slave position

mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=98;