mysql

How To Install on GNU/Linux CentOS 7 the LAMP stack (Apache, MySQL, PHP) + phpMyAdmin

 in GNU/Linux  tagged apache / CentOS / centos 7 / GNU / lamp / linux / mysql / php / phpmyadmin by

Following you will find the commands to perform the installation of the LAMP stack (Apache, MySQL, PHP) and phpMyAdmin on a GNU/Linux CentOS 7.
In this guide we kept the information to the bare minimum about what is what.

Installation

To perform the installation, please execute the following as a user with administrative rights:


#Update your system
sudo yum update -y;
#Install Apache, MariaDB (MySQL), PHP (with MySQL support and phpMyAdmin which is a free software tool written in PHP, intended to handle the administration of MySQL over the Web)
sudo yum install httpd mariadb-server mariadb php php-mysql phpmyadmin -y;
#Start Apache (PHP will start with Apache as module)
sudo systemctl start httpd;
#Enable Apache to start on boot
sudo systemctl enable httpd;
#Start MariaDB(MySQL)
sudo systemctl start mariadb;
#Enable MariaDB(MySQL) to start on boot
sudo systemctl enable mariadb;

Configuration of MariaDB (MySQL)

By now, your system should have all necessary packages installed and the services up and running.
Now it is best to configure your MariaDB (MySQL) in a way to disable some test data and test accounts and assign a password to your root user.
Execute the following to do so:


sudo mysql_secure_installation;

This tool will ask you for your current database root password. Since you just installed MariaDB (MySQL) you should not have one, so leave the field empty and press enter.
We recommend to answer with Y (Yes) in all of the security questions of this tool and set a strong password for your root user.


sudo mysql_secure_installation;

Following is the output we got from this tool:

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

In order to log into MariaDB to secure it, we'll need the current
 password for the root user.  If you've just installed MariaDB, 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):
 OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB
 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 MariaDB installation has an anonymous user, allowing anyone
 to log into MariaDB 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] Y
 ... Success!

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] Y
 ... Success!

By default, MariaDB 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] Y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

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 MariaDB
 installation should now be secure.

Thanks for using MariaDB!

Configuration of the firewall

Executing the following will enable access to your web-server from the network for http and https.


sudo firewall-cmd --permanent --zone=public --add-service=http;
sudo firewall-cmd --permanent --zone=public --add-service=https;
sudo firewall-cmd --reload;

Location of files / Where to upload your website

In CentOS 7, the directory where the website pages are located is at /var/www/html/.
You can place your html and php code there.

Configuration of phpMyAdmin

phpMyAdmin should be available at http://<IP of the web server>/phpmyadmin
The access to phpMyAdmin is controlled by the Apache Virtual Host File for phpMyAdmin that is found here /etc/httpd/conf.d/phpMyAdmin.conf.
By default, this configuration file only allows access to phpMyAdmin from the localhost (127.0.0.1).
In case you want to access it from another machine, you need to modify this file.
Before doing any changes, make a backup of the original file first:

 sudo cp /etc/httpd/conf.d/phpMyAdmin.conf /etc/httpd/conf.d/phpMyAdmin.conf.original;

To enable access only for a specific IP

Edit the Apache Virtual Host File for phpMyAdmin (/etc/httpd/conf.d/phpMyAdmin.conf) and change every reference of 127.0.0.1 with the IP address of the machine you want to give access to.

There should be four lines that you need to change.
In our installation it was the following lines:

  1. 17 – Require ip 127.0.0.1
  2. 25 – Allow from 127.0.0.1
  3. 34 – Require ip 127.0.0.1
  4. 42 – Allow from 127.0.0.1

Then restart the Apache service to apply the changes:


sudo systemctl restart httpd;

To enable access to any IP

Delete the existing file and create a new one.


sudo rm /etc/httpd/conf.d/phpMyAdmin.conf;
sudo touch /etc/httpd/conf.d/phpMyAdmin.conf;

Using the editor of your choice add the following content and save it:

Alias /phpMyAdmin /usr/share/phpMyAdmin
Alias /phpmyadmin /usr/share/phpMyAdmin

