Archive for category mySQL

CSV Export from MySQL Command line (Windows/Linux)

select col1, col2, col3
INTO OUTFILE 'c:/dump.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
from tblA a inner join tblB b on a.col1 = b.col2;

No Comments

MySQL 5.1 with DotNet claims Procedure or function ‘‘ cannot be found in database ‘

We have some dot net code which uses MySQL- our live setup it thus;

MySQL 5.1.45
MySQL Dot net connector dll version 6.2.2.0

We recently rebuilt our dev server, and installed MySQL 5.1- it turns out the minor revision number was slightly higher, but nothing to set off any alarm bells, or so we thought;

MySQL 5.1.57

With this combination or MySQL connector 6.2.2.0 and MySQL 5.1.57, we couldn’t execute any stored procedures- we would just get;

Procedure or function ‘ ‘ cannot be found in database ‘

Googling turned up lots of suggestions like “make sure your connection string is lower case” etc etc- nothing fixed our issue.

We found the solution was to update our MySQL dot net connector to the latest, which at time of writing was 6.3.7.0.

You can get MySQL here, and the MySQL dot net connector here.

No Comments

MySQL Backup & Restore

I did this on an install of MySQL 5 Community Server running on Windows 2008 Server Web Edition- From the command line- backup:

mysqldump -u [username] -p[password] [database name] > FILE.sql

restore;

mysql -u [username] -p[password] [database name] < FILE.sql

Remember; if you have restored your core mysql database, which contains all the info on users and privileges, you will need to do a

FLUSH PRIVILEGES;

before you will be able to login using those user accounts!

, , , ,

No Comments

MySQL Command Line – Setting up a new database and user

From the command line start up mySql;

mysql -u root -h localhost -p

you will be prompted to enter your root password.  Once you’re in you can go ahead and create the database;

CREATE DATABASE <data_base_name>;

BAMM- that’s your database done – you can see this by typing;

SHOW databases;

Next add a user and grant all privileges to that new database (and only that database);

GRANT ALL ON <data_base_name>
.* TO <user_name>@localhost IDENTIFIED BY '<password>';

Or, to be more specific (and secure!)

GRANT SELECT , INSERT , UPDATE , DELETE , CREATE , DROP , ALTER ON <data_base_name> . * TO <user_name>@localhost IDENTIFIED BY '<password>';

The last line you need before you can use the account is;

FLUSH PRIVILEGES; 

The PCTools.com website has a handy page for generating random passwords.

Note, the user created here will only have access when logging on from the local machine.  If you want a user which can logon from anywhere replace localhost with %.  Alternativly you can also specify specific ip addresses.

No Comments

Move from BlogEngine.net to WordPress 2.7.1!

I’m a .net developer by day, so am always keen to use .net technologies where ever possible.  For the past few months I’ve been persevering with BlogEngine.net, a dot net blogging platform which I run on my Windows 2k8 IIS7 box from home, however I can do it no more!  I’ve found myself looking on with envy at the features in the newer builds of WordPress as i struggle on with the various “quirks” of BlogEngine and finally decided I could no longer put up with it- I’ve made the switch to WordPress which I’ve used in the past and I have to say I’m pleasantly surprised- not only is it as good as i remember, but it’s come on leaps and bounds both feature and interface wise since I last used it.

To get WordPress up and running I had to setup PHP5, mySQL (and phpMyAdmin to save me the pain of mySQL command line syntax again) under IIS7 which I also found to be easy (all up and running within an hour!) following the guides over on the TrainSignal training website

No Comments

MySQL 5 / phpMyAdmin Installation – root account not accepted

I recently installed mysql 5 under Windows 2003, with phpMyAdmin running under IIS 6 and came across an issue trying to get phpMyAdmin to access the database- I found that the root credentials were’nt accepted. The error i receive was

“#1251 – Client does not support authentication protocol requested by server; consider upgrading MySQL client”

Apparently this is to do with me using an older version of phpMyAdmin and how the passwords are encrypted- I found an article on WebMasterWorld detailing how to resolve it;

Login from the command line to your MySQL database;

mysql -u root -h localhost -p
  • -u lets you set the username to use, in this case I’m using the root account
  • -h sets the host- I’m logging in from the local machine so used localhost- I havn’t enabled root access from any remote machines
  • -p indicates that you will be supplying a password

You will then be prompted the enter the password for your database. Once you’re in, execute the following sql to update the password

UPDATE mysql.user
SET password=OLD_PASSWORD('somepassword')
WHERE user='someuser';
AND host='somehost';

Once that’s done you will also want to flush the priviledges;

flush privileges;

You can now safely quit the mysql client with ‘exit’ and you are set!

This is also covered on the mySQL documentation site.

2 Comments