2017/08/26

Raspberry Pi - Installation and configuration of MariaDB and phpMyAdmin on Raspbian Jessie Lite

This post is a quick summary of how to install MariaDB on Raspberry Pi running Raspbian as well as on how to perform basic configuration and operation.

The Environment:

- Raspberry Pi 1 Model B
- OS: Raspbian Jessie Lite
- Primary Storage: 16GB Class 10 SD Card

MariaDB Installation

pi@raspberrypi:~ $ sudo apt-get install mariadb-server


Enter the password for the "root" user account for MariaDB (not for Raspbian). You will be prompted to enter the password again to confirm it.


pi@raspberrypi:~ $ sudo apt-get autoremove


pi@raspberrypi:~ $ sudo apt-get install libmysqlclient-dev

pi@raspberrypi:~ $ sudo apt-get install libmysqlclient-dev
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following NEW packages will be installed:
  libmysqlclient-dev
0 upgraded, 1 newly installed, 0 to remove and 1 not upgraded.
Need to get 897 kB of archives.
After this operation, 4,876 kB of additional disk space will be used.
Get:1 http://mirrordirector.raspbian.org/raspbian/ jessie/main libmysqlclient-dev armhf 5.5.57-0+deb8u1 [897 kB]
Fetched 897 kB in 2s (405 kB/s)
Selecting previously unselected package libmysqlclient-dev.
(Reading database ... 31905 files and directories currently installed.)
Preparing to unpack .../libmysqlclient-dev_5.5.57-0+deb8u1_armhf.deb ...
Unpacking libmysqlclient-dev (5.5.57-0+deb8u1) ...
Processing triggers for man-db (2.7.5-1~bpo8+1) ...
Setting up libmysqlclient-dev (5.5.57-0+deb8u1) ...

pi@raspberrypi:~ $ sudo apt-get install libqt5sql5-mysql

pi@raspberrypi:~ $ sudo apt-get install libqt5sql5-mysql
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following extra packages will be installed:
  libqt5core5a libqt5sql5 qttranslations5-l10n
Suggested packages:
  libthai0
The following NEW packages will be installed:
  libqt5core5a libqt5sql5 libqt5sql5-mysql qttranslations5-l10n
0 upgraded, 4 newly installed, 0 to remove and 1 not upgraded.
Need to get 2,855 kB of archives.
After this operation, 14.2 MB of additional disk space will be used.
Do you want to continue? [Y/n] y
Get:1 http://mirrordirector.raspbian.org/raspbian/ jessie/main libqt5core5a armhf 5.3.2+dfsg-4+rpi1 [1,638 kB]
Get:2 http://mirrordirector.raspbian.org/raspbian/ jessie/main libqt5sql5 armhf 5.3.2+dfsg-4+rpi1 [97.2 kB]
Get:3 http://mirrordirector.raspbian.org/raspbian/ jessie/main libqt5sql5-mysql armhf 5.3.2+dfsg-4+rpi1 [43.4 kB]
Get:4 http://mirrordirector.raspbian.org/raspbian/ jessie/main qttranslations5-l10n all 5.3.2-2 [1,077 kB]
Fetched 2,855 kB in 1min 1s (46.6 kB/s)
Selecting previously unselected package libqt5core5a:armhf.
(Reading database ... 31974 files and directories currently installed.)
Preparing to unpack .../libqt5core5a_5.3.2+dfsg-4+rpi1_armhf.deb ...
Unpacking libqt5core5a:armhf (5.3.2+dfsg-4+rpi1) ...
Selecting previously unselected package libqt5sql5:armhf.
Preparing to unpack .../libqt5sql5_5.3.2+dfsg-4+rpi1_armhf.deb ...
Unpacking libqt5sql5:armhf (5.3.2+dfsg-4+rpi1) ...
Selecting previously unselected package libqt5sql5-mysql:armhf.
Preparing to unpack .../libqt5sql5-mysql_5.3.2+dfsg-4+rpi1_armhf.deb ...
Unpacking libqt5sql5-mysql:armhf (5.3.2+dfsg-4+rpi1) ...
Selecting previously unselected package qttranslations5-l10n.
Preparing to unpack .../qttranslations5-l10n_5.3.2-2_all.deb ...
Unpacking qttranslations5-l10n (5.3.2-2) ...
Setting up libqt5core5a:armhf (5.3.2+dfsg-4+rpi1) ...
Setting up libqt5sql5:armhf (5.3.2+dfsg-4+rpi1) ...
Setting up libqt5sql5-mysql:armhf (5.3.2+dfsg-4+rpi1) ...
Setting up qttranslations5-l10n (5.3.2-2) ...
Processing triggers for libc-bin (2.19-18+deb8u10) ...

