Category Archives: mysql

Top Five MySQL Commands

As Sys admins we are often tasks with working on multiple and disparate systems we need to know a little about everything to allow use to do our jobs effectively. This post will detail my top five MySQL commands to know – with one bonus command! The commands are:

  1. Create database wordpress;
    1. This command creates a database call wordpress.
  2. Show databases;
    1. The Command shows all databases that reside on the server.
  3. Use wordpress;
    1. Selects the wordpress database for use – all subsequent commands will run the wordpress database.
  4. show tables;
    1. Shows all tables within the current selected database.
  5. select * from courses;
    1. Displays all information from the courses table.
  6. Bonus Command! grant all on WordPress.* to ‘wordpressuser’@’localhost’ identified by ‘wordpresspass’;
    1. Grants all permissions on the WordPress database from the user wordpressuser from the local machine with the password wordpresspass

If you want to see these commands in action check out the youtube video here:

Also be sure to download the cheetsheet here which you can save and print out:


Top Five MySQL Commands

Set root password MariaDB

MariaDB is a community-developed fork of the MySQL relational database management system intended to remain free under the GNU GPL. Development is led by some of the original developers of MySQL, who forked it due to concerns over its acquisition by Oracle Corporation.

After you install MariaDB you log in using the following command:

mysql -u root -p

You may notice that you don’t get prompted for a password. In order to correct this you will need to run the following :

Step 1

SET PASSWORD FOR ‘root’@’localhost’ = PASSWORD(‘yourPassword’);

Step 2

UPDATE mysql.user SET plugin = ‘ ‘ WHERE user = ‘root’ AND host = ‘localhost’

Step 3


Step 4


Step 5

Run mysql -u root -p You will now need your password to log in.

A full video tutorial can be found here:


How to check MariaDB version

“MariaDB is a community-developed fork of the MySQL relational database management system intended to remain free under the GNU GPL. Development is led by some of the original developers of MySQL, who forked it due to concerns over its acquisition by Oracle Corporation”

This short MariaDB tutorial will show you how to check the version of MariaDB you are running. We are running MariaDB on CentOS 7 but the process should be much the same for other operating systems.

Step 1 

Log into your MariaDB instance, in our case we log in using the command:

mysql -u root -p

Step 2

After you log in you can see your version in the welcome text – highlighted in the screen-grab below:

Step 3

If you cannot see your version here you can also run the following command to see it:


A full video tutorial on the process can be found here:



How to Install WordPress on Nginx on CentOS 7

Hi there,

This tutorial will show you how to install WordPress using Nginx on CentOS 7. You will need the following commands to follow along with this tutorial.

First we’ll need to install additional repos that include php71 and then install php71. The commands to do so are:

  • sudo wget
  • sudo rpm -Uvh remi-release-7.rpm
  • sudo yum install yum-utils -y
  • sudo yum-config-manager –enable remi-php71
  • sudo yum –enablerepo=remi,remi-php71 install php-fpm php-common
  • sudo yum yum –enablerepo=remi,remi-php71 install php-opcache php-pecl-apcu php-cli php-pear php-pdo php-mysqlnd php-pgsql php-pecl-mongodb php-pecl-redis php-pecl-memcache php-pecl-memcached php-gd php-mbstring php-mcrypt php-xml


Next we will download WordPress, place it in the directory that we want and then correct the permissions for that folder.

  • sudo wget
  • sudo mv latest.tar.gz /usr/share/nginx/htm
  • cd /usr/share/nginx/htm
  • sudo tar -zxvf latest.tar.gz
  • sudo chown -R nginx:nginx /usr/share/nginx/html/wordpress
  • sudo chmod -R 755 /usr/share/nginx/html/wordpress
  • sudo setenforce 0

Now lets set up a config file for our wordpress site. Run the command below and then copy and paste the text into your file, insuring that you enter your IP address into the file.

  • sudo vi /etc/nginx/conf.d/default.conf