<Directory /usr/share/phpMyAdmin/>
 AllowOverride None
 Options None
 Allow from All
 Require all granted
</Directory>

Then restart the Apache service to apply the changes:


sudo systemctl restart httpd;

Following is the original content of our Apache Virtual Host File for phpMyAdmin (/etc/httpd/conf.d/phpMyAdmin.conf)

[download id=”3647″]

$ cat /etc/httpd/conf.d/phpMyAdmin.conf
 # phpMyAdmin - Web based MySQL browser written in php
 #
 # Allows only localhost by default
 #
 # But allowing phpMyAdmin to anyone other than localhost should be considered
 # dangerous unless properly secured by SSL

Alias /phpMyAdmin /usr/share/phpMyAdmin
Alias /phpmyadmin /usr/share/phpMyAdmin

<Directory /usr/share/phpMyAdmin/>
 AddDefaultCharset UTF-8

 <IfModule mod_authz_core.c>
  # Apache 2.4
  <RequireAny>
   Require ip 192.168.0.20
   Require ip ::1
  </RequireAny>
 </IfModule>
 <IfModule !mod_authz_core.c>
  # Apache 2.2
  Order Deny,Allow
  Deny from All
  Allow from 192.168.0.20
  Allow from ::1
  </IfModule>
</Directory>

<Directory /usr/share/phpMyAdmin/setup/>
 <IfModule mod_authz_core.c>
  # Apache 2.4
  <RequireAny>
   Require ip 192.168.0.20
   Require ip ::1
  </RequireAny>
 </IfModule>
 <IfModule !mod_authz_core.c>
  # Apache 2.2
  Order Deny,Allow
  Deny from All
  Allow from 192.168.0.20
  Allow from ::1
 </IfModule>
</Directory>

# These directories do not require access over HTTP - taken from the original
# phpMyAdmin upstream tarball
#
 <Directory /usr/share/phpMyAdmin/libraries/>
  Order Deny,Allow
  Deny from All
  Allow from None
 </Directory>

<Directory /usr/share/phpMyAdmin/setup/lib/>
  Order Deny,Allow
  Deny from All
  Allow from None
</Directory>

<Directory /usr/share/phpMyAdmin/setup/frames/>
 Order Deny,Allow
 Deny from All
 Allow from None
</Directory>

# This configuration prevents mod_security at phpMyAdmin directories from
# filtering SQL etc.  This may break your mod_security implementation.
#
#<IfModule mod_security.c>
#    <Directory /usr/share/phpMyAdmin/>
#        SecRuleInheritance Off
#    </Directory>
#</IfModule>

[download id=”3647″]

Ubuntu server 16.04+ MySQL port is only accessible from localhost (127.0.0.1)

 in GNU/Linux  tagged mysql / mysqld / mysqld.cnf / nmap / port / ubuntu by

Recently, we got access to an Ubuntu 16.04 LTS server that had MySQL server installed on it but was not accessible to our external servers.
The service was accessible when testing from localhost but it was not when testing from any other machine.
Executing nmap from another machine would return the value 3306/tcp closed mysql   conn-refused as below.

