List By Client
List By Client Groups
List By DBA
List Briefing Notes
List CR Action Plans
Search for CRs
Clients' Open CRs
My New CRs
Open CRs
Client Hours
Business Issues
Client Groups
Daily Activities
Daily Client
Effort by Client Group
Effort by Comment Group
Effort by Month
Last Comment
DBA Resourcing
Overdue SRRs
Avail Config
Cluster Config
Track Clients
Client Users
Backup Strategy
CR Approval
Do Dailies
Page Acknowledgment
Set SRRs
Client Feedback
Spotcheck
Track Admin
Avail Scheduler
Boardroom
Client Sheets
BoardroomCam
Holiday Schedule
Pythian Wiki
OnCall Schedule
Preferences
Running Total
Webmail
Logged in as: cabral        
Jump to CR 
Total Booked 02:04        
Day Booked 02:04		Day Started  8:20am        
Mins Unbooked  (+0)
Micro CR:     
1mdappul16_midtier.mdsps
Support Track
Log Out   Version 5.03_L
    The Pythian Group    
CR Number: 264516 -- ActiveProspect     Team 13  DBA:Nicklas Westerlund

Warnings
Wiki link https://secure.pythian.com/trac/trac.cgi/wiki/ClientPages/actp
Hot Backups! Do not start hot backups during business day! They don′t like it!
Database Restarts

The customer must be notified any time a production MySQL database is restarted, as this requires a restart of the application. This includes all database restarts, whether or not we initiated the restart.


Apparently, they have no monitoring on the application (or website) itself, and rely upon us to provide these notifications.

Title
Total Hours
11 Hours 22 Minutes
Created by
Phone
Database
Severity
Status
Client Group
Circulation Slip
DBA
Notify List
Related CRs

| CR255732 | CR265314 | CR265319 |
Comments
 


  
Description
db3 is already set up as a database server, and has some data on it already. ActiveProspect will make sure that the data is exported, and Pythian will:

1) copy a hot backup of db1 (the backup is 90G and a copy is estimated to take about 1 day).

2) after making sure the logical copy of the database is kept, set up the backup in its place, import the data (only about 1.6G, in different databases than the backup has) and start replication again.

3) Let the replication slave catch up to ensure that the backup is fine, replication works, etc. Binary logs are kept for 7 days.

4) Stop the slave process, stop all writes to mysqld, and do a mysqldump (logical export) of the data.

5) Stop mysqld cleanly, set the slave so it doesn't start on restart in the my.cnf and also set to innodb_file_per_table, move the existing ibdata and ib_logfile files to a backup location (/var/lib/mysql/OLD for example), and restart the database.

6) Import the data, which will convert the database to use innodb_file_per_table.

7) Start replication again. Take skip_slave_start out of the my.cnf.


Swap Log Display Order
02-Mar-2009 at  4:47pm (GMT -05)Singer X.J. WangSeverity: 3 - Green
CR Modified
Variable Old value New value
CR Status 3 - Pending 7 - Closed

this CR has been completed, db03 is setup and avail is running now so we can close this CR



02-Mar-2009 at 12:26pm (GMT -05)Singer X.J. WangSeverity: 3 - Green
Status Review Reminder: Monday , March 02, 2009



25-Feb-2009 at  9:25am (GMT -05)Sheeri K. Cabral11 MinutesSeverity: 3 - Green
added monitoring for db3_3307 too.


25-Feb-2009 at  9:14am (GMT -05)Sheeri K. Cabral47 MinutesSeverity: 3 - Green
installing monitoring (paging and dailies) on db3_3306.


24-Feb-2009 at  6:32pm (GMT -05)Singer X.J. Wang
Severity: 3 - Green
it is a glorious day! db3 has caught up! it is now 0 seconds behind..

root@localhost:(none)>
root@localhost:(none)>
root@localhost:(none)>
root@localhost:(none)> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 67.192.44.130
                Master_User: repl
                Master_Port: 3306
              Connect_Retry: 10
            Master_Log_File: db2-binlog.002031
        Read_Master_Log_Pos: 521492418
             Relay_Log_File: db3-3306-relay.000047
              Relay_Log_Pos: 521489126
      Relay_Master_Log_File: db2-binlog.002031
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB:
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table: avail.%,avail\_db_.%,mysql.%,test.%,staging.%
                 Last_Errno: 0
                 Last_Error:
               Skip_Counter: 0
        Exec_Master_Log_Pos: 521488988
            Relay_Log_Space: 521492556
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: Yes
         Master_SSL_CA_File: /var/lib/mysql/ssl_db2/ca-cert.pem
         Master_SSL_CA_Path: /var/lib/mysql/ssl_db2
            Master_SSL_Cert: /var/lib/mysql/ssl_db2/server-cert.pem
          Master_SSL_Cipher:
             Master_SSL_Key: /var/lib/mysql/ssl_db2/server-key.pem
      Seconds_Behind_Master: 0
1 row in set (0.00 sec)

root@localhost:(none)>
root@localhost:(none)> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 67.192.44.130
                Master_User: repl
                Master_Port: 3306
              Connect_Retry: 10
            Master_Log_File: db2-binlog.002031
        Read_Master_Log_Pos: 521967103
             Relay_Log_File: db3-3306-relay.000047
              Relay_Log_Pos: 521967241
      Relay_Master_Log_File: db2-binlog.002031
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB:
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table: avail.%,avail\_db_.%,mysql.%,test.%,staging.%
                 Last_Errno: 0
                 Last_Error:
               Skip_Counter: 0
        Exec_Master_Log_Pos: 521967103
            Relay_Log_Space: 521967241
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: Yes
         Master_SSL_CA_File: /var/lib/mysql/ssl_db2/ca-cert.pem
         Master_SSL_CA_Path: /var/lib/mysql/ssl_db2
            Master_SSL_Cert: /var/lib/mysql/ssl_db2/server-cert.pem
          Master_SSL_Cipher:
             Master_SSL_Key: /var/lib/mysql/ssl_db2/server-key.pem
      Seconds_Behind_Master: 0
1 row in set (0.00 sec)

root@localhost:(none)>


24-Feb-2009 at 10:04am (GMT -05)Singer X.J. Wang
Severity: 3 - Green

root@localhost:(none)> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 67.192.44.130
                Master_User: repl
                Master_Port: 3306
              Connect_Retry: 10
            Master_Log_File: db2-binlog.002029
        Read_Master_Log_Pos: 680575136
             Relay_Log_File: db3-3306-relay.000031
              Relay_Log_Pos: 940404269
      Relay_Master_Log_File: db2-binlog.002026
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB:
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table: avail.%,avail\_db_.%,mysql.%,test.%,staging.%
                 Last_Errno: 0
                 Last_Error:
               Skip_Counter: 0
        Exec_Master_Log_Pos: 940404131
            Relay_Log_Space: 3901806803
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: Yes
         Master_SSL_CA_File: /var/lib/mysql/ssl_db2/ca-cert.pem
         Master_SSL_CA_Path: /var/lib/mysql/ssl_db2
            Master_SSL_Cert: /var/lib/mysql/ssl_db2/server-cert.pem
          Master_SSL_Cipher:
             Master_SSL_Key: /var/lib/mysql/ssl_db2/server-key.pem
      Seconds_Behind_Master: 63312
1 row in set (0.00 sec)

root@localhost:(none)>


Less then 65000 seconds left, we ran through 220000 seconds in less then 20 hours.. so far so good...


24-Feb-2009 at  9:33am (GMT -05)Sheeri K. CabralSeverity: 3 - Green
CR Modified
Variable Old value New value
Title Make db3 a slave of db1 Make db3 a slave of db1 -- including monitoring

Singer, please make sure to install monitoring when this slave is caught up (you can install it before the slave is caught up and turn it on after the slave is caught up, or install everything but the slave lag check and turn it on).



23-Feb-2009 at  4:08pm (GMT -05)Singer X.J. Wang
Severity: 3 - Green
we're progressing at about twice real time, so I figure another day or so before its caught up..


root@localhost:(none)> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 67.192.44.130
                Master_User: repl
                Master_Port: 3306
              Connect_Retry: 10
            Master_Log_File: db2-binlog.002021
        Read_Master_Log_Pos: 917243803
             Relay_Log_File: db3-3306-relay.000004
              Relay_Log_Pos: 197341587
      Relay_Master_Log_File: db2-binlog.002017
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB:
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table: avail.%,avail\_db_.%,mysql.%,test.%,staging.%
                 Last_Errno: 0
                 Last_Error:
               Skip_Counter: 0
        Exec_Master_Log_Pos: 197341449
            Relay_Log_Space: 5212418769
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: Yes
         Master_SSL_CA_File: /var/lib/mysql/ssl_db2/ca-cert.pem
         Master_SSL_CA_Path: /var/lib/mysql/ssl_db2
            Master_SSL_Cert: /var/lib/mysql/ssl_db2/server-cert.pem
          Master_SSL_Cipher:
             Master_SSL_Key: /var/lib/mysql/ssl_db2/server-key.pem
      Seconds_Behind_Master: 274999
1 row in set (0.00 sec)

root@localhost:(none)>


23-Feb-2009 at  2:57pm (GMT -05)Singer X.J. Wang
Severity: 3 - Green
restarted with correct positions,
change master to master_log_file='db2-binlog.002016', master_log_pos=805934290;

and now replication is catching up..


root@localhost:(none)> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: Queueing master event to the relay log
                Master_Host: 67.192.44.130
                Master_User: repl
                Master_Port: 3306
              Connect_Retry: 10
            Master_Log_File: db2-binlog.002016
        Read_Master_Log_Pos: 889233385
             Relay_Log_File: db3-3306-relay.000002
              Relay_Log_Pos: 5730792
      Relay_Master_Log_File: db2-binlog.002016
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB:
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table: avail.%,avail\_db_.%,mysql.%,test.%,staging.%
                 Last_Errno: 0
                 Last_Error:
               Skip_Counter: 0
        Exec_Master_Log_Pos: 811664846
            Relay_Log_Space: 83299331
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: Yes
         Master_SSL_CA_File: /var/lib/mysql/ssl_db2/ca-cert.pem
         Master_SSL_CA_Path: /var/lib/mysql/ssl_db2
            Master_SSL_Cert: /var/lib/mysql/ssl_db2/server-cert.pem
          Master_SSL_Cipher:
             Master_SSL_Key: /var/lib/mysql/ssl_db2/server-key.pem
      Seconds_Behind_Master: 281445
