33 Comments on “Probably Elemental MySQL Database Questions”

1. In theory, it should be relatively easy. In practice, I’m not actually sure how MT stores data, so it could get a bit dicey. Does 1&1 give you direct database access? Or even command line access?

   http://www.sixapart.com/movabletype/docs/3.2/01_installation_and_upgrade/mysql_backup_restore.html

2. If 1&1 provides phpMyAdmin you can try using it to export your WP tables in SQL format and then import them again with phpMyAdmin on your new host. To get the MT data into WP tables you’ll have to get an export file out of MT somehow.

3. It’s very probably something that you can do yourself, and very probably using only WordPress and PHPMyAdmin as your weapons.

   What I’d likely do is create a new *instance* of WordPress to do the collating/collecting for you. Export from MT, and from WP, and import all into the new instance.

   But, you say… this damn export timeout! Yeah, yeah… very probably it’s a scripty thing that’s taking too long, and more likely still, it’s prolly timing out exporting crap that you don’t care about. The most likely culprit is a huuuuge table full of SPAM that one SPAM-killing plugin or another has been collecting — and keeping — for quite some time. (If not SPAM, then maybe you had a Stats plugin that wrote a bunch of stuff to a table that you may not even be aware is still around.)

   If you Google a bit for things like “how to migrate my WordPress blog” you’ll find some useful entries not unlike this one that detail to a greater or lesser extent how to purge SPAMmy records from your database. You’ll be shocked and amazed that an export operation that had been taking 30 minutes or more may finish up in under a minute. (That was my experience… with about 8 years of blog entries.) Maybe better still, there are now plugins that appear to do this for you. (Note: I haven’t used this.)

   Just be sure you make a backup of your database before you get too ambitious, or use a plugin to do database maintenance that you might not feel 100% comfy with…

4. John,

   First, yes, you can install MySQL on your Vista box use phpmyadmin to interact with the database. Phpmyadmin, by the way, is merely a very popular and straightforward web app that a lot of us use to deal with MySQL.

   What I’d do in your place would be to do a database dump from MySQL of the two databases as .sql files. These will, when imported into a blank database, give you a replica of the databases.

   At that point, you’d have two databases with the complete data The issue is going to be that the structure of the two will be different so you’d need to figure out some mapping between the MT data structures and the WP ones. Given that they’re the same kind of information this should be doable.

   Could you do it? Yes, but you’d need to learn some SQL unless there’s are tools that let you export the relevant data in some common XML format (RSS would work *I think*).

   Two final things – please feel free to email me if you want more info (I assume the email attached to each comment is something you can get at). And secondly, if I can help out, I’d be happy to, even if it’s just getting the exports done for ya.

5. There are a bunch of ways to do this, if you are on a linux host I would suggest:

   mysqldump -u username -p databasename > output_file.txt

   This dumps the db to plain text. Then you can use something like:

   mysql -u root -p newdbname < output_file.txt

   To import the data into a new directory. The big problem you will run into is that MT and WP have different DB schemas, and you’d need to do some import/conversion/munging to get the two to work together. You’ll need to convert the MT db to a WP db, then you’ll need to merge the two WP dbs together without overwriting any primary keys, etc.

   If you’d like I can come up with some better step by step instructions or something.

