MySQL/Replication

< MySQL

What is replication

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

Applications:

Replication types:

Replication configurations:

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:

server-id = 1
log-bin
# or log-bin = /var/log/mysql/mysql-bin.log

Create a new user for the slave to connect with:

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

Verify your server identifier:

 SHOW VARIABLES LIKE 'server_id';

Configuration on each slave

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

server-id = 2
 SHOW VARIABLES LIKE 'server_id';

report-host=slave1

Declare the master:

 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):

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

Start the replication:

 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:

 SHOW SLAVE STATUS;

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

 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 suspect 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:

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.

Uninstalling

To erase the replication:

mysql> RESET SLAVE;

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)
This article is issued from Wikibooks. The text is licensed under Creative Commons - Attribution - Sharealike. Additional terms may apply for the media files.