Posts Tagged by MySQL
|06-Mar-2012||Posted by Sonia Hamilton under MySQL, Windows|
Years ago I wrote a post on how to Backup multiple MySQL databases into separate files (Linux). I get a lot of questions about how to do the same on Windows; here’s a script submitted by one of my readers:
:: MySQl DB user set dbuser=xxxx :: MySQl DB users password set dbpass=xxxx :: Switch to the MySQL data directory and collect the folder names pushd "C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.5\data" :: Loop through the folders and use the fnames for the sql filenames, collects all databases automatically this way echo "hello" echo "Pass each name to mysqldump.exe and output an individual .sql file for each" FOR /D %%F IN (*) DO ( "C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqldump.exe" --user=%dbuser% --password=%dbpass% --databases %%F > "C:\SQLBackup\%%F.%backupdate%.sql" )
|01-Oct-2008||Posted by Sonia Hamilton under MySQL, Ruby, Ruby on Rails|
As an ex-DBA, one of the things that annoys me about Rails is that migrations don’t have a way of setting up referential integrity (I’m still using Rails 1.2.3, so this may have changed). But apart from that, I lurv Rails :-)
Here’s some code I wrote to for adding and removing foreign key constraints on MySQL (using InnoDB, of course). There’s other code out there to do the same thing, but they didn’t do what I wanted, or required installing a plugin. (more…)
|05-Jun-2008||Posted by Sonia Hamilton under MySQL, Ssh|
I use ssh to tunnel all the time, especially to get around brain-dead firewalls. But this doesn’t work easily for mysql – if you tunnel a port from localhost (eg localhost:3306) to the target db, mysql client tools will see ‘localhost’ and try to connect via the local socket. Very broken – there’s bugs lodged here and here about this (and lots of chest beating and wailing, too).
Solution: tunnel ssh via an intermediate machine. For example:
intermediate$ ssh -o GatewayPorts=yes -L 3306:targetdb:3306 targetdb localhost$ mysql -h intermediate -P 3306
GatewayPorts – dangerous yes – but so is life…
The problem seems to have been fixed; either that or I was having a very blond moment last time I tried using - – protocol.
localhost$ mysql --version localhost$ mysql Ver 14.12 Distrib 5.0.51a, for debian-linux-gnu (i486) using readline 5.2 localhost$ ssh -fN -L3306:targetdb:3306 targetdb localhost$ mysql --protocol=tcp -hlocalhost -uroot Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 14788104 Server version: 4.0.21-Max-log Type 'help;' or 'h' for help. Type 'c' to clear the buffer. mysql>
However graphical tools like OpenOffice Base, GMySqlCC and MySQL Query Browser either don’t obey the – - protocol=tcp flag or don’t have a place to set it, and therefore try to connect via the local socket file when connecting to localhost. Aarghhhhh….
Using 127.0.0.1 instead of localhost works for OpenOffice – yay.
|05-Jun-2008||Posted by Sonia Hamilton under MySQL|
There’s an article here that explains how to connect to a mysql database from OpenOffice.org Base using odbc, but the author makes it sound like rocket science. It’s easy – summary:
sudo aptitude install unixodbc libmyodbc unixodbc-bin
- driver is /usr/lib/odbc/libmyodbc.so
- setup is /usr/lib/odbc/libodbcmyS.so
- run the wizard, connect using the saved odbc configuration
|21-Dec-2007||Posted by Sonia Hamilton under MySQL|