RE: Rename Database - Why Would This Not Work?
RENAME TABLE olddb.table1 TO newdb.table1, olddb.table2 TO newdb.table2 put the whole list in here, the whole statement will be applied to the system atomically The database has 1200+ tables, so your approach seems like more work to me. As it is, all I'm doing is: service mysql stop mv olddb newdb service mysql start mysqlcheck -o newdb -- Eric Robinson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Rename Database - Why Would This Not Work?
Does this work if any of the tables are InnoDB? On Mon, Nov 23, 2009 at 8:17 AM, Robinson, Eric eric.robin...@psmnv.comwrote: RENAME TABLE olddb.table1 TO newdb.table1, olddb.table2 TO newdb.table2 put the whole list in here, the whole statement will be applied to the system atomically The database has 1200+ tables, so your approach seems like more work to me. As it is, all I'm doing is: service mysql stop mv olddb newdb service mysql start mysqlcheck -o newdb -- Eric Robinson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=jlyons4...@gmail.com -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
Re: Rename Database - Why Would This Not Work?
You don't even need to stop the server afaik. As mentioned previously, though, works for MyISAM only. On Mon, Nov 23, 2009 at 3:17 PM, Robinson, Eric eric.robin...@psmnv.comwrote: RENAME TABLE olddb.table1 TO newdb.table1, olddb.table2 TO newdb.table2 put the whole list in here, the whole statement will be applied to the system atomically The database has 1200+ tables, so your approach seems like more work to me. As it is, all I'm doing is: service mysql stop mv olddb newdb service mysql start mysqlcheck -o newdb -- Eric Robinson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be
Re: Rename Database - Why Would This Not Work?
On Mon, Nov 23, 2009 at 7:37 AM, Johan De Meersman vegiv...@tuxera.bewrote: You don't even need to stop the server afaik. As mentioned previously, though, works for MyISAM only. While this is strictly true there are some big caveats (flushing tables, etc). It is safer to shut down the database before moving files around. -- Rob Wultsch wult...@gmail.com
Re: Rename Database - Why Would This Not Work?
On Sat, Nov 21, 2009 at 2:43 PM, Robinson, Eric eric.robin...@psmnv.comwrote: DB engines that have their own data dictionary (Innodb, etc) in addition to what is in the .frm could easily be messed up. Like I said, there are only MyISAM tables in the database, so would there be any risks associated with my simple approach? (Also there are no stored procedures because this is MySQL 4.1.22.) --Eric Within your specific circumstances what you suggest should be safe. -- Rob Wultsch wult...@gmail.com
Re: Rename Database - Why Would This Not Work?
Safer and much less work: since you have the luxury of stopping the server, stop it, restarting with skip-networking and log in from a local console which should guarantee that you are alone on the system. RENAME TABLE olddb.table1 TO newdb.table1, olddb.table2 TO newdb.table2 put the whole list in here, the whole statement will be applied to the system atomically meaning all changes logically take place at the same time. I have done this under 4.1 at an industrial level.. works like a charm for any table type I tried. - michael dykman On Sun, Nov 22, 2009 at 3:01 AM, Rob Wultsch wult...@gmail.com wrote: On Sat, Nov 21, 2009 at 2:43 PM, Robinson, Eric eric.robin...@psmnv.comwrote: DB engines that have their own data dictionary (Innodb, etc) in addition to what is in the .frm could easily be messed up. Like I said, there are only MyISAM tables in the database, so would there be any risks associated with my simple approach? (Also there are no stored procedures because this is MySQL 4.1.22.) --Eric Within your specific circumstances what you suggest should be safe. -- Rob Wultsch wult...@gmail.com -- - michael dykman - mdyk...@gmail.com May you live every day of your life. Jonathan Swift Larry's First Law of Language Redesign: Everyone wants the colon. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Rename Database - Why Would This Not Work?
I used a simple procedure to rename my MySQL 4.1.22 database, which has only My-ISAM tables: 1. Stopped MySQL 2. Renamed the database directory from olddbname to newdbname 3. Started mysql At this point, I grepped for 'olddbname' and found that many of the old .MYI files still had references to 'olddbname'. So I... 4. Did mysqlcheck -o newdbname Then all the references to 'olddbname' were removed from the index files. I then started our application and everything seems to be working fine using the new database name. Yet I'm still worried because when I Google it, I see people talking about lots of different ways to do a database rename, and people are making it sound like a complicated, dangerous procedure. Why would my simple approach not work? Should I be watching for potential problems down the road because I did it this way? -- Eric Robinson Director of Information Technology Physician Select Management, LLC 775.885.2211 x 111
Re: Rename Database - Why Would This Not Work?
DB engines that have their own data dictionary (Innodb, etc) in addition to what is in the .frm could easily be messed up. On Sat, Nov 21, 2009 at 10:38 AM, Robinson, Eric eric.robin...@psmnv.comwrote: I used a simple procedure to rename my MySQL 4.1.22 database, which has only My-ISAM tables: 1. Stopped MySQL 2. Renamed the database directory from olddbname to newdbname 3. Started mysql At this point, I grepped for 'olddbname' and found that many of the old .MYI files still had references to 'olddbname'. So I... 4. Did mysqlcheck -o newdbname Then all the references to 'olddbname' were removed from the index files. I then started our application and everything seems to be working fine using the new database name. Yet I'm still worried because when I Google it, I see people talking about lots of different ways to do a database rename, and people are making it sound like a complicated, dangerous procedure. Why would my simple approach not work? Should I be watching for potential problems down the road because I did it this way? -- Eric Robinson Director of Information Technology Physician Select Management, LLC 775.885.2211 x 111 -- Rob Wultsch wult...@gmail.com
RE: Rename Database - Why Would This Not Work?
DB engines that have their own data dictionary (Innodb, etc) in addition to what is in the .frm could easily be messed up. Like I said, there are only MyISAM tables in the database, so would there be any risks associated with my simple approach? (Also there are no stored procedures because this is MySQL 4.1.22.) --Eric
