Linode Forum
https://forum.linode.com/

MySQL Setup
https://forum.linode.com/viewtopic.php?f=19&t=10317		 Page 1 of 1
Author:  camelcrushfresh [ Sun Aug 04, 2013 7:03 pm ]
Post subject:  MySQL Setup
I've been trying to setup mysql properly, but it isn't working. This won't be locally stored, so from my own understanding I shouldn't be using 'kai'@'localhost' rather I use @'%' correct? I have tried following guides everywhere I could find, but they don't get it working. I want to setup a database called 'eve' and have a user on it called 'kai'. It's not that I'm unwilling to learn but it's on Linux and I don't have cpanel with my host. I have set up the database, mysql, etc. I've also tried setting up phpmyadmin but it made things more confusing. I took a step back to use ssh mysql commands being I only need one table and user.? So far:

Code:
create user 'kai'@'%' identified by password 'mypass';
grant all privileges on . to 'kai'@'%' with grant option;


ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'grant all privileges on . to 'kai'@'%' with grant option' at line 1

that's one example

I've tried several different ways including @%, @localhost, etc. i tried going through the mysql guide they have in place, but it didn't help too much. I considered maybe I needed to use @192., etc, but I don't think that'd be secure.

Any pointers would be excellent, I'm sure I'm just skipping something easy and I don't know the proper terminology to google or I would have found it already ;). This is on Debian 7 64bit, dunno how important that is but everything is updated, etc.

I've been following this: http://dev.mysql.com/doc/refman/5.5/en/ ... users.html

errors I've been having: 1396, 1064 and a couple of others. I'm essentially wanting to do what someone would normally easily do through cPanel, etc. but in a ssh client. If there's a way to setup something like that to where I would have a gui to work with, that'd be something I wouldn't have a problem trying.

Should I be using localhost? My IP? sdnam.net?

error 1396 create user 'kai'@'%' identified by password 'mypass'; ERROR 1396 (HY000): Operation CREATE USER failed for 'kai'@'%

Cheers

edit upon completed attempts on the "application"(?) I'm trying to use it with I either have a server error (500) or
Code:
access denied for 'kai'@'localhost'
Author:  vonskippy [ Sun Aug 04, 2013 7:24 pm ]
Post subject:  Re: MySQL Setup
camelcrushfresh wrote:
This won't be locally stored

Define what you think "not locally stored" means.

Do you mean you'll have at least two VPS's and one VPS will have the MySQL engine running on it and will store the data, and another VPS will have a web framework that accesses the database on the other VPS?

Or what?
Author:  camelcrushfresh [ Sun Aug 04, 2013 8:06 pm ]
Post subject:  Re: MySQL Setup
vonskippy wrote:
camelcrushfresh wrote:
This won't be locally stored

Define what you think "not locally stored" means.

Do you mean you'll have at least two VPS's and one VPS will have the MySQL engine running on it and will store the data, and another VPS will have a web framework that accesses the database on the other VPS?

Or what?


One VPS, I meant not locally on my own hard drive. It will be on one linode.
Author:  vonskippy [ Sun Aug 04, 2013 8:10 pm ]
Post subject:  Re: MySQL Setup
Assuming you have MySQL Server installed and running.

Login as MySQL root and create the database and user....

Code:
    #mysql -u root -p  <then enter mysqld root password>
       > Create Database eve;  (should respond OK)
       >Grant ALL privileges on eve.* to kai@localhost Identified by “kais-special-password”;(should respond OK)
       >Flush Privileges; (should respond OK)
       > Exit (should respond with BYE)
Author:  camelcrushfresh [ Sun Aug 04, 2013 8:37 pm ]
Post subject:  Re: MySQL Setup
vonskippy wrote:
Assuming you have MySQL Server installed and running.

Login as MySQL root and create the database and user....

Code:
    #mysql -u root -p  <then enter mysqld root password>
       > Create Database eve;  (should respond OK)
       >Grant ALL privileges on eve.* to kai@localhost Identified by “kais-special-password”;(should respond OK)
       >Flush Privileges; (should respond OK)
       > Exit (should respond with BYE)

That all went along smoothly, I'm sorry if this is a no-brainer to you. I have experience with other hosts, but they were shared hosting platforms so it did all this important legwork for me. Is everything okay to run in the application I am trying to use it with? I'm assuming the password/all that should be the same in the fields it is asking for.

One thing I ran into was it asks to use "localhost" or something else "if I'm not sure" and I read that I should use 127.0.0.1 any comments?

I think I have a question I haven't asked yet, but I'm sure I can find it in the docs. Cheers


[edit: the above is from someone inexperienced, I'm aware this is all very important for someone to know how to do, but I'm a novice and doing this for fun. :)]
Author:  Piki [ Sun Aug 04, 2013 9:15 pm ]
Post subject:  Re: MySQL Setup
camelcrushfresh wrote:
One thing I ran into was it asks to use "localhost" or something else "if I'm not sure" and I read that I should use 127.0.0.1 any comments?


127.0.0.1 and localhost are roughly equivalent, however I recommend using the same one you used in the "grant all privileges" command in MySQL. I seem to remember MySQL yelling at me last time I tried to switch them.

Technically speaking, localhost is an alias that each individual computer kind sorta use for itself, and 127.0.0.1 is a sort of "virtual IP address" that is used by some applications to send data between each other within the localhost. For example, if you're running MySQL and a web site that requires MySQL on the same machine, their location relative to each other is considered "localhost" because they are both on the same computer, and they can use either the name "localhost" or the IP address 127.0.0.1 to communicate with each other.

