Editing Infrastructure Machines (section)

== Slaving ==

If we were going to setup traffic database slaving (we don't do this anymore), perhaps cause the bwdb machine gets busy and it cannot handle traffic requests and netflow, here's how it's done:

On the traffic master:

 GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.1.4.8' IDENTIFIED BY 'qERUG8wf';

in my.cnf:
<pre>bin-log
server-id=1
max_binlog_size=500M
expire_logs_days = 3</pre>

on slave:
in my.cnf:
<pre>server-id       = 2
master-host     =   10.1.4.203
master-user     =   repl
master-password =   qERUG8wf
master-connect-retry=60
replicate-wild-do-table=traffic.daily%
max_relay_log_size=500M
expire_logs_days = 3

replicate-wild-do-table=traffic.%</pre>

on master:
 touch /usr/home/working/.lock
(make sure processflows not running)

<pre>FLUSH TABLES WITH READ LOCK;
cd /usr/home/database/traffic
tar -czf mysql-traffic-snapshot.tgz ./daily*
(~1G)
SHOW MASTER STATUS;
+-----------------+-----------+--------------+------------------+
| File            | Position  | Binlog_Do_DB | Binlog_Ignore_DB |
+-----------------+-----------+--------------+------------------+
| bwdb-bin.000039 | 154432615 |              |                  |
+-----------------+-----------+--------------+------------------+

(write down info)
UNLOCK TABLES;
scp mysql-traffic-snapshot.tgz 10.1.4.5:/mnt/data1/db/mysql/traffic/</pre>

on slave:
<pre>mkdir /mnt/data1/db/mysql/traffic
cd /mnt/data1/db/mysql/traffic/
tar xzvf mysql-traffic-snapshot.tgz
(restart mysql)
CHANGE MASTER TO MASTER_HOST='10.1.4.203',MASTER_USER='repl',MASTER_PASSWORD='qERUG8wf',MASTER_LOG_FILE='bwdb-bin.000059',MASTER_LOG_POS=482502186;
START SLAVE;</pre>

<pre>cd /usr/home/database/traffic
scp *</pre> 

<pre>optimize table dailyPortTotals_69_55_224;
optimize table dailyPortTotals_69_55_225;
optimize table dailyPortTotals_69_55_226;
optimize table dailyPortTotals_69_55_227;
optimize table dailyPortTotals_69_55_228;
optimize table dailyPortTotals_69_55_229;
optimize table dailyPortTotals_69_55_230;
optimize table dailyPortTotals_69_55_231;
optimize table dailyPortTotals_69_55_232;
optimize table dailyPortTotals_69_55_233;
optimize table dailyPortTotals_69_55_234;
optimize table dailyPortTotals_69_55_235;
optimize table dailyPortTotals_69_55_236;
optimize table dailyPortTotals_69_55_237;
optimize table dailyPortTotals_69_55_238;
optimize table dailyPortTotals_69_55_239;</pre>