migration via replication for large DB?
Hello. I need to migrate a master and slave to new hardware. The DB is approx 50G on disk and my time window for downtime is approximately 4 hours. My question is, is it advisable to do a mysqldump from the old master and then load on the new master and slave, or is it faster to just set the new master up as a slave, and when it catches up to the old master I flip the switch? The catch is that the new hardware is in a different datacenter. In other words, which is faster: dump and load or replication over the Internet? Thanks for your time.
Need Help Writing Simple Query
I have been away from sql for awhile, and can't seem to figure out how to write a simple query for two tables. Table 1 has many columns, two of which are hID and vID. Table 2 has two columns, ID and name. The hID and vID in table 1 correspond to the IDs in table 2. I want to make a query so I get all the columns from table 1, but substitute the names from table 2 for the hID and vID values. For example, Table 1: col 1, col 2, hID, vID, col 3 AB1 2 C Table 2: ID, name 1fred 2sam Query result: col1, col 2, hName, vName, col 3 A Bfred sam C Thanks! Mark
Re: Need Help Writing Simple Query
Hi Mark, Please test this query: select test1.*, (select name from test2 where test2.id=test1.`v_id` limit 1) as name_1, (select name from test2 where test2.id=test1.`h_id` limit 1) as name_2 from test1; - test1 table: col1v_idh_id America 1 2 - test2 table: id name 2 SAM 1 UNCLE - Original Message - From: Mark Phillips m...@phillipsmarketing.biz To: Mysql List mysql@lists.mysql.com Sent: Monday, July 26, 2010 8:29:00 AM Subject: Need Help Writing Simple Query I have been away from sql for awhile, and can't seem to figure out how to write a simple query for two tables. Table 1 has many columns, two of which are hID and vID. Table 2 has two columns, ID and name. The hID and vID in table 1 correspond to the IDs in table 2. I want to make a query so I get all the columns from table 1, but substitute the names from table 2 for the hID and vID values. For example, Table 1: col 1, col 2, hID, vID, col 3 AB1 2 C Table 2: ID, name 1fred 2sam Query result: col1, col 2, hName, vName, col 3 A Bfred sam C Thanks! Mark -- Best Regards, Cuongmc. -- Nguyen Manh Cuong Phong Ky Thuat - Cong ty Vien Thong So - VTC Dien thoai: 0912051542 Gmail : philipscu...@gmail.com YahooMail : philipscu...@yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Need Help Writing Simple Query
On 07/25/2010 09:29 PM, Mark Phillips wrote: I have been away from sql for awhile, and can't seem to figure out how to write a simple query for two tables. Table 1 has many columns, two of which are hID and vID. Table 2 has two columns, ID and name. The hID and vID in table 1 correspond to the IDs in table 2. I want to make a query so I get all the columns from table 1, but substitute the names from table 2 for the hID and vID values. For example, Table 1: col 1, col 2, hID, vID, col 3 AB1 2 C Table 2: ID, name 1fred 2sam Query result: col1, col 2, hName, vName, col 3 A Bfred sam C select a, b, c, hTable.name as hName, vTable.name as vName from Table1, Table2 as hTable, Table2 as vTable where hId = hTable.ID and vID = vTable.ID or select a, b, c, hTable.name as hName, vTable.name as vName from Table1 left join Table2 as hTable on hID = hTable.ID left join Table2 as vTable on vID = vTable.ID -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: migration via replication for large DB?
On Sun, Jul 25, 2010 at 12:55 PM, Geoff Galitz ge...@galitz.org wrote: Hello. I need to migrate a master and slave to new hardware. The DB is approx 50G on disk and my time window for downtime is approximately 4 hours. My question is, is it advisable to do a mysqldump from the old master and then load on the new master and slave, or is it faster to just set the new master up as a slave, and when it catches up to the old master I flip the switch? The catch is that the new hardware is in a different datacenter. In other words, which is faster: dump and load or replication over the Internet? Thanks for your time. There may be significantly better options available to you. What version are you coming from and what version are you to? What engines do you use (Innodb, MyISAM,etc)? Is the data directory currently mounted on a lvm volume? -- Rob Wultsch wult...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
