# 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
# name reported to the master during slave registration
# give a server id
# give bin log file location
# 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'@'%';
the slave server has a ‘read-only’ variable because that is its purpose. (Do not use this in master-master replication)
# 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
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;
# 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.
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
# the slave is working when you see this in the status
# 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;
On master when changing the replicate user password there was an error on the slave. Then the slave needed to be reset.