Anyone know MySQL

disneyjoe7

I have 3 MySQL databases going on, can I combine them into one? I like to do this so member’s registrations are not need to redo to each database.

Jstas

That would require exporting the data from two of the databases and then importing the data in to a temporary table and then either using a union or a join to merge all the data in to a common database.

I can do them but it's alot of work and would require access to your databases as well as consulting fees.

Once the union or join is complete, you would not have 3 separate databases anymore.

Otherwise, you would have to have a complex report built that could query all 3 instances, build a linked list out of the results and then query that. Your performance would drop through the floor and you'd have tons of replication problems.

That is if I understood your question properly.

disneyjoe7

What do you need for a proper consulting fee cost?

dbaldus

Could be a fairly easy problem, as well, depending on the schemas of your DBs. Are they 3 separate DBs with the same (or at least similar) schemas?

disneyjoe7

To help you what I trying to do 3 MySQL databases are for these site pages Sale cart CubeCart, Forums phpBB, Blog WordPress. All seperate databases but on the same website being /forums, /purchase, /blog.

reeltrouble1

so your now trying to become a highly trained data base technician??????

wow.

you geek.

RT1

AsSiMiLaTeD

Do you have full access to the server they're running on?

I don't work with MySQL, I'm a TSQL guy. What I would do in SQL Server is create a shell table (or tables) with the fields from the tables from all 3 databases and then just simply insert into those tables from each database. I'm sure this is also easily done in MySQL, I'm just not familiar with the exact approach. Hell you don't even really need to create a shell table, if you have the ability to query across databases, a simple select into would do the trick:

Select *
Into NewDatabase.tablename
From OldDatabase.TableName

I expect the syntax for MySQL with vary a little, but that's the basic gist of it and exactly what it would look like in TSQL.

One thing I can tell you is that it sounds like you're using some canned programs there, and with those programs you typically run a php script up front during installation that indicates the db name and all that. Even if you find a way to 'merge' all the databases together, it's likely that the products installed would likely encounter problems because the databases and table they're looking for aren't there any more.

If I were you I'd look more into a way to 'pass' the registration info from one database to another. With 'real' SQL the databases can easily communicate with each other, I assume the same structure would exist in MySQL but I'm not sure on that.

In my world, it's actually better to have more, smaller databases as opposed to 1 big one from a backup/restore perspective. I'm not sure how much that matters to you, and I understand your desire for 1 combined database, but with multiple smaller databases there are 2 distinct advantages in terms of data backup/restore:

1) If you do have to revert to an earler state of your database obviously you're going to lose data for some period of time (there are actually ways around this sometimes but hear me out). With multiple databases you're likely going to lose less data. So with your example, right now if your Blog crashes and you have to restore it you're going to lose data since the last backup in that db, but your cart and forum are still good - if you combine all these into one then you've lost data from the whole thing.

2) This is probably not a huge concern for you, but it is for us here, but smaller databases are much quicker to backup and restore. If you have one huge database that's 120 GB in size it's going to take alot longer to backup or restore than a smaller database, meaning downtime may be increased. I don't think you're going to run into that issue on a website, but there it is.

eor

disneyjoe7 wrote: »

To help you what I trying to do 3 MySQL databases are for these site pages Sale cart CubeCart, Forums phpBB, Blog WordPress. All seperate databases but on the same website being /forums, /purchase, /blog.

In addition to the "what database" does the program look to problem there is another issue with trying to combine the user info for the three programs. Look at the table structure for the three "user" tables, if it's not EXACTLY identical, you're going to have to modify two of the three programs so they can interact with the new combined table. If one is looking for a 15 char case sensitive username and the other doesn't mind if it's 20 char case insensitive user (different field name), you're in for some rewriting of the programs or maintaining three tables within the same database, which would be pointless.

If the tables are fairly similar, you can use a simple Perl (or similar) script to copy from database A to database B. Copy out of username and into user, copy out of pwd into password. If all three programs store password information in the same manner, that might actually fly. If you don't consider one the master and the other secondary then you'll have to do it round-robin to keep things sync'd.

It's been a while since I've done much in Mysql, but last thing I knew there was a command to basically script out a table and the entire contents to text. Playing back said command against a different database would recreate the table and all the entries. But the actual copying will probably be the easy part.

If you don't have command line access to the box that your databases on, this project will be much harder. If I had to do it under those circumstances, I'd probably copy all three to another box I could control, do the work, then copy the "new" database back up.

Don't forget to make backups before you do anything. And test them.

disneyjoe7

Ok going in to take a look Cpanel thing wish me luck.

disneyjoe7

Ok not geek enough, or just not worth my time to figure out. The multiple registrations irritates my customers, I wish to fixed that problem if I could. Some here state yes can be done, anyone wish to make a fast buck and help me out? Or just look into it to say yes 2 hours or ? and this $$ to make it work.

Site is www.4dtvblackbox.com databased sites are www.4dtvblackbox.com/blog www.4dtvblackbox.com/forums www.4dtvblackbox.com/purchase/index.php?act=viewProd&productId=2 going homepage here jumps back out to www.4dtvblackbox.com main page. Google messes with page rating if just the purchase database pages, so all main pages are out of databases.

disneyjoe7

Not sure if this would help me, but found it funny this guy uses the same software to a tee.

http://www.practicalecommerce.com/blogs/post/63-Using-PHP-in-your-templates-

Polk addict

Um... Dude, since you're using three different programs, you should be able to merge the three databases by renaming the suffixes...

disneyjoe7

Easy for some, not worth it to others. But still need to combine user info's anyone wish to do it?

disneyjoe7

CASH!


Ok not really CASH, but a check.


Not killer CASH, But indeed CASH.