Extract tables and insert'em onto a new database, HOWTO???

I have a fedora 9 server, the main reason for the server is running a weather service, and all data is stored on a mysql database (wviewDB) which was created by script by the weather program, and on that database is a table called archive... some time ago I installed wordpress and since was asking for a database, and I now too little close to nothing about creating databases, I setup wordpress using the same database, so now I have more tables on that database...

an upgrade of the weather program has been release, but it ask you to delete the database and all its tables, data won't get lost, since it is using sqllite3 database as the main database, now my question is... how do I create a new database called somethingDB and move all the non weather program tables from wviewDB to somethingDB table, including permissions and stuffs??? and how do I delete the wviewDB???

This are all the tables in the wviewDB database...

 archive
 wp_comments
 wp_links
 wp_options
 wp_postmeta
 wp_posts
 wp_terms
 wp_term_relationships
 wp_term_taxonomy
 wp_usermeta
 wp_users
 wp_whatweather_blogcomments
 wp_whatweather_bloglinks
 wp_whatweather_blogoptions
 wp_whatweather_blogpostmeta
 wp_whatweather_blogposts
 wp_whatweather_blogterms
 wp_whatweather_blogterm_relationships
 wp_whatweather_blogterm_taxonomy
 wp_whatweather_blogusermeta
 wp_whatweather_blogusers


Is not like I have lots of stuffs there but... whatever...
LVL 6
My name is MudSystems EngineerAsked:
Who is Participating?
 
theGhost_k8Connect With a Mentor Database ConsultantCommented:
1. If your wviewDB database is of no use then you may consider renaming it only; after renaming you may drop other tables. [check version 5.1]
http://dev.mysql.com/doc/refman/5.1/en/rename-database.html

2. You may go on and create exactly same database as wviewDB and then drop un-required tables:
cd /path/to/data-directory
mysqlhotcopy wviewDB somethingDB

3. create dump of required tables:
- mysqldump -uUSERNAME -pPASSWORD wviewDB  wp_whatweather_blogterm_relationships   wp_whatweather_blogterm_taxonomy   wp_whatweather_blogusermeta   wp_whatweather_blogusers .... > WVVIEWDB-dump.sql
** use all space saperated table names which ever required in new database somethingDB
- mysql -uUSERNAME -pPASSWORD -e "create database somethingDB"
- mysql -uUSERNAME -pPASSWORD  <  WVVIEWDB-dump.sql

I think this will do    

0
 
My name is MudSystems EngineerAuthor Commented:
Oh, I friend of mine told me to do this to create the database...

mysql -u root -p
createdb somethingDB

but then the prompt just says ----> is like WTF??? you want me to look that way or what???
0
 
TekServerConnect With a Mentor Commented:
Okay, as you probably know, this is not really my area of expertise.  But no one else seems to be jumping on it, and I'm a pretty fair hand with Google, so I'll at least try to find some info to get you started.  Maybe a more knowledgeable DB expert can take it from there.

I found this site that looks like it might have some useful info.  It seems to be geared toward a product and/or service called OnSite, but it may still be applicable.

I also found a script template here, which says it's a "Shell Script To Back Up All MySQL Databases, Each Table In An Individual File And Upload To Remote FTP".  I figure that may be even more useful to you, if you can get the script customized for your setup.

(See, I was paying attention to the fact that your system is NOT Windows.)  ;)

I copied the script into the code snippet box below for your convenience.

It looks like there's another script in the comment section of the second page that leaves out the FTP part, if that would be more useful.

There's also mention of an open source MySQL backup utility called AutoMySQLBackup that may be of use to you.

Well, that may or may not solve your problem, but hopefully it will get you moving in the right direction, and kick-start some other Experts.

HTH!
:)

#!/bin/sh
# System + MySQL backup script
# Copyright (c) 2008 Marchost
# This script is licensed under GNU GPL version 2.0 or above
# ---------------------------------------------------------------------
 
#########################
######TO BE MODIFIED#####
 
### System Setup ###
BACKUP=YOUR_LOCAL_BACKUP_DIR
 
