MySQL root password and other configuration issues Options

[Christian J]

I'm finally going to try learning MySQL, and have installed a MySQL server on my computer (Windows with Apache and PHP). After some confusion I found that in order to succeed with PHP mysql_connect() I needed the username "root" and the MySQL root password created during the installation. Related questions:

- Is a root password necessary or useful on an offline testing server? Is it practical to make a new user account (with passwords?) in addition to root for each site I'm testing offline?

- Any other security precautions? For example there's a "skip-networking" directive, which stops MySQL from listening on a TCP/IP port.

- In phpinfo.php the "mysql.default_user" and "mysql.default_password" directives are listed. What are they used for? Apparently they're not the same as the MySQL root and password.

- What is http://www.php.net/manual/en/ini.core.php#ini.sql.safe-mode used for? When enabled mysql_connect() fails, apparently because it makes PHP ignore my root password. So when do you use safe mode --when passwords are not used (sounds contradictory)?

- Anything else to think of, so that my offline configurations won't differ too much from what online webhosts look like? I don't have any web host offering MySQL, so I can't check.

[Brian Chandler]

The short answers is that I don't know: I use mysql on my hosting service (pair), and am saved having to administer the db server.

For each DB I create (using the pair web interface) I get new user names (actually three: full, read-write, and read-only) and passwords. I *guess* that you need the mysql _root_ password to do things like Create new database.

In phpinfo.php the "mysql.default_user" and "mysql.default_password" directives are listed. What are they used for?

I guess (again) that these are defaults php will use to connect to mysql. In a low/zero security system this would mean you don't need to suppy them to the mysql_connect() function. Perhaps?

At some stage I must have an offline system for testing my shop. I would be grateful to hear any feedback on how you get on.

[Christian J]

I use mysql on my hosting service (pair), and am saved having to administer the db server.

Does that mean you must test DB-related PHP scripts online too, or are there tricks you can use to test without uploading them (like temporarily substituting the DB data with e.g. a PHP array)? If so maybe I don't really need an offline DB.

I guess (again) that these are defaults php will use to connect to mysql. In a low/zero security system this would mean you don't need to suppy them to the mysql_connect() function. Perhaps?

Could be. Can't remember if the root password was mandatory during the MySQL installation, if not the PHP defaults make sense.

At some stage I must have an offline system for testing my shop. I would be grateful to hear any feedback on how you get on.

So far it's just as terrible as I recalled it was last time I tried (and gave up). Can't even find a link to the MySQL manual on the site (Google found it though).

[pandy]

I've never set up MySQL locally. It's been years since I even had Apache installed. Not much point anymore when it's almost as quick to play on the remote server with the advantage of being in the environment the stuff is supposed to work in all along.

[Christian J]

Not much point anymore when it's almost as quick to play on the remote server with the advantage of being in the environment the stuff is supposed to work in all along.

That sounds pretty persuasive, but how about fixing errors on published pages? I rather do things like that offline, without any stress. When it comes to databases coding mistakes may even distort the data, so again it may be safer to work with a dummy DB offline.

[pandy]

You set up a playground area on the server.

[Christian J]

You set up a playground area on the server.

That's a thought, at least for PHP. Do you save a backup on your own computer?

[pandy]

No, not unless its something very important. The server backups are seldom more than 2 days old, so I wouldn't lose very much. But that's me. If it's more important than my stuff is you could make daily backups or something.

[Frederiek]

Or, simply use SQLite.

[Christian J]

Or, simply use SQLite.

But then the web host must have it also?

Maybe XAMPP is another simple alternative for offline testing, but I didn't dare install it since I already had Apache and PHP.

[Christian J]

I would be grateful to hear any feedback on how you get on.

Forgot to mention: using PHP I could create a DB, a DB table, and then populate the latter with some entries. So far all of it feels terribly involved compared with a nice flatfile DB, though.

[pandy]

So it works.

[Brian Chandler]

I would be grateful to hear any feedback on how you get on.

