mysql


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

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. 17Require ip 127.0.0.1
  2. 25Allow from 127.0.0.1
  3. 34Require ip 127.0.0.1
  4. 42Allow 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)

phpMyAdmin.conf (compressed) (4 downloads)

$ 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>

phpMyAdmin.conf (compressed) (4 downloads)


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

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.

[[email protected] ~]$ 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:

[email protected]:~$ 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:

[[email protected] ~]$ 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

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

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.


WordPress: Google AdSense Plugin 3

Recently, we were trying to changing the position of the Google Ads that are shown on a website using the Google AdSense plugin for WordPress.

After clicking on the Manage Ads button at the configuration page (http://example.com/wp-admin/options-general.php?page=GooglePublisherPlugin) we would get an error that another theme had set the active ads and that the plugin cannot proceed to modify them unless they are disabled from the other theme (Specifically we would get the following error: Due to a theme change, ads are not being shown. Please reconfigure your ads or restore the previous theme.).

(a) Disabling the plugin, (b) Uninstalling the plugin (c) Reconnecting with another AdSense account did not help at all.

What we did at the end was to remove the GooglePublisherPlugin entry from the wp_option database of our installation.

DELETE FROM `MyDatabase`.`wp_options` WHERE `option_name` = "GooglePublisherPlugin";

Next time we visited the configuration page, we were disconnected from the previous account and we had to reconnect.

GetStarted-AdSense
After reconnecting, we followed the configuration procedure, we pressed the Manage Ads button, the website was analyzed, we got the confirmation message and we clicked on the Save & Activate button to store the changes.

For a few hours, no ads were shown on the site. We did not take any action and on the next day that ads started appearing as expected.


Copy all databases to another host

The following command will use mysqldump to create a dump of all available databases in the OLD_HOST that are available for the user OLD_USER.
The results will be imported to another server via the mysql pipe.

OLD_USER="myUser"; OLD_PASS="myPASS"; OLD_HOST="myHost";
NEW_USER="myUserNEW"; NEW_PASS="myPASSNEW"; NEW_HOST="myHostNEW";
mysqldump -u "$OLD_USER" -p"$OLD_PASS" -h "$OLD_HOST" --all-databases | mysql -h "$NEW_HOST" -u "$NEW_USER" -p"$NEW_PASS";

The user must have the LOCK TABLES privilege for the above command to work or else you will get the following error.

mysqldump: Got error: 1044: "Access denied for user 'OLD_USER'@'OLD_HOST' to database 'DBNAME'" when using LOCK TABLES

In case you cannot give the privilege to the user, then use the parameter --single-transaction to mitigate the problem. The command changes as follows.

OLD_USER="myUser"; OLD_PASS="myPASS"; OLD_HOST="myHost";
NEW_USER="myUserNEW"; NEW_PASS="myPASSNEW"; NEW_HOST="myHostNEW";
mysqldump -u "$OLD_USER" -p"$OLD_PASS" -h "$OLD_HOST" --single-transaction --all-databases | mysql -h "$NEW_HOST" -u "$NEW_USER" -p"$NEW_PASS";

In case you want to copy only specific databases, use the following

OLD_USER="myUser"; OLD_PASS="myPASS"; OLD_HOST="myHost"; OLD_DBS=("DB1" "DB2");
NEW_USER="myUserNEW"; NEW_PASS="myPASSNEW"; NEW_HOST="myHostNEW";
mysqldump -u "$OLD_USER" -p"$OLD_PASS" -h "$OLD_HOST" "${OLD_DBS[@]}" | mysql -h "$NEW_HOST" -u "$NEW_USER" -p"$NEW_PASS";

In case you want to copy only specific tables from a database, use the following

OLD_USER="myUser"; OLD_PASS="myPASS"; OLD_HOST="myHost"; OLD_DB="DB1"; OLD_TABLES=("TBL1" "TBL2");
NEW_USER="myUserNEW"; NEW_PASS="myPASSNEW"; NEW_HOST="myHostNEW"; NEW_DB="NewDB";
mysqldump -u "$OLD_USER" -p"$OLD_PASS" -h "$OLD_HOST" "$OLD_DB" "${OLD_TABLES[@]}" | mysql -h "$NEW_HOST" -u "$NEW_USER" -p"$NEW_PASS" "$NEW_DB";

Delete a paste from a private hosted Stikked installation

To delete a paste from a Stikked installation that you have database access do the following:

Visit the post you like to delete: e.g http://example.com/stikked/htdocs/view/267c4d64
From this URI we need to get the part after view which is the pid (paste ID) of the paste you want to delete. In this case we get 267c4d64.

Then, you need to login to your MySQL DB. In case you are connecting via a terminal you would be using a similar command to this:

mysql -D MY_DB -h MY_HOST -u MY_USER -p

Replace MY_DB, MY_HOST and MY_USER with your credentials, afterwards you will be prompted for you password (if any).

Once in the MySQL console, issue the following:

DELETE FROM `pastes` WHERE `pid` = "267c4d64";

The above line will find the paste we want to delete and perform the deletion.

If you want to see the structure of the pastes table issue the following: SHOW COLUMNS FROM `pastes`;

Type exit to exit the MySQL console.


mysqldump: Got error: 1044: Access denied for user ‘username’@’ip’ to database ‘db’ when doing LOCK TABLES

When trying to issue the command

mysqldump -h "mysql.example.com" -u "username" -p"password" db table_A table_B

to get the MySQL dump for two tables (table_A and table_B), you will get the error

mysqldump: Got error: 1044: Access denied for user 'username'@'ip' to database 'db' when doing LOCK TABLES

when your account does not have the ‘LOCK TABLES’ right.

The ‘LOCK TABLES’ right gives you access to the command LOCK that can be used to emulate transactions or to get more speed when updating tables by preventing other sessions from modifying the locked tables.

To mitigate the problem, use the --single-transaction option as follows

mysqldump -h "mysql.example.com" -u "username" -p"password" --single-transaction db table_A table_B

 

Documentation:

·   --single-transaction

This option sets the transaction isolation mode to REPEATABLE READ and sends a START TRANSACTION SQL statement to the server before dumping data. It is useful only with transactional tables such as InnoDB, because then it dumps the consistent state of the database at the time when START TRANSACTION was issued without blocking any applications.

When using this option, you should keep in mind that only InnoDB tables are dumped in a consistent state. For example, any MyISAM or MEMORY tables dumped while using this option may still change state.

While a --single-transaction dump is in process, to ensure a valid dump file (correct table contents and binary log coordinates), no other connection should use the following statements: ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE. A consistent read is not isolated from those statements, so use of them on a table to be dumped can cause the SELECT that is performed by mysqldump to retrieve the table contents to obtain incorrect contents or fail.

The --single-transaction option and the --lock-tables option are mutually exclusive because LOCK TABLES causes any pending transactions to be committed implicitly.

This option is not supported for MySQL Cluster tables; the results cannot be guaranteed to be consistent due to the fact that the NDBCLUSTER storage engine supports only the READ_COMMITTED transaction isolation level. You should always use NDB backup and restore instead.

To dump large tables, combine the --single-transaction option with the --quick option.

 

 


Ubuntu Linux: How to install Apache2 – MySQL – PHP (LAMP) phpMyAdmin and fix a few installation errors

We won’t give out pretty much any comments/descriptions for this post because we want to keep it small 🙂

First, install a bunch of stuff:

sudo apt-get install apache2
sudo apt-get install mysql-server
sudo apt-get install php5 libapache2-mod-php5 php5-mysql

Later, restart your apache so that it loads all modules (php, etc)

sudo /etc/init.d/apache2 restart

and if you get the following error:
“Could not reliably determine the server’s fully qualified domain name, using 127.0.1.1 for ServerName” resolve it by adding the line:

ServerName localhost

to the file /etc/apache2/httpd.conf and then try restarting again. (You need root access to edit, if you are not familiar with command line text editors line nano, try sudo gedit /etc/apache2/httpd.conf, gedit is usually available with all gnome installations).

After that, to install phpMyAdmin and make it available at http://localhost/phpmyadmin:

sudo apt-get install phpmyadmin
sudo ln -s /usr/share/phpmyadmin /var/www

(The default MySQL login username ‘root’ and the password is the one you entered during installation in the last step).

Happy developing, hope we helped 🙂