running a mysql server on Napp It

zos

Member
Oct 27, 2013
40
20
8
/opt/local/share/doc/mysql
/opt/local/share/mysql
/opt/local/pkg/mysql-server-5.6.23nb1
/opt/local/pkg/mysql-client-5.6.23
/opt/local/pkg.refcount/users/mysql
/opt/local/pkg.refcount/groups/mysql
/opt/local/lib/mysql
/opt/local/include/mysql
/opt/local/include/mysql/mysql
/opt/local/include/php/ext/mysqlnd
/var/log/mysql
/var/mysql.org
/var/mysql.org/mysql
I suppose your mysql-data-dir is /var/mysql.org.

So let's try if I'm right, please enter:

cp -R /var/mysql.org /var/mysql
svcadm clear mysql
svcadm enable mysql

If mysql is online now, it should work now and you're done.
 
  • Like
Reactions: T_Minus

epicurean

Active Member
Sep 29, 2014
641
34
28
I did as you said Zos. Still mysql seen as in maintenance state,although I can access phpmyadmin (still dunno the password)
 

zos

Member
Oct 27, 2013
40
20
8
I did as you said Zos. Still mysql seen as in maintenance state,although I can access phpmyadmin (still dunno the password)
Hi epicurean,

let's finally try to create a clean mysql-data-directory, please enter:

rm -rf /var/mysql
mkdir /var/mysql
chown -R mysql:mysql /var/mysql
mysql_install_db --user=mysql
svcadm clear mysql
svcadm enable mysql

Have a look if mysql is still in maintenance state. If yes, I suppose only reinstalling amp would help you now.

But if mysql is online, try accessing phpmyadmin again using user "root" and an empty passoword. I guess you'll get an error message because an empty password is not allowed.

If I'm right, enter:
/opt/local/bin/mysql_secure_installation

You'll be asked if you want to set a new root-password now. Enter "y" and just press <Enter> for the old password and enter a new password of your choice (and keep it in mind).

You'll be asked some more questions, like:
Do you want to remove root accounts accessable from outsite localhost? no (for our test purpose not)
Do you want to remove anonymous-user accounts? yes
Do you want to remove the test database? yes

Now try again accessing phpmyadmin, user "root" with the password you've kept in mind.
 

epicurean

Active Member
Sep 29, 2014
641
34
28
Thanks again Zos for responding so quickly.
Unfortunately the mysql is still in maintenance state. Can you guide me to how to do a complete clean mysql installation again?
 

zos

Member
Oct 27, 2013
40
20
8
Thanks again Zos for responding so quickly.
Unfortunately the mysql is still in maintenance state. Can you guide me to how to do a complete clean mysql installation again?
Before we reinstall amp, please post the output of the following commands:

List logfile of mysql:
cat /var/svc/log/pkgsrc-mysql:default.log

List datadir of mysql:
ls -al /var/mysql

List BootEnvironments:
beadm list
 
  • Like
Reactions: T_Minus

epicurean

Active Member
Sep 29, 2014
641
34
28
Hi Zos,
out put from list logfile is very long and attached as txt file

2nd list datadir is:

Monitor-Extension: Command Log interface.pl 894# ls -al /var/mysql
p1
main, /_lib/interface.pl, line 898
exe: ls -al /var/mysql
total 110615
drwxr-xr-x 2 mysql 6 Jul 15 01:17 .
drwxr-xr-x 37 root 37 Jul 15 01:16 ..
-rw-rw---- 1 mysql 56 Jul 15 01:17 auto.cnf
-rw-rw---- 1 mysql 50331648 Jul 15 01:17 ib_logfile0
-rw-rw---- 1 mysql 50331648 Jul 15 01:17 ib_logfile1
-rw-rw---- 1 mysql 12582912 Jul 15 01:17 ibdata1
 

Attachments

epicurean

Active Member
Sep 29, 2014
641
34
28
3rd beadm list:
Monitor-Extension: Command Log interface.pl 894# beadm list
p1
main, /_lib/interface.pl, line 898
exe: beadm list
BE Active Mountpoint Space Policy Created
initial_backup - - 342M static 2014-12-19 21:01
omnios-1 NR / 14.6G static 2014-12-19 20:35
pre_amp_setup_1436407558 - - 39.0K static 2015-07-09 02:05
pre_phpmyadmin_setup_1436587608 - - 70.0K static 2015-07-11 04:06
pre_phpmyadmin_setup_1436587792 - - 70.0K static 2015-07-11 04:09
 

zos

Member
Oct 27, 2013
40
20
8
Hi Zos,
out put from list logfile is very long and attached as txt file

2nd list datadir is:

