Help with moving tables between databases

[drmike]

Greets:

I've, um, been clued into how to do the multiple databases but am having an issue with actually moving the tables into their new databases.

Writing a script is fairly easy but I note when I move the tables with phpMyAdmin, it makes special note of the auto-increament number. 

And I don't know how to pull that out from the database.

Should I worry about it or not worry about it and just sort the tables.

thanks,
-drmike

[Luke]

Makes a note of the auto-increment value for what?

I can't see it as really hurting, depending on what field it's for.

[drmike]

Say posts.  It signifies the new post id number.

For example:

Code:

CREATE TABLE `dariabe_serverE`.`wp_242_categories` (
`cat_ID` bigint( 20 ) NOT NULL AUTO_INCREMENT ,
`cat_name` varchar( 55 ) NOT NULL default '',
`category_nicename` varchar( 200 ) NOT NULL default '',
`category_description` longtext NOT NULL ,
`category_parent` bigint( 20 ) NOT NULL default '0',
`category_count` bigint( 20 ) NOT NULL default '0',
`link_count` bigint( 20 ) NOT NULL default '0',
`posts_private` tinyint( 1 ) NOT NULL default '0',
`links_private` tinyint( 1 ) NOT NULL default '0',
PRIMARY KEY ( `cat_ID` ) ,
KEY `category_nicename` ( `category_nicename` )
) ENGINE = MYISAM DEFAULT CHARSET = latin1 AUTO_INCREMENT =3069;

INSERT INTO `dariabe_serverE`.`wp_242_categories`
SELECT *
FROM `dariabe_daria`.`wp_242_categories` ;

DROP TABLE `dariabe_daria`.`wp_242_categories` ;

[Andrew]

I wouldn't worry about it.

The ONLY tables you even might need to be concerned about regarding this are the global tables (wp_blogs, wp_users, etc).

[drmike]

And those I moved manually.

thanks,
-drmike

(Hopefully this will work...)

[Andrew]

Btw, if you feel like sharing the script that you're using to sort the tables to the appropriate databases, i'd be very grateful. I can't seem to come up with a method that doesn't take forever and a day (literally).

Thanks,
Andrew

[drmike]

I'm not writing one.  That auto INCREMENT concerned me so I just started doing it all by hand.

Day 3 currently.  About 3-4k tables moved.  Another 6k to go.

What I did though is copy all of the major tables and their movement commands into a text file and just search and replace for the blog id number.  Then make a list of all of the blog ids current and their md5 first digit.  Found all of the ones that go into a specific database and then just ran through that database. (ie all the blogs going to the 'f' database)

Took about 30 minutes to do about a 100.

You're more than welcome to it though.

[Andrew]

I thought you had come up with some genius script to auto copy the tables 

The first time I did the split it was with ~495,000 tables and it was a real pain. After that I came up with a script that I could run as a cronjob. However, "efficient" is not exactly a word you could use to describe the script.

Thanks,
Andrew

[drmike]

My solution would probably be the same as yours.  Grab the blogid, md5 it, take the first few characters, and drop them into a query.

Gotta admit though that, considering how things are falling, I'd suggest the 256 databases over the 16 databases, right from that start.  About 60% done and one database already has 5k tables within it while the rest are still under a 1k.  It's kind of bias as I've been deleteing tables after a month of being labeled a spammer or deletion by the user.

[Andrew]

That was one of my mistakes when I first split a db. I should've went 256 right off the bat.

[drmike]

I thought about it but couldn't figure out a method of autogenerating all those dbs.

[Luke]

That's the only problem with any type of deal like this.

If you go the Hash route, you have no control over how much is in one table vice another.

If you go the route of ID's 1-5000 in DB 1, 5000-10000 in DB 2, etc, you're still screwed because while you're setting a "max" per DB, you're still going to get fewer and fewer in the earlier DB's as spammers are deleted and people leave, etc.

Hashing should be somewhat of an average, but as Doc mentioned, he's already up to 5k in one DB.

256 would space it out a little better, but still.

If only there were some magic solution to balance it out amongst DB's.


As an aside to this particular topic, I've been contemplating a way to specify a special DB for a handful of blogs. Mainly for blogs that are bigger and more frequently used. I guess an interception of those blog id's and rerouting would be possible.

Not sure if it would help the load though for the other blogs that would have been in that particular database though.

