Category: Databases - MySQL
Posted by: spyhwearslayer
Look... what I am talking about here is simply writing some php code to connect to a local MySQL Server, Local MySQL Server means clearly a MySQL Server that is on the same computer as the Apache Web server that your web pages are on.... here we are doing this on a computer with Apache 2.2.25 configures with PHP 5.2.5 running on the same machine and MySQL 5.6.14. we are using Workbench 6.0 as a gui interface to create sql commands and also to see that users we have and databases etc.
So we are going to create a user with a password for that My SQL Server, and remember that password, you will not be able to connect without both the user name and the password. We are going to use the php function mysql_connect to actually make the connection and we will place a bit of code around that so we know for sure whether the connection was successfully made.
There are a few things you should do as soon as you install a new MySQL Server, and if you do not do them. you could be in for a few unpleasant surprises and some frustrations as well.
Ever locked your car then opened the boot of the car and placed your car keys in the boot and then slammed the boot shut, and suddenly what you have done dawns on you...

Well... MySQL When you install it creates a root user, called of-course root, with a password that is blank. This of course means that anyone could connect to the Mysql Server as the root user with out having to enter any pass word at all, since the root user has access to more less everything then any one could log in and do anything including read all the tables or delete (drop) them, or a whole load of other things.

So you should create a password for the root user that is longer than 6 characters long and not just letters but a combination of letters and numbers..

A New installation of MySQL has two anonymous users that are created as part of the installation of MySQL, these are there to allow connections to the MySQL Server, from the local host using any user name. What this means is connection on the computer that the server is installed on, (thats what local host ) means. These two anonymous users support connections from the local host as well as connections not from the actual computer the server is on but from another computer where the hosts name is specified.

The upshot of this is two things : these users allow connection to the server of someone with any user name from any computer, and secondly if you create a new user on the server with a password they may not be able to connect to the server at all, because the server will match the user with one of the anonymous users that requires no password. but you logged in with a password, so MySQL will refuse to authenticate you and you can not connect, like the guy with the car keys in the boot.!!?

There are a number of answers to this problem of your new valid user not being able to connect, the neatest one is to delete these two anonymous users.

So in true style we show you how to do this:
you need to issue this bit of sql to the server:
DELETE FROM mysql.user WHERE user=*.*
you can either do this at the command line once connected to the server or easier use My SQL Query Browser, which we advise you download when you download the MySQL Server itself.

Look most of this applies to a local MySQL Server installation, on a Live Web Server the only way you can probably interact with the MySQL server is probably via phpMyadmin.