6. Since John said he’s using the “export” function, that sounds a lot like he’s already using phpMyAdmin to manage the databases (the db dump functionality there is called “export”) and since that runs as web server scripts, 1&1’s resource limiting is kicking into effect.

   John: I have more questions than answers here (see below), but I suspect the short answer to these problems is that it should be possible to achieve the goal, but not easy. You can’t just do the naive move data from MT database A and WP database B into WP database C, since the MT data is sprocket-shaped and the WP data is widget-shaped. But if you can get a copy of both databases off the hosted server, you can merge the datasets and then there should be / must be (that’s the bit that will depend a bit on how your hosted account is set up) a way to get the new database in place.

   You won’t necessarily have to reconfigure your local machine to do this work, since there isn’t really a huge difference between server and not-server even in Windows land (I don’t know /can’t remember if you’re primarily a Windows or a Mac use these days). However the easiest plan could well involve a couple of free downloads from Vmware, and setting things up in some virtual machines if you’ve got enough memory. That way, worse comes to worst, you can blow away the virtual machines to go back to step 1 without destroying your day-to-day working environ.

   A couple of questions…

   (1) Do you have shell access at all as part of this account deal, or do you have to do everything through web front-ends like phpMyAdmin? If you have shell access, you might get a longer timeslice to dump the databases that way (using “mysqldump” ). Alternatively, do 1&1 provide database backups — which will just be the results of mysqldump, most likely — that you can download?

   (2) From memory of days a year ago, you already tried using MT’s export posts functionality on the server and it died in interesting ways, right? Was that because of 1&1’s limiting or other errors? If you can get the MT database onto a local machine, I would give MT’s export stuff another go, since WP’s import functionality (with a few patches that may not yet be in the WordPress download) works pretty well.

   End of the day: I suspect the conversion process is going to be painful. It won’t involve cutting-and-pasting, since the industrial revolution has now happened and we write scripts to do the conversion stuff these days. But either it will go really, really smoothly or it will be moderate levels of difficulty for you. It shouldn’t be too hard for somebody who’s familiar with both systems at the code level to do, though, providing you can get ahold of the databases involved.

7. Just to clarify, the over all path you would want to follow is something like this:

   1) Get backup copies of MT and WP DBs. (mysql dump, etc)
   2) Setup clones of sites from copies for testing on your local machine.
   3) Do a standard MT to WP conversion. (now you have two WP instances locally rather than one MT and one WP.)
   4) Verify conversion worked.
   5) Merge the two WP dbs. (This is tricky because of comment users connections.)
   6) Verify merge worked.
   7) Repeat whole thing in production.

8. Ryan – problem with phpMyAdmin is that it can suffer from the same sort of script timeout as WP and MT, because phpMyAdmin is just a graphical interface to mySQL…it’s a script, just like WordPress and Moveable Type, and it relies on PHP to run.

   John–you might want to contact 1&1 support and see if they can extract your SQL from each DB and put it into a zip file that you can then download with a FTP client to your local computer. Alternately, if you have SSH access on your account, you may be able to use Putty to remote into your webhosting account, and run some Linux commands to do the extraction yourself. (I don’t know the exact commands but I know it’s possible if you are able to SSH in yourself with appropriate permissions; perhaps someone else here would be able to tell you exactly how to do this.) If you can SSH in and do a export/dump of your MySQL dbs into a zip file, you can then connect with FTP to download them to your local machine. FTP won’t have the issue with downloading the big file. You’d be bypassing the MT and WP software this way, and avoiding the thing probably causing the timeout. (I agree with you it’s probably the script execution timeout causing this.)

   In regards to *combining* the two DBs once you have them downloaded…the issue there is that the MT db and the WP db would be formatted differently–the names of the fields would be different, the *type* of fields would be different. Computers are dumb and trip over stuff like that. You might be able to find some sort of WP feature or addon that will convert things for you easily, but even then, you might run into the script timeout thing again, if you’re doing this while still on 1and1, just because your DBs are large.

   If you know enough SQL, and you’re running into timeouts when importing, you can essentially break the big SQL text file into smaller chunks that might be importable via WP or something else, but this does require some sort of comprehension of what the SQL is doing in the file when you open it up with a text editor so you cut it up in such a way that when it’s imported WP or phpMyAdmin or whatever can understand what’s going on (if you try this…make sure you have a nice, new backup of the WP db in case things break and you need to restore it!). Also, if the SQL file is big enough, it might slow your computer to a crawl if you open the SQL file in a text editor to chop it up. I’ve done this before, and it’s a bit of a pain, a lot of time, and a learning curve if you’ve never done it before, but it can work. If you did this, you would want to know that you have some sort of program that converts MT to WP, because you wouldn’t want to try to run SQL in phpMyAdmin that’s formatted for MT on a WP database…it might mess up the WP database pretty badly. phpMyAdmin is really something you want to be REALLLLYY careful with, because it assumes you know what you’re doing. It’s easy to drop (read: delete) a table before you realize you’ve goofed up.

   It might be easiest to plunk down a bit of cash for a DB-savvy person to write a script that will re-format the MT data to fit the WP standard. Or, there might be someone here with the leet skillz to do it.

   Hope this points you in the right direction, and that I didn’t get too technical here…let me know if something doesn’t make sense.

