Upload new dbs, then get permissions for all staging admins to restore/backup staging db

In IRC amazon asked if there is any way to adapt the stagingdb restore/backup scripts to run from stagingvm. As far as I know there is not, the script on staging db works by copying the entire mysql directory for the db in question. It doesn't actually make a db connection/dump.

Using a different way of doing backups and restore from stagingvm was also discussed: it could be done entirely on stagingvm if a db connection is made from stagingvm and the dump is stored stagingvm. But to do this the dbs would have to have export/import perms on them, which they don't seem to now. Allowing export/import perms might have unacceptable security implications, because there would then be dump files existing on stagingvm which a lot of people have access to.

Fulfilling the requirements of getting scripts/access for redesign admins to restore/backup should also end up completing http://drupal.org/node/632530 (get new db dump in all staging sites), as these are the tools we'll use to get the dbs in place.

Hm. A disk size issue on stagingvm has scuttled the possibility of solely using stagingvm to handle dumping/importing all the dbs. stagingvm has a 32gb hard drive, and 26gb of it is used already. I'm actually concerned that it has enough for file system needs at this point let alone database....(may that needs to be a separate issue, eh?)

Marking critical because http://drupal.org/node/632530 (updating db across all staging sites) was going to be fulfilled in the course of completing this issue, though the issues can be untied from one another if need be (e.g., if this issue gets blocked for any amount of time, I can just copy across the databases from stagingdb instead and we can take care of admin rights to restore/dump later).

The importance of the disk space has changed following conversations in IRC. stagingvm shouldn't have disk space issues since we're going to use just one file directory, and we will not be using it for db backup either.

Also, instead of trying to solve a) getting new db across all staging sites, AND, b) get perms for restore/backup for all staging admins, I'm having to break things into smaller do-able steps (we need nnewton's time to solve everything below):

So here goes the latest attempt at prioritizing things in such a way that we can at least keep forward progress every day:

1. Get the new db that exists on staging7, copied to other staging sites. I am currently talking to nnewton about this very thing, but he's putting out a fire with mongodb atm. I believe this is an important thing to get done first because once the new dbs are in place admins/devs/whomever can at least go about doing *something* on the staging sites (e.g., like configuring the new db's for the staing-specific bits).

2. After we get the new db across all the staging sites, work on getting restore/backup access in place for all staging devs. Seems like in the current architecture the only way to do this is to give everyone access to scripts on stagingdb.

UPDATE I'm just forging ahead with getting the dbs upload without nnewton. At some point it would be handy to have a quicker way to do this than individually uploading each database, but for now it works and takes some load off him.

Update: staging4, staging5, and staging7 have the new prod db plus the newly merged file system, so they are fully ready to be worked with to determine what configuration needs to be done with a new-from-production db.

I should have staging3 and staging9 updated this way by eod tomorrow, which will just leave:
a) the three borked sites (6, 8, 10) which I need nnewton's help on
b) staging1 and staging2, which I'm intending not to update immediately so that we can use them to diff/compare things

This is the current status of the staging sites as of this morning:

staging10 - broken, needs db dropped and remade, then import new db to (need perms or help for this)
staging9 - updated db and updated file system to current production - ready to be worked with
staging8 - broken, needs db dropped and remade, then import new db to (need perms or help for this)
staging7 - broken, needs db dropped and remade, then import new db to (need perms or help for this)
staging6 - broken, needs db dropped and remade, then import new db to (need perms or help for this)
staging5 - updated db and updated file system to current production - ready to be worked with
staging4 - updated db and updated file system to current production - ready to be worked with
staging3 - updated db and updated file system to current production - ready to be worked with
staging2 - updated db and updated file system to current production - ready to be worked with
staging1 - temporarily leaving as is to compare/diff config with new db

For the future this, along with mysql root access on stagingdb, is what is needed to fix the borked db on stagingdb (from nnewton):

if the tables in staging8 are screwed up you may have to shutdown mysql, delete the stagingN db folder in /var/lib/mysql, then restart mysql create the database and drop it
that will prevent the corrupted ibd files from crashing the server, but creating and droping it will drop the value for stagingN in the global innodb index

Rearranged and added a couple things:

1. sudo /etc/init.d/mysql stop
2. sudo rm -r /var/lib/mysql/whatever_database
3. sudo /etc/init.d/mysql start
4. echo "create whatever_database;" | sudo mysql -u root
5. echo "drop whatever_database;" | sudo mysql -u root
6. echo "create whatever_database;" | sudo mysql -u root
7. sudo mysql -u root whatever_database < some_database_load_file.sql

Also, fyi I just sudo su, then do 'mysql' directly and enter all the commands from the mysql prompt, which your echo'ing and piping - so haven't tried these commands explicitly, but the sequence is correct.

Also, for the moment I'm doing this command from stagingvm to bring in the database:
mysql --user=stagingN --password=stagingN stagingN --host=civicspace.drupal.org < /root/staging3.sql