External MySQL host, authentication, part 2

v3
#1

Hello

I’ve been banging my head on the wall for several hours now. Can someone please show a working sample of files on how to set up a new WordPress site with EasyEngine when using external MySQL Server.

Setup:

I have created a new schema EasySite and database user EasySiteUser with MySQL workbench and granted full access to this user to the schema. Connection works fine from web server. Ttested with

I’ve followed instructions to the letter and created following ~/.my.cnf file:

In my /etc/easyengine/ee.conf database settings are following: [mysql]
grant-host = localhost
db-name = true
db-user = true

But when creating the site, this happens:

user@webserver:~$ sudo ee site create myserver.com --wpsubdom
Creating myserver.com, please wait…
Creating symbolic link for myserver.com
Creating htdocs & logs directory
Downloading WordPress, please wait…
Enter the MySQL database name [myserver_com]: EasySite
Enter the MySQL database username [myserver_com]: EasySiteUser
Enter the MySQL database password [I0STLpHjUbHkSiX]: ERROR 1007 (HY000) at line 1: Can’t create database ‘EasySite’; database exists
[ Wed Sep 3 04:46:26 EEST 2014 ] Unable to create EasySite database, exit status = 1

I tried dropping the EasySite database but that just created another error message about lacking grant permissions.

If I create the site to local MySQL database everything works fine.

What are the username and password configured to .my.cnf file supposed to be anyway? Is it supposed to be perhaps database root?

Regards,

  • Ossi
#2

Hi @Ossi_Mantylahti,

I think you missed this step.
On database server use following query:

grant all privileges on *.* to 'EasySiteUser'@'<PUBLIC_IP_OF_EASYENGINE_SERVER>'  IDENTIFIED BY 'ThePassWordOfEasySiteUser' with grant option;

You can also follow our wiki:

#3

Hi,

Getting back for this old topic since I took a closer look at this. The set-up does not work. Workaround is to create first a local database and then perform export & import for it.

My setup is following.

easyengine is going to be installed to

wpmu.myserver.com

Database server:

db.myserver.com

Database schema name:

wpmu2015

Username and password:

wpmu2015 
wpmu2015

user ‘wpmu2015’ has been granted all priviledges for schema ‘wpmu2015’. Here’s test run:

user@wpmu:~$ mysql -h db.myserver.com -u wpmu2015 -p
password:
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
| wpmu2015           |
+--------------------+
3 rows in set (0.00 sec)

mysql> use wpmu2015;
Database changed
mysql> create table pet (name VARCHAR(20), owner VARCHAR(20), birth DATE);
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO pet VALUES ('foo2','bar2', CURDATE());
Query OK, 1 row affected (0.00 sec)

mysql> select * from pet;
+------+-------+------------+
| name | owner | birth      |
+------+-------+------------+
| foo2 | bar2  | 2015-02-02 |
+------+-------+------------+
1 rows in set (0.00 sec)

mysql> drop table pet;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
user@wpmu:~$

The file ~/.my.cnf looks following:

[client]
host=db.myserver.com
user=wpmu2015
password=wpmu15

The file /etc/easyengine/ee.conf looks following:

# EasyEngine (ee) configuration file

[stack]
        apt-get-assume-yes = true
        gpg-key-fix = false
        ip-address =

[mysql]
        grant-host = db.myserver.com
        db-name = false
        db-user = false

[wordpress]
        prefix = false
        user =
        password =
        email =

Now when I try to create a site, following happens:

user@wpmu:~$ sudo ee site create myserver.com --wpsubdom
Creating myserverr.com, please wait...
Creating symbolic link for myserverr.com
Creating htdocs & logs directory
Downloading WordPress, please wait...
ERROR 1045 (28000): Access denied for user 'wpmu2015'@'wpmu.myserver.com' (using password: YES)
[ Tue Feb  3 00:17:12 EET 2015 ] Unable to create myserver_com database, exit status =  1
user@wpmu:~$

Any ideas? I’m puzzled where the user ‘wpmu2015’@’**wpmu.**myserver.com’ came from. It almost looks like the ee installation script does not even try to contact to the right database.

Regards,

  • Ossi
#4

And BTW, please correct me if I’m wrong, but you were suggesting running on following command on the database server:

grant all privileges on *.* to 'EasySiteUser'@'<PUBLIC_IP_OF_EASYENGINE_SERVER>'  IDENTIFIED BY 'ThePassWordOfEasySiteUser' with grant option;

If I am not mistaken, this would grant full permissions to ALL database schemas in the database server for EasySiteUser. And this is naturally too wide permission set - easyengine user should have access to only his database. Not other 20 databases running on the DB server.

Regards,

  • Ossi
#5

Hi @Ossi_Mantylahti

Generally, we assume a use-case where we have a dedicated database server.

Anyways you can use this:

grant all privileges on db_name.* to ‘EasySiteUser’@’<PUBLIC_IP_OF_EASYENGINE_SERVER>’ IDENTIFIED BY ‘ThePassWordOfEasySiteUser’ with grant option;`

It’s been a long time, and we haven’t heard from you. It looks like your issue is resolved.

I am closing this support topic for now. Feel free to create a new support topic if you have any queries further. :slight_smile:

#6