MySQL

pellicle · 07-25-2009, 02:06 PM

Hi

found a few similar threads but still can't get my mySQL to work

I've got an older Redhat 4 installation which I installed mySQL from the cd's at system install time

Code:

# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

I can use this to get a connection like:

Code:

# mysql -umysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 42 to server version: 4.1.12

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

mysql>

but I can't seem to do much in there ... this works

Code:

mysql> show databases;
+----------+
| Database |
+----------+
| test     |
+----------+
1 row in set (0.00 sec)

... but

I can't seem to create anything (user or database) ... I can't even grant myself anything.

any thoughts?

TB0ne · 07-25-2009, 03:35 PM

Quote:

Originally Posted by pellicle

Hi

found a few similar threads but still can't get my mySQL to work

I've got an older Redhat 4 installation which I installed mySQL from the cd's at system install time

... but

I can't seem to create anything (user or database) ... I can't even grant myself anything.

any thoughts?

Yep...you don't have rights to do anything, and don't have any databases defined. From the MySQL box, try SU'ing to root, and (as root), try just typing in "mysql".

The root user (from a fresh installation), is granted all rights locally. You should be able to create more users, databases, etc., from that point, and not have to go in as root again, except for rare cases.

pellicle · 07-26-2009, 12:25 AM

Hi

sorry ... should have said, this was as root ... should I be su - ing to mysql?

kirukan · 07-26-2009, 02:08 AM

You have logged in as mysql user (i mean your mysql login) this user haven't all rights therefore login your mysql using root user(supper user).

Read again TB0ne post

Quote:

Yep...you don't have rights to do anything, and don't have any databases defined. From the MySQL box, try SU'ing to root, and (as root), try just typing in "mysql".

The root user (from a fresh installation), is granted all rights locally. You should be able to create more users, databases, etc., from that point, and not have to go in as root again, except for rare cases.

pellicle · 07-26-2009, 02:18 AM

Hi

I can seemingly start, but I can only connect to the test database;

Code:

mysql> status;
--------------
mysql  Ver 14.7 Distrib 4.1.12, for redhat-linux-gnu (i386) using readline 4.3

Connection id:          9
Current database:       test
Current user:           mysql@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         4.1.12
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    latin1
Conn.  characterset:    latin1
UNIX socket:            /var/lib/mysql/mysql.sock
Uptime:                 3 min 49 sec

Threads: 1  Questions: 19  Slow queries: 0  Opens: 12  Flush tables: 1  Open tables: 1  Queries per second avg: 0.083
--------------

mysql> create database pergamentit;
ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'pergamentit'
mysql>

I tried making an edit in my conf file to add root ... just a wild stab

Code:

[root@home ~]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

[mysql.server]
user=mysql
user=root
basedir=/var/lib

[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[root@home ~]#

micxz · 07-26-2009, 02:26 AM

Quote:

Originally Posted by pellicle

Current user: mysql@localhost

Still not root here as mentioned above'

pellicle · 07-26-2009, 05:52 AM

but I'm ssh'ed into the server as root ... when I run the command

Quote:

# mysql --user=root
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

yet ...

Quote:

# mysql --user=mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 4.1.12

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

mysql>

I have even edited the file

Quote:

[root@oracle ~]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

[mysql.server]
user=root
#user=mysql
basedir=/var/lib

[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

and restarted

so what more do you mean by run as root, and how can I run as root if you can assist please

EricTRA · 07-26-2009, 06:01 AM

Hello,

Your MySQL installation doesn't accept the root user without password. This password you need is the one provided upon installation when asked.

Code:

mysql -u root -p

should ask you for a password. Once provided you are logged in as 'root' with all rights. If you still don't have any rights you will be able to grant yourself all rights on all databases, either on localhost or via client, or both.

Kind regards,

Eric

pellicle · 07-26-2009, 06:03 AM

Hi

Quote:

Originally Posted by EricTRA

Your MySQL installation doesn't accept the root user without password. This password you need is the one provided upon installation when asked.

Code:

mysql -u root -p

should ask you for a password.

well and good ... but I know the root password (its my personal box) and it seems not to accept it ... as I already of course tried that

pellicle · 07-26-2009, 06:06 AM

For instance

I ssh to the box as root

enter my root password

then when I try mysql I get this :

Quote:

# mysql --user=root -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

EricTRA · 07-26-2009, 06:06 AM

Hi,

The root password for MySQL root user is NOT always the same as the root user for your linux box. When installed MySQL asked you for a root password. If you provided the same as for your linux box then it should work. You can always reset the MySQL root password. Root on linux and Root on MySQL are two different logins.

Kind regards,

Eric

pellicle · 07-26-2009, 09:32 AM

Hi

Quote:

Originally Posted by EricTRA

You can always reset the MySQL root password. Root on linux and Root on MySQL are two different logins.

I seem not to be able to ...

EricTRA · 07-26-2009, 09:41 AM

Hi,

Using one of the procedures described HERE should do the trick. After resetting the root password you should be able to log in.

Kind regards,

Eric

jschiwal · 07-26-2009, 09:59 AM

FYI. You don't need to su to root first when logging in to mysql as root. Using the "-u root" is enough. You are accessing the 'root' user on the database, using the MySQL password, not the Linux root password.

There should be a mysql manual that came with your installation (probably on a pdf). Be sure to read
the section on securing mysql after installing.

use mysql;
select Host, User, password from user;
Make sure the password field isn't blank for the root user account when you are done following the last post.

pellicle · 07-26-2009, 02:05 PM

Hi

well it seemed like good advice, and perhaps somewhere else it may work.

I followed the steps in 1.4.1.2. Resetting the Root Password on Unix Systems

obviously substituting parameters such as

Quote:

shell> kill `cat /mysql-data-directory/host_name.pid`

made the script file

Quote:

UPDATE mysql.user SET Password=PASSWORD('blahblah') WHERE User='root';
FLUSH PRIVILEGES;

and followed the rest of the steps

nope

I must say its confusing having users like "root" on a system that root has a specific meaning to.

its been a long day with zero productivity and I got Oracle going in less than 1/4 the time.

perhaps my installation has something squiff about it ... but I am past caring now its too late.

another day

thanks for the contributions however