Forgot to mention: using PHP I could create a DB, a DB table, and then populate the latter with some entries. So far all of it feels terribly involved compared with a nice flatfile DB, though.

I recommend using phpmyadmin (what a dreadful name!) for all "setup" operations. "Setup" basically means things you will only ever do once, so writing a program to do them is a bit inefficient.

Are you going to have a db system you administer every day? In which case it is definitely worth writing a program to arrange the updates conveniently. It's easy to present a colour-coded view of things like status codes. And each new program (for a new db table) gets easier and easier to write, because you copy the last one and change the bits that are different.

Incidentally, yes, currently I update everything directly on the server (which is not really the thing to do), except when I am really changing something, in which case I just give the file (program? web page? what should one call it?) a different name. But I think having a complete development system locally would be a better way to do it. I certainly keep copies of all files both on the server (one continent) and here (different continent), plus roughly monthy backup copies on a CD-R. Remember there is no chance of retrieving copies of programs from Google.

HTH. If you give us more concrete details of what you plan to do, perhaps more suggestions...

[geoffmerritt]

I have Xampp for windows running on my laptop, which comes with php, apache, mysql, it is easy to setup and update.

localhost is the root of the server, setup of database can be done in phpmyadmin.

I like the fact I can create a complete working site on my pc and then export the *.sql file and then import the file to the web hosting service via phpmyadmin.

Using root in mysql as a user name should be avoided. It has been suggested to me that I should set up several users with different privileges and use the relevant user for the job. eg SELECT, the user should have read only privileges, etc. Maybe on my current project I will do this.

[Christian J]

I recommend using phpmyadmin (what a dreadful name!) for all "setup" operations. "Setup" basically means things you will only ever do once, so writing a program to do them is a bit inefficient.

Otherwise setup with PHP was very simple (I just used http://www.w3schools.com/php/php_mysql_create.asp ), but I guess phpmyadmin lets you do more things.

If you give us more concrete details of what you plan to do, perhaps more suggestions...

Just want to learn a little MySQL. Some kind of practice project might be useful, the hard thing is finding the motivation to use MySQL instead of a flatfile (which I'm more familiar with). While I'm sure MySQL is more efficient for large DBs, I'll hardly make a large one just for practice.

[Christian J]

Using root in mysql as a user name should be avoided.

Even offline? On an online server, does the web host take care of things like this or do you have to do it yourself?

[pandy]

You usually don't have root anything on a shared server.

[geoffmerritt]

Using root in mysql as a user name should be avoided.

Even offline? On an online server, does the web host take care of things like this or do you have to do it yourself?

Offline, guess it doesn't make a difference... If the web host uses cpanel you may need to create your own dbase, users and passwords.

I have my own hosting server, and replicate the databases identically on my laptop.... so i guess it a matter of course for me not to use root

[Frederiek]

Or, simply use SQLite.

But then the web host must have it also?

No, not as far as I know. Read up at http://devzone.zend.com/article/760-SQLite-Introduction

[Brian Chandler]

I recommend using phpmyadmin (what a dreadful name!) for all "setup" operations. "Setup" basically means things you will only ever do once, so writing a program to do them is a bit inefficient.

Otherwise setup with PHP was very simple (I just used http://www.w3schools.com/php/php_mysql_create.asp ), but I guess phpmyadmin lets you do more things.

No, phpmyadmin cannot let you do more -- it makes it easier to do standard-ish things that you only do occasionally, in particular like creating tables. Actually I started with the mysql command line, but I honestly don't think you miss anything by skipping that.

If you give us more concrete details of what you plan to do, perhaps more suggestions...

Just want to learn a little MySQL. Some kind of practice project might be useful, the hard thing is finding the motivation to use MySQL instead of a flatfile (which I'm more familiar with). While I'm sure MySQL is more efficient for large DBs, I'll hardly make a large one just for practice.

The point of a DB is storing structured information: it's much easier to do this with DB columns than a "flat file" (assuming you just mean writing out stuff, reading it back in, and *parsing* it (which is the tedious bit).