### MySQL Setup ###
MUSER="MYSQL_USER"
MPASS="MYSQL_USER_PASSWORD"
MHOST="localhost"
 
### FTP server Setup ###
FTPD="YOUR_FTP_BACKUP_DIR"
FTPU="YOUR_FTP_USER"
FTPP="YOUR_FTP_USER_PASSWORD"
FTPS="YOUR_FTP_SERVER_ADDRESS"
 
######DO NOT MAKE MODIFICATION BELOW#####
#########################################
 
### Binaries ###
TAR="$(which tar)"
GZIP="$(which gzip)"
FTP="$(which ftp)"
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
 
### Today + hour in 24h format ###
NOW=$(date +"%d%H")
 
### Create hourly dir ###
 
mkdir $BACKUP/$NOW
 
### Get all databases name ###
DBS="$($MYSQL -u $MUSER -h $MHOST -p$MPASS -Bse 'show databases')"
for db in $DBS
do
 
### Create dir for each databases, backup tables in individual files ###
  mkdir $BACKUP/$NOW/$db
 
  for i in `echo "show tables" | $MYSQL -u $MUSER -h $MHOST -p$MPASS $db|grep -v Tables_in_`;
  do
    FILE=$BACKUP/$NOW/$db/$i.sql.gz
    echo $i; $MYSQLDUMP --add-drop-table --allow-keywords -q -c -u $MUSER -h $MHOST -p$MPASS $db $i | $GZIP -9 > $FILE
  done
done
 
### Compress all tables in one nice file to upload ###
 
ARCHIVE=$BACKUP/$NOW.tar.gz
ARCHIVED=$BACKUP/$NOW
 
$TAR -cvf $ARCHIVE $ARCHIVED
 
### Dump backup using FTP ###
cd $BACKUP
DUMPFILE=$NOW.tar.gz
$FTP -n $FTPS <<END_SCRIPT
quote USER $FTPU
quote PASS $FTPP
cd $FTPD
mput $DUMPFILE
quit
END_SCRIPT
 
### Delete the backup dir and keep archive ###
 
rm -rf $ARCHIVED

Open in new window

0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
My name is MudSystems EngineerAuthor Commented:
Ok, its too late, time to go to sleep, I'll give it a try tomorrow!!! thanks... both...
0
 
Atdhe NuhiuConnect With a Mentor Commented:
I use various gui tools to administer my mysql database

I use SQLyog windows client on my local pc. They probably do a linux version or there will be others out there.

For my online db I use WizmySQLAdmin and more pertinent to your problem, PHPmybackuppro.

The point I am making is that these gui s protect you from the command line .


They can script out database and data and you can remove the bits you don't need. They can probably script out just the tables you need to save.

You look into this.
0
 
My name is MudSystems EngineerAuthor Commented:
Sorry guys... I should have close this thread yesterday, I got sick... feeling better now...

Ok, Tek, I think you just give me a backup solution there, I didn't ask for one, but thanks anyway...



Mr. Ghost... after checking the process a renaming wasn't an option, see I can do the upgrade if I don't delete the database, but I can't do the renaming without doing the upgrade first, So. I really needed to copy the tables to a new database...


Andy, yes, I forgot I have installed mySQLAdmin so I use mySQL query browser to create the new database (schema weird name to put it that way...) and did a

CREATE TABLE somethingDB.wp_nametable LIKE wviewDB.wpnametable;
INSERT INTO somethingDB.wp_nametable SELECT * FROM wviewDB.wpnametable;

And then used MySQLAdmin to assign permissions...

And drop every non weather program from the old database and it work...


I'll split the poinks if you don't mind...


Thanks anyway...
0
 
My name is MudSystems EngineerAuthor Commented:
Almost equally given...
0
 
Atdhe NuhiuCommented:
If you need any more help you know where I am. I am not a MySQL expert, I let the gui s do the work but I've done a bit of faffing.

ATB
A

BTW ta for poinks err points! they're real ones!
0
 
TekServerCommented:
Thanks!

Glad we could help.

:)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.