MySQL Memory engine + init-file

Question

I'm trying to set up a MySQL database so that the tables are ran by the memory engine. I don't really care about loosing some data that gets populated but I would like to dump it daily (via mysqldump in a cronjob) and have the init-file set to this dump. However I can't seem to figure out how to get the mysqldump to be compatable with how the init-file wants the SQL statements to be formatted.

Am I just missing something completely obvious trying to set up a database this way?

Answer 1

MySQL dumps are exactly that -- dumps of the MySQL database contents as SQL. So, there isn't any way to read this directly as a database file.

What you can do, is modify your init script for MySQL to automatically load the last dump (via the command line) every time MySQL starts.

An even better solution would be to use a ramdisk to hold the entire contents of your database in memory, and then periodically copy this to a safe location as your backup.

Although, if you want to maintain the contents of your databases at all, you're better off just using one of the disk-based storage engines (InnoDB or MyISAM), and just giving your server a lot of RAM to use as a cache.

Answer 2

My understanding is that the --init-file is expecting each SQL statement on a single line and that there are no comments in the file.

You should be able to clear up the comments with:

mysqldump --comments=false

As for each SQL statement on one line, I'm not familiar with a mysqldump option to do that, but what you can do is a line of Perl to remove all of the newlines:

perl -pi -w -e 's/\n//g;' theDumpFilename

I don't know if --init-file will like it or not, but it's worth a shot.

The other thing you could do is launch mysql from a script that also loads in a regular mysqldump file. Not the solution you were looking for, but it might accomplish the effect you're after.

Answer 3

I stumbled onto this, so I'll tell you what I do. First, I have an ip->country db in a memory table. There is no reason to try to "save" it, its easily and regularly dropped and recreated, but it may be unpredictable how the php will act when its missing and its only scheduled to be updated weekly. Second, I have a bunch of other memory tables. There is no reason to save these, as they are even more volatile, with lifespans in minutes. They will be refreshed very quickly, but stale data is better than none at all. Also, if you are using any separate key caches, they may (in some cases) need to loaded first or you will be unable to load them. And finally, be sure to put a "use" statement in there if you're not dumpling complete databases, as there is no other interface (like mysql client) to open the database at start up.. So..

cat << EOF > /var/lib/mysql/initial_load.tmp
use fieldsave_db;                 
cache index fieldsave_db.search in search_cache;
EOF

mysqldump --comments=false -udrinkin -pbeer@  fieldsave_db ip2c \
    >> /var/lib/mysql/initial_load.tmp                  
mysqldump --comments=false -ufields -pavenue  -B memtables \
    >> /var/lib/mysql/initial_load.tmp

grep -v -- ^-- /var/lib/mysql/initial_load.tmp |tr -d '\012' \
   |sed -e 's/;/;\n/g' > /var/lib/mysql/initial_load.sql

As always, YMMV, but it works for me.