A/I Orange Book (1.0): An how-to for the realization of a resilient network of self-managed servers
Prev	Chapter 8. The Web Services	Next

8.2. MySQL

MySQL is the main database used by user websites and it does not need particular configurations. We only had to find a way to read in the LDAP database which MySQL databases have to be on a specific server and to create them when necessary.

However, there are some databases that it is useful to share on the different servers and that are normally unbalanced towards read queries (our blog for example). Considering this unbalance, the MySQL replication functionalities can be the answer to our problem.

8.2.1. Replication

In our present situation the only MySQL replication possible is the master-slave one: the master, where write operations can be performed, and slaves replicating the database (in near-realtime). The slave has also a backup function if the master fails.

The idea of having a single solution not only for backup but also for load distribution would imply setting up the servers in master-master replication, with each server allowing for reading and writing on the database balancing load out. This solution implies high and overall stable bandwidth, a quality our servers do not have.

For further documentation refer to "MySQL Reference Manual: Chapter 6, Replication in MySQL".

To setup the replication system:

this is the configuratoin of the my.cnf on the master server:
```
log-bin 
#binlog-do-db=(if we want to limit the configuration of the single db)
server-id       = 1
```
this enables the binary log (a file in /var/lib/mysql that can become quite big). In debian it is also necessary to modify the /etc/mysql/debian-log-rotate.conf to increase the KEEP_BINARY_LOGS= option. If the replication fails the slave can use these old logs to rebuild the database.
this is the configuration of the my.cnf on the slave server:
```
server-id = 2 
master-host = xxx.xxx.xxx.xxx 
master-user = repl 
master-password = xxxxxxxx 
master-port = 3306 
#replicate-do-db = 
#skip-slave-start
```
the user repl is to be created on the master server with the following permissions:
```
GRANT REPLICATION SUPER RELOAD ON *.* TO REPL@IP_SLAVE IDENTIFIED BY 'PASSWORD';
```
The SUPER and RELOAD privileges are used when the slave needs to import a table from the master with the LOAD TABLE FROM MASTER command. This method is highly deprecated for big databases.

this is how you can make a snapshot of the db:

first and foremost stop writing on the master:
```
FLUSH TABLES WITH READ LOCK;
```

then leave the mysql console open and copy the db:

cd /var/lib/mysql 
tar cvf /tmp/database.tar ./  --exclude mysql 
scp /tmp/database.tar utente@slave

Take note of the master status when the snapshot is made (both *file name* and *position*)

SHOW MASTER STATUS;
+----------------+-----------+--------------+------------------+ 
| File           | Position  | Binlog_do_db | Binlog_ignore_db | 
+----------------+-----------+--------------+------------------+ 
| FILE-bin.011   | 117646022 | borsa        |                  | 
+----------------+-----------+--------------+------------------+

Remove the lock
```
UNLOCK TABLES;
```

Copy the snapshot on the slave server:
```
cd /var/lib/mysql 
tar xvf database.tar
```

Inform the slave server on where to begin replication from:

mysql> CHANGE MASTER TO MASTER_LOG_FILE='FILE-BIN.00X', MASTER_LOG_POS=XXXXXXXXX;

Launch the slave:
```
SLAVE START;
```
Monitor the replication status:
```
SHOW MASTER STATUS \G;
SHOW MASTER LOGS
```