1 row in set (0.00 sec)

root@localhost:(none)>


23-Feb-2009 at 11:21am (GMT -05)Singer X.J. Wang
Severity: 3 - Green
investigating why replication failed, we failed to import the last binlog.. .. sourcing the last log now..


23-Feb-2009 at 10:36am (GMT -05)Sheeri K. CabralSeverity: 3 - Green
CR Modified
Variable Old value New value
DBA Sheeri K. Cabral Singer X.J. Wang

Singer is taking over for this....



23-Feb-2009 at 10:35am (GMT -05)Sheeri K. CabralSeverity: 3 - Green
Status Review Reminder: Saturday , February 28, 2009



22-Feb-2009 at 10:54pm (GMT -05)Singer X.J. Wang
Severity: 3 - Green
created directory /var/lib/mysql/ssl_db1 for SSL certs from db1 and similarily /var/lib/mysql/ssl_db2 for certs from db02.. since Sheeri was replicating from db02, I used the SSL certs from db02
and started replication..

CHANGE MASTER TO
  MASTER_SSL = 1,
  MASTER_SSL_CA = '/var/lib/mysql/ssl_db2/ca-cert.pem',
  MASTER_SSL_CAPATH = '/var/lib/mysql/ssl_db2',
  MASTER_SSL_CERT = '/var/lib/mysql/ssl_db2/server-cert.pem',
  MASTER_SSL_KEY = '/var/lib/mysql/ssl_db2/server-key.pem';


documented on clientSheet the two SSL cert directories...


22-Feb-2009 at  5:39pm (GMT -05)Sheeri K. Cabral10 MinutesSeverity: 3 - Green
Hrm, I'm getting similar errors on commandline:

[pythian@db3:/mnt/tmp] mysql -u repl -p -h 67.192.44.130 --ssl-key=/var/lib/mysql/ssl/server-key.pem --ssl-ca=/var/lib/mysql/ssl/ca-cert.pem --ssl-cert=/var/lib/mysql/ssl/server-cert.pem
Enter password:
ERROR 2026 (HY000): SSL connection error



22-Feb-2009 at  5:27pm (GMT -05)Sheeri K. Cabral21 MinutesSeverity: 3 - Green
Imported the logs, but couldn't start the slave again on db03. I had to delete logs on db01 for space, so I found the log position on db02 that we should start at:

master_log_file='db2-binlog.002015', master_log_pos=891368743,

and the slave started, but I'm getting the following in the error logs:

090222 17:25:54 [Note] Slave SQL thread initialized, starting replication in log 'db2-binlog.002015' at position 891368743, relay log '/var/lib/mysql/relay/db3-3306-relay.000001' position: 4
090222 17:25:55 [ERROR] Slave I/O thread: error connecting to master 'repl@67.192.44.130:3306': Error: 'SSL connection error' errno: 2026 retry-time: 10 retries: 86400

Am fixing this currently.


20-Feb-2009 at  9:49am (GMT -05)Sheeri K. Cabral17 MinutesSeverity: 3 - Green
copying the logs that raj compressed over to db3, after assessing the db1 space situation.


19-Feb-2009 at 11:15pm (GMT -05)Sheeri K. Cabral20 MinutesSeverity: 3 - Green
I checked several times during the day; the import is still running and it's all set to go with all the logs up to db1-binlog.002315.sql, so in the morning it should be ready.


19-Feb-2009 at  2:04pm (GMT -05)Sheeri K. CabralSeverity: 3 - Green
Status Review Reminder: Friday , February 20, 2009



19-Feb-2009 at  1:33pm (GMT -05)Sheeri K. Cabral13 MinutesSeverity: 3 - Green
[pythian@db3:/mnt/tmp] date
Thu Feb 19 13:28:11 EST 2009
[pythian@db3:/mnt/tmp] mysql < db1-binlog.002309.sql

it takes about 2h for 1 binlog to import.....


19-Feb-2009 at 11:30am (GMT -05)Sheeri K. Cabral2 MinutesSeverity: 3 - Green
importing binlogs now -- imported db1-binlog.002307POS.sql, now importing db1-binlog.002308.sql


19-Feb-2009 at 11:28am (GMT -05)Sheeri K. CabralSeverity: 3 - Green
Status Review Reminder: Thursday , February 19, 2009



19-Feb-2009 at 10:19am (GMT -05)Sheeri K. Cabral1 MinutesSeverity: 3 - Green
Making a binary log for starting at the correct position from the binlog:

Master_Log_File: db1-binlog.002307
Read_Master_Log_Pos: 653305988


[pythian@db3:/mnt/tmp] mysqlbinlog db1-binlog.002307 --start-position=653305988 > db1-binlog.002307POS.sql


19-Feb-2009 at 10:08am (GMT -05)Sheeri K. CabralSeverity: 3 - Green
CR Modified
Variable Old value New value
DBA Singer X.J. Wang Sheeri K. Cabral
Related CRs 255732,265314 255732,265314,265319


19-Feb-2009 at  9:59am (GMT -05)Sheeri K. Cabral9 MinutesSeverity: 3 - Green
The binlogs were copied:


[mysql@db3:/mnt/tmp] ls -lrth !$
ls -lrth *binlog*
-rw-rw---- 1 pythian pythian 1.1G Feb 19 01:35 db1-binlog.002307
-rw-rw---- 1 pythian pythian 1.1G Feb 19 01:48 db1-binlog.002308
-rw-rw---- 1 pythian pythian 1.1G Feb 19 02:01 db1-binlog.002309
-rw-rw---- 1 pythian pythian 1.1G Feb 19 02:15 db1-binlog.002310
-rw-rw---- 1 pythian pythian 1.1G Feb 19 02:28 db1-binlog.002311
-rw-rw---- 1 pythian pythian 1.1G Feb 19 02:41 db1-binlog.002312
-rw-rw---- 1 pythian pythian 1.1G Feb 19 02:55 db1-binlog.002313
-rw-rw---- 1 pythian pythian 1.1G Feb 19 03:08 db1-binlog.002314
-rw-rw---- 1 pythian pythian 1.1G Feb 19 03:21 db1-binlog.002315

working on applying them now, first have to convert them:

[pythian@db3:/var/tmp] cd /mnt/tmp
[pythian@db3:/mnt/tmp] df -h .
Filesystem Size Used Avail Use% Mounted on
/dev/sdb 414G 182G 212G 47% /mnt
[pythian@db3:/mnt/tmp] for i in `ls *binlog*`; do echo $i; mysqlbinlog $i > $i.sql; done
db1-binlog.002307