But, speaking of DB load, Quenting brought up a good point last week about the rewrite rules for pages taking up an awful lot in the DB.

[Luke]

I thought about it but couldn't figure out a method of autogenerating all those dbs.

Create Database If Not Exist, similar to a table?

[Andrew]

I'm going to release some multi-db code in about two weeks and i'll add the feature to allow certain blogs to be located on separate database.

If you guys have any more feature requests just let me know.

Thanks,
Andrew

[Luke]

Cool Andrew. I was just kinda thinking out loud, but hey that's cool by me.

[ron_r]

I thought about it but couldn't figure out a method of autogenerating all those dbs.

A shell script with nested for loops (say $X & $Y), each looping through the values 0-9,a-f

Code:

echo create database $X$Y; >> dbscript.sql

to a file. Then run the file from mysql command line.

Statistically, I would expect that 256 databases using md5 would mostly balance out and distribute pretty well.

You have to watch the autoincrement fields if they are used in a relationship with another table (eg. post_id in posts and post_comments).

[Luke]

Statistically, I would expect that 256 databases using md5 would mostly balance out and distribute pretty well.

Wouldn't the same hold true for 16 as well? I guess if the id's went in order, started from 1 and had no gaps then it might. Then again, same thing probably goes with 256 as well, although there is just more room to spread around.

You have to watch the autoincrement fields if they are used in a relationship with another table (eg. post_id in posts and post_comments).

Just moving tables over, it should be fine since it will carry over and insert the new data, plus the current auto inc value. Well, technically the "next" value, but whatever.

[drmike]

Statistically, I would expect that 256 databases using md5 would mostly balance out and distribute pretty well.

Wouldn't the same hold true for 16 as well? I guess if the id's went in order, started from 1 and had no gaps then it might. Then again, same thing probably goes with 256 as well, although there is just more room to spread around.

I delete after six months of never used and spammers a couple times a month if they never protest. (Only one has.)  Even though I only list about 750 blogs current, my ID count is up at 3k.

Just moving tables over, it should be fine since it will carry over and insert the new data, plus the current auto inc value. Well, technically the "next" value, but whatever.

That's what I'm asking because I'm currently not passing the auto inc number over as I would have to set it for each and every table.

[Luke]

It should be part of the dump, right? It was in your example above:

Code:

) ENGINE = MYISAM DEFAULT CHARSET = latin1 AUTO_INCREMENT =3069;

You'll need that auto increment value, else it will start out at 1 again, and cause more collisions than rain on 85 during rush hour.

[drmike]

See post #1 of this thread about if I had to include it or not.  I haven't been.  I've just been changing the table names.

edit: Well great.  I'm fucked now...

[drmike]

Well maybe not.  Seems like it went to the next number, at least with a post it did.

[Luke]

Could be worse. At least it wasn't the live db yet?

Check it to see what happens.

It "shouldn't" insert an id in an auto increment field if it already exists, since it is supposed to be unique as well. Copy a table like you have been over to a test db. Then do an insert on it and see what happens.

You may luck out, although I can't guarantee it.

[Luke]

OK, I see you've popped back on and off again.  lol

[drmike]

Nah, it's the live database.  Since I don't have decent home internet access, (ie 60 year old copper and the apartment manager won't allow any digital line service since his brother owns a Dish Network service.  All Spanish folks pretty much anyway and time warner isn't known for its spanish services) I have to do everything pretty much live.

[Luke]

Well, at least the result seems to still be positive.

Heck, if the project hasn't already caused it, this experience may drive ya to drink.

[quentin]

well i could post my script but i dispatch blogs based on their first letter(s), not on a md5, so that may not be very helpful.
What i know is mysqldump isn't an option once you get to lots of blogs. I used to do it and it took 8h to dump a 1/20th of my blogs.
Then I started using mysqlhotcopy to move blogs around, but it still was slow. Now I just use rsync with the DB down. You need similar mysql versions to be sure no problems come up (and I'd recommend similar distros too), but I have never had any this far when respecting this rule. rsync'ing 10000 blogs takes a few minutes, and it's much better to have mysql down a few minutes (while htaccessing the concerned blogs) than the server on its knees for hours.

[Andrew]

I learned the mysqldump lesson the hard way as well. I still use it when I need to transfer a single blog but other than that rsync is the way to go.

Thanks,
Andrew