ApplicationNotes/MySQLNotes

1. MySQL Notes

1.1. 5-Minute Guide to Replication

For a full description of setting up replication in MySQL, see the Replication HOWTO. This is just a basic guide; there are plenty of options and ways to do it differently.

1.1.1. Assumptions

The slave server is fresh; there are no databases on it that you want to preserve.
Both MySQL servers are the same version.
You want the slave to take-over updates when the master goes down.

1.1.2. Steps

On the master server, edit /etc/my.cnf and add the following lines. The server needs to be restarted for this to take effect, so do it preemptively when you have down time:
```
[mysqld]
...
log-bin
server-id=1
```
MySQL will now start creating master (or "binary") logs of changes which will be propagated to the slaves. These logs will increase disk usage in the MySQL data file system, so be prepared to check and purge them as necessary.
Start the monitor on the master and run the following SQL commands:

GRANT FILE ON *.* TO 'repl'@'myslave.mydomain.com' IDENTIFIED BY 'slavepass';

Or if you have 4.0.2 or later:

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'myslave.mydomain.com' IDENTIFIED BY 'slavepass';

Record the output of the Position column:

mysql > SHOW MASTER STATUS;
+------------------+-----------+--------------+------------------+
| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+-----------+--------------+------------------+
| mysql-bin.000226 | 644126706 |              |                  | 
+------------------+-----------+--------------+------------------+
1 row in set (0.00 sec)

Ship the existing data to the new slave server. There are several ways of doing this. The first is the most straightforward:

Stop the master server, tar up the MySQL data directory (/var/lib/mysql on RHEL) and then restart it.
Copy the tarball to the slave server and untar it in the appropriate place on the slave.

rsync

Use rsync to sync the data to the slave while the server is running.
Stop the master and re-rsync so the data will be "clean".

Edit /etc/my.cnf on the slave server. Using the log-bin line is necessary if you want to be able to have the slave server take-over update if the master server goes down.
```
[mysqld]
...
log-bin
server-id=10
```
Start the server on the slave.
Start the monitor on the slave:

Plug in the values you used or retrieved above:

mysql> CHANGE MASTER TO
  ->     MASTER_HOST='master_host_name',
  ->     MASTER_USER='replication_user_name',
  ->     MASTER_PASSWORD='replication_password',
  ->     MASTER_LOG_FILE='recorded_log_file_name',
  ->     MASTER_LOG_POS=recorded_log_position;

Fire up the slave replications:
```
mysql> START SLAVE;
```

1.2. Purging Master Logs

mysql> show master logs;
+-------------------+
| Log_name          |
+-------------------+
| rheingold-bin.282 |
| rheingold-bin.283 |
| rheingold-bin.284 |
| rheingold-bin.285 |
| rheingold-bin.286 |
| rheingold-bin.287 |
+-------------------+
6 rows in set (0.00 sec)

mysql> purge master logs to 'rheingold-bin.287';
Query OK, 0 rows affected (1.08 sec)