using mysql, help needed

[Sarmad Al-Mashta]

hello tinkerers 

1. I am trying to run mysql on DNS-323 to store long term logs from another piece of hardware. 

once I install the package I was not able to run it giving me error that the port is already binded (probably because of sqlite that is required for lighttpd - more on that below)

rebooting the machine, solves the problem

the log file shows

200614 02:29:57 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
200614  2:29:58 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead.
200614  2:29:58 [Note] Event Scheduler: Loaded 0 events
200614  2:29:58 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.73'  socket: '/var/run/mysql/mysql.sock'  port: 3306  Source distribution

I connect to the box with putty using SSH, when I try to login 

[root@Enki]# mysql -u root -p
Enter password: (my root password)

I get :

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

then I tried to use MySQL Workbench (https://www.mysql.com/products/workbench/)

I get: 

Your connection attempt failed for user 'root' to the MySQL server at xxx.xxx.xxx.xxx(my IP):3306
Host 'mypc' is not allowed to connect to this MySQL server

Please:
bunch of things to check

Q1: How do I enable both local and remote access for 'root' ?

2. I thought of using phpMyAdmin 

so installed lighttpd and enabled php, both are running well. 

in hello.php I see:

mysql

MySQL Support	enabled
Active Persistent Links	0
Active Links	0
Client API version	5.1.73
MYSQL_MODULE_TYPE	external
MYSQL_SOCKET	/var/run/mysql/mysql.sock
MYSQL_INCLUDE	-I/home/jcard/Alt-F/alt-f-sf/build/build_arm/staging_dir/usr/include/mysql
MYSQL_LIBS	-L/home/jcard/Alt-F/alt-f-sf/build/build_arm/staging_dir/usr/lib/mysql -lmysqlclient

Directive	Local Value	Master Value
mysql.allow_local_infile	On	On
mysql.allow_persistent	On	On
mysql.connect_timeout	60	60
mysql.default_host	no value	no value
mysql.default_password	no value	no value
mysql.default_port	no value	no value
mysql.default_socket	/var/run/mysql/mysql.sock	/var/run/mysql/mysql.sock
mysql.default_user	no value	no value
mysql.max_links	Unlimited	Unlimited
mysql.max_persistent	Unlimited	Unlimited
mysql.trace_mode	Off	Off

Q2: How can Install phpMyAdmin (https://www.phpmyadmin.net/) ? (Memory is at 78%. swap at 0)

Q3: Can I switch lightpd to use mysql instead of sqlite (feels redundant having both servers running)

I have been scouring the internet for few hours trying to figure it out,but no success

any tutorials or help in that regards highly appreciated. 

[Joao Cardoso]

On Sunday, June 14, 2020 at 4:40:09 AM UTC+1, Sarmad Al-Mashta wrote:

hello tinkerers 

1. I am trying to run mysql on DNS-323 to store long term logs from another piece of hardware. 

once I install the package

There is an issue during the first run. If you where using the command line you would notice its warning.

Starting fresh:

[root@DNS-320L]# rcmysqld stop # always use the rc<service> start|stop|status|eanble|disable...

Stopping mysqld: OK. 

[root@DNS-320L]# rm -rf /var/lib/mysql # remove everything (if nothing added), first run will recreate them

[root@DNS-320L]# rcmysqld start 

Creating MySQL system tables...

/usr/bin/mysql_install_db: line 325: /usr/bin/hostname: not found 

The issue is that it thinks that the 'hostname' program is in /usr/bin/hostname, while it is at /bin/hostname.

You can fix that error by using before the first run the command:

ln -sf /bin/hostname /usr/bin/hostname

 

WARNING: The host '' could not be looked up with resolveip.

This probably means that your libc libraries are not 100 % compatible

with this binary MySQL version. The MySQL daemon, mysqld, should work

normally with the exception that host name resolving will not work.

This means that you should use IP addresses instead of hostnames

 

This hostname error is not worrying unless you want to connect from other hosts, what you seem to want to do.

when specifying MySQL privileges !

Installing MySQL system tables...

200618  3:36:07 [Warning] '--skip-locking' is deprecated and will be removed in a future relea

se. Please use '--skip-external-locking' instead.

OK

Filling help tables...

200618  3:36:09 [Warning] '--skip-locking' is deprecated and will be removed in a future relea

se. Please use '--skip-external-locking' instead.

OK

To start mysqld at boot time you have to copy

support-files/mysql.server to the right place for your system

disregard the above, use 'rcmysqld enable' or use the webui to boot enable (and save settings) 

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !

To do so, start the server, then issue the following commands:

/usr/bin/mysqladmin -u root password 'new-password'

/usr/bin/mysqladmin -u root -h  password 'new-password'

No root password is installed, and mysqld is running as user 'mysql'.

The next suggesting is better, but you have to install the 'mysql' Alt-F package first:

 

Alternatively you can run:

/usr/bin/mysql_secure_installation

which will also give you the option of removing the test

databases and anonymous user created by default.  This is

strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:

cd /usr ; /usr/bin/mysqld_safe &

disregard the above, use 'rcmysqld start' 

You can test the MySQL daemon with mysql-test-run.pl

cd /usr/mysql-test ; perl mysql-test-run.pl

perl is not installed by default and the above programs and test scripts are not distributed either.

 

Please report any problems with the /usr/bin/mysqlbug script!

Starting mysqld_safe: OK.

 

I was not able to run it giving me error that the port is already binded (probably because of sqlite that is required for lighttpd - more on that below)

sqlite is not a server process, it only runs when called, so is not using any port. lighttpd only uses it when webdav is enabled to validate user names and passwords.

 

rebooting the machine, solves the problem

probably mysqld was already running?

 

the log file shows

200614 02:29:57 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
200614  2:29:58 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead.
200614  2:29:58 [Note] Event Scheduler: Loaded 0 events
200614  2:29:58 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.73'  socket: '/var/run/mysql/mysql.sock'  port: 3306  Source distribution

I connect to the box with putty using SSH, when I try to login 

[root@Enki]# mysql -u root -p
Enter password: (my root password)

I get :

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

then I tried to use MySQL Workbench (https://www.mysql.com/products/workbench/)

I get: 

Your connection attempt failed for user 'root' to the MySQL server at xxx.xxx.xxx.xxx(my IP):3306
Host 'mypc' is not allowed to connect to this MySQL server

Please:
bunch of things to check

Q1: How do I enable both local and remote access for 'root' ?

run the recommended  'mysql_secure_installation' program.

2. I thought of using phpMyAdmin 

I'm afraid that recent versions require a more recent php version. But it might work. Be warned that php and phpMyAdmin are subjected to automated attacks.

 

so installed lighttpd and enabled php, both are running well. 

in hello.php I see:

That page shows you the capabilities of php, not lighttpd. 

Follow their instructions? I never did it.

 

Q3: Can I switch lightpd to use mysql instead of sqlite (feels redundant having both servers running)

You don't need, sqlite does not runs as a server. You are probably confusing lighttpd with php capabilities. php can use sqlite or mysql or both.

Of course it depends on what you intend to do with lighttpd, virtual hosting uses mysql.

 

I have been scouring the internet for few hours trying to figure it out,but no success

any tutorials or help in that regards highly appreciated. 

Hope this helps. 

[Sarmad Al-Mashta]

First enormous thank you Joao for everything .... 

I followed your instruction, keep in mind I am a windows guy, I know .... so embedded Linux is a real challenge for me... 

I did not get the host name error !!!

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h  password 'new-password'

this did not work so  used :

Alternatively you can run:
/usr/bin/mysql_secure_installation

and it worked I was able to set the root password, and login locally .... however I was unable to to log in remotely ... even after executing the host name correction that you suggested 

ln -sf /bin/hostname /usr/bin/hostname

I scoured the internet, I found so many different things that I got lost ... things are not in the same place where the articles say the should be. for instance ... this article: https://linuxize.com/post/mysql-remote-access/

I managed to add bind-address = 0.0.0.0 to /etc/mysql/my.cnf although not sure if this is the right place. now it looks like this 

[mysqld]
port = 3306
socket = /var/run/mysql/mysql.sock
skip-locking
key_buffer_size = 16K
max_allowed_packet = 1M
table_open_cache = 4
sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 128K
bind-address = 0.0.0.0

still when I try to connect from MySQL Bench connection is refused. 

then the article talks about granting a certain user on a certain machine to access a certain database. what I want is open it up without any security (not an issue for me) to connect to it freely. manage it ...etc. with MySQL Bench since installing phpMyAdmin is beyond my capabilities without a step by step guide. and then connect from other software to add data to it. 

Any further help is highly appreciated

here is the terminal log:

login as: root
root@enki's password:
COLUMNS=80;LINES=60;export COLUMNS LINES;
[root@Enki]#
[root@Enki]#
[root@Enki]# rcmysqld stop
Stopping mysqld: OK.
[root@Enki]# rm -rf /var/lib/mysql
[root@Enki]# rcmysqld start
Creating MySQL system tables...
Installing MySQL system tables...
200618 10:33:31 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead.
OK
Filling help tables...
200618 10:33:33 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead.

OK


To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:


/usr/bin/mysqladmin -u root password '

new-password'
/usr/bin/mysqladmin -u root -h Enki password 'new-password'

Alternatively you can run:
/usr/bin/mysql_secure_installation


which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.


See the manual for more instructions.


You can start the MySQL daemon with:
cd /usr ; /usr/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd /usr/mysql-test ; perl mysql-test-run.pl

Please report any problems with the /usr/bin/mysqlbug script!


Starting mysqld_safe: OK.

[root@Enki]# rcmysqld status
mysqld running
[root@Enki]# rcmysqld enable
Enabling boot execution of S44mysqld
[root@Enki]# /usr/bin/mysqladmin -u root password 'xxpassxx'
-sh: /usr/bin/mysqladmin: not found
[root@Enki]# /usr/bin/mysqladmin -u root password 'xxpassxx'
[root@Enki]# /usr/bin/mysqladmin -u root -h password 'xxpassxx'
/usr/bin/mysqladmin: connect to server at 'password' failed
error: 'Unknown MySQL server host 'password' (1)'
Check that mysqld is running on password and that the port is 3306.
You can check this by doing 'telnet password 3306'


[root@Enki]# cls
[root@Enki]# rcmysqld stop
Stopping mysqld: OK.
[root@Enki]# rm -rf /var/lib/mysql
[root@Enki]# rcmysqld start
Creating MySQL system tables...
Installing MySQL system tables...
200618 10:39:42 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead.
OK
Filling help tables...
200618 10:39:44 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead.

OK


To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:


/usr/bin/mysqladmin -u root password '

new-password'
/usr/bin/mysqladmin -u root -h Enki password 'new-password'

Alternatively you can run:
/usr/bin/mysql_secure_installation


which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.


See the manual for more instructions.


You can start the MySQL daemon with:
cd /usr ; /usr/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd /usr/mysql-test ; perl mysql-test-run.pl

Please report any problems with the /usr/bin/mysqlbug script!


Starting mysqld_safe: OK.

[root@Enki]# /usr/bin/mysql_secure_installation








NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!




In order to log into MySQL to secure it, we'll need the current
password for the root user.  If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.


Enter current password for root (enter for none):

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

Enter current password for root (enter for none):
OK, successfully used password, moving on...


Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.


Set root password? [Y/n] Y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
 ... Success!




By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.


Remove anonymous users? [Y/n] n
 ... skipping.


Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.


Disallow root login remotely? [Y/n] n
 ... skipping.


By default, MySQL comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.


Remove test database and access to it? [Y/n] n
 ... skipping.


Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.


Reload privilege tables now? [Y/n] Y
 ... Success!


Cleaning up...






All done!  If you've completed all of the above steps, your MySQL
installation should now be secure.


Thanks for using MySQL!


[root@Enki]# rcmysqld start
Starting mysqld_safe: OK.

[Joao Cardoso]

On Sunday, June 21, 2020 at 4:27:03 PM UTC+1, Sarmad Al-Mashta wrote:

First enormous thank you Joao for everything .... 

I followed your instruction, keep in mind I am a windows guy, I know .... so embedded Linux is a real challenge for me... 

I did not get the host name error !!!

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h  password 'new-password'

this did not work so  used :

The first worked for me. 

 

Alternatively you can run:
/usr/bin/mysql_secure_installation

and it worked I was able to set the root password, and login locally .... however I was unable to to log in remotely ... even after executing the host name correction that you suggested 

ln -sf /bin/hostname /usr/bin/hostname

  

I scoured the internet, I found so many different things that I got lost ... things are not in the same place where the articles say the should be. for instance ... this article: https://linuxize.com/post/mysql-remote-access/

The article looks great. As you have read there is several points to take care off, the firewall being one of them.

You don't need to use (I didn't) the bind-address, as the server is already listening to all IPs, as 'netstat -lnp' says:

Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program nam

tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      3940/mysqld

There have been some 30 years since my last SQL usage, but I remembered to use a sqldump to examine the user table current values without going to the details of the database and tables, then I used the SQL statement bellow to add a new user 'rroot' (remote root), allowed to connect from IP 192.168.1.73, and it worked! (kind off, more on that latter)

Notice that the rroot user has the same privileges as the local (sql) root user! And that is not advisable! The third inserted value is some kind of hash of the desired password, the other Y values are grant permissions. 

[root@DNS-320L]# mysql -p
Enter password:
...
mysql> insert into mysql.user values ('192.168.1.73','rroot','*467A21F53C095283DA1E0E990F2BCEDEF6EF8B00','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0);

mysql> flush privileges;

To dump the user table I used:

[root@DNS-320L]# mysqldump mysql user -p
Enter password:
...

INSERT INTO `user` VALUES ('localhost','root'...
...

I could then use from a remote (yoga) host:

jcard@yoga:~/> mysql -urroot -hdns-320l.homenet -p
Enter password:  
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.1.73-log Source distribution
 
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
MySQL [(none)]>

What is the catch? The IP entered in the table is off by one than the real remote host IP. The remote host IP is 192.168.1.74 and I had to enter 192.168.1.73 in the table.

I noticed that when, using the mysql command above at 'yoga', I received the error

ERROR 1130 (HY000): Host '192.168.1.73' is not allowed to connect...

and 'yoga' is 192.168.1.74.

Hope it helps.

[Joao Cardoso]

Here is a script replacement for the mysql_secure_installation that also sets remote logins.

Save it to a file, mysql.txt used, read the comments, edit to the desired password and execute it.

Instruction included in the comments

# setup initial password for localhost ('popo' used as password in this scrip, change it )
#   mysqladmin -u root password 'popo'
#
# or, when no initial password is yet setup, use this file 'mysql.txt'
#   mysql -u root < mysql.txt
# which:
# -setup localhost password
# -delete test database
# -delete guest user
# -allow all remote connections
#
# or, if password is already set ('popo') use instead:
#   mysql -u root -ppopo < mysql.txt
# notice that when using the '-p' option there must be no space to the password

use mysql;

# set password to 'popo'
update user set password=password('popo') where user='root';

# drop test database
drop database if exists test;

# and remove privileges on it

delete from db where db='test' or db='test\\_%';

# delete anonymous user
delete from user where user='';

# delete all allowed connections but localhost
delete from user where user='root' and host not in ('localhost', '127.0.0.1', '::1');

# duplicate root user to accept IP or wildcard (%) remote host connections
# using a IP is off by one, and using a FQDN hostname is not working (because of the IP bug?)
create temporary table temp_table select * from user where user='root' and host='localhost';
#update temp_table set host='yoga.homenet'; # use host name, not working
#update temp_table set host='192.168.1.73'; # use a host with IP 192.168.1.74
update temp_table set host='%'; # allow all remote connections
insert into user select * from temp_table;
drop table temp_table;

flush privileges;

# check from the cmd line after running this script:
#   echo 'select user,host,password from user;' | mysql -ppopo mysql
#
# or from a remote host to the host named dns-320l running the mysqlserver:
#   echo 'select user,host,password from user;' | mysql -u root -h dns-320l -ppopo mysql

 

[Edited: remove removed test database privileges]

[Sarmad Al-Mashta]

Hi Jaoa,

I was trying with first method and couldn't get it to work. I managed to run the above script. 

when I run on Alt-F

echo 'select user,host,password from user;' | mysql -ppopo mysql

I get: 

root    localhost       *68B1DEF0C1AE65E4A8A6C6E3B044F50AC71E30C9

root    127.0.0.1       *68B1DEF0C1AE65E4A8A6C6E3B044F50AC71E30C9

root    %       *68B1DEF0C1AE65E4A8A6C6E3B044F50AC71E30C9

so "'%','root'" has been added. now when I try to connect with MySQL Bench I get a different error :

Failed to connect to MySQL at
myAlt-F-IP:3306 with user root


Unable to connect to myAlt-F-IP:3306

Any Ideas ?

[João Cardoso]

Having "MySQL Bench" you must have a 'mysql' binary installed somewhere, try using it directly from the command line 'mysql -u root -h yourmysqlserverbox -p'.

Or try using telnet directly to rule out firewall or other issues. This only tests if the box is reachable and listening, e.g.

silver:~ # telnet dns-320l 3306
Trying 192.168.1.118...
Connected to dns-320l.
Escape character is '^]'.
8
5.1.73-lo->/nay^H!0VA$-S<pC<Ar

Connection closed by foreign host.

[Joao Cardoso]

The issue must be on your side.

I have installed mysql-workbench on a linux host and I can access and manage the mysql server on the box either by using its host name or its IP (after allowing remote connections,  as per previous posts).

I was suggesting you to use the host IP instead, but you seems to be doing that already.

At this point I believe it to be a reverse DNS issue.

Next is just me thinking:

I have already noticed the "off by one" issue when allowing remote connections from a host when supplying its IP (see above posts)

I noticed also that with the pristine database (build with 'rcmysql stop; rm -rf /var/lib/mysql; rcmysql start'), my box hostname (I use 'dns-320l') is already on the allowed hosts table:

# echo 'select user,host,password from mysql.user' | mysql 
user    host    password
root    localhost
root    dns-320l
root    127.0.0.1
       localhost
       dns-320l

but if I try to do a connection using the host name I get a host not allowed message:

# echo 'select user,host,password from mysql.user' | mysql -h dns-320l 
ERROR 1130 (HY000): Host 'DNS-320L.homenet' is not allowed to connect to this MySQL server

I get the same message when using the host IP:

# echo 'select user,host,password from mysql.user' | mysql -h 192.168.1.118

ERROR 1130 (HY000): Host 'DNS-320L.homenet' is not allowed to connect to this MySQL server

So, some form of reverse host name <-> host IP is done and that might triggers the error. If a reverse lookup on the received IP is done and that lookup fails, the connection fails, even before checking for allowed connections. Just guessing.