19-Feb-2009 at  1:23am (GMT -05)Singer X.J. Wang
Severity: 3 - Green
okay, my estimate was somehow off (or there were data deletions in the ibdata file because things are done! - sourcing the
mysql.sql to get all the privileges..

root@db3.leadconduit.com:~$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7076
Server version: 5.0.67-community-log MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

root@localhost:(none)>
root@localhost:(none)> show processlist;
+------+------+-----------+------+---------+-------+-------+------------------+
| Id   | User | Host      | db   | Command | Time  | State | Info             |
+------+------+-----------+------+---------+-------+-------+------------------+
| 6642 | root | localhost | NULL | Sleep   | 10085 |       | NULL             |
| 7076 | root | localhost | NULL | Query   |     0 | NULL  | show processlist |
+------+------+-----------+------+---------+-------+-------+------------------+
2 rows in set (0.00 sec)

root@localhost:(none)> source /mnt/tmp/mysql.sql;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Database changed
Query OK, 0 rows affected (0.03 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.12 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 96 rows affected (0.00 sec)
Records: 96  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.05 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 38 rows affected (0.00 sec)
Records: 38  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 404 rows affected (0.00 sec)
Records: 404  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.03 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 841 rows affected (0.01 sec)
Records: 841  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 479 rows affected (0.08 sec)
Records: 479  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.13 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.03 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.05 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 34 rows affected (0.00 sec)
Records: 34  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

root@localhost:mysql> flush privileges;
Query OK, 0 rows affected (0.03 sec)

root@localhost:mysql>


unfortunately, with the archiving of the binlogs in CR.. 266130 we need to apply some manually before we can start the replication- ETA 4 hours

-rw-rw----  1 mysql mysql 1073741925 Feb 16 13:00 db1-binlog.002309
-rw-rw----  1 mysql mysql 1073741882 Feb 16 18:41 db1-binlog.002310
-rw-rw----  1 mysql mysql 1073742174 Feb 17 04:33 db1-binlog.002311
-rw-rw----  1 mysql mysql 1073742006 Feb 17 11:14 db1-binlog.002312
-rw-rw----  1 mysql mysql 1073742231 Feb 17 15:43 db1-binlog.002313
-rw-rw----  1 mysql mysql 1073742148 Feb 17 23:40 db1-binlog.002314
-rw-rw----  1 mysql mysql 1073741976 Feb 18 08:48 db1-binlog.002315
pythian@db1 binlog $ scp -C db1-binlog.002307 db1-binlog.002308 db1-binlog.002309 db1-binlog.002310 db1-binlog.002311 db1-binlog.002312 db1-binlog.002313 db1-binlog.002314 db1-binlog.002315 pythian@db3.leadconduit.com:/mnt/tmp
db1-binlog.002307                                                                                                                                                              1%   19MB   1.2MB/s   13:35 ETA


















18-Feb-2009 at  4:52pm (GMT -05)Singer X.J. Wang
Severity: 3 - Green
the FRM files are small, so ignoring those we have 208GB in the data directory. Subtracting 22GB for nextgen_archive (all MyISAM), we are at 188GB for 2.5 days is about 3.5GB/hour (this include significant time for rebuilding indexes).

Now the original ib data files were ~300GB, assuming a 100% conversion to file per table. So figuring about 85 hours.. we are at 60 so 25 hours left.


18-Feb-2009 at  4:35pm (GMT -05)Singer X.J. Wang
Severity: 3 - Green

root@localhost:(none)> show processlist;
+------+------+-----------+---------+---------+------+--------+------------------------------------------------------------------------------------------------------+
| Id   | User | Host      | db      | Command | Time | State  | Info                                                                                                 |
+------+------+-----------+---------+---------+------+--------+------------------------------------------------------------------------------------------------------+
|   11 | root | localhost | nextgen | Query   |    2 | update | INSERT INTO `PRIMARY_KEY_HASH` VALUES (113561067,NULL,'0e041fc778d578f7964fcbdb5bde24be','00b6d8hlb' |
| 5810 | root | localhost | NULL    | Query   |    0 | NULL   | show processlist                                                                                     |
+------+------+-----------+---------+---------+------+--------+------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

root@localhost:(none)>


still on P!


18-Feb-2009 at 10:07am (GMT -05)Sheeri K. Cabral17 MinutesSeverity: 3 - Green
Still going, but it's on the "P" tables now.....

Also the root password has changed to what it used to be, I think Singer added in the data from the previous instance.

[pythian@db3:~] mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4998
Server version: 5.0.67-community-log MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

root@localhost:(none)> show processlist;
+------+------+-----------+---------+---------+------+--------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+------+-----------+---------+---------+------+--------+------------------------------------------------------------------------------------------------------+
| 11 | root | localhost | nextgen | Query | 2 | update | INSERT INTO `PRIMARY_KEY_HASH` VALUES (51332178,NULL,'c5d965ee77f55452eff0df3d048b2b4e','000lepbc1', |
| 4998 | root | localhost | NULL | Query | 0 | NULL | show processlist |
+------+------+-----------+---------+---------+------+--------+------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)


17-Feb-2009 at  3:48pm (GMT -05)Singer X.J. Wang
Severity: 3 - Green
still going, its like the never ending energizer rabbit..

root@db3.leadconduit.com:/var/lib/mysql/data/nextgen$ mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2574
Server version: 5.0.67-community-log MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

root@localhost:(none)> show processlist;
+------+------+-----------+---------+---------+------+--------+------------------------------------------------------------------------------------------------------+
| Id   | User | Host      | db      | Command | Time | State  | Info                                                                                                 |
+------+------+-----------+---------+---------+------+--------+------------------------------------------------------------------------------------------------------+
|   11 | root | localhost | nextgen | Query   |    1 | update | INSERT INTO `LEAD` VALUES ('000t5bgya','000e7l3ao','000t5bhcj',NULL,0,'2008-07-17','2008-07-17 09:50 |
| 2574 | root | localhost | NULL    | Query   |    0 | NULL   | show processlist                                                                                     |
+------+------+-----------+---------+---------+------+--------+------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

root@localhost:(none)>


17-Feb-2009 at  1:05pm (GMT -05)Singer X.J. Wang
Severity: 3 - Green
still going..

root@localhost:(none)> show processlist;
+------+------+-----------+---------+---------+------+--------+------------------------------------------------------------------------------------------------------+
| Id   | User | Host      | db      | Command | Time | State  | Info                                                                                                 |
+------+------+-----------+---------+---------+------+--------+------------------------------------------------------------------------------------------------------+
|   11 | root | localhost | nextgen | Query   |    0 | update | INSERT INTO `FIELD_VALUE` VALUES ('00ej9jb9d','00ej8jxk6','0001es22r','Valid',NULL,NULL,NULL,NULL,NU |
| 2156 | root | localhost | NULL    | Query   |    0 | NULL   | show processlist                                                                                     |
+------+------+-----------+---------+---------+------+--------+------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

root@localhost:(none)>


17-Feb-2009 at 11:23am (GMT -05)Singer X.J. WangSeverity: 3 - Green
Status Review Reminder: Wednesday, February 18, 2009



17-Feb-2009 at  9:52am (GMT -05)Singer X.J. Wang
Severity: 3 - Green
still loading from file..


[pythian@db3:/mnt/tmp] mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1778
Server version: 5.0.67-community-log MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

root@localhost:(none)> show processlist;
+------+------+-----------+---------+---------+------+--------+------------------------------------------------------------------------------------------------------+
| Id   | User | Host      | db      | Command | Time | State  | Info                                                                                                 |
+------+------+-----------+---------+---------+------+--------+------------------------------------------------------------------------------------------------------+
|   11 | root | localhost | nextgen | Query   |    1 | update | INSERT INTO `FIELD_VALUE` VALUES ('000xg9cvz','000xg9btm','0001er23z','Valid',NULL,NULL,NULL,NULL,NU |
| 1778 | root | localhost | NULL    | Query   |    0 | NULL   | show processlist                                                                                     |
+------+------+-----------+---------+---------+------+--------+------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

root@localhost:(none)>


16-Feb-2009 at  7:28pm (GMT -05)Singer X.J. Wang
Severity: 3 - Green
the gzip finally finished and archived

root@db3.leadconduit.com:/var/lib/mysql/innodb$ ls -l
total 65033532
-rw-r----- 1 mysql mysql    74459348 Feb 16 10:57 ib_logfile0.gz
-rw-r----- 1 mysql mysql    79098346 Feb 16 03:02 ib_logfile1.gz
-rw-r----- 1 mysql mysql   595903223 Feb 16 10:57 ibdata1.gz
-rw-r----- 1 mysql mysql 65844859920 Feb 16 10:57 ibdata2.gz
root@db3.leadconduit.com:/var/lib/mysql/innodb$
root@db3.leadconduit.com:/var/lib/mysql/innodb$ mv * ../old/
root@db3.leadconduit.com:/var/lib/mysql/innodb$


and we're clear for a new instance

root@db3.leadconduit.com:/var/lib/mysql$ ls -l binlog/
total 0
root@db3.leadconduit.com:/var/lib/mysql$ ls -l data/
total 0
root@db3.leadconduit.com:/var/lib/mysql$ ls -l innodb/
total 0
root@db3.leadconduit.com:/var/lib/mysql$ ls -l log/
total 0
root@db3.leadconduit.com:/var/lib/mysql$


initializing directory

root@db3.leadconduit.com:/var/lib/mysql$ mysql_install_db --datadir=/var/lib/mysql/data/
Installing MySQL system tables...
OK
Filling help tables...
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h db3.leadconduit.com password 'new-password'

Alternatively you can run:
/usr/bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:
cd / ; /usr/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd mysql-test ; perl mysql-test-run.pl

Please report any problems with the /usr/bin/mysqlbug script!

The latest information about MySQL is available on the web at
http://www.mysql.com
Support MySQL by buying support/licenses at http://shop.mysql.com
root@db3.leadconduit.com:/var/lib/mysql$


modified my.cnf for file_per_table and a 10MB ibdata file (which is required)

innodb_data_home_dir            = /var/lib/mysql/innodb
#innodb_data_file_path=ibdata1:2432M;ibdata2:1024M:autoextend
#innodb_data_file_path          = ibdata1:1024M:autoextend
innodb_data_file_path           = ibdata1:10M:autoextend
innodb_file_per_table
innodb_log_group_home_dir       = /var/lib/mysql/innodb


starting mysql:3306

InnoDB: Setting log file /var/lib/mysql/innodb/ib_logfile1 size to 256 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100 200
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
090216 19:10:38  InnoDB: Started; log sequence number 0 0
090216 19:10:38 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.0.67-community-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Edition (GPL)


root@db3.leadconduit.com:/var/lib/mysql/log$


root@localhost:(none)> show global variables like '%file_per%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.02 sec)

root@localhost:(none)>



generated and ran a script which will source all the sql files, except for the mysql db, which I will do manually and flush privileges
at the end

root@db3.leadconduit.com:/mnt/tmp$
root@db3.leadconduit.com:/mnt/tmp$
root@db3.leadconduit.com:/mnt/tmp$
root@db3.leadconduit.com:/mnt/tmp$ cat 3.sh
mysql -uroot -e 'set SQL_LOG_BIN=0; source avail_db1.sql;' &
mysql -uroot -e 'set SQL_LOG_BIN=0; source avail_db2.sql;' &
mysql -uroot -e 'set SQL_LOG_BIN=0; source batchrobot_prod.sql;' &
mysql -uroot -e 'set SQL_LOG_BIN=0; source batchrobot_staging.sql;' &
mysql -uroot -e 'set SQL_LOG_BIN=0; source bin.sql;' &
mysql -uroot -e 'set SQL_LOG_BIN=0; source blacklistd.sql;' &
mysql -uroot -e 'set SQL_LOG_BIN=0; source engine_prod.sql;' &
mysql -uroot -e 'set SQL_LOG_BIN=0; source leadrobot_prod.sql;' &
mysql -uroot -e 'set SQL_LOG_BIN=0; source maatkit.sql;' &
mysql -uroot -e 'set SQL_LOG_BIN=0; source nextgen.sql;' &
mysql -uroot -e 'set SQL_LOG_BIN=0; source nextgen_archive.sql;' &
mysql -uroot -e 'set SQL_LOG_BIN=0; source ssl.sql;' &
mysql -uroot -e 'set SQL_LOG_BIN=0; source staging.sql;' &
mysql -uroot -e 'set SQL_LOG_BIN=0; source test.sql;' &
mysql -uroot -e 'set SQL_LOG_BIN=0; source vphone200704.sql;' &
mysql -uroot -e 'set SQL_LOG_BIN=0; source vphone200707.sql;' &
mysql -uroot -e 'set SQL_LOG_BIN=0; source vphone200711.sql;' &
mysql -uroot -e 'set SQL_LOG_BIN=0; source vphone200712.sql;' &
mysql -uroot -e 'set SQL_LOG_BIN=0; source vphone200801.sql;' &
mysql -uroot -e 'set SQL_LOG_BIN=0; source vphone200803.sql;' &
mysql -uroot -e 'set SQL_LOG_BIN=0; source vphone200804.sql;' &
mysql -uroot -e 'set SQL_LOG_BIN=0; source vphone200805.sql;' &
mysql -uroot -e 'set SQL_LOG_BIN=0; source vphone200901.sql;' &
mysql -uroot -e 'set SQL_LOG_BIN=0; source vzip200805.sql;' &
mysql -uroot -e 'set SQL_LOG_BIN=0; source vzip200809.sql;' &
mysql -uroot -e 'set SQL_LOG_BIN=0; source vzip200811.sql;' &
root@db3.leadconduit.com:/mnt/tmp$ sh 3.sh
root@db3.leadconduit.com:/mnt/tmp$ ps -ef | grep mysql
root      7656 25473  0 Feb13 pts/3    00:00:00 mysql -u root -p
mysql     9997     1  0 10:47 ?        00:00:26 /usr/sbin/mysqld --user=mysql --port=3307 --default-storage-engine=InnoDB --character_set_server=latin1 --old_passwords=1 --skip-bdb --tmpdir=/var/lib/mysql2/tmp --socket=/var/lib/mysql2/mysql.sock --pid-file=/var/lib/mysql2/mysql.pid --log-error=/var/lib/mysql2/log/mysql-err.log --log_slow_queries=/var/lib/mysql2/log/mysql-slow.log --long_query_time=1 --skip-slave-start --replicate-wild-ignore-table=avail.% --replicate-wild-ignore-table=avail\_db_.% --replicate-wild-ignore-table=mysql.% --replicate-wild-ignore-table=test.% --server-id=0102510549 --log-bin=/var/lib/mysql2/binlog/db3-3307-binlog --log-bin-index=/var/lib/mysql2/binlog/db3-3307-binlog.index --expire_logs_days=7 --sync_binlog=1 --basedir=/usr --datadir=/var/lib/mysql2/data --language=/usr/share/mysql/english --skip-locking --key_buffer=32M --max_allowed_packet=1M --thread_stack=128K --sort_buffer_size=8M --max_connections=500 --query_cache_type=1 --query_cache_size=64M --query_cache_limit=3145728 --table_cache=2048 --read_buffer_size=2M --innodb_data_home_dir=/var/lib/mysql2/innodb --innodb_data_file_path=ibdata1:10M:autoextend --innodb_file_per_table --innodb_log_group_home_dir=/var/lib/mysql2/innodb --innodb_buffer_pool_size=1G --innodb_additional_mem_pool_size=32M --innodb_log_file_size=256M --innodb_log_files_in_group=2 --innodb_log_buffer_size=8M --innodb_flush_log_at_trx_commit=1 --innodb_flush_method=O_DSYNC --innodb_adaptive_hash_index=0
pythian  10428 10409  0 10:51 pts/10   00:00:00 mysql -p -uroot --socket=/var/lib/mysql2/mysql.sock
mysql    10564     1  5 19:10 pts/7    00:00:22 /usr/sbin/mysqld --user=mysql --port=3306 --default-storage-engine=InnoDB --character_set_server=latin1 --old_passwords=1 --skip-bdb --tmpdir=/var/lib/mysql/tmp --socket=/var/lib/mysql/mysql.sock --pid-file=/var/lib/mysql/mysql.pid --log-error=/var/lib/mysql/log/mysql-err.log --log_slow_queries=/var/lib/mysql/log/mysql-slow.log --long_query_time=1 --skip-slave-start --replicate-wild-ignore-table=avail.% --replicate-wild-ignore-table=avail\_db_.% --replicate-wild-ignore-table=mysql.% --replicate-wild-ignore-table=test.% --replicate-wild-ignore-table=staging.% --server-id=010251051048 --log-bin=/var/lib/mysql/binlog/db3-3306-binlog --log-bin-index=/var/lib/mysql/binlog/db3-3306-binlog.index --expire_logs_days=7 --sync_binlog=1 --log-slave-updates --relay-log=/var/lib/mysql/relay/db3-3306-relay.log --relay-log-info-file=/var/lib/mysql/relay/db3-3306-relay-log.info --relay-log-index=/var/lib/mysql/relay/db3-3306-relay.index --basedir=/usr --datadir=/var/lib/mysql/data --language=/usr/share/mysql/english --skip-locking --key_buffer=32M --max_allowed_packet=1M --thread_stack=128K --sort_buffer_size=8M --max_connections=500 --query_cache_type=1 --query_cache_size=64M --query_cache_limit=3145728 --table_cache=2048 --read_buffer_size=2M --innodb_data_home_dir=/var/lib/mysql/innodb --innodb_data_file_path=ibdata1:10M:autoextend --innodb_file_per_table --innodb_log_group_home_dir=/var/lib/mysql/innodb --innodb_buffer_pool_size=4G --innodb_additional_mem_pool_size=32M --innodb_log_file_size=256M --innodb_log_files_in_group=2 --innodb_log_buffer_size=8M --innodb_flush_log_at_trx_commit=1 --innodb_flush_method=O_DSYNC --innodb_adaptive_hash_index=0
root     10984     1  0 19:17 pts/7    00:00:00 mysql -uroot -e set SQL_LOG_BIN=0; source avail_db1.sql;
root     10985     1  0 19:17 pts/7    00:00:00 mysql -uroot -e set SQL_LOG_BIN=0; source avail_db2.sql;
root     10988     1  1 19:17 pts/7    00:00:00 mysql -uroot -e set SQL_LOG_BIN=0; source batchrobot_prod.sql;
root     10990     1  0 19:17 pts/7    00:00:00 mysql -uroot -e set SQL_LOG_BIN=0; source batchrobot_staging.sql;
root     10995     1  0 19:17 pts/7    00:00:00 mysql -uroot -e set SQL_LOG_BIN=0; source engine_prod.sql;
root     10996     1  0 19:17 pts/7    00:00:00 mysql -uroot -e set SQL_LOG_BIN=0; source leadrobot_prod.sql;
root     11002     1  0 19:17 pts/7    00:00:00 mysql -uroot -e set SQL_LOG_BIN=0; source nextgen.sql;
root     11004     1 10 19:17 pts/7    00:00:01 mysql -uroot -e set SQL_LOG_BIN=0; source nextgen_archive.sql;
root     11008     1  0 19:17 pts/7    00:00:00 mysql -uroot -e set SQL_LOG_BIN=0; source staging.sql;
root     11010     1  0 19:17 pts/7    00:00:00 mysql -uroot -e set SQL_LOG_BIN=0; source vphone200704.sql;
root     11013     1  0 19:17 pts/7    00:00:00 mysql -uroot -e set SQL_LOG_BIN=0; source vphone200707.sql;
root     11016     1  0 19:17 pts/7    00:00:00 mysql -uroot -e set SQL_LOG_BIN=0; source vphone200711.sql;
root     11018     1  0 19:17 pts/7    00:00:00 mysql -uroot -e set SQL_LOG_BIN=0; source vphone200712.sql;
root     11020     1  0 19:17 pts/7    00:00:00 mysql -uroot -e set SQL_LOG_BIN=0; source vphone200801.sql;
root     11021     1  0 19:17 pts/7    00:00:00 mysql -uroot -e set SQL_LOG_BIN=0; source vphone200803.sql;
root     11022     1  0 19:17 pts/7    00:00:00 mysql -uroot -e set SQL_LOG_BIN=0; source vphone200804.sql;
root     11023     1  0 19:17 pts/7    00:00:00 mysql -uroot -e set SQL_LOG_BIN=0; source vphone200805.sql;
root     11027     1  0 19:17 pts/7    00:00:00 mysql -uroot -e set SQL_LOG_BIN=0; source vphone200901.sql;
root     11028     1  0 19:17 pts/7    00:00:00 mysql -uroot -e set SQL_LOG_BIN=0; source vzip200805.sql;
root     11030     1  0 19:17 pts/7    00:00:00 mysql -uroot -e set SQL_LOG_BIN=0; source vzip200809.sql;
root     11033     1  0 19:17 pts/7    00:00:00 mysql -uroot -e set SQL_LOG_BIN=0; source vzip200811.sql;
root     11037 10850  0 19:17 pts/7    00:00:00 grep mysql
root@db3.leadconduit.com:/mnt/tmp$


restarting dump after removing the slow query logging, as it was going up from the many simultanous dumps to 5GB within 20 minutes... ETA for restore is prehaps morning.. I will
check it on before EOD tonight


16-Feb-2009 at  6:04pm (GMT -05)Singer X.J. Wang
Severity: 3 - Green

root@db3.leadconduit.com:/var/lib/mysql/innodb$ ls -lh
total 334G
-rw-r----- 1 mysql mysql  72M Feb 16 10:57 ib_logfile0.gz
-rw-r----- 1 mysql mysql  76M Feb 16 03:02 ib_logfile1.gz
-rw-r----- 1 mysql mysql 569M Feb 16 10:57 ibdata1.gz
-rw-r----- 1 mysql mysql 280G Feb 16 10:57 ibdata2
-rw------- 1 root  root   54G Feb 16 18:03 ibdata2.gz
root@db3.leadconduit.com:/var/lib/mysql/innodb$

still going, we're 80% of the way there..


16-Feb-2009 at  1:37pm (GMT -05)Singer X.J. Wang
Severity: 3 - Green
we're about 20% done compression, based on the fact that the compressed file from the backup was ~70MB..

root@db3.leadconduit.com:/var/lib/mysql/innodb$ ls -lh
total 295G
-rw-r----- 1 mysql mysql  72M Feb 16 10:57 ib_logfile0.gz
-rw-r----- 1 mysql mysql  76M Feb 16 03:02 ib_logfile1.gz
-rw-r----- 1 mysql mysql 569M Feb 16 10:57 ibdata1.gz
-rw-r----- 1 mysql mysql 280G Feb 16 10:57 ibdata2
-rw------- 1 root  root   15G Feb 16 13:36 ibdata2.gz
root@db3.leadconduit.com:/var/lib/mysql/innodb$


16-Feb-2009 at 11:49am (GMT -05)Singer X.J. Wang
Severity: 3 - Green
db3:3307 is already innodb_fiel_per_table - I took the liberty of setting that up since it was a brand new instance (and you were moving the origional, I shall call, db3:3306) to it...

since we have 2 instances, I'm using mysqld_multi to manage them.. the configuration for both are in /etc/my.cnf under [mysqld3306] and [mysqld3307] respectively...


16-Feb-2009 at 11:28am (GMT -05)activeprospect common accountSeverity: 3 - Green
Rick Benavidez wrote:
---------------------------
Thanks, Singer! Looks good so far. Is db3:3307 innodb_per_file_table yet or does that need to be done? Also, where does the conf for this instance live?


16-Feb-2009 at 11:00am (GMT -05)Singer X.J. Wang
Severity: 3 - Green
the old database has been restored on instance2 (socket: /var/lib/mysql2/mysql.sock, port: 3307)..

root@localhost:regpath_staging> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hyperic            |
| mysql              |
| regpath            |
| regpath_staging    |
| staging            |
| test               |
| zabbix             |
+--------------------+
8 rows in set (0.00 sec)

root@localhost:regpath_staging>


I'm compressing the slave data before I import the dumps (in case)..


16-Feb-2009 at 10:44am (GMT -05)Singer X.J. Wang
Severity: 3 - Green
I'll start on the import now for 3307 (the second instance)..


16-Feb-2009 at 10:40am (GMT -05)activeprospect common accountSeverity: 3 - Green
Rick Benavidez wrote:
---------------------------
Thanks, Sheeri. Please let me know when that secondary instance has it's data loaded (from the original data on that first instance before the reload) and is ready to use as we need to get that ramped back up as soon as possible.


16-Feb-2009 at 10:34am (GMT -05)Sheeri K. CabralSeverity: 3 - Green
Status Review Reminder: Monday , February 16, 2009



16-Feb-2009 at 10:34am (GMT -05)Sheeri K. CabralSeverity: 3 - Green
CR Modified
Variable Old value New value
DBA Sheeri K. Cabral Singer X.J. Wang


16-Feb-2009 at 12:18am (GMT -05)Singer X.J. Wang
Severity: 3 - Green
mounted new volumn as /var/lib/mysql2 (very uncreative name

root@db3.leadconduit.com:~$ mkfs.xfs /dev/sdh
root@db3.leadconduit.com:~$ mkdir /var/lib/mysql2
root@db3.leadconduit.com:~$ cd /etc/
root@db3.leadconduit.com:/etc$ cp fstab fstab2
eroot@db3.leadconduit.com:/etc$ emacs fstab
root@db3.leadconduit.com:/etc$ diff -u fstab fstab2
--- fstab       2009-02-15 23:42:31.000000000 -0500
+++ fstab2      2009-02-15 23:42:06.000000000 -0500
@@ -1,7 +1,6 @@
 /dev/sda1  /         ext3    defaults        1 1
 /dev/sdb  /mnt      ext3    defaults        1 2
 /dev/sdj  /var/lib/mysql       xfs     noatime,nodiratime      1 2
-/dev/sdh  /var/lib/mysql2      xfs     noatime,nodiratime      1 2
 /swapfile      swap    swap    defaults        0 0
 none       /dev/pts  devpts  gid=5,mode=620  0 0
 none       /dev/shm  tmpfs   defaults        0 0
root@db3.leadconduit.com:/etc$ mount /var/lib/mysql2
root@db3.leadconduit.com:/etc$ df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda1             9.9G  3.4G  6.0G  36% /
/dev/sdb              414G  2.7G  390G   1% /mnt
none                  3.8G     0  3.8G   0% /dev/shm
/dev/sdj              500G  373G  127G  75% /var/lib/mysql
/dev/sdh              100G  544K  100G   1% /var/lib/mysql2
root@db3.leadconduit.com:/etc$


converted my.cnf to mysqld_multi format and created instance 3307 - also ensured instance 3307 (the new one) uses innodb_file_per_table

root@db3.leadconduit.com:/etc$
root@db3.leadconduit.com:/etc$ mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld3306 is running
MySQL server from group: mysqld3307 is not running
root@db3.leadconduit.com:/etc$
root@db3.leadconduit.com:/etc$
root@db3.leadconduit.com:/etc$
root@db3.leadconduit.com:/etc$



[pythian@db3:~/working]
[pythian@db3:~/working]
[pythian@db3:~/working] mysql -uroot -p --socket=/var/lib/mysql2/mysql.sock

Enter password:
\sWelcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 3
Server version: 5.0.67-community-log MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

root@localhost:(none)> \s
--------------
mysql  Ver 14.12 Distrib 5.0.67, for redhat-linux-gnu (x86_64) using readline 5.1

Connection id:          3
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.0.67-community-log MySQL Community Edition (GPL)
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    latin1
Conn.  characterset:    latin1
UNIX socket:            /var/lib/mysql2/mysql.sock
Uptime:                 3 min 29 sec

Threads: 1  Questions: 2635  Slow queries: 1  Opens: 307  Flush tables: 1  Open tables: 29  Queries per second avg: 12.608
--------------

root@localhost:(none)>


I will run the sql files in the morning, as the dumps are already fairly slow..


15-Feb-2009 at 11:38pm (GMT -05)Singer X.J. Wang
Severity: 3 - Green
stopped replication on db2, letting db3 catchup and now we have a master position to point db3->db1 from. now resetting slave on db03 and restarting replication
on db02

root@localhost:(none)> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State:
                Master_Host: 192.168.2.131
                Master_User: replicant
                Master_Port: 3306
              Connect_Retry: 10
            Master_Log_File: db1-binlog.002307
        Read_Master_Log_Pos: 653305988
             Relay_Log_File: db2-relay.000558
              Relay_Log_Pos: 3209031
      Relay_Master_Log_File: db1-binlog.002307
           Slave_IO_Running: No
          Slave_SQL_Running: No
            Replicate_Do_DB:
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table: avail.%,avail\_db_.%,mysql.%,test.%,zabbix.%
                 Last_Errno: 0
                 Last_Error:
               Skip_Counter: 0
        Exec_Master_Log_Pos: 653305988
            Relay_Log_Space: 3209031
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File:
         Master_SSL_CA_Path:
            Master_SSL_Cert:
          Master_SSL_Cipher:
             Master_SSL_Key:
      Seconds_Behind_Master: NULL
1 row in set (0.00 sec)

root@localhost:(none)>



issued a change master statement to switch db3 to replicate from master.. the statement has been censored for password

CHANGE MASTER TO
  MASTER_HOST='67.192.44.131',
  MASTER_USER='repl',
  MASTER_PASSWORD='[PASSWORD]',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='db1-binlog.002307',
  MASTER_LOG_POS=653305988,
  MASTER_CONNECT_RETRY=10,
  MASTER_SSL = 1,
  MASTER_SSL_CA = '/var/lib/mysql/ssl/ca-cert.pem',
  MASTER_SSL_CAPATH = '/var/lib/mysql/ssl',
  MASTER_SSL_CERT = '/var/lib/mysql/ssl/server-cert.pem',
  MASTER_SSL_KEY = '/var/lib/mysql/ssl/server-key.pem';


and now we are about 700s behind the master, catching up VERY rapidly..

root@localhost:(none)> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 67.192.44.131
                Master_User: repl
                Master_Port: 3306
              Connect_Retry: 10
            Master_Log_File: db1-binlog.002307
        Read_Master_Log_Pos: 657212366
             Relay_Log_File: mysql-relay-bin.000002
              Relay_Log_Pos: 31502
      Relay_Master_Log_File: db1-binlog.002307
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB:
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table: avail.%,avail\_db_.%,mysql.%,test.%,zabbix.%
                 Last_Errno: 0
                 Last_Error:
               Skip_Counter: 0
        Exec_Master_Log_Pos: 653337254
            Relay_Log_Space: 3906614
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: Yes
         Master_SSL_CA_File: /var/lib/mysql/ssl/ca-cert.pem
         Master_SSL_CA_Path: /var/lib/mysql/ssl
            Master_SSL_Cert: /var/lib/mysql/ssl/server-cert.pem
          Master_SSL_Cipher:
             Master_SSL_Key: /var/lib/mysql/ssl/server-key.pem
      Seconds_Behind_Master: 716
1 row in set (0.00 sec)

root@localhost:(none)>


stopped slave to take the dump..

root@localhost:(none)>
root@localhost:(none)> slave stop;
Query OK, 0 rows affected (0.05 sec)

root@localhost:(none)> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State:
                Master_Host: 67.192.44.131
                Master_User: repl
                Master_Port: 3306
              Connect_Retry: 10
            Master_Log_File: db1-binlog.002307
        Read_Master_Log_Pos: 679481764
             Relay_Log_File: mysql-relay-bin.000003
              Relay_Log_Pos: 2758132
      Relay_Master_Log_File: db1-binlog.002307
           Slave_IO_Running: No
          Slave_SQL_Running: No
            Replicate_Do_DB:
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table: avail.%,avail\_db_.%,mysql.%,test.%,zabbix.%
                 Last_Errno: 0
                 Last_Error:
               Skip_Counter: 0
        Exec_Master_Log_Pos: 678399445
            Relay_Log_Space: 3840451
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: Yes
         Master_SSL_CA_File: /var/lib/mysql/ssl/ca-cert.pem
         Master_SSL_CA_Path: /var/lib/mysql/ssl
            Master_SSL_Cert: /var/lib/mysql/ssl/server-cert.pem
          Master_SSL_Cipher:
             Master_SSL_Key: /var/lib/mysql/ssl/server-key.pem
      Seconds_Behind_Master: NULL
1 row in set (0.00 sec)

root@localhost:(none)>


dumping to /mnt/tmp purely since it has more free space then ~pythian/working

root@db3.leadconduit.com:/mnt/tmp$
root@db3.leadconduit.com:/mnt/tmp$
root@db3.leadconduit.com:/mnt/tmp$
root@db3.leadconduit.com:/mnt/tmp$
root@db3.leadconduit.com:/mnt/tmp$
root@db3.leadconduit.com:/mnt/tmp$ pwd
/mnt/tmp
root@db3.leadconduit.com:/mnt/tmp$ ls -l
total 0
-rw-r--r-- 1 root root 0 Feb 15 23:33 k
root@db3.leadconduit.com:/mnt/tmp$ df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda1             9.9G  3.4G  6.0G  36% /
/dev/sdb              414G  199M  393G   1% /mnt
none                  3.8G     0  3.8G   0% /dev/shm
/dev/sdj              500G  373G  127G  75% /var/lib/mysql
root@db3.leadconduit.com:/mnt/tmp$ mysqldump -uroot -p --opt --lock-all-tables --all-databases > data.sql
Enter password:









13-Feb-2009 at  5:22pm (GMT -05)Singer X.J. Wang
Severity: 3 - Green
got SSL replication up!



generated the certificates

root@db2 ssl #
root@db2 ssl #
root@db2 ssl # pwd
/var/lib/mysql/ssl
root@db2 ssl # ls -l
total 32
-rw-r--r--  1 mysql mysql 1334 Feb 13 15:40 ca-cert.pem
-rw-r--r--  1 mysql mysql 1675 Feb 13 15:39 ca-key.pem
-rw-r--r--  1 mysql mysql 1103 Feb 13 15:42 client-cert.pem
-rw-r--r--  1 mysql mysql 1679 Feb 13 15:41 client-key.pem
-rw-r--r--  1 mysql mysql  960 Feb 13 15:41 client-req.pem
-rw-r--r--  1 mysql mysql 1103 Feb 13 15:41 server-cert.pem
-rw-r--r--  1 mysql mysql 1679 Feb 13 15:41 server-key.pem
-rw-r--r--  1 mysql mysql  960 Feb 13 15:41 server-req.pem
root@db2 ssl #


modified the my.cnf

root@db2 etc #
root@db2 etc # cd /etc/
root@db2 etc #
root@db2 etc #
root@db2 etc # cp my.cnf my.cnf_ORIGINAL
root@db2 etc # nano -w my.cnf
root@db2 etc # diff -u my.cnf my.cnf_ORIGINAL
--- my.cnf      2009-02-13 15:43:49.000000000 -0600
+++ my.cnf_ORIGINAL     2009-02-13 15:43:09.000000000 -0600
@@ -20,11 +20,6 @@
 old_passwords          = 1
 skip-bdb

-
-ssl-key=/var/lib/mysql/ssl/server-key.pem
-ssl-ca=/var/lib/mysql/ssl/ca-cert.pem
-ssl-cert=/var/lib/mysql/ssl/server-cert.pem
-
 tmpdir                 = /var/lib/mysql/tmp
 socket                 = /var/lib/mysql/mysql.sock
 pid-file               = /var/lib/mysql/mysql.pid
root@db2 etc #


and we have SSL

root@localhost:(none)> show global variables like '%ssl%';
+---------------+------------------------------------+
| Variable_name | Value                              |
+---------------+------------------------------------+
| have_openssl  | YES                                |
| have_ssl      | YES                                |
| ssl_ca        | /var/lib/mysql/ssl/ca-cert.pem     |
| ssl_capath    |                                    |
| ssl_cert      | /var/lib/mysql/ssl/server-cert.pem |
| ssl_cipher    |                                    |
| ssl_key       | /var/lib/mysql/ssl/server-key.pem  |
+---------------+------------------------------------+
7 rows in set (0.01 sec)

root@localhost:(none)>


CHANGE MASTER TO
MASTER_SSL = 1,
MASTER_SSL_CA = '/var/lib/mysql/ssl/ca-cert.pem',
MASTER_SSL_CAPATH = '/var/lib/mysql/ssl',
MASTER_SSL_CERT = '/var/lib/mysql/ssl/server-cert.pem',
MASTER_SSL_KEY = '/var/lib/mysql/ssl/server-key.pem';


and we are now replicating with SSL!
root@localhost:(none)> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 67.192.44.130
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: db2-binlog.001988
Read_Master_Log_Pos: 68621946
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 2277928
Relay_Master_Log_File: db2-binlog.001988
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table: avail.%,avail\_db_.%,mysql.%,test.%,zabbix.%
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 55305370
Relay_Log_Space: 15594504
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: Yes
Master_SSL_CA_File: /var/lib/mysql/ssl/ca-cert.pem
Master_SSL_CA_Path: /var/lib/mysql/ssl
Master_SSL_Cert: /var/lib/mysql/ssl/server-cert.pem
Master_SSL_Cipher:
Master_SSL_Key: /var/lib/mysql/ssl/server-key.pem
Seconds_Behind_Master: 150543
1 row in set (0.00 sec)

root@localhost:(none)>


13-Feb-2009 at  4:35pm (GMT -05)Singer X.J. Wang
Severity: 3 - Green
Conversation with ACTP - Rich Benavidez on 2/13/2009 1:43:27 PM:
(1:43:30 PM) Singer Wang (TPG-AIM): hey RIck
(1:43:35 PM) Singer Wang (TPG-AIM): are you there any chance?
(1:43:38 PM) ACTP - Rich Benavidez: hey man
(1:43:39 PM) ACTP - Rich Benavidez: i am
(1:43:48 PM) Singer Wang (TPG-AIM): Sheeri had to go but I'm finishing up
(1:43:50 PM) Singer Wang (TPG-AIM): but quick question
(1:44:03 PM) Singer Wang (TPG-AIM): she wanted me to ask if you still want to replicate ignore the staging and vphone200901 dbs
(1:45:38 PM) ACTP - Rich Benavidez: just talked to alex
(1:45:59 PM) ACTP - Rich Benavidez: ignore staging
(1:46:07 PM) ACTP - Rich Benavidez: but the vphone and vzip just leave them as-is
(1:46:18 PM) ACTP - Rich Benavidez: they are static databases and shouldn't necessarily be updated (external party data)
(1:46:23 PM) ACTP - Rich Benavidez: but it'll be nice to just leave them in the chain
(1:46:26 PM) Singer Wang (TPG-AIM): okay, will do
(1:46:28 PM) Singer Wang (TPG-AIM): thanks :)
(1:46:31 PM) ACTP - Rich Benavidez: thanks! :)


---

16:30
(4:30:52 PM) Singer Wang (TPG-AIM): hey rick
(4:30:56 PM) Singer Wang (TPG-AIM): I just stoppped replication
(4:30:57 PM) ACTP - Rich Benavidez: hey
(4:31:02 PM) Singer Wang (TPG-AIM): it was unfortunately replicating iwthout SSL
(4:31:03 PM) ACTP - Rich Benavidez: ah, so we didn't have ssl
(4:31:12 PM) Singer Wang (TPG-AIM): we have it on db1 but not db2
(4:31:16 PM) ACTP - Rich Benavidez: ok, what is all that's necessary for us to do that
(4:31:22 PM) ACTP - Rich Benavidez: we just need to add ssl to db2 and restart right?
(4:31:26 PM) ACTP - Rich Benavidez: and then we can start it up?
(4:31:27 PM) Singer Wang (TPG-AIM): pretty much
(4:31:39 PM) ACTP - Rich Benavidez: then after that is when it switches to repl from db1?
(4:31:55 PM) Singer Wang (TPG-AIM): right, when db3 catches up we can go from db2->db1
(4:32:24 PM) ACTP - Rich Benavidez: ok, hopefully it won't take long to add ssl to db2 and restart (you can restart db2 at any time)
(4:32:40 PM) ACTP - Rich Benavidez: please feel free to do so to get replication moving again
(4:32:47 PM) Singer Wang (TPG-AIM): sure, I will do that now
(4:32:54 PM) ACTP - Rich Benavidez: thanks!


13-Feb-2009 at  4:31pm (GMT -05)Singer X.J. Wang
Severity: 3 - Green
It is not using SSL, and I've stopped the replication..


root@localhost:(none)> slave stop;
Query OK, 0 rows affected (0.01 sec)

root@localhost:(none)> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State:
                Master_Host: 67.192.44.130
                Master_User: replicant
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: db2-binlog.001994
        Read_Master_Log_Pos: 461755022
             Relay_Log_File: mysql-relay-bin.000022
              Relay_Log_Pos: 1038275922
      Relay_Master_Log_File: db2-binlog.001987
           Slave_IO_Running: No
          Slave_SQL_Running: No
            Replicate_Do_DB:
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table: avail.%,avail\_db_.%,mysql.%,test.%,zabbix.%
                 Last_Errno: 0
                 Last_Error:
               Skip_Counter: 0
        Exec_Master_Log_Pos: 1038275784
            Relay_Log_Space: 7978039294
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File:
         Master_SSL_CA_Path:
            Master_SSL_Cert:
          Master_SSL_Cipher:
             Master_SSL_Key:
      Seconds_Behind_Master: NULL
1 row in set (0.00 sec)

root@localhost:(none)>


I can generate SSL certificates for db02 and use them to replicate with SSL, but that will unfortunately require a restart of db02..


13-Feb-2009 at  4:19pm (GMT -05)activeprospect common accountSeverity: 3 - Green
Rick Benavidez wrote:
---------------------------
Can someone confirm that we are using SSL over the wire right now to catch up on replication? If I understand what I'm reading correctly right now we are not which is a problem. All communication outside the local network should definitely be encrypted. Please stop replication if this is the case and let us know so we can figure out how to proceed next. Thanks!


13-Feb-2009 at  2:00pm (GMT -05)Singer X.J. Wang
Severity: 3 - Green

root@localhost:(none)>
root@localhost:(none)> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 67.192.44.130
                Master_User: replicant
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: db2-binlog.001993
        Read_Master_Log_Pos: 1024737519
             Relay_Log_File: mysql-relay-bin.000013
              Relay_Log_Pos: 232523496
      Relay_Master_Log_File: db2-binlog.001984
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB:
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table: avail.%,avail\_db_.%,mysql.%,test.%,zabbix.%
                 Last_Errno: 0
                 Last_Error:
               Skip_Counter: 0
        Exec_Master_Log_Pos: 232523358
            Relay_Log_Space: 10688510010
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File:
         Master_SSL_CA_Path:
            Master_SSL_Cert:
          Master_SSL_Cipher:
             Master_SSL_Key:
      Seconds_Behind_Master: 201023
1 row in set (0.00 sec)

root@localhost:(none)> select 201023/60/60;
+--------------+
| 201023/60/60 |
+--------------+
|  55.83972222 |
+--------------+
1 row in set (0.00 sec)

root@localhost:(none)>


its been 2 hours in real time, and we're now only 56 hours beind.. so approximately 1 real hour = 8 hours behind time...


13-Feb-2009 at  1:49pm (GMT -05)Singer X.J. Wang
Severity: 3 - Green
13:40
(1:43:30 PM) Singer Wang (TPG-AIM): hey RIck
(1:43:35 PM) Singer Wang (TPG-AIM): are you there any chance?
(1:43:38 PM) ACTP - Rich Benavidez: hey man
(1:43:39 PM) ACTP - Rich Benavidez: i am
(1:43:48 PM) Singer Wang (TPG-AIM): Sheeri had to go but I'm finishing up
(1:43:50 PM) Singer Wang (TPG-AIM): but quick question
(1:44:03 PM) Singer Wang (TPG-AIM): she wanted me to ask if you still want to replicate ignore the staging and vphone200901 dbs
13:45
(1:45:38 PM) ACTP - Rich Benavidez: just talked to alex
(1:45:59 PM) ACTP - Rich Benavidez: ignore staging
(1:46:07 PM) ACTP - Rich Benavidez: but the vphone and vzip just leave them as-is
(1:46:18 PM) ACTP - Rich Benavidez: they are static databases and shouldn't necessarily be updated (external party data)
(1:46:23 PM) ACTP - Rich Benavidez: but it'll be nice to just leave them in the chain
(1:46:26 PM) Singer Wang (TPG-AIM): okay, will do
(1:46:28 PM) Singer Wang (TPG-AIM): thanks :)
(1:46:31 PM) ACTP - Rich Benavidez: thanks! :)


13-Feb-2009 at 12:42pm (GMT -05)Singer X.J. Wang
Severity: 3 - Green
Hi Rick/Alex,

I was just talking to Sheeri, I just want to confirm that you want to replicate-ignore-db those staging and vphone200901..


13-Feb-2009 at 12:41pm (GMT -05)Singer X.J. Wang
Severity: 3 - Green
Hi Rick, I don't see an issue. I'm going to start in about 30 right after I get some lunch.


13-Feb-2009 at 12:23pm (GMT -05)activeprospect common accountSeverity: 3 - Green
Rick Benavidez wrote:
---------------------------
Ok, so here's the plan for now.

Please create a new mysql instance on db3 apart from the new one that is replicating from db2. This new instance will serve as the primary database for projects coming in via ec2 itself.

I've created a new 100GB ebs volume apart from the current 500GB assigned to the first mysql instance. Please mount this (/dev/sdh) and create a new xfs partition for us to use with the new instance. We'll have to dial back the amount of memory allotted to db3-1. Since we have plenty of memory on that box it'd be nice to give 2GB to this new instance. Once it's up please restore the old data that previously existed on the primary instance before we restored that hot backup. That should get us most of the way there for now. Please let me know if you have any questions.

Thanks!


13-Feb-2009 at 11:55am (GMT -05)Sheeri K. Cabral2 MinutesSeverity: 3 - Green
Currently we're still 72h behind in replication, so once that's caught up we'll switch to db1 and the ssl user ('repl' can only connect using ssl, same password as the 'replicant' user).

Then we'll do the innodb_file_per_table dump and load.


13-Feb-2009 at 11:53am (GMT -05)Sheeri K. Cabral4 MinutesSeverity: 3 - Green
#### Added the user on db1:

GRANT REPLICATION SLAVE ON *.* TO repl@'%.compute-1.amazonaws.com' IDENTIFIED BY 'replicant' REQUIRE SSL;

####### Copied over the client certificates and the ca-cert, and made a connection, confirming the connection was via ssl:

[mysql@db3:~] mysql -u repl -p -h db1.leadconduit.com --ssl-ca=/var/lib/mysql/data/ssl/ca-cert.pem
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 76114
Server version: 5.0.67-community-log MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

repl@db1.leadconduit.com:(none)> show global status like 'ssl_cipher%'\G
*************************** 1. row ***************************
Variable_name: Ssl_cipher
Value: DHE-RSA-AES256-SHA
*************************** 2. row ***************************
Variable_name: Ssl_cipher_list
Value: DHE-RSA-AES256-SHA:DHE-DSS-AES256-SHA:AES256-SHA:DHE-RSA-AES128-SHA:DHE-DSS-AES128-SHA:AES128-SHA:AES256-RMD:AES128-RMD:DES-CBC3-RMD:DHE-RSA-AES256-RMD:DHE-RSA-AES128-RMD:DHE-RSA-DES-CBC3-RMD:DHE-DSS-AES256-RMD:DHE-DSS-AES128-RMD:DHE-DSS-DES-CBC3-RMD:RC4-SHA:RC4-MD5:DES-CBC3-SHA:DES-CBC-SHA:EDH-RSA-DES-CBC3-SHA:EDH-DSS-DES-CBC3-SHA:EDH-RSA-DES-CBC-SHA:EDH-DSS-DES-CBC-SHA
2 rows in set (0.08 sec)


13-Feb-2009 at 11:26am (GMT -05)activeprospect common accountSeverity: 3 - Green
Rick Benavidez wrote:
---------------------------
Sheeri, can you hold on adding back the original databases on db3 back into the system (I see they are not back yet). We are considering leaving db3 as a clean replicate and perhaps making a separate instance for other things we want to do. Please ping us on IM when you have a moment. Thanks!


13-Feb-2009 at 11:19am (GMT -05)Sheeri K. Cabral18 MinutesSeverity: 3 - Green
Imported the staging database, and replication started back up fine. Waiting for replication to catch up, then will switch to replicating db1, then will proceed with conversion to innodb_file_per_table.


13-Feb-2009 at 10:44am (GMT -05)activeprospect common accountSeverity: 3 - Green
Rick Benavidez wrote:
---------------------------
Alex dutifully reminded me that we needed 3306 from db3 to db2 so he's now punched the appropriate hole in the firewall inbound from db3.


13-Feb-2009 at 10:33am (GMT -05)activeprospect common accountSeverity: 3 - Green
Rick Benavidez wrote:
---------------------------
I've punched a hole for mysql traffic to get through to db3 from our network range at rackspace. This should be good now (tested OK) and you can move forward now. Excellent job moving this all forward, Sheeri. Thanks!


13-Feb-2009 at  9:35am (GMT -05)Sheeri K. CabralSeverity: 3 - Green
Status Review Reminder: Friday , February 13, 2009



12-Feb-2009 at  8:52pm (GMT -05)Sheeri K. Cabral3 MinutesSeverity: 3 - Green
current status:

Replication is set up, but db3 cannot connect to the master to retrieve the binary logs. Can we make sure that db1 and db2 can allow connections from db3?

The replicant user is at host %, and the previous comment's telnet attempts clearly show that there's a firewall in place.

Note: db3 has to replicate db2 first, when it catches up we can:

a) stop slave on db2
b) make sure db3 is caught up to db2
c) change db3 to use the binlog file and position from db2 (of where it is in db1)
d) start slave on db3, make sure it works
e) start slave on db2


