# MySQL/Replication

## What is replication

Replication means that data written on a master MySQL will be sent to
separate server and executed there.

Applications:

-   backups
-   spread read access on multiple servers for scalability
-   failover/HA

Replication types:

-   Asynchronous replication (basic master/slave)
-   Semi-asynchronous replication (asynchronous replication + enforce 1
    slave replication before completing queries)

Replication configurations:

-   standard: master-\>slave
-   dual master: master\<-\>master

In Master-Master replication both hosts are masters and slaves at the
same time. ServerA replicates to serverB which replicates to serevrA.
There are no consistency checks and even with
auto_increment_increment/auto_increment_offset configured both servers
should not be used for concurrent writes.

## Asynchronous replication

That\'s the most simple replication. A master writes a binary log file,
and slaves can read this log file (possibly selectively) to replay the
query statements. It\'s asynchronous, which mean the master and slaves
may have different states at a specific point of time; also this setup
can survive a network disconnection.

### Configuration on the master

In `/etc/mysql/my.cnf`, in the `[mysqld]` section:

-   Define a server identifier (detects loops?); customarily we\'ll use
    `1` for the server, but it can be different:

`server-id = 1`

-   Replication is based on binary
    logs, so enable them:

`log-bin`\
`# or log-bin = /var/log/mysql/mysql-bin.log`

Create a new user for the slave to connect with:

``` sql
 CREATE USER 'myreplication';
 SET PASSWORD FOR 'myreplication' = PASSWORD('mypass');
 GRANT REPLICATION SLAVE ON *.* to 'myreplication';
```

Verify your server identifier:

``` sql
 SHOW VARIABLES LIKE 'server_id';
```

### Configuration on each slave

In `/etc/mysql/my.cnf`, in the `[mysqld]` section:

-   Define a server identifier, different than the master (and different
    than the other slaves):

`server-id = 2`

-   Verify with:

``` sql
 SHOW VARIABLES LIKE 'server_id';
```

-   You can also declare the slave hostname to the master (cf.
    `SHOW SLAVE HOSTS` below):

`report-host=slave1`

Declare the master:

``` sql
 CHANGE MASTER TO MASTER_HOST='master_addr', MASTER_USER='myreplication', MASTER_PASSWORD='mypass';
```

If setting up replication from backup, specify start point (add to
previous command):

``` sql
 MASTER_LOG_FILE='<binary_log_from_master>', MASTER_LOG_POS=<master_binary_log_position>; 
```

Start the replication:

``` sql
 START SLAVE;
```

This will create a file named `master.info` in your data directory,
typically `/var/lib/mysql/master.info`; this file will contain the slave
configuration and status.

TODO:

`Oct 15 21:11:19 builder mysqld[4266]: 101015 21:11:19 [Warning] Neither --relay-log nor --relay-log-index were used; so`\
`  replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use`\
`  '--relay-log=mysqld-relay-bin' to avoid this problem.`

### Check the replication

#### On the slave

On a slave, type:

``` sql
 SHOW SLAVE STATUS;
```

Or more for a more readable (line-based) output:

``` sql
 SHOW SLAVE STATUS\G
```

Example:

`*************************** 1. row ***************************`\
`             Slave_IO_State: `\
`                Master_Host: master_addr`\
`                Master_User: myreplication`\
`                Master_Port: 3306`\
`...`

Check in particular:

`           Slave_IO_Running: Yes`\
`          Slave_SQL_Running: Yes`

You can inspect the asynchronous nature of the replication:

`      Seconds_Behind_Master: 0`

See also:

`mysql> SHOW GLOBAL VARIABLES LIKE "%SLAVE%";`

#### On the master

You can see a connection from the slave in the process list.