Monitor-Extension: Command Log interface.pl 894# ls -al /var/mysql
p1
main, /_lib/interface.pl, line 898
exe: ls -al /var/mysql
total 110615
drwxr-xr-x 2 mysql 6 Jul 15 01:17 .
drwxr-xr-x 37 root 37 Jul 15 01:16 ..
-rw-rw---- 1 mysql 56 Jul 15 01:17 auto.cnf
-rw-rw---- 1 mysql 50331648 Jul 15 01:17 ib_logfile0
-rw-rw---- 1 mysql 50331648 Jul 15 01:17 ib_logfile1
-rw-rw---- 1 mysql 12582912 Jul 15 01:17 ibdata1
Interesting, the mysql-datadir looks fine and the mysql-service seems to be OK too (entry "[ Jul 15 01:17:15 Method "start" exited with status 0. ]").

The reason for the maintenance state seems to be a restart which executes too fast (entry "[ Jul 15 01:17:16 Restarting too quickly, changing state to maintenance. ]").

So one last try, enter:
svcadm clear mysql
svcadm enable mysql

Finally post the response of
svcs mysql

Is it still in maintenance?
 
  • Like
Reactions: T_Minus

epicurean

Active Member
Sep 29, 2014
641
34
28
Hi Zos,
Unfortunately I think it is:

Monitor-Extension: Command Log interface.pl 894# svcs mysql
p1
main, /_lib/interface.pl, line 898
exe: svcs mysql
STATE STIME FMRI
maintenance 7:49:39 svc:/pkgsrc/mysql:default

what could be the reason?
 

zos

Member
Oct 27, 2013
40
20
8
Hi epicurean,

so I think now it's time to try reinstalling amp.

To do so, enter:
beadm activate pre_amp_setup_1436407558
reboot

After rebooting has completed and your logged in as root again, enter
wget -O - www.napp-it.org/amp | perl

Take a coffee, this will take a while as you already know...

After the script has finished, first check if mysql is online now.

If it's online, go on with (I wrote it already in an earlier post):
/opt/local/bin/mysql_secure_installation

You'll be asked if you want to set a new root-password now. Enter "y" and just press <Enter> for the old password and enter a new password of your choice (and keep it in mind).

You'll be asked some more questions, like:
Do you want to remove root accounts accessable from outsite localhost? no (for our test purpose not)
Do you want to remove anonymous-user accounts? yes
Do you want to remove the test database? yes

Now try again accessing phpmyadmin, user "root" with the password you've kept in mind.

Finally I'm wishing that I may congratulate you in my next post :)
 
  • Like
Reactions: T_Minus

epicurean

Active Member
Sep 29, 2014
641
34
28
Hi Zos,

after checking that mysql is online, I type in " /opt/local/bin/mysql_secure_installation" and got these error messages

Can't find a "mysql" client in PATH or in ./bin
Cleaning up...
warning: could not unlink .my.cnf.6613: no such file or directory
warning: could not unlink. myql.6613: no such file or directory
 

zos

Member
Oct 27, 2013
40
20
8
Hi Zos,

after checking that mysql is online, I type in " /opt/local/bin/mysql_secure_installation" and got these error messages

Can't find a "mysql" client in PATH or in ./bin
Cleaning up...
warning: could not unlink .my.cnf.6613: no such file or directory
warning: could not unlink. myql.6613: no such file or directory
Hi epicurean,

fine, mysql is online, I think you'll see phpmyadmin if you enter
http://<your-server-ip>/phpmyadmin. The rest coudn't be so difficult.

First enter:
export PATH=/opt/local/bin:/opt/local/sbin:/usr/gnu/bin:/usr/bin:/usr/sbin:/sbin

Please post output of
find / -name mysql_secure_installation
 

zos

Member
Oct 27, 2013
40
20
8
Hi Zos,

after checking that mysql is online, I type in " /opt/local/bin/mysql_secure_installation" and got these error messages

Can't find a "mysql" client in PATH or in ./bin
Cleaning up...
warning: could not unlink .my.cnf.6613: no such file or directory
warning: could not unlink. myql.6613: no such file or directory
Hi epicurean,

fine, mysql is online, I think you'll see phpmyadmin if you enter
http://<your-server-ip>/phpmyadmin. The rest coudn't be so difficult.

First enter:
export PATH=/opt/local/bin:/opt/local/sbin:/usr/gnu/bin:/usr/bin:/usr/sbin:/sbin

Please post output of
find / -name mysql_secure_installation
 
  • Like
Reactions: T_Minus

epicurean

Active Member
Sep 29, 2014
641
34
28
Hi epicurean,

fine, mysql is online, I think you'll see phpmyadmin if you enter
http://<your-server-ip>/phpmyadmin. The rest coudn't be so difficult.