12-Feb-2009 at  8:49pm (GMT -05)Sheeri K. Cabral5 MinutesSeverity: 3 - Green
090212 20:45:43 [ERROR] Slave I/O thread: error connecting to master 'replicant@67.192.44.130:3306': Error: 'Can't connect to MySQL server on '67.192.44.130' (4)' errno: 2003 retry-time: 60 retries: 86400
Query OK, 0 rows affected (2.01 sec)



pythian@db2 ~ $ /sbin/ifconfig -a | grep inet
inet addr:192.168.2.130 Bcast:192.168.2.255 Mask:255.255.255.0
inet addr:10.241.107.114 Bcast:10.241.127.255 Mask:255.255.224.0
inet addr:172.28.1.127 Bcast:172.28.255.255 Mask:255.255.0.0
inet addr:127.0.0.1 Mask:255.0.0.0
pythian@db2 ~ $ hostname
db2.leadconduit.com


[pythian@db3:~] telnet 67.192.44.130 3306
Trying 67.192.44.130...
^C
[pythian@db3:~] telnet 10.241.107.114 3306
Trying 10.241.107.114...
^C
[pythian@db3:~] telnet 172.28.1.12 3306
Trying 172.28.1.12...

[pythian@db3:~] telnet 172.28.1.127 3306
Trying 172.28.1.127...

