Today I was asked to transfer an OSCommerce store with MySQL for a client.

First of all, we need to login to the old server and archive all the data. So navigate to home directory and issue the following command

tar -cvf old.tar ./

Now FTP this directory to the new server and untar it.

tar -xvf old.tar

Now we need to dump MySQL databases. Login to old server and issue:

mysqldump -uusername -ppassword -a --databases database1 database2 database3 | ssh user@newhost.com "mysql -uusername_new -h hostname_new.com -ppassword"

The hostname is needed in the above command if you cannot connect using localhost.

Q: Does anyone know how I can dump a database that …

As a "little developer", I don't really know what's exactly going on behind the scenes - business stuff is not so my big thing.

However, I feel that it would be good for MySQL to have its own full featured storage engine(s) (with foreign key constraints, ACID transaction and all that stuff) under its control. Maybe there are no risks for the short term (I don't know), but it would still be better for MySQL's reputation to be completely independent from any competitor and from anybody else.

Insecure users will more likely migrate to another database systems than those who know what the future will bring. And people who now use other database systems will more likely not migrate to MySQL, if they can't anticipate how data will be stored in the future. So I would see an own and independent full featured storage engines as one of the very highest priorities for MySQL's future - not only from the technical point of view, …

April 1st is still more than a month away and at least one rumour about Oracle's upcoming purchases is true: today the software giant annnounced their acquisition of Sleepycat Software, the makers of Berkeley DB (and various other products).

One interesting point is that Berkeley DB was already seeing competition from SQLite (which is an excellent, fast and free (as in beer and freedom) RDBMS). I wonder how much the acquisition is going to drive adoption of SQLite?

Additionally, Oracle now owns both half of MySQL's transactional storage engines, which perhaps gains them another measure of control over the Swedish upstart. (The other engines are …

Wow, the rumors were true. Oracle is snapping up Open Source Database companies now. First it was Innobase (see Oracle buys Innobase. MySQL between rock and hard place?) and now it's Sleepycat Software.

The purchase of Sleepycat, which has been rumored for weeks, gives Oracle another open-source product to complement its proprietary database offerings. At an investor conference last week, Oracle CEO Larry Ellison reiterated the company's strategy to generate revenue from a combination of open-source and proprietary software.

They produce and support the famed Berkeley DB embedded database engine and have radically improved it's features since the version 1.x days. …

With Oracle Corporation purchasing InnoBase, the company providing the InnoDB Storage Engine, and now reliable rumors of the acquisition of SleepyCat, the BDB Storage Engine, both key transactional storage engines for MySQL are effectively owned by a competitor.

While the is a strange and probably unchartered territory for both organisations, I’m personally concerned. I use InnoDB extensively, however if there was a comparable alternative within MySQL I’d consider switching out of principle. Is Oracle purchasing these organisations a bad thing? We don’t know. That’s the problem. While MySQL will undoubtly continue to provide these storage engines as part of the MySQL Database I believe a call to arms is needed.

It’s true that Oracle helped more general adoption of Linux …

The new 5.1.6 release comes crammed with goodies for MySQL Cluster:

1) It is now possible to replicate between clusters.
2) Non-indexed attributes can now be stored on disk.
3) Disk-stored attributes uses a new page cache, data is stored in
tablespaces and UNDO log records for disk-stored attributes are
stored in LOGFILE GROUP's.
4) The default partitioning scheme has been changed to even distribution
and the previous linear hashing scheme can still be used by using
PARTITION BY LINEAR KEY().
5) Automatic partitioning can still be used, but NDB tables can also be
partitioned by the user. All the partitioning defined in the MySQL manual can
also be used to partition MySQL Cluster tables.
6) With user defined partitioning it is possible to use heterogenous machines
by using NODEGROUP when defining a partition. This defines in which
NODEGROUP a …

This morning I faced an interesting problem that needed a quick solution: One of our developers urgently had to add some functionality to an existing application. The problem appeared rather tedious to solve in the application code. But looking at the underlying database schema, a missing foreign key constraint was discovered, that (if put to right use) could solve everything on the database level with just a few CASCADEs and TRIGGERs.

Our original problem has nothing to do with OOP and our schema of course contains many more tables and fields. I just came up with a simplified classes and objects paradigm to present it in a more abstract way, as I think it fits quite well. That's what we basically got (only key columns shown, everything else omitted for brevity and clarity):

    +-------------+           +------------------+
    | classes     |           | class_properties |
    +-------------+ …

Having completed my travel/lodging arrangements for the MySQL Users Conference I decided to poke around and see how things are shaping up. I am pretty excited about what's been put on the presentation list since I last looked. A few items I'm looking forward to seeing (hoping they don't all stack up on the same time slot):

• Dynamic SQL in Stored Procedures - Konstantin Osipov
• Advanced User-Defined Functions in MySQL 5 - John David Duncan
• Measuring MySQL Server Performance for the Sensor Data Stream Processing Jacob Nikom (Boston MySQL meetup attendee)
• MySQL Cluster: New Features and Enhancements (Disk Data, RBR) - Vinay Joosery
• MySQL Partitioning - Mikael Ronström
• MySQL Replication: New Features and Enhancements (RBR) - Lars Thalmann, Elliot Murphy
• Panel: Scale Out - Brian Aker, Dorion Carroll, Jeremy Cole, …

Although I've had a few ideas brewing for some time, things finally came together tonight for my O'Reilly Open Source Convention talk proposals. I just finished putting the last of three in:

• 
  
• Creating Art with MySQL Routines (45-minute talk): a look at 10 new procedures/functions designed to be fun, not functional (looking to the Perl ACME modules).
  
• The 30-minute MySQL Cluster Installation (45-minute talk): complete step-by-step setup of a MySQL cluster (highlighting history, hardware, anatomy of the cluster, configuration, and management).
  
• Hands on MySQL 5: Procedures, Functions, Triggers and Views (3-hour tutorial): hands-on building of examples of each new enterprise feature in MySQL 5 (co-presented with Jay)
  

I was going to submit the …

A while ago someone asked me "why doesn't myslimport load more tables at once?". Which was clearly an excellent question to ask since you have a number of gains by doing this, especially if you are using MySQL Cluster.

Welcome to:
mysqlimport --use-threads=#

mysqlimport will now use as many threads as you want to load tables. I had a perl script I used to do this with on Slashdot, but frankly there really is no point to this not being available through the import utility.

An example set of SQL to load two tables:

create table t1 (a text , b text);
create table t2 (a text , b text);
insert t1 values ("Duck, Duck", "goose");
insert t1 values ("Duck, Duck", "pidgeon");
insert t2 values ("We the people", "in order to perform");
insert t2 values ("a more perfect", "union");
select * from t1;
select * from t2;

Now execute: …