Hopefully that doesn't confuse you even more, it's been ages since I was at you're level of newbiness :wink:
Author:  camelcrushfresh [ Sun Aug 04, 2013 9:17 pm ]
Post subject:  Re: MySQL Setup
Piki wrote:
camelcrushfresh wrote:
One thing I ran into was it asks to use "localhost" or something else "if I'm not sure" and I read that I should use 127.0.0.1 any comments?


127.0.0.1 and localhost are roughly equivalent, however I recommend using the same one you used in the "grant all privileges" command in MySQL. I seem to remember MySQL yelling at me last time I tried to switch them.

Technically speaking, localhost is an alias that each individual computer kind sorta use for itself, and 127.0.0.1 is a sort of "virtual IP address" that is used by some applications to send data between each other within the localhost. For example, if you're running MySQL and a web site that requires MySQL on the same machine, their location relative to each other is considered "localhost" because they are both on the same computer, and they can use either the name "localhost" or the IP address 127.0.0.1 to communicate with each other.

Hopefully that doesn't confuse you even more, it's been ages since I was at you're level of newbiness :wink:


So it seems! That does make it make more sense, but for my own future understanding the inter-workings of this what should I be researching? I asked on an IRC channel and they said to looking to bash shell commands, and other O'Reilly books. Any suggestions? Thanks! This puts me in the right direction at the very least :)

That server would be my website hostname then? Ex: sdnam.net ? Sorry I actually am a little more confused haha. :oops:

Edit: It doesn't make sense to use eve, and I did use @localhost because that's what I was told to do..?
Author:  vonskippy [ Sun Aug 04, 2013 9:46 pm ]
Post subject:  Re: MySQL Setup
camelcrushfresh wrote:
It doesn't make sense to use eve

Database (DB) names are completely relative - so name it whatever you want. Most people make the db name relative to what uses the db (so for a wordpress site, I'd use wpdb01 as the db name). Same for user names (so in my example, I'd use wpuser01 as the user name to access the database called wpdb01). Just use strong passwords (12+ mixed characters) and the rest doesn't really matter.

Whatever you do, just be SURE to document EVERYTHING.

You should have notes such that you can do a complete build from scratch (i.e. a brand new VPS) just by following EXACTLY your notes and at the end of your notes, you should have a working system.

Be sure to backup ALL your config files, your application files (like wordpress) and your database files.

Those and your detailed notes means you're set to go if you have to rebuild or move your site.
Author:  camelcrushfresh [ Sun Aug 04, 2013 10:22 pm ]
Post subject:  Re: MySQL Setup
vonskippy wrote:
camelcrushfresh wrote:
It doesn't make sense to use eve

Database (DB) names are completely relative - so name it whatever you want. Most people make the db name relative to what uses the db (so for a wordpress site, I'd use wpdb01 as the db name). Same for user names (so in my example, I'd use wpuser01 as the user name to access the database called wpdb01). Just use strong passwords (12+ mixed characters) and the rest doesn't really matter.

Whatever you do, just be SURE to document EVERYTHING.

You should have notes such that you can do a complete build from scratch (i.e. a brand new VPS) just by following EXACTLY your notes and at the end of your notes, you should have a working system.

Be sure to backup ALL your config files, your application files (like wordpress) and your database files.

Those and your detailed notes means you're set to go if you have to rebuild or move your site.


Definitely! I have kept a notepad open with passwords, things I've noticed I lose upon each re-build, etc. I meant in wordpress, phpbb [example] when it asks for the database password upon install...?

This is what I'm looking at:
https://www.dropbox.com/s/626zrq5b2p9vx ... tled-1.png

Edit: also how can I check to make sure the user was created? It returned:

Code:
Query OK, 0 rows affected (0.00 sec)


checked the SHOW TABLES, had my user there in the right database, tried running the install and had this returned
Unable to connect to database and my index gives:

Code:
[1045] dbconn: mysql_connect: Access denied for user 'kai'@'localhost' (using password: YES)


Progress? :\

Edit: installed webmina and the user/database is created and I no longer have the same familiar "failed" messages now it's error 500 through the browser after installing (successfully according to the app). Any ideas?
Author:  sweh [ Mon Aug 05, 2013 6:51 am ]
Post subject:  Re: MySQL Setup
Here's my example "build" script. In this example I'm picking a bad password ("foo") for root and the user because it's just an example. First I create the empty database and user as the "root" user, then I reconnect as the new user to create the objects. For this example, the database is called "ssp".
Code:
#!/bin/sh
mysql -u root -pfoo << 'EOF'

-- Dummy to ensure user exists so the drop user stuff will work
grant usage on *.* to ssp_owner;
grant usage on *.* to ssp_owner@localhost;

drop user ssp_owner;
drop user ssp_owner@localhost;
create user 'ssp_owner'@'localhost' identified by 'foo';

drop database if exists SSP;
create database SSP;
grant all privileges on SSP.* to ssp_owner@localhost;
EOF

mysql -u ssp_owner -pfoo << 'EOF'
use SSP;

create table example1
(
  x integer
);
EOF

Here we will only be allowed to connect of the local socket, so this works if the webserver and database are on the same machine. We didn't create any user access for network logins, so the database can't be attacked that way.

This script is useful to me because it means I can rebuild a brand new blank database with one command and know the structure is exactly what it should be.
Page 1 of 1 All times are UTC-04:00
Powered by phpBB® Forum Software © phpBB Group
http://www.phpbb.com/