[pythian@db3:~] /sbin/ifconfig -a | grep inet
inet addr:10.251.51.48 Bcast:10.251.51.255 Mask:255.255.254.0
inet addr:127.0.0.1 Mask:255.0.0.0
[pythian@db3:~] telnet db2.leadconduit.com 3306
Trying 67.192.44.130...


12-Feb-2009 at  8:45pm (GMT -05)Sheeri K. Cabral16 MinutesSeverity: 3 - Green
w00t! It started!

[pythian@db3:~] sudo /etc/init.d/mysql start
Starting MySQL.090212 20:29:48 mysqld started
....InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
090212 20:29:50 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: In a MySQL replication slave the last master binlog file
InnoDB: position 0 626738740, file name db1-binlog.002285
InnoDB: Last MySQL binlog file position 0 495985099, file name /var/lib/mysql/binlog/db2-binlog.001980
090212 20:29:52 InnoDB: Started; log sequence number 670 2278117388
090212 20:29:52 [Note] Recovering after a crash using /var/lib/mysql/binlog/db1-binlog
090212 20:29:52 [Note] Starting crash recovery...
090212 20:29:52 [Note] Crash recovery finished.
[ OK ]
[pythian@db3:~] 090212 20:29:52 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.0.67-community-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Edition (GPL)


