- 8 Week Game
- Quick Ref
Archive for category mySQL
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;
We have some dot net code which uses MySQL- our live setup it thus;
MySQL Dot net connector dll version 22.214.171.124
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;
With this combination or MySQL connector 126.96.36.199 and MySQL 5.1.57, we couldn’t execute any stored procedures- we would just get;
Procedure or function ‘
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 188.8.131.52.
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
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
before you will be able to login using those user accounts!
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;
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;
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.
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
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;
You can now safely quit the mysql client with ‘exit’ and you are set!
This is also covered on the mySQL documentation site.