Setting up user accounts on mySQL

Question

I see the command to set up a user account on mySQL is:

CREATE USER 'userName'@'localhost' IDENTIFIED BY 'some_pass';

For the localhost, do I keep that local host if I want the user to be able to insert from another ip adress that the mySQL DB is not on?

Also if I was creating a connecting class to match the above, would it look like this:

<?php
    class myConnect extends mysqli{

        public function __construct($hostname='localhost',  
            $user='userName',
            $password='some_pass', 
            $dbname='dbName'){
            parent::__construct($hostname, $user, $password, $dbname);
        }
    }

    ?>

Again I am concerned about the localhost part in the php class above. Basically the php is not goign to be on the same server as the database.

Update:

Tried the answer below but am getting this php error still:

Warning: mysqli::mysqli() [mysqli.mysqli]: (HY000/2003): Can't connect to MySQL server on 'mySQLIP' (111) in /home4/m133414/public_html/myDigitalOcean.php on line 12

Make sure MySQL is also using the default port 3306. If it isn't, you'll need to add the port to the IP address too. — smolinari
– smolinari, Commented Mar 19, 2015 at 5:11
Oh, and a bit off topic, but if you want to simplify your work a bit, it is better to work with PDO, instead of with mysqli directly. php.net/manual/en/pdo.connections.php — smolinari
– smolinari, Commented Mar 19, 2015 at 5:14

juacala · Accepted Answer · 2015-03-19 02:29:04Z

1

If php is installed on a different server than MySQL, you need to change localhost to whatever the IP address is that the MySQL sees the php server as. That's in the user creation. If you want it avaiable from anywhere, change localhost to '%'

In php, in place of localhost, put the IP address of the MySQL server.

edited Mar 19, 2015 at 2:29

answered Mar 19, 2015 at 2:14

juacala

2,2351 gold badge23 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

George Cummins · Accepted Answer · 2015-03-19 02:23:04Z

0

If your PHP server has a separate IP address or host name than the MySQL server, use the PHP server's IP/hostname in the grant statement

CREATE USER 'userName'@'php.server.ip.goes.here' IDENTIFIED BY 'some_pass';

If you want users to be able to connect to your server from anywhere, you can use a wildcard:

CREATE USER 'userName'@'%' IDENTIFIED BY 'some_pass';

though this is generally insecure.

You should use 'localhost' if you want users to be able to connect to your server only from the same IP/host as your MySQL server. This would be appropriate in situations where your webserver is on the same host as the MySQL server.

answered Mar 19, 2015 at 2:23

George Cummins

29k8 gold badges74 silver badges91 bronze badges

Comments

rivimey · Accepted Answer · 2015-03-19 03:41:09Z

Mysql is very picky about user account string formatting, and in particular treats the hostname 'localhost' as special (and exactly how depends on the version).

If the hostname is 'localhost' then many mysql versions will use a local Unix Socket (and not TCP/IP) to connect. If you use a DNS name such as mysql.server.example.org, be aware that you need to include exactly the string mysql sees on connect: it's not 'intelligent' in saying 'mysql' is the same thing, for example.

Be aware also that creating a user does not give it permission to do anything. You will normally need to use GRANT to do that as well. You can grant permission to the whole server (not recommended!) or to all tables in a database, or even to individual tables. I would strongly recommend testing using 'phpmyadmin' to investigate this if needed.

Finally, I'm just slightly worried that you are conflating user-of-mysql and user-of-application. Normally, the mysql user table is not used for application level users (e.g. website profiles). I say this because DB user creation is often a one-off thing and so doesn't need application code to do it...

Collectives™ on Stack Overflow

Setting up user accounts on mySQL

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related