9. For testing on your local machine, XAMPP packages up all the relevant software (MySQL, Apache, PHP, phpMyAdmin) into something that’s pretty easy to get running:

   http://www.apachefriends.org/en/xampp-windows.html

   SP

10. Addition: People were quicker to reply then me. Tom gave you the commands to dump and import the DBs that I said I didn’t know. Of course, like he said…MT and WP have different database formats, so it’s not as easy as exporting then importing. Have to do the data conversion from the MT format to the WP somewhere along the line.

    Also, there’s no reason to install MySQL or anything on your local computer just to test things (unless you want to). You can probably (depending on your plan with 1and1) set up a separate test install on your hosting. Make a new MySQL db, make a folder at something like scalzi.com/whatever_test/, do a WP install into that folder, import your current WP db, and then test out your merging on that test db.

11. Another thing, while I think about it: Assuming the load on your server is the cause of the problems you’ve been experiencing, you may want to consider the impact the db changes will have on the system.

    Larger DB tables being queried will take more resources, etc. But even if that causes a problem, off loading other tasks might help (such as image serving, etc). Not every part of the system has to run on the same machines and there are some really cheap alternatives these days.

12. You may also have some luck in letting the export queries finish if you temporarily disable the Whatever while you’re running them; if 1×1 is limiting the number of DB connections you can have open at any given time, bringing down everything else that’s hitting the DB while you work may help.

13. FYI, there are standalone Apache installation kits for Windows, Mac OS X, & Linux that include MySQL, PHP/Perl, and similar bits. They’re intended to simplify having a local webserver for development, with a relatively simple interface to launch Apache and MySQL, and drag and drop installation of CMS’ like WordPress and websites in general. Many have phpMyAdmin and similar utilities.

    I’ve got a free one for the Mac (MAMP) that I use with learning Drupal development, and will probably reinstall XAMPP for Windows (also free):

    http://www.apachefriends.org/en/xampp.html

    soon for web development work on my PC at work. It’s pretty clean, and doesn’t force one to totally reconfigure one’s PC as a server, or go the VMWare route. Conceivably one could install WordPress on this setup, and do all the import/cleanup work locally before exporting the result back up to a website.

    That said, my suspicion is that this will be big, and that it’s best to get outside help.

14. Aside from the initial export on the server of both databases there’s no reason to run any of the rest of the process there. You can easily setup the relevant stuff on any normal machine including a desktop version of Windows. The limitation will be more that the 2 databases are different.

    A though on that by the way. There’s no real reason you couldn’t setup MT and WP on your local machine, import each database, and have both sites running locally. This is basically Tom’s proposal above. If you can export from MT and import into the local copy of Whatever (vs into a new, blank WP) that would save you a step.

15. If you are allowed to run CGI scripts, you could try starting a mysqldump as a background job. I never have tried this, because I have command line access on my server – but I assume a background job started from a cgi shell script would not be killed.

    Normally there are 2 ways of creating cgi scripts which are configured in your apache config file. Either you have a cgi-bin directory and any script in it will be a cgi-script; or it depends on the suffix of the file, e.g. foo.cgi

    You create such a file on your server and set its permission to 755. The contents should be:

    #! /bin/sh
    echo Content-Type: text/plain
    echo
    echo DONE

    If you get it to work, when you access this file from your browser you should see DONE in the browser window, not the content of the file.

    Then you put this line before the “echo DONE” line (replacing everything in capitals with the values for your installation, the export file name should be something you can access with your ftp client).

    mysqldump –user=XXX –password=YYY DBNAME > EXPORTFILE 2> ERROROUTFILE &

    You should see “DONE” in the browser window immediately, but the script is still running in the background. Once the EXPORTFILE stops growing, the export is done. Do check the ERROROUTFILE to see if anything went wrong.

