Archive for category mySQL

MySQL Backup & Restore

Posted by shawson in mySQL on January 29th, 2010

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

Posted by shawson in mySQL on May 31st, 2009

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!

Posted by shawson in IIS, blogging, mySQL, php on April 8th, 2009

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

Posted by shawson in IIS, mySQL, php on October 29th, 2008

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.

No Comments