Note: It is recommended to uninstall and then reinstall mysql and attendant packages on the slave if it has been used a a slave for replication previously. There may be a permissions or other issue which often brings up problems when bringing the slave back up upon reconfiguration if packages are not reinstalled. There may also be a better way to avoid problems but I do not know what that may be.

If you are using replication, you should not delete old binary log files until you are sure that no slave still needs to use them. For example, if your slaves never run more than three days behind, once a day you can execute ”’mysqladmin flush-logs”’ on the master and then remove any logs that are more than three days old. You can remove the files manually, but it is preferable to use PURGE MASTER LOGS, which also safely updates the binary log index file for you (and which can take a date argument).

An example is as follows:

shell> PURGE MASTER LOGS TO 'mysql-bin.010';

This information comes from the following website: http://mysql.org/doc/refman/5.0/en/replication-howto.html

It is important to have compatibility between MySQL on both servers. Basically it is best to have the same versions on each machine. Make sure to keep them at the same version as well.

Create an account on the master server that the slave server can use to connect. Make sure to give REPLICATION SLAVE privileges. It is recommended this account is specifically for replication with no additional privileges necessary or desired. In this case a user repl was created. These privileges are granted only to
the IP mentioned and the password is the correct one used in this case.

mysql> GRANT REPLICATION SLAVE ON *.*
  -> TO 'repl'@'192.168.0.xxx' IDENTIFIED BY 'user_password';

Flush all tables and block write statements by executing a FLUSH TABLES WITH READ LOCK statement. Leave this client connected otherwise the lock will be released upon exit. Further commands should be done from a separate client connection.

mysql> FLUSH TABLES WITH READ LOCK;

InnoDB tables behave differently but but can perform crash recovery when started on this snapshot without corruption.

Tar up the databases at this point using the ”’tar”’ command or slower mysqldump can be used. Databases are located in /var/lib/mysql/.

shell> tar -cvf /location/to/store/mysql-snapshot.tar ./

Copy the archive to the slave server host. Unpack the archive file with the following command:

shell> tar -xvf /location/of/mysql-snapshot.tar

Back at the master server and from a separate client but with the FLUSH TABLES WITH READ LOCK still in place, read the value of the current binary log name and offset on the master.

mysql> SHOW MASTER STATUS;
  +---------------+----------+--------------+------------------+
  | File | Position | Binlog_Do_DB | Binlog_Ignore_DB
  +---------------+----------+--------------+------------------+
  | mysql-bin.003 | 73 | test | manual,mysql
  +---------------+----------+--------------+------------------+

The File column shows the name of the log and the Position shows the offset within the file. In this case, the binary log file is mysql-bin.003 and the offset is 73. Record these values as they are necessary when setting up the slave.

If the master previously was running without binary logging enabled, the log name and position values displayed by SHOW MASTER STATUS or mysqldump -master-data will be empty. In that case, the values that you need to use later when specifying the slave’s log file and position are the empty string (‘ ‘) and “4”.

After you have taken the snapshot of your databases and recorded the log name and offset, you can re-enable write activity on the master:

mysql> UNLOCK TABLES;

If using InnoDB, please refer to the URL at the top of this document.

Make sure the mysqld section of the /etc/my.cnf file on the master host includes a log-bin option. The section should also have a server-id=master_id option, where master_id must be a positive integer value.

For example:

[mysqld]
  log-bin=mysql-bin
  server-id=1

If those values are not present, add them and restart the server. The server cannot act as a replication master unless binary logging is enabled. (see listed URL for InnoDB)

Stop the server that is to be used as a slave and add the following lines to its my.cnf file. Make sure the server-id is different from the master. If you have more slave servers each server-id must be unique.

[mysqld]
  server-id=2

If you made a binary backup of the master servers data, copy it to the slave server’s data directory, /var/lib/mysql, before starting the mysql daemon on the slave. Make sure the privileges on the files and directories are correct. The system account that you use to run the slave server must be able to read and write the files, just as on the master. Be careful. If you move the mysql database over, all slave database users will be replaced with those from the master.

If you made a backup using mysqldump, start the slave first. The dump file is loaded in a later step.

Start the slave server. If it has been replicating previously, start the slave server with the mysqld_safe –skip-slave-start option so that it doesn’t immediately try to connect to its master.

# mysqld_safe --skip-slave-start

Execute the following mysql command on the slave, replacing the option values with the actual values relevant to your system.

This particular set assumes the master was previously running without binary logging enabled.

mysql> CHANGE MASTER TO
  -> MASTER_HOST='blah.blah.com',
  -> MASTER_USER='repl',
  -> MASTER_PASSWORD='user_password',
  -> MASTER_LOG_FILE='',
  -> MASTER_LOG_POS=4;

Start your slave threads.

mysql> START SLAVE;

After you have performed this procedure, the slave should connect to the master and catch up on any updates that have occurred since the snapshot was taken. You can find error messages in the slave’s error log, /var/log/mysql.

Once a slave is replicating, you can find in its data directory one file named master.info and another named relay-log.info. The slave users these two files to keep track of how much of the master’s binary log it has processed. Do not remove or edit these files.

Rsync in Preparation

Prior to any sort of replication, you should set up rsync to copy over the correct files. Here is an example of how to copy files using rsync:

# rsync -avz /var/www/html/* 192.168.0.xxx:/var/www/html/

This copies the local files in /var/www/html over to the same directory on the 192.168.0.xxx server.

Additional Tips

Prior to setting up a new web1 Sorenson Media server a few files:

mod_ssl
php-mysql
compat-libstdc++-33 (this is required for the validSerial app)