16. I am a 1&1 user as well. I have the basic 1&1 Business package and have both phpMyAdmin and SSH shell access available.

    IIRC, you have to go to the admin panel for 1&1 and turn SSH Shell access yourself if it isn’t already set up.

17. As someone said before:
    1) dump the MovableType and WordPress databases using the command line tools (mysqldump etc. etc.), that should avoid the script timing limitations
    2) copy the SQL files to your local machine
    3) install Movable Type and WordPress in said local machine (shouldn’t be too hard, they should need just Apache, MySQL and Perl/PHP and there are some all-in-one packages for Windows out there. Should be even easier on a Linux machine)
    4) load the databases’ dumps into the local installations of MT/WP (google for how to do it)
    5) use the admin control panels to export MT database to the standard MT backup format and import it into WordPress (on your local machine, obviously)
    6) check if everything is working
    7) dump the merged SQL database with MySQL tools
    8) copy it on the server and load it on the server WP installation

18. All of the suggestions above that say to use phpmyadmin are missing the point: you can’t get the data out if php can only run for so long.

    Try the command line suggestions if you can, and otherwise, somebody is going to have to write you a custom script to pull the info out in a few chunks. There are plenty of us who would volunteer.

19. I just went through this with WordPress as I changed hosts.

    We ended up using PuTTY and once I got all my server names stright and stopped making stupid mistakes it worked like a DREAM.

    Additionally, I use the Word Press Database Backup plugin, which may or may not run into the same issues you had. I didn’t have problems with it, and it exported into a format I could import straight into my new (empty) databases (this is where we used PuTTY, but it was so fast, I’d use it for a export as well next time.)

20. You’ve got quite a few answers going here, which drop into the nitty gritty of the issue, I thought I’d make some suggestions from a higher level. Further instructions on a given step should be easy to google.

    1) Rather than export, you probably want to deal with a MySQL backup and restore. This should be less likely to kick off script time limitation issues (which are likely part of the webserver/php environment). Note that this assumes you have shell access. If you do not, you’ll probably need to either upgrade to that for a while, or plead your case with 1&1. Get these backup files transferred to your PC where you can play with them at your leisure.

    2) Instead of restoring and merging these databases on the host, you probably want to do this on your Vista box. If you don’t feel like getting into a week long project to even get everything up and running, I’d recommend you take a look at XAMPP, which is a sort of apache, PHP, MySQL environment for lazy developers. Once you have the database sets merged properly, you can easily upload them back to your server and restore them into MySQL again. If you’re moving to a new host, this is even easier, as you can use the new hosting provider as a secondary testbed until everything looks correct.

    3) Finally, you should be able to get some pretty useful information on the MT/WP merger here: http://codex.wordpress.org/Importing_from_Movable_Type_to_WordPress

    I’d be happy to help with this on a part-time voluntary basis – feel free to email me

21. Twp buttons are all that’s needed “crtl” + “p”

22. John,

    Regarding your script time-out, you have two options:

    1) Ask your host to provide you a copy of the databases you want OR
    2) Do your export in more manageable chunks and put it all back together at the end

    Basically, you need to find out how many records you can get exported before the thing blows up on you and locks you out. Then, just export the DB in chunks below that max number of records.

    Alternately, you can do some trial & error and export something like 3 months of information (or less or more) at a time.

    Once you have your multiple export files, you can use MySQL and PHPmyAdmin installed on your Vista computer to merge them all back in to one database.

    It would be fairly easy to do this, and with a little bit of PHP knowledge you could write a script that would more-or-less automate it for you.

    Hope this helps.