`mysql> SHOW PROCESSLIST\G`\
`[...]`\
`*************************** 6. row ***************************`\
`     Id: 14485`\
`   User: myreplication`\
`   Host: 10.1.0.106:33744`\
`     db: NULL`\
`Command: Binlog Dump`\
`   Time: 31272`\
`  State: Has sent all binlog to slave; waiting for binlog to be updated`\
`   Info: NULL`

If you enabled `report-host`, the slave is also visible in:

`mysql> SHOW SLAVE HOSTS;`\
`+-----------+---------+------+-------------------+-----------+`\
`| Server_id | Host    | Port | Rpl_recovery_rank | Master_id |`\
`+-----------+---------+------+-------------------+-----------+`\
`|         2 | myslave | 3306 |                 0 |         1 | `\
`+-----------+---------+------+-------------------+-----------+`\
`1 row in set (0.00 sec)`

### Consistency

Note that this replication is a simple replay, similar to feeding a
`mysqldump` output to the `mysql` client. Consequently, to maintain the
consistency:

-   Avoid writing critical data on the slave
-   Start the replication with identical initial data on both the master
    and the slave
-   To test: we suspect it would be best to use the same version of
    MySQL on the master and slaves

### Fixing

By default, replicate will stop if it meets an error. This can happen if
your master and slaves were not consistent in the beginning, or due to a
network error causing a malformed query.

In this case, you\'ll get a trace in the system log (typically
`/var/log/syslog`):

`Oct 15 21:11:19 builder mysqld[4266]: 101015 21:11:19 [ERROR] Slave: Error 'Table 'mybase.form'`\
`  doesn't exist' on query. Default database: 'mybase'.  Query:`\
``   'INSERT INTO `form` (`form_id`,`timestamp`,`user_id`) VALUES ('abed',1287172429,0)', ``\
`  Error_code: 1146`

The best way is to reset the replication entirely.

You can also fix the mistake manually, and then ask MySQL to skip `1`
statement this way:

`STOP SLAVE;`\
`SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;`\
`START SLAVE;`

You can set `SQL_SLAVE_SKIP_COUNTER` to any number, e.g. `100`. Beware
that in this case, it will skip both valid and invalid statements, not
only errors.

Another way to fix broken replication is to use Maatkit tools.

-   mk-slave-restart (to restart replication on slave if there are more
    errors and `SQL_SLAVE_SKIP_COUNTER` can\'t help)
-   mk-table-checksum (to perform checksumming of tables on master and
    slave)
-   mk-table-sync (to sync slave with master based on stats generated by
    mk-table-checksum)

### Uninstalling

To erase the replication:

-   Type:

`mysql> RESET SLAVE;`

-   Note: at this point, MySQL paused the slave and replaced the
    configuration with default values. The `master.info` file was also
    removed.
-   Restart MySQL to clear all configuration.

**Warning:** `STOP SLAVE` will stop replication. It can be started
manually again or (by default) it will automatically resume if you
restart the MySQL server. To avoid auto start of replication during
process of startup, add to your configuration file:

`slave-skip-start `

If you want to stop the replication for good (and use the server for
another purpose), you need to reset the configuration as explained
above.

At this point your slave configuration should be completely empty:

`mysql> SHOW SLAVE STATUS;`\
`Empty set (0.00 sec)`

## SQL Hints

Some hints can be placed in comment before each request concerning the
replication. For example via the PHP Mysqlnd plugin (for *native
driver*).[^1]

-   MYSQLND_MS_MASTER_SWITCH: forces the request execution on the
    master.
-   MYSQLND_MS_SLAVE_SWITCH: forces the request execution on the slave.
-   MYSQLND_MS_LAST_USED_SWITCH: forces the request execution on the
    last used server.

## Federated tables

As an alternative to the replication and clustering, the
 storage engine allows to create a
table on a server which synchronizes with the same on another one.

## References

fr:MySQL/Réplication

[^1]: <https://dev.mysql.com/doc/connectors/en/apis-php-mysqlnd-ms.quickstart.sqlhints.html>
