How do I get MySQL working?

Fasn8n · 03-01-2006, 01:55 PM

I have the following packages installed...
mysql-client
mysql-common
mysql-server
There's also one more package not installed and that's mysql-common-4.1. Should I install that one as well?

Anyways... Now that I have these on my machine, and that appears to be all I need (correct me if i'm wrong). What do I do to create a user, database and start accessing it with MySQL commands?

Fasn8n · 03-01-2006, 01:59 PM

I found this little gem and I tried taking bits and pieces from it to get it to work but it's a no-go.

http://yolinux.com/TUTORIALS/LinuxTutorialMySQL.html

fouldsy · 03-01-2006, 02:58 PM

What distro are you running? You should find your package management tool will handle all the neccessary dependencies for you by choosing to install mysql-server (and yes, it would install mysql-common, mysql-client, etc.). There's not really a huge amount of setting up to do depending on your distro... Also, an additional package, phpmyadmin is useful for maniuplating MySQL database unless you're wanting to go hardcore and to it all through the command line!

Fasn8n · 03-01-2006, 04:09 PM

lol definitely setting up phpmyadmin, already planned on that. But I'm using Ubuntu. Every time I try to login with "mysql -u root password 'bleh'" it says I cant connect with user "root" even if I sudo.

sipsipi · 03-01-2006, 11:04 PM

Can you not get root in Ubuntu? I have heard this is the case.

renaissance · 03-02-2006, 12:20 AM

use this, open your terminal.
Then write command: mysql and afther that:
SET PASSWORD = PASSWORD('give passwd');
quit;

smudge|lala · 03-20-2006, 10:08 AM

A tutorial I read about MySQL was going fine until it came to running the freshly compiled and installed MySQL. After doing mysql_install_db it says to issue the command chown -R var . Perhaps this is of no concern to many but I cannot for the life of me get mysql to run and wonder if this command is related to it? I get no ./configure or make / make install errors. Only when I run mysql.

The typical errors I get sound like a bug posted on the mysql site with MySQL 5.0.18 and the error is:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

I also get PID errors when I try to change anything and am wondering if chown -R var has confused the group ownership.

A directory listing says 'var' is owned by root and the group is 'staff'. Think this shold be Mysql by default? Also the dir var is 'locked'. Command # ls -l says dir 'var' is set to 700, or drwx------

Starting again but without doing chown -R var

Quote:

# chown: `mysql': invalid user
Starting mysqld daemon with databases from /usr/local/mysql/var
STOPPING server from pid file /usr/local/mysql/var/SGI.pid
060319 timestamp mysqld ended

Right, so now lets chown it:

Quote:

# chown -R mysql var
chown: `mysql': invalid user

I did chown -R var inside /usr/local/mysql and /usr/local incase for some reason I couldn't change ownership. It seems for some reason, the user mysql will not be accepted and I cannot proceed? There's my PID error figured, but not sure how to resolve it!

Having trawled the SQL manual I am still none the wiser. However, no other tutorial on this subject has said chown -R var. I understand it's purpose but why can't I change it? Did anyone else using this latest version of mysql get a similar error? Why did I get stuck with group 'staff'?

Quote:

Default user in 'make' notes is 'mysql'.

My Error Log

Quote:

/usr/local/mysql/var/SGI.err (SGI is my workstation username)

060319 19:56:47 mysqld started
060319 19:56:47 [ERROR] Fatal error: Can't change to run as user 'mysql' ; Please check that the user exists!

060319 19:56:47 [ERROR] Aborting

060319 19:56:47 [Note] /usr/local/mysql/libexec/mysqld: Shutdown complete

060319 19:56:47 mysqld ended

060320 10:43:01 mysqld started
060320 10:43:01 [ERROR] Fatal error: Can't change to run as user 'mysql' ; Please check that the user exists!

060320 10:43:01 [ERROR] Aborting

060320 10:43:01 [Note] /usr/local/mysql/libexec/mysqld: Shutdown complete

060320 10:43:01 mysqld ended

Yes I have TCP Port 3306 open for MySQL and have tried with no firewall just to be sure.

timmeke · 03-20-2006, 10:23 AM

Quote:

chown: `mysql': invalid user

Are you sure that you've created the user "mysql" and also the group with same name?

On my system - but I installed MySql without compiling it - /var/lib/mysql practically everything is owned by
user "mysql" and group "mysql".
In this directory, the lock file (mysql.lock) gets created, so the user that mysql runs under (typically "mysql" must have write access to the /var/lib/mysql directory.

smudge|lala · 03-20-2006, 10:51 AM

Thanks for that. I thought staff was a strange group. I have no idea why it defaulted to that! I thought at least root would have access, but even root cannot start anything. Now I'm afraid to change group ownership incase the 'staff' group messes up something else! Any ideas?

Should I create the user mysql with the next available GID and user ID?

Naturally my command

chown -R mysql var

is attempting to change all files and directories in 'var' to ownership of 'mysql' user. It wouldn't let me without creating the user of course. So I tried adding my own user to it:

chown -R MYUSER var the change is accepted but I get the same problem running mysql.

I guess the best way is to create GID and UID for mysql (tips on which ID would be gratefully received). Then to recursively change all staff ownership on 'anything' mysql (from make install) to mysql user.

Bit of a mess, not sure where to begin. Why can't MYUSER run MySQL either?

smudge|lala · 03-20-2006, 03:41 PM

Thanks for the above. I have created user mysql and group mysql with access to root, users, staff. I can now get MySQL running.

Problem with MySQL setup:

I am pretty new to MySQL and have at least managed to get it compiled and running ok.

Trouble is I am sailing blind. It's not the basic commants and database/table creation I am having trouble with. I know exactly how many databases and tables there are, but I have no idea what users exist for MySQL, and what their passwords are. I missed the essential part right after mysql_install_db by accidentally skipping a chunk of my tutorial.

I can login as root by issuing from root #:

/mysql/bin/mysql -u -p

Which gives me a mysql> prompt.

I have tried to issue

mysql> UPDATE user SET Password = PASSWORD('MegaHotNewPassword') WHERE User = 'root';

Then I get

ERROR 1046 (3D000): No database selected

So I mysql> use phpdb; (One I newly created) and repeated.

mysql> UPDATE user SET Password = PASSWORD("MegaHotNewPassword") WHERE User = 'root';
ERROR 1146 (42S02): Table 'phpdb.user' doesn't exist
mysql>

Now I try a more direct approach at mysql command:

mysql> set password = password("MegaHotNewPassword");
Query OK, 0 rows affected (0.00 sec)

One thing that confuses me is I can issue mysql> status and get the selected database, but is this the only one running? What if I messed up my user accounts and tried to login a few times. I think I may have several running. Need a spring clean, and start with only the users I need, and a password for each and drop anything else or other running users or databases. By default I have:

Quote:

Database

information_schema
mysql
phpdb

If I do a user$ ps aux | grep mysql I get this information:

Quote:

root 32543 0.0 0.1 4668 1312 ? S 14:40 0:00 /bin/sh bin/mysqld_safe
mysql 32558 0.0 1.5 92440 16360 ? Sl 14:40 0:00 /installpath/mysqld --basedir=/installpath/mysql --datadir=/installpath/mysql/var --user=mysql --pid-file=/installpath/mysql/var/master.pid --skip-locking
username 1950 0.0 0.0 3708 592 pts/4 S+ 16:45 0:00 grep mysql

timmeke · 03-21-2006, 02:27 AM

root password isn't changed via the "mysql" client. Use the "mysql_admin" program instead, as probably mentioned in the MySql docs.

Quote:

/mysql/bin/mysql -u -p

Use the "-D database" option to immediately start using a database after login.

For security reasons, don't make "mysql" part of any important system groups, like root. The whole point of running MySql under a dedicated user (like "mysql") is to avoid running it under root (for security reasons).
By making "mysql" part of an important system group like "wheel" or "root" or something, you bypass that security effort.

To know what databases have been created, check out:
http://dev.mysql.com/doc/refman/5.0/...databases.html
Similarly, there is also a "show tables" command to show all tables in the database you're using.

If you want a clean MySql, simply drop all databases you created and start over.

smudge|lala · 03-21-2006, 02:52 PM

Please treat this post as half working example and half queries about issues I'm having doing so. Thank you.

Thanks for your input. I believe all the mysql user and group settings are safe and operational now, and no root access between groups. After editing my Users and Groups I was successfull in doing chown -R mysql var or change ownership on var directory to mysql user recursively.

Very good tips on changing root password here.

From this guide, I did:

Quote:

# su
# killall mysql*
# bin/mysqladmin
mysql> UPDATE mysql.user SET password=PASSWORD('MegaHotNewPassword') WHERE user='root';
exit; Great password for root changed and accepted!
# mysqladmin -u root shutdown or
# killall mysql* then
bin/mysqld_safe & switch to another console as other becomes dedicated.
#bin/mysql -p Entered the 'MegaHotNewPassword'
mysql> SHOW DATABASES; This works. If I exit and run from root,
mysql > exit;
# ps aux | grep mysql* I get
root 7498 0.0 0.1 4672 1316 pts/4 S 15:07 0:00 /bin/sh bin/mysqld_safe
mysql 7513 0.0 1.4 100520 14796 pts/4 Sl 15:07 0:00 /install_path/mysql/libexec/mysqld --basedir=/install_path/mysql --datadir=/install_path/mysql/var --user=mysql --pid-file=/install_path/mysql/var/master.pid --skip-locking

So mysqld_safe is still running and all looks ok.

Now if I access MySQL as a user:

Quote:

USER@[~]$ /install_path/mysql/bin/mysql -u -p
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.19

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

I can login directly as no password was specified for this user. However this user cannot create, edit or anything to the tables without root changing privileges.

I'm slowly getting the hang of this. I still don't know if any php connection should be made to the database with the root password? Lets say I created database phpdb for use with a simple website or even a bulletin board.

Questions, considerations and problems:

Quote:

How do I create database phpdb for my bulletin board which can only be edited in MySQL by root and user called php. My user cannot create, only root so far.. is this normal?

How do I add user php to MySQL phpdb with password ch33se, for example?

Can anyone else creat a database without being root in MySQL? If so, I still have user password issues.

I can do very little as user in MySQL.

timmeke · 03-22-2006, 02:26 AM

root, or the database administrator, must grant privileges to other users to create tables, do queries,... pretty much anything you can do in a database.

Check out the MySql docs on the "grant" SQL statement.

If root doesn't give any privileges (per database!) to other users, then the other users can probably do nothing at all in the database.
Needless to say, this "privileges" method is the basic security of your database. So be careful in what you allow to other users.

To add users to a database on to grant them privileges, please look at
http://dev.mysql.com/doc/refman/5.0/...ing-users.html

I suggest you create the databases using root, create the users for the database and then grant the necessary privileges (as little as possible) to them.

smudge|lala · 03-22-2006, 11:35 PM

Thank you for your input. I realise it was a long post, partly as it was a mix of my notes as well as a mix of my questions. I have gone to some extent to enable full support for custom compiled sources with various options. I will post a tutorial when I am ready and hopefully it will help others. Thanks again.

smudge|lala · 03-26-2006, 08:21 PM

I've actually realised how long my posts above are. I have been working on a condensed version, part notes part tutorial as I am getting a feel for it now. One thing I am not sure about is editing users in MySQL. I have searched the MySQL manual and I already know how to create a user for a database with X password and Y permissions, BUT I don't know how to drop a user or update the info.

Lets say the install created a root account

Code:

mysql > use mysql;
mysql > show tables;
mysql > select * from user \G

I can easily see from the data that's printed out that I have a host called localhost, a user called root and a hashed passwd. Lets say I messed up adding a new user, and added the following by mistake:

Host: MyUserName (This should be in field below!)
User: root (Above should read localhost!)

Well this is obviously wrong! Host should still be localhost, the user this time isn't mean to be root, but MyUsername and I need to add a new password. Please explain how I change this? I know the answer is here, but I can't understand which command I need to issue without loosing the other users.

I need to do a UPDATE user WHERE command I think, or just dump (DROP) the user altogether and start again. Ironically I can add 100 users no sweat, but I can't figure out how to change one.

Code:

mysql > Update user WHERE hostname="MyUserName";

or something.