server {
 listen 80;
 server_name YOURIPADDRESS;
 # note that these lines are originally from the "location /" block
 root /usr/share/nginx/html/wordpress;
 index index.php index.html index.htm;
 location / {
 try_files $uri $uri/ =404;
 error_page 404 /404.html;
 error_page 500 502 503 504 /50x.html;
 location = /50x.html {
 root /usr/share/nginx/html;
 location ~ \.php$ {
 try_files $uri =404;
 fastcgi_pass unix:/var/run/php-fpm/php-fpm.sock;
 fastcgi_index index.php;
 fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
 include fastcgi_params;

Next make some final changes to your php configuration. Run the command:

  • sudo vi /etc/php-fpm.d/www.conf

Make the following changes to this file.

  • user = apache to user = nginx
  • group = apache to group = nginx
  • listen.owner = nobody to listen.owner = nginx
  • = nobody to = nginx
  • Finally append under  this line listen =
    • listen = /var/run/php-fpm/php-fpm.sock

Now restart all relevant services and ensure that they are all configured to start when the server boots.

  • sudo service php-fpm restart
  • sudo service nginx restart
  • sudo chkconfig php-fpm on
  • sudo chkconfig nginx on

Now lets do the DB settings needed.

  • mysql -u root -p
  • CREATE DATABASE wordpress;
  • GRANT ALL PRIVILEGES ON wordpress.* TO wordpressuser@localhost IDENTIFIED BY ‘password123’;

How to install Joomla 3 on CentOs 7 /Redhat 7 Linux.

This tutorial shows you how to install Joomla 3 on CentOS 7 / Redhat 7 Linux (RHEL).It shows you first how to download, unzip and move the Joomla files to the correct directory for apache.


Next it shows you how to download and install php including the php-mysqli extension that is needed by Joomla. You will then see how to configure Apache to see the Joomla files.


Next you will then configure permissions and set up your Joomla Database in MYSQL (MariaDB) Finally you will install Joomla.



The commands that you need to follow along with this tutorial are:


Downloading, Unzipping and Moving Joomla:


sudo Wget
Sudo mkdir Joomla
Sudo mv\?format\=zip Joomla
Cd Joomla
Sudo unzip\?format\=zip Joomla
Sudo rm\?format\=zip
Sudo mv Joomla /var/www/html


Installing PHP


Sudo yum install php
Sudo yum install php-mysqli


Configuring Apache and Permissions  


Sudo vi /etc/httpd/conf/httpd.conf
Sudo service httpd restart
Sudo chown apache:apache -R /var/www/html/Joomla
Sudo chcon -Rv –type=httpd_sys_rw_content_t /var/www/html

Configuring MYSQL / MariaDB


Mysql -u root -p
Create database joomla;
grant all on joomla.* to ‘darren’@’localhost’ identified by ‘password123’;



How To Install WordPress On Centos 7

This video shows you how to Install WordPress on Centos 7.

The commands that you need to follow along with the tutorial are:


Installing PHP.

  • sudo yum install php
  • sudo yum install php-mysqli


Setting up the Database.

  • Mysql -u root -p
  • CREATE DATABASE wordpress;
  • GRANT ALL PRIVILEGES ON wordpress.* TO wordpressuser@localhost IDENTIFIED BY ‘password123’;


Downloading WordPress and getting it ready for install.

  • wget
  • tar -zxvf latest.tar.gz
  • sudo mv wordpress/ /var/www/html/
  • sudo chown -R apache:apache /var/www/html/wordpress/
  • sudo chcon -Rv –type=httpd_sys_rw_content_t /var/www/html/wordpress
  • sudo vi /etc/httpd/conf/httpd.conf


How To Install Own Cloud on CentOS 7

This video shows how to install Own Cloud on Centos. Own Cloud is simailr to dropbox but you install it on your own server which gives you full control over your data. There are several prerequisites needed to follow along with this video, which I have also included below.

Firstly the commands needed are:

sudo rpm –import


sudo curl -L -o /etc/yum.repos.d/ownCloud.repo


sudo yum clean expire-cache


sudo yum install owncloud


sudo mysql -u root -p




GRANT ALL ON owncloud.* to ‘owncloud’@’localhost’ IDENTIFIED BY ‘password123’;






sudo setenforce 0


How To Install Own Cloud on CentOS 7:



How To Install Apache (HTTPD) On CentOs7:

How to download and install MariaDB (MySQL) on centos 7:


How to set up Lets Encrypt on CentOS 7

How To Disable SELinux on CentOS 7

SOLVED: ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

This video shows how to correct the error:


ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes


The commands that you will need to run to correct the error are:


SET GLOBAL innodb_file_format=Barracuda;
SET GLOBAL innodb_file_per_table=on;
SET GLOBAL innodb_large_prefix=on;



Moodle, how to convert InnoDB tables to Barracuda.

I recently installed Moodle 3.0, everything was going to plan until I noticed that Moodle was giving me the warning below:


“Your database has tables using Antelope as the file format. You are recommended to convert the tables to the Barracuda file format. See the documentation Administration via command line for details of a tool for converting InnoDB tables to Barracuda.”


As the warning alludes to, there is a simple script that you can run that will correct the issue for you. However, there is a caveat, you need to give your moodle database user super permissions, something that I did not wish to do.


You can convert your file format by running the commands directly on the DB using your main administrator account.


SET GLOBAL innodb_file_per_table=1;
SET GLOBAL innodb_file_format=Barracuda;
ALTER TABLE mdl_certificate ROW_FORMAT=Compressed;
ALTER TABLE mdl_data ROW_FORMAT=Compressed;
ALTER TABLE mdl_data_fields ROW_FORMAT=Compressed;
ALTER TABLE mdl_enrol_paypal ROW_FORMAT=Compressed;
ALTER TABLE mdl_lti ROW_FORMAT=Compressed;
ALTER TABLE mdl_user ROW_FORMAT=Compressed;
ALTER TABLE mdl_user_info_field ROW_FORMAT=Compressed;

Once you have ran the above commands the warning on the Moodle site will no longer show.