MariaDB Basic Configuration

IMPORTANT!! BE SURE TO CARRY OUT THE ACTIONS IN THIS SECTION TO IMPROVE THE SECURITY OF THE DATABASE.

pi@raspberrypi:~ $ mysql_secure_installation

Enter the password for the "root" user account which was entered in step-1 of the installation procedure.


Unless a new password is needed, enter "n" to skip the setting of a new password.


Enter "y" to delete the anonymous user account.


Normally, the answer is "y" to the question. But as I want to do something experiment, I enter "n" to allow root remote connection.

NOTE, FOR PRODUCTION VERSION, BE SURE TO CHANGE IT BACK TO "Y" TO DISALLOW ROOT REMOTE LOGIN!!


Enter "y" to delete the test database.


The privilege tables contain info. such as user accounts, passwords, privileges, etc. Enter "y" to reload them for the changes to take effect..


MariaDB Basic Operation

Launch MariaDB monitor

pi@raspberrypi:~ $ mysql -u root -p


Type "help" for a list of available built in commands. To exit or quit MariaDB, type "quit" or "\q".


Alternatively, mysqladmin can be used to manage MariaDB.

pi@raspberrypi:~ $ mysqladmin

pi@raspberrypi:~ $ mysqladmin
mysqladmin  Ver 9.1 Distrib 10.0.32-MariaDB, for debian-linux-gnueabihf on armv8l
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Administration program for the mysqld daemon.
Usage: mysqladmin [OPTIONS] command command....

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf
The following groups are read: mysqladmin client client-server client-mariadb
The following options may be given as the first argument:
--print-defaults        Print the program argument list and exit.
--no-defaults           Don't read default options from any option file.
--defaults-file=#       Only read default options from the given file #.
--defaults-extra-file=# Read this file after the global files are read.

  -c, --count=#       Number of iterations to make. This works with -i
                      (--sleep) only.
  --debug-check       Check memory and open file usage at exit.
  --debug-info        Print some debug info at exit.
  -f, --force         Don't ask for confirmation on drop database; with
                      multiple commands, continue even if an error occurs.
  -C, --compress      Use compression in server/client protocol.
  --character-sets-dir=name
                      Directory for character set files.
  --default-character-set=name
                      Set the default character set.
  -?, --help          Display this help and exit.
  -h, --host=name     Connect to host.
  -l, --local         Local command, don't write to binlog.
  -b, --no-beep       Turn off beep on error.
  -p, --password[=name]
                      Password to use when connecting to server. If password is
                      not given it's asked from the tty.
  -P, --port=#        Port number to use for connection or 0 for default to, in
                      order of preference, my.cnf, $MYSQL_TCP_PORT,
                      /etc/services, built-in default (3306).
  --protocol=name     The protocol to use for connection (tcp, socket, pipe,
                      memory).
  -r, --relative      Show difference between current and previous values when
                      used with -i. Currently only works with extended-status.
  -s, --silent        Silently exit if one can't connect to server.
  -S, --socket=name   The socket file to use for connection.
  -i, --sleep=#       Execute commands repeatedly with a sleep between.
  --ssl               Enable SSL for connection (automatically enabled with
                      other flags).
  --ssl-ca=name       CA file in PEM format (check OpenSSL docs, implies
                      --ssl).
  --ssl-capath=name   CA directory (check OpenSSL docs, implies --ssl).
  --ssl-cert=name     X509 cert in PEM format (implies --ssl).
  --ssl-cipher=name   SSL cipher to use (implies --ssl).
  --ssl-key=name      X509 key in PEM format (implies --ssl).
  --ssl-crl=name      Certificate revocation list (implies --ssl).
  --ssl-crlpath=name  Certificate revocation list path (implies --ssl).
  --ssl-verify-server-cert
                      Verify server's "Common Name" in its cert against
                      hostname used when connecting. This option is disabled by
                      default.
  -u, --user=name     User for login if not current user.
  -v, --verbose       Write more information.
  -V, --version       Output version information and exit.
  -E, --vertical      Print output vertically. Is similar to --relative, but
                      prints output vertically.
  -w, --wait[=#]      Wait and retry if connection is down.
  --connect-timeout=#
  --shutdown-timeout=#
  --plugin-dir=name   Directory for client-side plugins.
  --default-auth=name Default authentication client-side plugin to use.

Variables (--variable-name=value)
and boolean options {FALSE|TRUE}  Value (after reading options)
--------------------------------- ----------------------------------------
count                             0
debug-check                       FALSE
debug-info                        FALSE
force                             FALSE
compress                          FALSE
character-sets-dir                (No default value)
default-character-set             auto
host                              (No default value)
local                             FALSE
no-beep                           FALSE
port                              3306
relative                          FALSE
socket                            /var/run/mysqld/mysqld.sock
sleep                             0
ssl                               FALSE
ssl-ca                            (No default value)
ssl-capath                        (No default value)
ssl-cert                          (No default value)
ssl-cipher                        (No default value)
ssl-key                           (No default value)
ssl-crl                           (No default value)
ssl-crlpath                       (No default value)
ssl-verify-server-cert            FALSE
user                              (No default value)
verbose                           FALSE
vertical                          FALSE
connect-timeout                   43200
shutdown-timeout                  3600
plugin-dir                        (No default value)
default-auth                      (No default value)

Where command is a one or more of: (Commands may be shortened)
  create databasename     Create a new database
  debug                   Instruct server to write debug information to log
  drop databasename       Delete a database and all its tables
  extended-status         Gives an extended status message from the server
  flush-all-statistics    Flush all statistics tables
  flush-all-status        Flush status and statistics
  flush-client-statistics Flush client statistics
  flush-hosts             Flush all cached hosts
  flush-index-statistics  Flush index statistics
  flush-logs              Flush all logs
  flush-privileges        Reload grant tables (same as reload)
  flush-binary-log        Flush binary log
  flush-engine-log        Flush engine log(s)
  flush-error-log         Flush error log
  flush-general-log       Flush general log
  flush-relay-log         Flush relay log
  flush-slow-log          Flush slow query log
  flush-status            Clear status variables
  flush-table-statistics  Clear table statistics
  flush-tables            Flush all tables
  flush-threads           Flush the thread cache
  flush-user-statistics   Flush user statistics
  flush-user-resources    Flush user resources
  kill id,id,...        Kill mysql threads
  password [new-password] Change old password to new-password in current format
  old-password [new-password] Change old password to new-password in old format
  ping                  Check if mysqld is alive
  processlist           Show list of active threads in server
  reload                Reload grant tables
  refresh               Flush all tables and close and open logfiles
  shutdown              Take server down
  status                Gives a short status message from the server
  start-slave           Start slave
  stop-slave            Stop slave
  variables             Prints variables available
  version               Get version info from server

Note,

If MariaDB is placed behind firewall, be sure to to open port 3306 to allow users / applications to access the database.

pi@raspberrypi:~ $ sudo iptables -A INPUT -p TCP -i eth0 --dport 3306 --sport 1024:65534 -j ACCEPT

Below is the iptables before and after executing the above command.

pi@raspberrypi:~ $ sudo iptables -L


In case the Pi is having trouble executing the iptables command (especially on a machine running newly installed OS or machine with OS hasn't been updated for a long time), please refer to the below section on how to upgrade the OS.

--------------------------------------------------------------------------------------------------------------------------
Troubleshooting problem running iptables

The Problem:

Running  sudo iptables -L  results in error.


The Solution: Upgrade Raspbian

Check current version

uname -a


See a list of upgradable packages

pi@raspberrypi:~ $ sudo apt list --upgradable


Perform the upgrade

pi@raspberrypi:~ $ sudo apt upgrade


Note, plymouth is the startup logo package for Raspbian.


Check again the current version after the upgrade.

uname -a


Reboot the system then check again the version info.

sudo reboot

uname -a



The version has been updated!!

Try running "sudo iptables -L" again. This time, there is no error!!


Reference:

iptables: nat table does not exist
https://raspberrypi.stackexchange.com/questions/51276/iptables-nat-table-does-not-exist

--------------------------------------------------------------------------------------------------------------------------

phpMyAdmin Installation

pi@raspberrypi:~ $ sudo apt-get install phpmyadmin

Select "y" to continue the installation.


Select apache2 (note, use up / down arrow key to select, use space key to confirm, use tab key to switch between the 2 options and OK).


Select "Yes".


Enter the password for the MariaDB.


Enter the password that you want to use for phpmyadmin.


Re-enter the password to confirm it.


phpMyAdmin Configuration

Now, we need to combine apache with phpMyAdmin. For that, you should edit the apache2.conf file. For that, type the following in the terminal:

pi@raspberrypi:~ $ sudo nano /etc/apache2/apache2.conf

At the end of this file (navigate using Ctrl+V), add a line:

Include /etc/phpmyadmin/apache.conf


Use Ctrl + X to save the change and exit nano.

Now restart the server using:

sudo /etc/init.d/apache2 restart


Now, check out the link in your web browser: <pi’s ip address>/phpmyadmin or localhost/phpmyadmin in your Pi. You should see the page below.


IMPORTANT!! - The login info. (Username and Password) needed is for MariaDB, not for phpMyAdmin so you need to enter the Username and Password for MariaDB..

Once the correct Username and Password are entered, the below screen will appear.


Note,

On the bottom of the above screen there is this message "Your PHP MySQL library version 5.5.57 differs from your MySQL server version 10.0.32. This may cause unpredictable behavior."

To solve this problem, issue the 2 commands below in the terminal then restart apache2 and mysql (or simply do a reboot of the system).

pi@raspberrypi:~ $ sudo apt-get update

pi@raspberrypi:~ $ sudo apt-get install php5-mysqlnd

The warning message disappears after the update is done and a reboot is performed.


References:

ติดตั้ง MariaDB (MySQL) Database on Raspberry Pi ตอนที่ 1
https://youtu.be/BZO1M7SjmBY

Raspberry Pi 的實作 - MySQL / MariaDB 資料庫的安裝與基本設定
http://blog.itist.tw/2015/02/raspberry-pi-install-mysql-or-mariadb.html

***How to Make a Raspberry Pi Web Server***
https://diyhacking.com/raspberry-pi-web-server/

Your PHP MySQL library version 5.5.50 differs from your MySQL server version 5.6.31. This may cause unpredictable behavior.
https://www.digitalocean.com/community/questions/your-php-mysql-library-version-5-5-50-differs-from-your-mysql-server-version-5-6-31-this-may-cause-unpredictable-behavior

--------------------------------------------------------------------------------------------------------------------------

MySQL修改 root 密碼必勝方法
https://blog.toright.com/posts/1145/mysql-%E6%94%B9-root-%E5%AF%86%E7%A2%BC%E5%BF%85%E5%8B%9D%E6%96%B9%E6%B3%95%EF%BC%88%E7%AD%86%E8%A8%98%EF%BC%89.html

忽然忘記 MySQL root 密碼,或者 CentOS 剛裝好的時候在 root 密碼尚未設定的狀態下都可以透過以下方法來修改 root 密碼。

MySQL 無法使用 root 連線,先來看看發生什麼事情:

[root@centos ~]# mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[root@centos ~]# mysqladmin -u root -p password
Enter password:
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: NO)'
[root@centos ~]#

既然 mysqladmin 也改不了密碼了,那我們只好重新啟動MySQL並且進入安全模式,這時候就可以不用 root 密碼登入了。

[root@centos ~]# service mysqld stop
正在停止 MySQL:                                            [  確定  ]
[root@centos ~]# mysqld_safe --user=mysql --skip-grant-tables --skip-networking &
[1] 27349
Starting mysqld daemon with databases from /var/lib/mysql
[root@centos ~]# mysql -u root mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.77 Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> UPDATE user SET Password=PASSWORD('iamroot') where USER='root';
Query OK, 3 rows affected (0.02 sec)
Rows matched: 3  Changed: 3  Warnings: 0
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> quit
Bye
[root@centos ~]#

重新將 MySQL啟動在正常模式,並且測試新的 root 密碼是否能夠使用。

[root@centos ~]# service mysqld restart
STOPPING server from pid file /var/run/mysqld/mysqld.pid
101220 16:58:31  mysqld ended
正在停止 MySQL:                                            [  確定  ]
正在啟動 MySQL:                                            [  確定  ]
[1]+  Done                    mysqld_safe --user=mysql --skip-grant-tables --skip-networking
[root@centos ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.77 Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>

搞定。
----------------------------------------------------------------------------------------------------------------------------------

ติดตั้ง PhpMyAdmin บน Raspberry Pi ตอนที่ #1
https://youtu.be/tkeMgUFTAaE

No comments:

Post a Comment