And I connected and looked at the table status for all tables in nextgen.


Now the big test:

root@localhost:(none)> CHANGE MASTER TO MASTER_HOST='67.192.44.130', master_port=3306, master_user='replicant', master_password=ELIDED, master_log_file='db2-binlog.001980', master_log_pos=495985099;
Query OK, 0 rows affected (0.03 sec)

root@localhost:(none)> slave start;
Query OK, 0 rows affected (0.00 sec)

root@localhost:(none)> 090212 20:44:16 [Note] Slave SQL thread initialized, starting replication in log 'db2-binlog.001980' at position 495985099, relay log './mysql-relay-bin.000001' position: 4

Woo hoo! replicating!


12-Feb-2009 at  8:29pm (GMT -05)Sheeri K. Cabral39 MinutesSeverity: 3 - Green
SQUEE!!!!

[mysql@db3:~] more mysql-stdout
innobackup hello 3
innobackup hello 3
innobackup hello 4
innobackup hello 4
innobackup hello 5
innobackup hello 5
Note (Code 1051): Unknown table 'ibbackup_binlog_marker'
innobackup hello 6
innobackup hello 6
Warning (Code 1287): 'TYPE=storage_engine' is deprecated; use 'ENGINE=storage_engine' instead
innobackup hello 7
innobackup hello 7
innobackup hello 8
innobackup hello 8
innobackup hello 9
innobackup hello 9
innobackup hello 10
innobackup hello 10
innobackup hello 11
innobackup hello 11
File Position Binlog_Do_DB Binlog_Ignore_DB
db2-binlog.001980 495985099
innobackup hello 12
innobackup hello 12
innobackup hello 13
innobackup hello 13
innobackup hello 14
innobackup hello 14

