[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 360
  • Last Modified:

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...
0
My name is Mud
Asked:
My name is Mud
  • 4
  • 2
  • 2
  • +1
3 Solutions
 
My name is MudAuthor 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
 
TekServerCommented:
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
 
K VDatabase 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
My name is MudAuthor Commented:
Ok, its too late, time to go to sleep, I'll give it a try tomorrow!!! thanks... both...
0
 
Philip PinnellCommented:
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 MudAuthor 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 MudAuthor Commented:
Almost equally given...
0
 
Philip PinnellCommented:
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

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now