[bytefreaks@fedora ~]$ nmap -vv -p 3306 192.168.10.11


 
 Starting Nmap 7.40 ( https://nmap.org ) at 2017-03-06 17:21 EET
 Initiating Ping Scan at 17:21
 Scanning 192.168.10.11 [2 ports]
 Completed Ping Scan at 17:21, 0.06s elapsed (1 total hosts)
 Initiating Parallel DNS resolution of 1 host. at 17:21
 Completed Parallel DNS resolution of 1 host. at 17:21, 0.00s elapsed
 Initiating Connect Scan at 17:21
 Scanning 192.168.10.11 [1 port]
 Completed Connect Scan at 17:21, 0.06s elapsed (1 total ports)
 Nmap scan report for 46.101.137.70
 Host is up, received syn-ack (0.061s latency).
 Scanned at 2017-03-06 17:21:31 EET for 1s
 PORT     STATE  SERVICE REASON
 3306/tcp closed mysql   conn-refused
 
 Read data files from: /usr/bin/../share/nmap
 Nmap done: 1 IP address (1 host up) scanned in 0.16 seconds

The problem was with the default configuration of mysqld that is found in the file /etc/mysql/mysql.conf.d/mysqld.cnf.
At line 41 we got the following snippet:

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            = 127.0.0.1

What the line bind-address            = 127.0.0.1 says is that, the service will only listen on localhost.
At this stage there are two solutions that you can apply using your favorite text editor (e.g. sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf):

Solution A:

Completely remove the line bind-address            = 127.0.0.1 or comment it out by adding a # in front of it as follows #bind-address            = 127.0.0.1.

Solution B:

Replace 127.0.0.1 with the IP that you want mysql service to be available to. In our case the line became bind-address            = 192.168.10.11.

After you are done with the change, you need to restart the service for the change to take place:

bytefreaks@OSUbuntu:~$ sudo /etc/init.d/mysql restart
 [ ok ] Restarting mysql (via systemctl): mysql.service.

From an external machine you can verify that the configuration was applied correctly using nmap as below:

[bytefreaks@fedora ~]$ nmap -vv -p 3306 192.168.10.11
 Starting Nmap 7.40 ( https://nmap.org ) at 2017-03-06 17:24 EET
 Initiating Ping Scan at 17:24
 Scanning 192.168.10.11 [2 ports]
 Completed Ping Scan at 17:24, 0.06s elapsed (1 total hosts)
 Initiating Parallel DNS resolution of 1 host. at 17:24
 Completed Parallel DNS resolution of 1 host. at 17:24, 0.00s elapsed
 Initiating Connect Scan at 17:24
 Scanning 192.168.10.11 [1 port]
 Discovered open port 3306/tcp on 46.101.137.70
 Completed Connect Scan at 17:24, 0.06s elapsed (1 total ports)
 Nmap scan report for 46.101.137.70
 Host is up, received syn-ack (0.061s latency).
 Scanned at 2017-03-06 17:24:30 EET for 0s
 PORT     STATE SERVICE REASON
 3306/tcp open  mysql   syn-ack
 Read data files from: /usr/bin/../share/nmap
 Nmap done: 1 IP address (1 host up) scanned in 0.16 seconds

You should get the value 3306/tcp open  mysql   syn-ack.

Convert a list of integers from MySQL to a Bash array

 in Bash  tagged array / bash / mysql / printf by

The following code will connect to a MySQL server, it will get a list of integers and convert the results to a bash array that will be iterated using a for loop and they will be printed using zero padding.


IDS_RAW=$(mysql --user="myuser" --password="mypass" --host="db.example.com" --database="mydb" --batch --compress --skip-column-names --execute="
  SELECT
    Id
  FROM
    Users
  WHERE
    Status = 0;
");

OLDIFS=$IFS;
IFS=$'\n' command eval;
'IDS=($IDS_RAW)';
IFS=$OLDIFS;

echo "Will process the following user IDs '${IDS[@]}'";

for ID in "${IDS[@]}"; do
  LEADING_ZERO=$(printf %08d $ID);
  echo "ID $LEADING_ZERO";
done;

MySQL Workbench: A solution to Error Code 1175 2

 in SQL  tagged mysql / MySQL Workbench / safe mode / Workbench by

While using MySQL Workbench, we tried to execute an UPDATE command that did not use the Key of the table in the WHERE clause. In our case, it did not matter and it was expected to perform that update without that restriction.

When we tried to execute the code though, we got the following error:

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

and the following solution from Workbench:

To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.

We did not want to close the session for many reasons (we had data in other tabs, we would lose the undo history, we would have to revert the change later on, etc.).

To solve this issue without reconnecting nor changing the configuration of the tool, we appended the following code above the update statement and executed both lines together (we selected the two commands and pressed CTRL+Shift+Enter):

SET SQL_SAFE_UPDATES = 0;

This line of code, instructed the tool to ignore Error 1175 and allowed us to complete our task.