How do I create new mysql db from a dump ?

habbardone

Hi,

I generated a database mysqldump by using the Webmin backup facility on my server.

After that I downloaded the file from the server to my pc.

I want to use it to create a copy of the database with a different name but all table names and content and indexes to be the same.

This is what the start of the file looks like:

-- MySQL dump 10.13  Distrib 5.1.52, for pc-linux-gnu (i686)
--
-- Host: localhost    Database: fred_wordpress
-- ------------------------------------------------------
-- Server version	5.1.52

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `wp_commentmeta`
--

DROP TABLE IF EXISTS `wp_commentmeta`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `wp_commentmeta` (
  `meta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `comment_id` bigint(20) unsigned NOT NULL DEFAULT '0',

As you can see it is a wordpress database.

Can I use php to generate the database tables ?
Or should I do it in a different way ?

Thanks.

Weedpacket

As you can see, it's a big file of SQL commands. Run it through MySQL's client program (which, if I remember rightly, is called "mysql").

habbardone

Are you my Mummy?

Nope - don't think so.

Run it through MySQL's client program

I have to run that from the command line I guess ?

Using putty of something ?

I found this with a bit of searching:

mysql -u user -p db-name < db-name.out

Since I named my file: mo_backup

After I login to the root user on putty, do I

mysql db-name < mo_backup

and db-name is the new name that I want ?

Thanks

Derokorian

That's one option, you could also run it through PHPMyAdmin or MySQL Workbench. There are probably many many other options as well. This is just a file containing a bunch of queries, so you can import it / execute it anywhere that you can run a query.

habbardone

Thanks

I found this with a bit of searching:

mysql -u user -p db-name < db-name.out

Since I named my file: mo_backup

After I login to the root user on putty, do I

mysql db-name < mo_backup

and db-name is the new name that I want ?

BTW

The reason for doing this is this:

I am running a wordpress plugin which generates webpages dynamically
but a bit slow. There are about 20 of these sites, with images etc.

The plugin now has an html export function which will output everything
needed to run the site standalone - and therefore much faster.

These 20 minisites are established on a url like this:

minisite1.myfirstdom.com
minisite2.myfirstdom.com

Because I already have backlinks to these urls I want to
put the HTML version on them.

So, what I want to do is setup a new wp on a different url,
install the plugin and then import content to the new installation.

What is the best way to do this?

Shall I make a copy of the existing database, ( doing that bit now )
then do a new wordpress install and give it the new database name,
then copy the wp_content folder over to the new install ?

What do you think ?

sneakyimp

Check your mysql dump file for 'create database' statements. I bet you won't find any.

Since I named my file: mo_backup

After I login to the root user on putty, do I

mysql db-name < mo_backup

and db-name is the new name that I want ?

Basically, yes. In order to populate a new database with all this data, you will have to create that new database separately and there are numerous ways, but for all of them you have to have a username and password for a mysql user privileged enough to create a new database. Or you have to ask your sysadmin or db guy to create a fresh new database for you and grant you permissions on the newly created database.

If you have access to phpMyAdmin and enough privileges there, copying a database in its entirety is a very quick and easy process. You select the database (and not a particular table) and click the 'Operations' tab and it should give you the option -- but only if you are logged in as a fairly privileged user.

It's not entirely clear to me what sort of migration you are doing and I don't know what it means to 'run standalone'. Setting up a new, empty instance of wordpress and replacing its database with your database dump might work fine if the wordpress versions between the new and old system are identical. If you are moving to a newer version of wordpress, the old database might be defined a bit differently. I.e., the new database might have some new tables or new columns in existing tables.

habbardone

Thanks.

Yer , I have root access to server.

I was going create the database first using VirtualMin.
Then do the putty thing.

I used to use phpMyAdmin when I used a shared hosting but I have never used it on my dedicated server - don't think I have it.

I can probably do it easier with Webmin or VirtualMin , I just don't know how :o

I've been looking around trying to find a way ...

sneakyimp

mysqldump and mysql via command line are really fast. I'd highly recommend getting accustomed to them. With most other solutions, you have to keep downloading dump files to your local machine when dumping and then uploading them again when you are trying to put them in a new database.

mysql and mysqldump run right on your server.

bradgrafelman

If you used phpMyAdmin before elsewhere, why not continue to use it on your dedicated server?

habbardone

Hi,

I found a SQL execution window under the VirtualMin
system.

Pasted in the dump file contents.

( the file is about 3 Mb )

But got:

failed : Got a packet bigger than 'max_allowed_packet' bytes

Guess I need to change a parameter or 2 somewhere ?