It has the binary log file and position!!!

STarting the mysql server now, crossing fingers that the tables come up well.


12-Feb-2009 at  7:50pm (GMT -05)Sheeri K. Cabral13 MinutesSeverity: 3 - Green
OK, saving the previous database and moving the new one in place:

[mysql@db3:~]ls
binlog data innodb log newdb2 tmp
[mysql@db3:~] mkdir previousdb
[mysql@db3:~] ls -l tmp/
total 0
[mysql@db3:~] cp -pr binlog/ data/ innodb/ log/ tmp/ previousdb/
[mysql@db3:~] rm -rf binlog/* data/* innodb/* log/* tmp/*

Moving files:

[mysql@db3:~] mv newdb2/2009-02-09_22-01-38/* .
[mysql@db3:~] mv avail_db* batchrobot_* blacklistd/ engine_prod/ leadrobot_prod/ maatkit/ nextgen* staging/ test/ vphone200* vzip2008* data/
[mysql@db3:~] ls
backup-my.cnf binlog ibbackup_binlog_info ibdata2 log mysql-stdout
backup-my.cnf_ORIGINAL data ibbackup_logfile ibdata2.ibz lost+found newdb2
backup-my.cnf~ ib_logfile0 ibdata1 innodb mysql previousdb
bin ib_logfile1 ibdata1.ibz k mysql-stderr tmp
[mysql@db3:~] mv bin data/
[mysql@db3:~] mv ib_logfile* ibdata? innodb/

The salient information in backup-my.cnf is:

innodb_data_file_path=ibdata1:2432M;ibdata2:1024M:autoextend
innodb_log_files_in_group=2
innodb_log_file_size=256M

So I've made sure that's in the my.cnf.



12-Feb-2009 at  7:37pm (GMT -05)Sheeri K. Cabral24 MinutesSeverity: 3 - Green
Unfortunately, the InnoDB hot backup shows this in the log:

innobackup: MySQL binlog position: filename 'Warning', position (Code 1287): 'TYPE=storage_engine' is deprecated; use 'ENGINE=storage_engine' instead

So the code to find and print the binlog position uses a deprecated feature. Actually I found this bug in my search:
http://bugs.mysql.com/bug.php?id=30786

which indicates that there's a table used to store the binary log position. It's still there, but it's an innodb table, so I will try to see if the number is in there (mysql.ibbackup_binlog_marker).

I have to restore the hot backup first to do that, so I shall.


12-Feb-2009 at  7:12pm (GMT -05)Sheeri K. Cabral4 MinutesSeverity: 3 - Green
Dumping the existing databases:

root@localhost:(none)> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hyperic |
| mysql |
| regpath |
| regpath_staging |
| zabbix |
+--------------------+
6 rows in set (0.00 sec)

root@localhost:(none)> exit
Bye

[pythian@db3:~/working] ls
[pythian@db3:~/working] mysqldump hyperic > hyperic.sql ; mysqldump mysql > mysql.sql; mysqldump regpath > regpath.sql; mysqldump regpath_staging > regpath_staging.sql; mysqldump zabbix > zabbix
[pythian@db3:~/working] ls -lrth
total 5.6M
-rw-rw-r-- 1 pythian pythian 408K Feb 12 19:11 mysql.sql
-rw-rw-r-- 1 pythian pythian 4.1M Feb 12 19:11 hyperic.sql
-rw-rw-r-- 1 pythian pythian 1.1M Feb 12 19:11 zabbix
-rw-rw-r-- 1 pythian pythian 26K Feb 12 19:11 regpath_staging.sql
-rw-rw-r-- 1 pythian pythian 8.7K Feb 12 19:11 regpath.sql


took very little time to do.


12-Feb-2009 at  2:20pm (GMT -05)Singer X.J. WangSeverity: 3 - Green
CR Modified
Variable Old value New value
DBA Singer X.J. Wang Sheeri K. Cabral


12-Feb-2009 at  1:38pm (GMT -05)Singer X.J. WangSeverity: 3 - Green
Status Review Reminder: Thursday , February 12, 2009



12-Feb-2009 at  1:37pm (GMT -05)Singer X.J. Wang
Severity: 3 - Green
I've just fixed the backups so that in the future it won't happen again. I'm now continuing my work on this..


12-Feb-2009 at  1:26pm (GMT -05)activeprospect common accountSeverity: 3 - Green
Alex wrote:
---------------------------
Any update on this?


11-Feb-2009 at  7:41pm (GMT -05)Singer X.J. Wang
Severity: 3 - Green
The slave binary log position should be included, but we're not sure why it wasn't. We are working on fixing it for future backups. For now we have a plan to build the db03 out..


1) msysqldump and also save the physical the existing data (db3 - origional -- this was on there before)
2) get the hotbackup working
3) guesstimate the binary log position and start replication, ignoring all the errors
4) do a sync check
5) restore the dump from step 1
6) rebuild as innodb_file_per_table using dump/restore



11-Feb-2009 at  6:30pm (GMT -05)activeprospect common accountSeverity: 3 - Green
Rick Benavidez wrote:
---------------------------
Singer,

Is this something we messed up during transfer? (did we miss something to move over?) Or is this a function of hotbackup that it should have given us that binary log position somewhere? Just want to make sure if we need this for the future or for some awful reason have to do this import again with a newer data set that we can ensure the hotbackup is correct and suiting our needs.

Thanks!


11-Feb-2009 at  6:26pm (GMT -05)Singer X.J. Wang
Severity: 3 - Green
we're missing some FRM files for the staging.* databases (I will recreate those).. what is particularly more worrisome is that the innodb backup didn't give us a binary log position to rebuild the slave from...


innobackup: Backup created in directory '/backup/hot_backup/db2.actp/working/2009-02-09_22-01-38'
innobackup: MySQL binlog position: filename 'Warning', position (Code 1287): 'TYPE=storage_engine' is deprecated; use 'ENGINE
=storage_engine' instead
090210 03:55:04  innobackup: innobackup completed OK!
[OK]: Deleting the folder "/backup/hot_backup/db2.actp/current" ...
  -- DEBUG: filename = /backup/hot_backup/db2.actp/current/1_2009-02-09_22h01m37s
  -- DEBUG: date_time = 2009-02-10 03:55:04



solution: I'm going to guesstimate the binary log position, start replication.. ignore errors and then do a sync check...


11-Feb-2009 at  5:54pm (GMT -05)Sheeri K. CabralSeverity: 3 - Green
CR Modified
Variable Old value New value
DBA Sheeri K. Cabral Singer X.J. Wang


11-Feb-2009 at  5:34pm (GMT -05)activeprospect common accountSeverity: 3 - Green
Rick Benavidez wrote:
---------------------------
It'd be great if you could take that mysqldump and then reimport the databases when ready. There are only a couple of databases in there and they are tiny. Also, will need the mysql database so we can at least make sure to recreate the few grants we have specifically for these databases. Let me know if you have any further questions. Thanks!


11-Feb-2009 at  5:27pm (GMT -05)Singer X.J. Wang
Severity: 3 - Green
Hi Rick,

Its almost done uncompressioning and applying logs. The plan mentioned you took a logical export of the data on db3 now? Is it available somewhere? Or should i just do one?


11-Feb-2009 at  5:08pm (GMT -05)Singer X.J. Wang
Severity: 3 - Green
still applying logs...


11-Feb-2009 at  2:08pm (GMT -05)Singer X.J. Wang
Severity: 3 - Green
started applying the logs from ibbackup..


~pythian/ibbackup --uncompress --apply-log /var/lib/mysql/newdb2/2009-02-09_22-01-38/backup-my.cnf


11-Feb-2009 at  1:48pm (GMT -05)Singer X.J. Wang
Severity: 3 - Green
okay, thanks..

also a few minutes at looking over the plan


11-Feb-2009 at 12:47pm (GMT -05)activeprospect common accountSeverity: 3 - Green
Rick Benavidez wrote:
---------------------------
Transfer is complete. Items are in /var/lib/mysql/newdb2 - bouncing it back into ya'lls court. Thanks!


11-Feb-2009 at  6:04am (GMT -05)Danil Zburivsky
Severity: 3 - Green
acknowledged.


11-Feb-2009 at 12:10am (GMT -05)activeprospect common accountSeverity: 3 - Green
Rick Benavidez wrote:
---------------------------
This transfer is still moving right along - close to 50% on that largest file - hope to be done early tomorrow. Will update when it is...


10-Feb-2009 at  5:30pm (GMT -05)activeprospect common accountSeverity: 3 - Green
Rick Benavidez wrote:
---------------------------
I've restarted the transfer myself on db2 (whose route to db3 looks better than the reverse) in a screen session with both -C and -vvv to see if we can get ssh to tell us what's causing the stall. So far so good - only 1 minor stall that lasted about 10 or so seconds - it's most of the way through ibdata1 - ibdata2 (the largest file) is next. Crossing my fingers - if it goes through I'll bounce this back over to you.


10-Feb-2009 at  5:30pm (GMT -05)Sheeri K. CabralSeverity: 3 - Green
CR Modified
Variable Old value New value
CR Status 1 - New 3 - Pending

OK



10-Feb-2009 at  4:31pm (GMT -05)activeprospect common accountSeverity: 3 - Green
Rick Benavidez wrote:
---------------------------
Yeah, let's go ahead and hold on this until I can reach rackspace and see what the best options are.


10-Feb-2009 at  4:26pm (GMT -05)Sheeri K. Cabral5 MinutesSeverity: 3 - Green
Just checked -- it stalled after 427 MB. So I'm not sure what makes it stall and why. Maybe we need to rate-limit the copy? Calling rackspace would be a good idea, ask them what the best way to do a one-time copy 90G would be.


10-Feb-2009 at  4:00pm (GMT -05)activeprospect common accountSeverity: 3 - Green
Rick Benavidez wrote:
---------------------------
All the proper holes and setup should be in place and it shouldn't stall (maybe we should set -v to see if ssh will give us any more info on any stalls. Also, would -C save us some time and bandwidth (since the data is not compressed) in this case? Let us know if you start getting anything less than 1MB/s - if we get too slow I might enlist rackspace to make sure we they can provide us the best route possible to db3.


10-Feb-2009 at  3:44pm (GMT -05)Sheeri K. Cabral3 MinutesSeverity: 3 - Green
Starting the backup again, this time calling it from a screen session on db3:

[pythian@db3:~] scp -pr db2.leadconduit.com:/backup/hot_backup/old/db2.actp/current_2009-02-10_03h55m04s /var/lib/mysql/newdb

This seems to be running more smoothly, but I'll check back in 10 min or so to be sure.


10-Feb-2009 at  3:40pm (GMT -05)Sheeri K. Cabral11 MinutesSeverity: 3 - Green
The copy is stalling after about 40 Mb being copied. Is there some kind of firewall perhaps on db2?


10-Feb-2009 at  3:29pm (GMT -05)Sheeri K. Cabral2 MinutesSeverity: 3 - Green
Started copy in a screen session on db2:

pythian@db2 db2.actp $ scp -pr current_2009-02-10_03h55m04s/ db3:/var/lib/mysql/newdb


copy should take about a day.


10-Feb-2009 at  3:06pm (GMT -05)Sheeri K. CabralSeverity: 3 - Green
This CR was created by: Sheeri K. Cabral