First enter:
export PATH=/opt/local/bin:/opt/local/sbin:/usr/gnu/bin:/usr/bin:/usr/sbin:/sbin

Please post output of
find / -name mysql_secure_installation
Hi Zos,
The ouput is:

Monitor-Extension: Command Log interface.pl 894# find / -name mysql_secure_installation
p1
main, /_lib/interface.pl, line 898
exe: find / -name mysql_secure_installation
/opt/local/bin/mysql_secure_installation


Is that ok? what should I do next?
 

zos

Member
Oct 27, 2013
40
20
8
Hi epicurean,

just try these commands:
cd ~
./mysqlsec.sh

and answer the questions like described some posts ago.
(./mysqlsec.sh should call /opt/local/bin/mysql_secure_installation)

Try again phpmyadmin...
 
  • Like
Reactions: T_Minus

epicurean

Active Member
Sep 29, 2014
641
34
28
HI Zos,

I got this output after typing in ./mysqlsec.sh

Monitor-Extension: Command Log interface.pl 894# ./mysqlsec.sh
p1
main, /_lib/interface.pl, line 898
exe: ./mysqlsec.sh
sudo: ./mysqlsec.sh: command not found
 

zos

Member
Oct 27, 2013
40
20
8
HI Zos,

I got this output after typing in ./mysqlsec.sh

Monitor-Extension: Command Log interface.pl 894# ./mysqlsec.sh
p1
main, /_lib/interface.pl, line 898
exe: ./mysqlsec.sh
sudo: ./mysqlsec.sh: command not found
Now I realize how you're typing in the commands...

You're not using a native console, you're using napp-it cmd, that's the reason why /opt/local/bin/mysql_secure_installation fails....

OK, if you're not comfortable with the console, we try to enter phpmyadmin with an empty passsword. For this, we need a prepared config-file for phpmyadmin. I 've prepared such a config file, which you can download, please enter:
wget www.wp10455695.server-he.de/config.inc.php
mv config.inc.php /opt/local/etc/phpmyadmin

Now try again
http://<your-server-ip>/phpmyadmin

Enter as user: root
Enter no password, just klick the <OK>-Button.

Voilá, I think, now you're done!

If you'ld like to set a new password instead of an empty one, click under "General settings" / "Change password".
 
  • Like
Reactions: T_Minus

zos

Member
Oct 27, 2013
40
20
8
A few remarks about MySQL and ZFS

If you‘ve installed MySQL using the SmartOS-repo (like the amp-script does) the default data-directory of MySQL is „/var/mysql“. It is a regular directory on a ZFS-filesystem (usually rpool).

For SOHO requirements it‘s perfectly good and normally there’s no need to change it, but there are some options to improve this configuration (i. e. modifying the blocksize, MySQL uses 16k, ZFS uses 128k per default).

I found this article and this presentation about best practises for using MySQL with ZFS. In general these guides are for professional use, therefore not every advice is an option for SOHO systems. But there are 3 modifications left, which IMHO are always useful for SOHO systems, even for the smallest one:

(1) Create ZFS-filesystems for MySQL
(2) Set primarycache = metadata
(3) Skip InnoDB double write

What I did:

(1) Create BootEnvironment
(2) Stop MySQL daemon
(3) Move MySQL original data-directory
(4) Create two ZFS-filesystems with different blocksizes (for InnoDB and Logs)
(5) Set user privilegs
(6) Copy and edit MySQL config-file

Code:
Code:
beadm create pre_mysql_mod
mv /var/mysql /var/mysql.org
zfs create -o recordsize=16k -o primarycache=metadata -o mountpoint=/var/mysql rpool/export/home/mysql
zfs create -o recordsize=128k rpool/export/home/mysql/.iblogs
chown -R mysql:mysql /var/mysql
cp /opt/local/my.cnf /opt/local/etc
Creating „iblogs“ as hidden („.iblogs“) prevents the directory to be shown (incorrectly as a database) in phpMyAdmin. Next I had to edit the MySQL config-file „/opt/local/etc/my.cnf“ to tell MySQL where to find needed directories:
Code:
basedir = /opt/local
datadir = /var/mysql
innodb_data_home_dir = /var/mysql
innodb_log_group_home_dir = /var/mysql/.iblogs
skip-innodb_doublewrite
Finally I initialized the new created MySQL directories (which are ZFS-filessystems now), started the MySQL daemon and reperformed mysql_secure_installation:
Code:
mysql_install_db --user=mysql
svcadm enable mysql
/opt/local/bin/mysql_secure_installation
 

epicurean

Active Member
Sep 29, 2014
641
34
28
Hi Zos,
Can you explain to a noob like me what your recommended actions actually do to improve a SOHO mysql setup like mine?