MySQL Replication for Offline DB

From Daya Bay
Jump to navigation Jump to search

Steps to replicating the offline DB using PDSF as the example master. See also here.

Make contact

Talk to Craig give him:

  • slave host IP number

He will give you

  • Mysql user name and password
  • URL to priming dump file and its check sum
  • Other parameters needed below and which are indicated like "<parameter>".

Confirm you can log into the master with the given credentials before continuing.

Set up slave

  • Edit "my.cnf" in either /etc or /etc/mysql or wherever it is on your system. Add to the [mysqld] stanza:
 server-id               = <slaveid>
 master-host             = dayabaydb.lbl.gov
 master-user             = <username>
 master-password         = <password>
 master-port             = 3306
 master-connect-retry    = 60
 replicate-wild-do-table = offline_db.%

Note: make sure this file is readable only by root or the user that runs mysql.

Restart the server. Depending on distribution you might do one of these:

/etc/init.d/mysql restart        # debian flavors
/sbin/service mysqld restart     # red hat flavors

Start slaving

Get the dump and checksum files (0.5 GB at time of writing):

wget http://dayabay.lbl.gov/mysql/offline_db-YYYYMMDDHHMMSS.db
wget http://dayabay.lbl.gov/mysql/offline_db-YYYYMMDDHHMMSS.cksum

(eg, offline_db-20101104134105.db).

Check sum the file:

cksum offline_db-YYYYMMDDHHMMSS.db ; cat offline_db-YYYYMMDDHHMMSS.cksum

These should print two identical lines, eg:

1405246325 520049177 offline_db-YYYYMMDDHHMMSS.db
1405246325 520049177 offline_db-YYYYMMDDHHMMSS.db

Configure and start the slave:

 % mysql -u root -p
 (give local root MySQL password)
 mysql> SLAVE STOP;
 mysql> CREATE DATABASE offline_db; USE offline_db;
 mysql> SOURCE offline_db-20101104134105.db;
 (lots of queries will stream by, takes a few minutes, now test:)
 mysql> SHOW TABLES; SELECT COUNT(*) FROM DaqRunInfo;
 mysql> CHANGE MASTER TO MASTER_LOG_FILE='<logfile>', MASTER_LOG_POS=<position>;
 mysql> SLAVE START;
 mysql> SHOW SLAVE STATUS\G;

The "\G" is an option that controls the output format.

Re-doing Things

On 24 June 2011, BNL's MySQL disk filled up. This led to a corrupted "mysqld-relay-bin.004271" file. These files hold the updates from the master. The corruption was enough to cause mysqld to not process any further updates, although the updates continued to stream in. It was decided the easiest approach was to wipe out offline_db and redo the process above.

This section captures how the cleanup was done. First drop the existing database:

home2:~# mysql -uroot -hlocalhost -p
mysql> SLAVE STOP;
mysql> DROP DATABASE offline_db;

Now, redo above setup

mysql> CREATE DATABASE offline_db; USE offline_db;
mysql> SOURCE offline_db-20101104134105.db;
(lots of queries will stream by, takes a few minutes, now test:)
mysql> SHOW TABLES; SELECT COUNT(*) FROM DaqRunInfo;
mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=55240033;
mysql> SLAVE START;
mysql> SHOW SLAVE STATUS\G;

Any existing mysql-relay-bin.* files will be removed and fresh ones will be downloaded from the start of when the dump was made.

Even starting from a dump made 7 months ago, the catch up should only take a couple of hours. Note, the mysqld-relay-bin.* files will not have the same numbering as before the rebuild. One quick way to confirm the catch up is to check the table:

mysql> select * from LOCALSEQNO;
+--------------+---------------+
| TABLENAME    | LASTUSEDSEQNO |
+--------------+---------------+
| *            |             0 |
| CalibFeeSpec |           113 |
| CalibPmtSpec |            50 |
| FeeCableMap  |             3 |
| HardwareID   |           372 |
| CableMap     |           460 |
+--------------+---------------+
6 rows in set (0.00 sec)

compared to the master.