23. To summarize:
    * Yes you can migrate from MT to WP using various export/import functions from both platforms without resorting to manual entry re-creation.

    * No, you won’t be able to do this from your existing 1&1 installs.

    * Yes, the migration process is likely to require a bit of trial and error, several test runs and some tweaking with the Mallet of Loving Coercion to work.

    * Three immediate action items:
    1) Get sql backups of both databases.
    2) Get backups of your MT/WP installs.
    3) Setup a development environment that mirrors your existing installs.

24. If you’re just running a query to get your export data, you could limit the rows using the limit clause. This allows you to page through the data and download a part at a time. Though, if the above suggestions work, they’re probably better.

25. In addition to the other tips mentioned here, this is a handy tool: http://dev.mysql.com/downloads/gui-tools/index.html

26. If you’ve got access to the MySQL data directory, you shouldn’t need to do anything more than download the files and reupload it again – MySQL uses standard files to store its data in. That means you should only need to download each file/table individually, which’ll increase your chances of not running into 1&1s process limiter. If some of the files are too large for that, use ‘split’ to chunk the files and then ‘cat’ to rejoin them at the other end.

    Copy the files into the equivalent location on your new host and you should just be able to start MySQL up and it’ll use the data you’ve copied across (assuming the MySQL versions aren’t wildly different).

    Tom’s method (@5) is probably easier, so I’d try that first – this method is most likely to be useful if every other method runs into a time limit.

27. I use MySQL in several large production environments, and use mysqldump extensively. It’s entirely the right tool…

    There are some faster things you can do if performance is critical.

28. Oh, the above comment only applies to the part of the problem which involves getting the data off 1&1. This is completely intentional, and not even slightly because I didn’t read the question properly.

    It may simply be easiest to export the MT data in chunks, rather than as a monolithic set, and then import each chunk into WordPress separately. See here. Running monthly sets should see you past the execution time/memory limit problems, and although it’s a bit laborious, it’s not half so bad as copying and pasting the lot.

    I can’t see an equivalent way to do partial exports from WordPress – it looks like it only gives the option to limit per author. Is there any reason why you need to run both into a new database rather than just importing the MT entries into the existing WP database?

29. If 1&1 won’t give you an exemption (and I don’t imagine they will) you’re going to have to write (or pay/beg someone to write) a script to pull it out piecemeal.

    Once you have it, I’d strongly recommend you move your site somewhere else. I’m not anti-1&1. I’m a happy customer of theirs, but my needs are very different than yours. I use them because they’re dirt cheap and I’ve the knowledge to get over the obstacles they throw up.

30. I think, given Whatever’s popularity, that you got spammed quite a bit, and thus I second deCadmus@#3. You do not, by any means, have enough non-spam data (even given some really, ah, excitable threads) in Whatever’s very short WordPress incarnation to cause PHP scripts to time out when they dump your tables.

    The other problem could be that your MySQL database is under heavy use by other people. Many other people. This means midnight extractions might work out better, or might not. We are talking 1×1 after all.

    Your MT database, on the other hand, probably has more than enough data to totally screw over all export attempts via web scripts on a 1×1 hosting solution. Actually, if you’re using the same database for both MT and WP–same DB name and all–then most export solutions will try to pull *all* the tables and so even the export for “only” wordpress will also try to get the MT data even though it’s not in WP tables. Which is substantial. I suspect that’s what’s going on.

    Basically, you’re going to need shell access, run mysqldump, follow the instructions from Tom@#7…. except that you can indeed import the MT posts directly into your same WP database without having to do some funky merge. You can import as many times into a WP install as you like; I have done so to piece a blog together from multiple blog installs over three years, so that takes out a few steps there.

31. (Sorry. I have no idea why I wrote 1×1 instead of 1&1. Probably because I’ve been thinking of a problem at work rather than here, and it involves a 1×1 that’s entirely different from … well you get the idea.)

32. Scalzi – Did you get your answer or should we contiue throwing ideas at you?

33. Patrick:

    Yes, this has actually all been very helpful. Thank you, folks.