Linux Blimp

Web apps, web crawling, database access, GUI creation, parsing, image processing, and lots more—these handy tools have you covered.

If you tend to issue commands and accidentally leave them running, you might want to employ the timeout command

How to use the Linux timeout command

It shows you how to use a built-in Linux command to keep you from accidentally leaving your commands running for hours on end.

Linux admins are notorious for depending on the command line. With good reason. The command line is incredibly powerful. There is no end to what you can do with Linux commands.

However, there are times when you want to run a command but don’t want the command to continue running until you forget it’s running and realize that the command has been gobbling up CPU cycles, filling up logs, or just generally doing its thing in the background, harming nothing.

How it works

Regardless of why you don’t want to allow a command to run forever, the how is quite simple—thanks to the timeout command. The timeout command should be installed by default and is very simple to use. Say you want to run a ping command, on google.com, for five seconds (because who hasn’t forgotten they’d run a ping command, only to come back hours later to see it still pinging the target address?).

To do this, log into your Ubuntu server or desktop, open a terminal window, and issue the command timeout 5 ping google.com. The ping command will do its thing for five seconds and stop. Or say you want to follow the syslog log file with tail for ten seconds. That command would be:

timeout 10 tail -f /var/log/syslog.

After the configured 10 seconds, the tail command will end. And that’s how you can automatically stop your commands, without having to resort to the old [Ctrl]+[C] keyboard combination. If you tend to issue commands and accidentally leave them running, you might want to start employing the timeout command, before your IT manager puts you in a timeout.

Also see

• How to use SSH to proxy through a Linux jump host (TechRepublic)
• How to use the tmux multiplexer for a more efficient Linux terminal (TechRepublic)
• How to scan for IP addresses on your network with Linux (TechRepublic)
• How to make your Linux machines visible to other Linux machines (TechRepublic)
• Dirty Sock vulnerability lets attackers gain root access on Linux systems (ZDNet)
• The 10 most important iPhone apps of all time (Download.com)
• It takes work to keep your data private online. These apps can help (CNET)
• Programming languages and developer career resources coverage (TechRepublic on Flipboard)

Source

The Ubuntu repositories contain thousands of deb packages which can be installed from the Ubuntu Software Center or by using the apt command line utility. Deb is the installation package format used by all Debian based distributions including Ubuntu. Some packages are not available in the standard Ubuntu repositories but it can be easily installed by enabling the appropriate source.

In most cases when the software vendor does not provide a repository they will have a download page from where you can download and install the deb package or download and compile the software from sources.

Although not so often, some software may be distributed only as an RPM package. RPM is a package format used by Red Hat and its derivatives such as CentOS. Luckily, there is a tool called alien that allows us to install an RPM file on Ubuntu or to convert an RPM package file into a Debian package file.

Before you Begin

This is not the recommended way to install software packages in Ubuntu. Whenever possible you should prefer installing software from the Ubuntu repositories.

Not all RPM packages can be installed on Ubuntu. Installing RPM packaged on Ubuntu may lead to package dependency conflicts.

You should never use this method to replace or update important system packages, like libc, systemd, or other services and libraries that are essential for the proper functioning of your system. Doing this may lead to errors and system instability.

Install Alien

Alien is a tool that supports conversion between Red Hat rpm, Debian deb, Stampede slp, Slackware tgz, and Solaris pkg file formats.

Before installing the alien package make sure the Universe repository is enabled on your system:

sudo add-apt-repository universe

Once the repository is enabled update the packages index and install the alien package with:

sudo apt updatesudo apt install alien

The command above will also install the necessary build tools.

Converting and Installing an RPM package

To convert a package from RPM to DEB format use the alien command followed by the RPM package name:

sudo alien package_name.rpm

Depending on the package size the conversion may take some time. In most cases, you will see warning messages printed on your screen. If the package is successfully converted the output will indicate that the DEB package is generated:

package_name.deb generated

To install the deb package, you can either use the dpkg or apt utility:

sudo dpkg -i package_name.deb

sudo apt ./package_name.deb

The package should now be installed, assuming it’s compatible with your system and all dependencies are met.

Installing an RPM package directly

Instead of converting and then installing the package you can use the -i option that will tell alien to install the RPM package directly.

sudo alien -i package_name.rpm

The command above will automatically generate and installed the package and remove the package file after it has been installed.

Conclusion

In this tutorial, you learned how to install RPM packages on Ubuntu.

If you have any question or feedback feel free to leave a comment.

Source

If you want to set your Ubuntu system to automatically take care of your system Trash over a particular period of time, you can make use of the command line utility called autotrash. Autotrash is a utility that looks in a FreeDesktop.org Trash folder for information on its contents and then purges a part of the trash depending on your specified options.

In this article, we will explain how to install, seek help on, and use the autotrash utility. We will elaborate on the procedure by presenting some examples that will help you with the widely used functions of this utility.

We have run the commands and procedures mentioned in this article on a Ubuntu 18.04 LTS system.

Install Autotrash through the Command line

We will install the autotrash command line utility through the Terminal application. You can launch the Terminal either through the system application launcher search or the Ctrl+Alt+T shortcut. Please note that only an authorized user can add/update, remove and configure software in Ubuntu.

Step1: Update your system’s Repository Index

In order to install the latest version of a software available in Ubuntu’s Internet repositories, your system’s repository index needs to be up-to-date.

Run the following command as sudo in order to update your system’s local repository index with that of the Internet.

$ sudo apt-get update

The system might ask you for the password of sudo. Enter the password and your command will take action.

Step 2: Install autotrash

Now, you can install the autotrash utility by running the following command as sudo in your Terminal application:

$ sudo apt-get install autotrash

The system might prompt you with a Y/n option before starting the installation procedure. Please enter Y to continue.

Step 3: Verify autotrash installation (optional)

You can check the version number of your installed autotrash software and also verify if it is indeed installed on your system. Run the following command to do so:

$ autotrash --version

Or,

$ autotrash -V

You are now ready to use autotrash on your Ubuntu.

How to use autotrash? (with examples)

The autotrash help will tell you all you can do with this application. You can view the help as follows:

$ autotrash --help

For even more detailed information, you can view the manpage of autotrash by using the following command:

$ man autotrash

Now we will present some examples that will elaborate on how you can make the best use of this utility:

Example 1: Delete Files older than x number of days

This is the most widely used feature of autotrash. You can use the following command syntax to automatically remove the files residing in your system trash for x number of days:

$ autotrash -d [days]

For example, the following command will remove the files that have completed their 30 days in the trash can.

$ autotrash -d 30

This gives you enough time to restore any files that you might wish to, during 30 days.

The following usage of the command lets you apply this feature for not only your trashed files but also deleted files of all the users using the system:

$ autotrash -td [days]

Example:

$ autotrash -td 30

Example 2: Delete files based on free-space left

Max-free

You can only use autotrash to purge files if there is less than M megabytes of free space left at the trash location. As an example, if you set this to 1024, then autotrash will only start to work if there is less than 1GB of free space in the trash. Till that time, autotrash will just exit normally without scanning the Trash directory.

Example:

$ autotrash --max-free 1024 -d 30

Min-free

Similarly, you can use autotrash to make sure there is a minimum of M megabytes of free space.

Example:

$ autotrash --min-free 1024

You can even combine the two command to make sure that autotrash starts reading the trash if there is less than 2 GB of free space, and then keep a check on. The command will then remove files older than 30 days and also remove extra files to ensure that 1 GB of space is always available in the trash.

$ autotrash --max-free 2048 --min-free 1024 -d 30

Example 3: Delete M number of megabytes

You can use the utility to purge at least M number off megabytes, deleting oldest trash addition first. This feature uses trash entries rather than the individual files.This means that if your oldest trashed item is a 1GB directory, and you request at least 10MB to be removed (M=10), autotrash will remove 1GB.

$ autotrash –delete M

Example

$ autotrash --delete 1024

Now you do not need to worry about space wastage on your system because of the system trash The autotrash utility will automatically take care of clearing your unwanted files based on your defined criteria.

Source

How to use git in Emacs

Download and config

To get started using git in Emacs for version control, choose and install a chosen solution. In this article you will see the Magit mode. Many users have praised magit for it’s design. In fact, it has the power to teach you Git because it shows you the options when they are available. Later you will see this in action, for now let’s install it. The package is available from MELPA, though advanced users may download the source.

You can Download from MELPA or add use-package to have it installed by the setup script. To configure, the simplest is to add a global key-binding as per the below code.

The key-chord is up to you but most users have C-c m for magit, if C-c g for Git is more logical to you, go for it. You can add many more but the magit interface is so powerful you will probably want to use it as is.

Running magit

Once magit is installed, when you run it, it shows a nice menu to help you along. The best way to start is to write your first file in a directory that will be your repository. Emacs is brilliant here, in the sense that you can type the whole path of your file, including the file. Emacs will ask to create the directories for you. Magit is also smart in that if you have just a few files in a directory and you try to add files to a repository, it will ask if it should create a git repository. Answer ‘yes’ and magit will run git init in your directory.

After that is done, it is your turn to choose what you want to add to your repository. The most common is that you add all, since you created the directory for this purpose. Later on you will make sure to ignore some files. Now, to see some effects, you need to create new files and edit existing ones. Start by writing a README.md file. Put a few sentences in it so people know what the repository is for.

Staging

When you have some changes, they need to be prepared in git. This is called staging and makes it possible to check what is new before you commit it. In the magit interface, you have all files listed under a heading that reflects their current status. The sections will show depending on if you have any files there. The states are Unstaged, Staged, Commited, Unmerged, and Merged which shows the main stages for your files. The brilliance of magit shines through here; when you have opened the interface you can type ‘s’ to stage a file, ‘S’ to stage all files.

If you feel a little lost about what actions you have available, just hit ‘?’, the question mark. All commands and their respective key-bindings show up and you can choose accordingly. Staging happens on the current branch, this will be main if you just created the repository. In reality, you will usually work on a branch.

Branching

Branches are used to create new code while still allowing the main code to be downloaded and tested. When you want to create a new feature or fix a problem, you create a branch. In magit, you create branch from the interface by pressing ‘b’. After that you can choose ‘n’ to create but not start working on it’s code. The better and more common alternative is to choose ‘c’. When you have chosen the branch name, magit creates the branch and checks it for you. You are now changing the branch and can do your coding in piece.

Commit

A commit is when you put the latest update in the current branch. For the most of your time developing software, you will be working on a branch. You do this so you can test your own code on your own machine before you add it to the main tree. The rest of the team will see your code once you merge it. Which branch you merge it with will depend on the team you are working with and the task you have just solved.

In magit you reach the commit function by opening status and choosing ‘c’. You can choose a lot of options but most of the time you will just repeat ‘c’ again to commit for real. Note that magit will not allow a commit without a message, so you will be presented with a place to put your text and then you can commit with ‘C-c, C-c’.

Merge branches

When you have a nice branch that works the way you want, or looks good, you need to merge it with the main and then run tests. When that is done you need to push it to the remote repository. In magit, you have all this at your fingertips. It is easiest to checkout the branch you want to merge to before you merge. In the magit status window, you start by choosing merge, most times you will merge simply with the ‘m’ choice but you can also merge into.

Other options that are available includes to absorb, merge into and merge. The absorb is for when you have too many branches and you want to combine them into one. These options are something you need to work through a few times to see when it is appropriate to use them.

Remote

If you are using GitHub, you need to create your project on GitHub before you start. After the first file is on GitHub, you can do most things from Magit and Emacs. To clone any project of any git service, use M-x magit-clone. When you start it, you will be prompted to put in the remote repository. Paste in the URL, git or https both works well. Now you have the entire project stored locally and you can start changing it. To do this, you want to create a new branch. With magit, you start a new branch in the status screen by choosing ‘b’ and then ‘n’.

Choose where to start from and give a name to your new branch. Once the branch is created, you can change code, add files, remove files in the directory. Any changes you make to existing files will be tracked, new files you will have to add yourself.

Join existing projects

The most rewarding part of coding, especially in free and open source, is to contribute to other projects. This can also help you get a job as a developer, when potential employers see that you have contributed to a project, they know that you have experience. Many projects use Git for version control so with Magit in your toolkit you are golden. To contribute, you have a few things to do before you can get started. First, clone the repository. Second, make an upstream branch to work with. Another important thing to consider is how the project you are working with documents their code and commits. They usually have a style guide you need to consult before you commit. Now that you are prepared to contribute you need to create a branch, actually many branches. Let me clarify: you create a branch for each task, this is so that a pull request can be connected to i.e a trouble report. The branch naming is also important, each project has their own style. Where you branch from will also differ depending on the task. A patch will be a branch from the stable branch and new features will hang off the development branch. This way, the whole team can test in the right order. First, make the main branch stable, then add new features. Rinse and repeat.

Logging

When you feel that the code has steered of course, you will need to use the log. The git log will show the reference hash and the commit message by default, If the commit messages have been well written, this will be enough. However, many times they are not. For more advanced cases you can also view more information. You can choose to show the users, the branches and even get a graphical view of the branching. You also have the possibility to show the entire patch from the log, this will be a very long file though. The point is, you have many options to find what you are looking for.

Conclusion

Git has been around for a long time. Developed by Linus Torvalds it is made to work for really big projects while you have use of it from your very first project. When you start actual software projects, you will have to use some system to keep track of changes to the code. Git is one of the most common right now, so try it out.

Source

Restarting a service is one of the administrative tasks that you will have to perform one time or the other while configuring software on your Ubuntu. In this article, we will explain how to restart a running service on your Ubuntu through the systemctl command. We will also explain some more service management systemctl commands that will help you further while working with services.

We have run the commands and procedures mentioned in this article on a Ubuntu 18.04 LTS system.

Restarting a Service with Systemctl

In this article, we will be using the Ubuntu command line, the Terminal, in order to explain how to restart a running service.

You can open the Terminal application on your Ubuntu either through the application launcher search bar or simply through the Ctrl+Alt+T shortcut.

The latest versions of Ubuntu use systemd to control and start services. To get a list of all enabled systemd units, run this command:

systemctl list-unit-files | grep enabled

The list will help you in fetching the exact service name that you can later use to restart the service.

You can use the systemctl command in order to restart a service. This command is a relatively new tool that you can use to control systemd (init system) and services. This tool is the replacement of the sysV init manager. These days, most modern Linux distros have switched to systemd and thus systemctl.

Here is how you can use the systemctl command in order to restart a running service:

$ sudo systemctl restart [servicename]

For example, the following command can be used to restart the UFW( Uncomplicated Firewall) service on Ubuntu:

$ sudo systemctl restart ufw

Please note that you need to be an authorized user in order to manage system services.

Manage Services with Systemd

Not only you can restart services with systemctl, but you can also:

• Check the status of a service

$ systemctl status [servicename]

• Start a service

$ systemctl start [servicename]

• Stop a service

$ systemctl stop [servicename]

• Reload a service (reload service configuration)

$ systemctl reload [servicename]

• Reload-or-restart a service (reloads a service/restarts if reload is not available)

$ sudo systemctl reload-or-restart [servicename]

• Enable a service

$ systemctl enable [servicename]

• Disable a service

$ systemctl disable [servicename]

• Check if service is enabled/active

$ sudo systemctl is-active [servicename]

$ sudo systemctl is-enabled [servicename]

Through this simple, yet useful tool you can restart a service without having to restart your entire system. Not only that, you can perform more service management with the help of the command usage described in this article.

Source

MySQL is one of the most popular database in the world. This database can be installed on the Microsoft Windows platform besides of Linux platform. Why this database is so popular? It may caused by its powerful feature and its free to use. As a database administrator, a database backup is really crucial to maintain the availability of the data. It will minimize the risk if something happens to our database.

Since MySQL is a popular database, there are many software that we can use to backup it. From the console mode to the web based software. Now we will give you a look of MySQLDumper as a tool for backup MySQL Database.

What is MySQLDumper?

MySQLDumper is a another open source web based tool for backing up MySQL databases. It built from PHP and Perl and can be easily dump and restore your MySQL data. It is especially suitable for shared hosting, where we don’t have access to Linux shell.

MySQLDumper Features

There are a lot of MySQLDumper features, but here are some features that may interest you.

1. Easy installation; just make sure that you have a working web server and point your browser to MySQLDumper installation file.
2. All parameters is shown before the backup is started; so you are sure what you are doing.
3. Database-Overview; look at running processes/
4. SQL-Browser: Access to your MySQL-Tables, delete tables, edit or insert data.
5. Two type of backup method, using PHP or Perl.
6. Complete log files.
7. Automatic file-deletion of your old backups.
8. Create directory protection.

Installation of MySQLDumper in Linux

Installing MySQLDumper is so easy. First we can download MySQLDumper from the following link.

1. Download MySQLDumper

At the time of writing this article, the latest version is 1.24. So, download latest version under your working web server directory (i.e. /var/www or /var/www/html). Once you have it, you can extract MySQLDumper1.24.4.zip.

$ unzip MySQLDumper1.24.4.zip

Then you will find a ‘msd1.24.4‘ folder. This folder contain all MySQLDumper files. The next step, you just need to point your browser to MySQLDumper installation file. The file is ‘msd1.24.4/install.php’. Here are the steps of super easy MySQLDumper.

1. We need to choose installation Language.

2. We need to fill some credentials such as hostname, user and MySQL password.

3. We can test the connection to the database by clicking Connect to MySQL button. If it succeed, then we will see a message saying that “Database connection was established”.

4. Once you got the message, click the ‘Save‘ and continue installation button. You will be taken into the home screen.

How to use MySQLDumper

As we can guess from its name, MySQLDumper main function is to backup your MySQL database. With this application, backup (and restore) MySQL database is very easy. Let’s start to take a look.

Backup Process using PHP

The function menu is located on the panel navigation on the left. First we need to select which database that we want to backup. We can see the option on the left menu.

In the screenshot above, we choose to backup a database named ‘employees‘.

Then we can select ‘Backup‘ menu on the left. Then choose ‘Backup PHP‘ on the top area. We will have a screen like this.

Then click on ‘Start New Backup‘. A progress of backup activity will show to you.

Once backup progress is finish, we can see the notification.

Backup Process using Perl

Another backup method that is supported by MySQLDumper is ‘Backup Perl’. With this method, we will use Perl as the backup engine.

Please notice that your web server must support ‘Perl/CGI‘ script before running this backup method. Otherwise, you will see an error like this when you click on Test Perl button.

Same with PHP backup method, we need to select which database that we want to backup. Then choose Backup menu from the left navigation panel. Then click Backup Perl button.

MySQLDumper will show you some active parameters on the bottom area. Then we can click ‘Run the Perl Cron‘ script button. Using this method, we will not see any progress bar appear. The duration of this backup process will be depend on the database which we are going to backup. If no error, then we will see a notification like this.

Restore Process

Restoring a backup is also easy using MySQLDumper. You can click on ‘Restore‘ menu from the navigation panel in the left. Unlike Backup activity, all backups are available at the bottom area of restore page.

When we need to select a backup, we can choose from there. At the above area is the selected backup which are ready to restore. If you want to do full restore, then click on the ‘Restore‘ button above. While if you want to restore some tables only, click on the ‘Choose tables‘ to be restored above.

Once it done, click ‘Restore‘. Just wait for a moment to complete the restore progress.

Create a Directory Protection

By default, the home page of MySQLDumper can be accessed by anyone who know its URL. Using Directory Protection, we can create a this home screen protected by password. This Directory Protection utilizes ‘.htaccess‘ function on Apache web server.

To create it, just click Create directory protection button on the home screen.

Then you will ask to provide some credential.

Once you finish with that, click Create directory protection button. After that, you will have a confirmation page about it.

If there is no error, a success message will be displayed.

Next time you visit the page, MySQLDumper will ask you a password before you see its home screen.

File Administration

This menu is used to maintain all available backups and restore.

Here are some activity that can be done in this page.

1. Delete backup(s) ; use the Delete buttons at the top area.
2. Download backup(s) ; click the backup name.
3. Select backup(s) ; click the Database name in the All Backups area.
4. Upload a big backup(s) to be restored.
5. Convert database into MySQLDumper (MSD) format.

Note: When we tried to convert database without using any compression, we found that MySQLDumper create a database with ‘part_1.sql’ name. The size is smaller than the original source.

SQL-Browser

If you want to run specific SQL command, you can do it in this SQL-Browser page. But please you should know what you are doing.

Configuration

All function above can be configured from Configuration menu. Here are some sections that we can configure.

General

Interface

Autodelete

Email

FTP

Cronscript

Log Management

MySQLDumper also provide basic logs for us. So we can know when the backup-restore activity occurred. To access log page, just click ‘Log’ menu from the navigation panel on the left.

There are 3 kind of logs. PHP-Log, Perl-Log and Perl-Complete Log.

Conclusion

MySQLDumper may not the best backup tool for MySQL. But with the ease of use of this application, people may start using this application. Unfortunately, I found that MySQLDumper is not equipped with offline documentation. But still, it is a great alternative tool for backup MySQL database.

http://www.mysqldumper.net/

Source

mysqladmin is a command-line utility the comes with MySQL server and it is used by Database Administratorsto perform some basic MySQL tasks easily such as setting root password, changing root password, monitoring mysql processes, reloading privileges, checking server status etc.

In this article we’ve compiled some very useful ‘mysqladmin‘ commands that are used by system/database administrators in their day-to-day work. You must have MySQL server installed on your system to perform these tasks.

If you don’t have MySQL server installed or you are using older version of MySQL server, then we recommend you all to install or update your version by following our below article.

1. Installation of MySQL 5.5.28 Server on RHEL/CentOS/Fedora

1. How to set MySQL Root password?

If you have fresh installation of MySQL server, then it doesn’t required any password to connect it as root user. To set MySQL password for root user, use the following command.

# mysqladmin -u root password YOURNEWPASSWORD

2. How to Change MySQL Root password?

If you would like to change or update MySQL root password, then you need to type the following command. For example, say your old password is 123456 and you want to change it with new password say xyz123.

mysqladmin -u root -p123456 password 'xyz123'

3. How to check MySQL Server is running?

To find out whether MySQL server is up and running, use the following command.

# mysqladmin -u root -p ping

Enter password:
mysqld is alive

4. How to Check which MySQL version I am running?

The following command shows MySQL version along with the current running status .

# mysqladmin -u root -p version

Enter password:
mysqladmin  Ver 8.42 Distrib 5.5.28, for Linux on i686
Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Server version          5.5.28
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/lib/mysql/mysql.sock
Uptime:                 7 days 14 min 45 sec

Threads: 2  Questions: 36002  Slow queries: 0  Opens: 15  Flush tables: 1  Open tables: 8  Queries per second avg: 0.059

5. How to Find out current Status of MySQL server?

To find out current status of MySQL server, use the following command. The mysqladmin command shows the status of uptime with running threads and queries.

# mysqladmin -u root -ptmppassword status

Enter password:
Uptime: 606704  Threads: 2  Questions: 36003  Slow queries: 0  Opens: 15  Flush tables: 1  Open tables: 8  Queries per second avg: 0.059

6. How to check status of all MySQL Server Variable’s and value’s?

To check all the running status of MySQL server variables and values, type the following command. The output would be similar to below.

# mysqladmin -u root -p extended-status

Enter password:
+------------------------------------------+-------------+
| Variable_name                            | Value       |
+------------------------------------------+-------------+
| Aborted_clients                          | 3           |
| Aborted_connects                         | 3           |
| Binlog_cache_disk_use                    | 0           |
| Binlog_cache_use                         | 0           |
| Binlog_stmt_cache_disk_use               | 0           |
| Binlog_stmt_cache_use                    | 0           |
| Bytes_received                           | 6400357     |
| Bytes_sent                               | 2610105     |
| Com_admin_commands                       | 3           |
| Com_assign_to_keycache                   | 0           |
| Com_alter_db                             | 0           |
| Com_alter_db_upgrade                     | 0           |
| Com_alter_event                          | 0           |
| Com_alter_function                       | 0           |
| Com_alter_procedure                      | 0           |
| Com_alter_server                         | 0           |
| Com_alter_table                          | 0           |
| Com_alter_tablespace                     | 0           |
+------------------------------------------+-------------+

7. How to see all MySQL server Variables and Values?

To see all the running variables and values of MySQL server, use the command as follows.

# mysqladmin  -u root -p variables

Enter password:
+---------------------------------------------------+----------------------------------------------+
| Variable_name                                     | Value                                        |
+---------------------------------------------------+----------------------------------------------+
| auto_increment_increment                          | 1                                            |
| auto_increment_offset                             | 1                                            |
| autocommit                                        | ON                                           |
| automatic_sp_privileges                           | ON                                           |
| back_log                                          | 50                                           |
| basedir                                           | /usr                                         |
| big_tables                                        | OFF                                          |
| binlog_cache_size                                 | 32768                                        |
| binlog_direct_non_transactional_updates           | OFF                                          |
| binlog_format                                     | STATEMENT                                    |
| binlog_stmt_cache_size                            | 32768                                        |
| bulk_insert_buffer_size                           | 8388608                                      |
| character_set_client                              | latin1                                       |
| character_set_connection                          | latin1                                       |
| character_set_database                            | latin1                                       |
| character_set_filesystem                          | binary                                       |
| character_set_results                             | latin1                                       |
| character_set_server                              | latin1                                       |
| character_set_system                              | utf8                                         |
| character_sets_dir                                | /usr/share/mysql/charsets/                   |
| collation_connection                              | latin1_swedish_ci                            |
+---------------------------------------------------+----------------------------------------------+

8. How to check all the running Process of MySQL server?

The following command will display all the running process of MySQL database queries.

# mysqladmin -u root -p processlist

Enter password:
+-------+---------+-----------------+---------+---------+------+-------+------------------+
| Id    | User    | Host            | db      | Command | Time | State | Info             |
+-------+---------+-----------------+---------+---------+------+-------+------------------+
| 18001 | rsyslog | localhost:38307 | rsyslog | Sleep   | 5590 |       |                  |
| 18020 | root    | localhost       |         | Query   | 0    |       | show processlist |
+-------+---------+-----------------+---------+---------+------+-------+------------------+

9. How to create a Database in MySQL server?

To create a new database in MySQL server, use the command as shown below.

# mysqladmin -u root -p create databasename

Enter password:

# mysql -u root -p

Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 18027
Server version: 5.5.28 MySQL Community Server (GPL) by Remi

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| databasename       |
| mysql              |
| test               |
+--------------------+
8 rows in set (0.01 sec)

mysql>

10. How to drop a Database in MySQL server?

To drop a Database in MySQL server, use the following command. You will be asked to confirm press ‘y‘.

# mysqladmin -u root -p drop databasename

Enter password:
Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.

Do you really want to drop the 'databasename' database [y/N] y
Database "databasename" dropped

11. How to reload/refresh MySQL Privileges?

The reload command tells the server to reload the grant tables. The refresh command flushes all tables and reopens the log files.

# mysqladmin -u root -p reload;
# mysqladmin -u root -p refresh

12. How to shutdown MySQL server Safely?

To shutdown MySQL server safely, type the following command.

mysqladmin -u root -p shutdown

Enter password:

You can also use the following commands to start/stop MySQL server.

# /etc/init.d/mysqld stop
# /etc/init.d/mysqld start

13. Some useful MySQL Flush commands

Following are some useful flush commands with their description.

1. flush-hosts: Flush all host information from host cache.
2. flush-tables: Flush all tables.
3. flush-threads: Flush all threads cache.
4. flush-logs: Flush all information logs.
5. flush-privileges: Reload the grant tables (same as reload).
6. flush-status: Clear status variables.

# mysqladmin -u root -p flush-hosts
# mysqladmin -u root -p flush-tables
# mysqladmin -u root -p flush-threads
# mysqladmin -u root -p flush-logs
# mysqladmin -u root -p flush-privileges
# mysqladmin -u root -p flush-status

14. How to kill Sleeping MySQL Client Process?

Use the following command to identify sleeping MySQL client process.

# mysqladmin -u root -p processlist

Enter password:
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host      | db | Command | Time | State | Info             |
+----+------+-----------+----+---------+------+-------+------------------+
| 5  | root | localhost |    | Sleep   | 14   |       |					 |
| 8  | root | localhost |    | Query   | 0    |       | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+

Now, run the following command with kill and process ID as shown below.

# mysqladmin -u root -p kill 5

Enter password:
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host      | db | Command | Time | State | Info             |
+----+------+-----------+----+---------+------+-------+------------------+
| 12 | root | localhost |    | Query   | 0    |       | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+

If you like to kill multiple process, then pass the process ID‘s with comma separated as shown below.

# mysqladmin -u root -p kill 5,10

15. How to run multiple mysqladmin commands together?

If you would like to execute multiple ‘mysqladmin‘ commands together, then the command would be like this.

# mysqladmin  -u root -p processlist status version

Enter password:
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host      | db | Command | Time | State | Info             |
+----+------+-----------+----+---------+------+-------+------------------+
| 8  | root | localhost |    | Query   | 0    |       | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+
Uptime: 3801  Threads: 1  Questions: 15  Slow queries: 0  Opens: 15  Flush tables: 1  Open tables: 8  Queries per second avg: 0.003
mysqladmin  Ver 8.42 Distrib 5.5.28, for Linux on i686
Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Server version          5.5.28
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/lib/mysql/mysql.sock
Uptime:                 1 hour 3 min 21 sec

Threads: 1  Questions: 15  Slow queries: 0  Opens: 15  Flush tables: 1  Open tables: 8  Queries per second avg: 0.003

16. How to Connect remote mysql server

To connect remote MySQL server, use the -h (host)  with IP Address of remote machine.

# mysqladmin  -h 172.16.25.126 -u root -p

17. How to execute command on remote MySQL server

Let’s say you would like to see the status of remote MySQL server, then the command would be.

# mysqladmin  -h 172.16.25.126 -u root -p status

18. How to start/stop MySQL replication on a slave server?

To start/stop MySQL replication on salve server, use the following commands.

# mysqladmin  -u root -p start-slave

# mysqladmin  -u root -p stop-slave

19. How to store MySQL server Debug Information to logs?

It tells the server to write debug information about locks in use, used memory and query usage to the MySQL log file including information about event scheduler.

# mysqladmin  -u root -p debug

Enter password:

20. How to view mysqladmin options and usage

To find out more options and usage of myslqadmin command use the help command as shown below. It will display a list of available options.

# mysqladmin --help

We have tried our best to include almost all of ‘mysqladmin‘ commands with their examples in this article, If still, we’ve missed anything, please do let us know via comments and don’t forget to share with your friends.

Source

Database is a structured set of data stored electronically. The concept of database was known to our ancestors even when there were no computers, however creating and maintaining such database was very tedious job. In a manual database say of 100 pages, if you have to search for all the employees whose salary were less than 10k, just think how much difficult it would have been, then.

In today’s world you just can’t escape Database. Right now millions of database is working around the world to store and fetch data of every kind be it strategic data, employee record or web technologies.

Database is oftenly termed as back-end process, as because it is neither visible to end user nor End Userinteracts directly with the database. They works on front-end process viz., PHP, VB, ASP.NET, etc. and ask the front end to deal with database in back-end.

There are several database server and client available like Oracle, MySQL, MySQLi, MariaDB, MongoDB etc. The syntax of all of these are more or less the same. Mastering one means gaining control on most of them and learning the queries of a database is very easy and fun.

Lets start with simple queries on database. We will be using MySQL which comes bundled with most of the Linux distributions by default, you could install it manually from repository, if it is not installed by default in your case.

Well a database query is a simple piece of code that is sent to database to get custom and refined result, as required.

Install MySQL Database

Use “yum” or “apt” package manager to install MySQL Database.

# yum install mysql mysql-client mysql-server  (on Yum based Systems)

# apt-get install mysql mysql-client mysql-server (on Apt based Systems)

Start MySQL

Start MySQL database service as:

# service mysqld start
or
# service mysql start

Well installing a MySQL database will take you to the configuration where you are asked to setup adminpassword, etc. Once finished installing and starting the server go to your MySQL prompt.

# mysql -u root -p

Replace root with your configured username and enter password when prompted, if the login credential is correct, you will be at your MySQL prompt at the blink of your eyes.

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 195 

Server version: 5.5.31-0+wheezy1 (Debian) 

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. 

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. 
Other names may be trademarks of their respective owners. 

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Now carrying out queries at this prompt is very educative and fun.

Create a database tecmint

mysql> create database tecmint ;
Query OK, 1 row affected (0.02 sec) 

mysql>

Note: It reports that the query was correct, means database is created. You can verify your newly created database as.

mysql> show databases; 
+--------------------+
| Database           | 
+--------------------+ 
| information_schema | 
| mysql              | 
| performance_schema | 
| tecmint            | 
| test               | 
+--------------------+ 
9 rows in set (0.00 sec) 
mysql>

Note: Notice your database in the above output.

Select Database

Now you need to select the database to work upon it.

mysql> use tecmint;
Database changed
mysql>

Create Tables in MySQL

Here we will be creating a table say “minttec” with three fields as:

mysql> CREATE TABLE minttec (
    -> id Int(3), 
    -> first_name Varchar (15), 
    -> email Varchar(20) 
    -> ); 
Query OK, 0 rows affected (0.08 sec) 
mysql>

Note: The above query says OK which means table was created without any error. To verify the table run the below query.

mysql> show tables; 
+-------------------+ 
| Tables_in_tecmint | 
+-------------------+ 
| minttec           | 
+-------------------+ 

1 row in set (0.00 sec) 

mysql>

Things are going fine till now. Yup! You can view the columns you created in the table “minttec” as:

mysql> show columns from minttec; 

+------------+-------------+------+-----+---------+-------+ 
| Field      | Type        | Null | Key | Default | Extra | 
+------------+-------------+------+-----+---------+-------+ 
| id         | int(3)      | YES  |     | NULL    |       | 
| first_name | varchar(15) | YES  |     | NULL    |       | 
| email      | varchar(20) | YES  |     | NULL    |       | 
+------------+-------------+------+-----+---------+-------+ 
3 rows in set (0.00 sec)

mysql>

It was nothing less than a magic. Anyway I will tell you about the types of declaration and their meaning.

1. Int is Integer
2. Varchar is char having variable length as defined. The value after Type is the length of field up-to which it can store data.

OK now we need to add a column say ‘last_name‘ after column ‘first_name‘.

mysql> ALTER TABLE minttec ADD last_name varchar (20) AFTER first_name; 
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

Now, verify it in your table.

mysql> show columns from minttec; 

+------------+-------------+------+-----+---------+-------+ 
| Field      | Type        | Null | Key | Default | Extra | 
+------------+-------------+------+-----+---------+-------+ 
| id         | int(3)      | YES  |     | NULL    |       | 
| first_name | varchar(15) | YES  |     | NULL    |       | 
| last_name  | varchar(20) | YES  |     | NULL    |       | 
| email      | varchar(20) | YES  |     | NULL    |       | 
+------------+-------------+------+-----+---------+-------+ 

4 rows in set (0.00 sec) 

mysql>

Add Column in MySQL

Now we will add a column to the right say a column ‘country‘ to the right of email.

mysql> ALTER TABLE minttec ADD country varchar (15) AFTER email; 
Query OK, 0 rows affected (0.16 sec) 
Records: 0  Duplicates: 0  Warnings: 0 

mysql>

Verify the above column insertion query.

mysql> show columns from minttec; 

+------------+-------------+------+-----+---------+-------+ 
| Field      | Type        | Null | Key | Default | Extra | 
+------------+-------------+------+-----+---------+-------+
| id         | int(3)      | YES  |     | NULL    |       | 
| first_name | varchar(15) | YES  |     | NULL    |       | 
| last_name  | varchar(20) | YES  |     | NULL    |       | 
| email      | varchar(20) | YES  |     | NULL    |       | 
| country    | varchar(15) | YES  |     | NULL    |       | 
+------------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec) 

mysql>

Insert Values in Field

What about inserting values to the field?

mysql> INSERT INTO minttec VALUES ('1' , 'Ravi' , 'Saive' , 'raivsaive@xyz.com' , 'India' );
Query OK, 1 row affected (0.02 sec) 

mysql>

How about inserting more than 1 value at a time in the above table.

mysql> INSERT INTO minttec VALUES ('2' , 'Narad' , 'Shrestha' , 'narad@xyz.com' , 'India' ), ('3' , 'user' , 'singh' , 'user@xyz.com' , 'Aus' ), ('4' , 'tecmint' , '[dot]com' , 'tecmint@gmail.com' , 'India' );
Query OK, 3 rows affected (0.05 sec) 
Records: 3  Duplicates: 0  Warnings: 0

Verify the above insertion.

mysql> select * from minttec; 
+------+------------+-----------+-------------------+---------+ 
| id   | first_name | last_name | email             | country | 
+------+------------+-----------+-------------------+---------+ 
|    1 | Ravi	    | Saive     | raivsaive@xyz.com | India   | 
|    2 | Narad      | Shrestha  | narad@xyz.com     | India   | 
|    3 | user       | singh     | user@xyz.com      | Aus     | 
|    4 | tecmint    | [dot]com  | tecmint@gmail.com | India   | 
+------+------------+-----------+-------------------+---------+ 

4 rows in set (0.00 sec)

mysql>

Delete Values in Field

Let’s say the third entry in the above output is invalid and we need to delete the third entry.

mysql> DELETE FROM minttec WHERE id = 3;

Query OK, 1 row affected (0.02 sec)

Verify the above operation.

mysql> select * from minttec;

+------+------------+-----------+-------------------+---------+ 
| id   | first_name | last_name | email             | country | 
+------+------------+-----------+-------------------+---------+
|    1 | Ravi       | Saive     | raivsaive@xyz.com | India   | 
|    2 | Narad      | Shrestha  | narad@xyz.com     | India   | 
|    4 | tecmint    | [dot]com  | tecmint@gmail.com | India   | 
+------+------------+-----------+-------------------+---------+
3 rows in set (0.00 sec)

Update Values in Field

The id (=4) needs to be edited.

mysql> UPDATE minttec SET id = 3 WHERE first_name = 'tecmint'; 
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>

Verify the above query.

mysql> UPDATE minttec SET id = 3 WHERE first_name = 'tecmint'; 
Query OK, 1 row affected (0.02 sec) 
Rows matched: 1  Changed: 1  Warnings: 0

mysql>

Note: The above query, as performed is not a good idea. It will change the id to ‘4‘ where ever the first name is ‘tecmint’. It is always a good idea to use more than one column with where clause to get minimal error, as:

mysql> UPDATE minttec SET id = 6 WHERE first_name = 'tecmint'AND last_name = '[dot]com'; 
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>

Delete Column in MySQL

Let we need to drop (delete) a column we think, is of no importance say ‘country‘ here.

mysql> ALTER TABLE minttec drop country; 
Query OK, 3 rows affected (0.15 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql>

Verify the table.

mysql> select * from minttec; 

+------+------------+-----------+-------------------+ 
| id   | first_name | last_name | email             | 
+------+------------+-----------+-------------------+ 
|    1 | Ravi       | Saive     | raivsaive@xyz.com | 
|    2 | Narad      | Shrestha  | narad@xyz.com     | 
|    6 | tecmint    | [dot]com  | tecmint@gmail.com | 
+------+------------+-----------+-------------------+
3 rows in set (0.00 sec) 

mysql>

Rename Table in MySQL

Don’t you think our table name “minttec” is not very much relevant. How about changing it to tecmint_table.

mysql> RENAME TABLE minttec TO tecmint_table; 
Query OK, 0 rows affected (0.03 sec)

mysql>

List all Tables

See all the tables under the current database.

mysql> show tables; 

+-------------------+ 
| Tables_in_tecmint | 
+-------------------+ 
| tecmint_table     | 
+-------------------+
1 row in set (0.00 sec) 

mysql>

The table has been renamed. Now take a backup of the above MySQL database, in a single line of command without any sophisticated tool. Run the below code at your terminal and not on mysql prompt.

# mysqldump -u root -p tecmint > tecmint.sql

check the dumped file on your desktop which would have contents something like
-- MySQL dump 10.13  Distrib 5.5.31, for debian-linux-gnu (i686) --
-- Server version 5.5.31-0+wheezy1 -- 
Dump completed on 2013-09-02 12:55:37

It is always a good idea to maintain Backup of MySQL databases. Restoring the backed up MySQL Data is again a simple line of code you need to run at your terminal prompt and not at your mysql prompt.

But, wait first we will delete the database to verify if our restore is perfect.

Delete a Database

mysql> drop database tecmint; 
Query OK, 1 row affected (0.02 sec)

Check for database ‘tecmint’ on your database server.

mysql> show databases; 

+--------------------+ 
| Database           | 
+--------------------+ 
| information_schema | 
| my_database        | 
| mysql              | 
| performance_schema | 
| phpmyadmin         | 
| sisso              | 
| test               | 
+--------------------+

7 rows in set (0.00 sec) 
mysql>

Great! The database is lost, but we need not to worry, we are having the backup.

Restore a Database

To restore lost database, run the following command.

# mysql -u root -p tecmint < tecmint.sql
Enter password:
ERROR 1049 (42000): Unknown database 'tecmint'

OOPS! An error, hey we have not create the database tecmint. So go to your mysql prompt and create a database ‘tecmint‘.

mysql> create database tecmint; 
Query OK, 1 row affected (0.00 sec) 

mysql>

Now time to run restore command at your shell prompt (strictly).

# mysql -u root -p tecmint < tecmint.sql 
Enter password:

Verify your database.

mysql> show databases; 

+--------------------+ 
| Database           | 
+--------------------+ 
| information_schema | 
| mysql              | 
| performance_schema | 
| tecmint            | 
| test               | 
+--------------------+ 
8 rows in set (0.00 sec)

Verify the contents of database.

mysql> show tables from tecmint;

+-------------------+ 
| Tables_in_tecmint | 
+-------------------+ 
| tecmint_table     | 
+-------------------+ 
1 row in set (0.00 sec)

mysql>

Verify the contents of your restored table.

mysql> select * from tecmint_table; 

+------+------------+-----------+-------------------+ 
| id   | first_name | last_name | email             | 
+------+------------+-----------+-------------------+ 
|    1 | Ravi       | Saive     | raivsaive@xyz.com | 
|    2 | Narad      | Shrestha  | narad@xyz.com     | 
|    6 | tecmint    | [dot]com  | tecmint@gmail.com | 
+------+------------+-----------+-------------------+

3 rows in set (0.00 sec)

This is not the end definitely, we will cover the concept of primary key, foreign key, multiple tables and running queries using simple PHP script in the next part of the article.

Source

This is the second part of a 2-article series about the essentials of MariaDB / MySQL commands. Please refer to our previous article on this topic before proceeding.

1. Learn MySQL/MariaDB Basics for Beginners – Part 1

In this second part of MySQL/MariaDB beginner series, we will explain how to limit the number of rows returned by a SELECT query, and how to order the result set based on a given condition.

Additionally, we will learn how to group the records and perform basic mathematical manipulation on numeric fields. All of this will help us to create a SQL script that we can use to produce useful reports.

Prerequisites

To begin, please follow these steps:

1. Download the employees sample database, which includes six tables consisting of 4 million records in total.

# wget https://launchpad.net/test-db/employees-db-1/1.0.6/+download/employees_db-full-1.0.6.tar.bz2
# tar xjf employees_db-full-1.0.6.tar.bz2
# cd employees_db

2. Enter the MariaDB prompt and create a database named employees:

# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 10.1.14-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> CREATE DATABASE employees;
Query OK, 1 row affected (0.00 sec)

3. Import it into your MariaDB server as follows:

MariaDB [(none)]> source employees.sql

Wait 1-2 minutes until the sample database is loaded (keep in mind we’re talking about 4M records here!).

4. Verify that the database was imported correctly by listing its tables:

MariaDB [employees]> USE employees;
Database changed
MariaDB [employees]> SHOW TABLES;
+---------------------+
| Tables_in_employees |
+---------------------+
| departments         |
| dept_emp            |
| dept_manager        |
| employees           |
| salaries            |
| titles              |
+---------------------+
6 rows in set (0.02 sec)

5. Create a special account to use with the employees database (feel free to choose another account name and password):

MariaDB [employees]> CREATE USER empadmin@localhost IDENTIFIED BY 'empadminpass';
Query OK, 0 rows affected (0.03 sec)

MariaDB [employees]> GRANT ALL PRIVILEGES ON  employees.* to empadmin@localhost;
Query OK, 0 rows affected (0.02 sec)

MariaDB [employees]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

MariaDB [employees]> exit
Bye

Now login as empadmin user into Mariadb prompt.

# mysql -u empadmin -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 10.1.14-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> USE employees;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

Make sure all of the steps outlined in the above image have been completed before proceeding.

Ordering and Limiting the Number of Rows in the Result Set

The salaries table contains all the incomes of each employee with start and end dates. We may wish to view the salaries of emp_no=10001 over time. This will help answer the following questions:

1. Did he / she get any raises?
2. If so, when?

Execute the following query to find out:

MariaDB [employees]> SELECT * FROM salaries WHERE emp_no=10001 ORDER BY from_date;
+--------+--------+------------+------------+
| emp_no | salary | from_date  | to_date    |
+--------+--------+------------+------------+
|  10001 |  60117 | 1986-06-26 | 1987-06-26 |
|  10001 |  62102 | 1987-06-26 | 1988-06-25 |
|  10001 |  66074 | 1988-06-25 | 1989-06-25 |
|  10001 |  66596 | 1989-06-25 | 1990-06-25 |
|  10001 |  66961 | 1990-06-25 | 1991-06-25 |
|  10001 |  71046 | 1991-06-25 | 1992-06-24 |
|  10001 |  74333 | 1992-06-24 | 1993-06-24 |
|  10001 |  75286 | 1993-06-24 | 1994-06-24 |
|  10001 |  75994 | 1994-06-24 | 1995-06-24 |
|  10001 |  76884 | 1995-06-24 | 1996-06-23 |
|  10001 |  80013 | 1996-06-23 | 1997-06-23 |
|  10001 |  81025 | 1997-06-23 | 1998-06-23 |
|  10001 |  81097 | 1998-06-23 | 1999-06-23 |
|  10001 |  84917 | 1999-06-23 | 2000-06-22 |
|  10001 |  85112 | 2000-06-22 | 2001-06-22 |
|  10001 |  85097 | 2001-06-22 | 2002-06-22 |
|  10001 |  88958 | 2002-06-22 | 9999-01-01 |
+--------+--------+------------+------------+
17 rows in set (0.03 sec)

Now what if we need to view the latest 5 raises? We can do ORDER BY from_date DESC. The DESC keyword indicates that we want to sort the result set in descending order.

Additionally, LIMIT 5 allows us to return only the top 5 rows in the result set:

MariaDB [employees]> SELECT * FROM salaries WHERE emp_no=10001 ORDER BY from_date DESC LIMIT 5;
+--------+--------+------------+------------+
| emp_no | salary | from_date  | to_date    |
+--------+--------+------------+------------+
|  10001 |  88958 | 2002-06-22 | 9999-01-01 |
|  10001 |  85097 | 2001-06-22 | 2002-06-22 |
|  10001 |  85112 | 2000-06-22 | 2001-06-22 |
|  10001 |  84917 | 1999-06-23 | 2000-06-22 |
|  10001 |  81097 | 1998-06-23 | 1999-06-23 |
+--------+--------+------------+------------+
5 rows in set (0.00 sec)

You can also use ORDER BY with multiple fields. For example, the following query will order the result set based on the employee’s birth date in ascending form (the default) and then by the last names in alphabetical descending form:

MariaDB [employees]> SELECT CONCAT(last_name, ', ', first_name) AS Name, gender AS Gender,  hire_date AS "Hire date" FROM employees ORDER BY birth_date, last_name DESC LIMIT 10;
+--------------------+--------+------------+
| Name               | Gender | Hire date  |
+--------------------+--------+------------+
| Whitcomb, Kiyokazu | M      | 1988-07-26 |
| Schaad, Ronghao    | M      | 1988-07-10 |
| Remmele, Supot     | M      | 1989-01-27 |
| Pocchiola, Jouni   | M      | 1985-03-10 |
| Kuzuoka, Eishiro   | M      | 1992-02-12 |
| Decaestecker, Moni | M      | 1986-10-06 |
| Wiegley, Mircea    | M      | 1985-07-18 |
| Vendrig, Sachar    | M      | 1985-11-04 |
| Tsukuda, Cedric    | F      | 1993-12-12 |
| Tischendorf, Percy | M      | 1986-11-10 |
+--------------------+--------+------------+
10 rows in set (0.31 sec)

You can view more information about LIMIT here.

Grouping Records / MAX, MIN, AVG, and ROUND

As we mentioned earlier, the salaries table contains the incomes of each employee over time. Besides LIMIT, we can use the MAX and MIN keywords to determine when maximum and minimum number of employees were hired:

MariaDB [employees]> SELECT CONCAT(last_name, ', ', first_name) AS Name, MAX(B.salary) AS "Max. salary" FROM employees A JOIN salaries B ON A.emp_no = B.emp_no WHERE A.emp_no IN (10001, 10002, 10003) GROUP BY A.emp_no;
+-----------------+-------------+
| Name            | Max. salary |
+-----------------+-------------+
| Facello, Georgi |       88958 |
| Simmel, Bezalel |       72527 |
| Bamford, Parto  |       43699 |
+-----------------+-------------+
3 rows in set (0.02 sec)

MariaDB [employees]> SELECT CONCAT(last_name, ', ', first_name) AS Name, MIN(B.salary) AS "Min. salary" FROM employees A JOIN salaries B ON A.emp_no = B.emp_no WHERE A.emp_no IN (10001, 10002, 10003) GROUP BY A.emp_no;
+-----------------+-------------+
| Name            | Min. salary |
+-----------------+-------------+
| Facello, Georgi |       60117 |
| Simmel, Bezalel |       65828 |
| Bamford, Parto  |       40006 |
+-----------------+-------------+
3 rows in set (0.00 sec)

Based on the above result sets, can you guess what the below query will return?

MariaDB [employees]> SELECT CONCAT(last_name, ', ', first_name) AS Name, ROUND(AVG(B.salary), 2) AS "Avg. salary" FROM employees A JOIN salaries B ON A.emp_no = B.emp_no WHERE A.emp_no IN (10001, 10002, 10003) GROUP BY A.emp_no;
+-----------------+-------------+
| Name            | Avg. salary |
+-----------------+-------------+
| Facello, Georgi |    75388.94 |
| Simmel, Bezalel |    68854.50 |
| Bamford, Parto  |    43030.29 |
+-----------------+-------------+
3 rows in set (0.01 sec)

If you agree that it will return the average (as specified by AVG) salary over time rounded to 2 decimals (as indicated by ROUND), you’re right.

If we want to view the sum of the salaries grouped by employee and return the top 5, we can use the following query:

MariaDB [employees]> SELECT emp_no, SUM(salary) AS Salary FROM salaries GROUP BY emp_no ORDER BY Salary DESC LIMIT 5;
+--------+---------+
| emp_no | Salary  |
+--------+---------+
| 109334 | 2553036 |
|  43624 | 2492873 |
|  66793 | 2383923 |
| 237542 | 2381119 |
|  47978 | 2374024 |
+--------+---------+
5 rows in set (2.22 sec)

In the above query, salaries are grouped by employee and then the sum is performed.

Bringing it all Together

Fortunately, we don’t need to run query after query to produce a report. Instead, we can create a script with a series of SQL commands to return all the necessary result sets.

Once we execute the script, it will return the required information without further intervention on our part. For example, let’s create a file named maxminavg.sql in the current working directory with the following contents:

--Select database
USE employees;
--Calculate maximum salaries
SELECT CONCAT(last_name, ', ', first_name) AS Name, MAX(B.salary) AS "Max. salary" FROM employees A JOIN salaries B ON A.emp_no = B.emp_no WHERE A.emp_no IN (10001, 10002, 10003) GROUP BY A.emp_no;
--Calculate minimum salaries
SELECT CONCAT(last_name, ', ', first_name) AS Name, MIN(B.salary) AS "Min. salary" FROM employees A JOIN salaries B ON A.emp_no = B.emp_no WHERE A.emp_no IN (10001, 10002, 10003) GROUP BY A.emp_no;
--Calculate averages, round to 2 decimal places
SELECT CONCAT(last_name, ', ', first_name) AS Name, ROUND(AVG(B.salary), 2) AS "Avg. salary" FROM employees A JOIN salaries B ON A.emp_no = B.emp_no WHERE A.emp_no IN (10001, 10002, 10003) GROUP BY A.emp_no;

Lines beginning with two dashes are ignored, and the individual queries are executed one after another. We can execute this script either from the Linux command line:

# mysql -u empadmin -p < maxminavg.sql
Enter password: 
Name	Max. salary
Facello, Georgi	88958
Simmel, Bezalel	72527
Bamford, Parto	43699
Name	Min. salary
Facello, Georgi	60117
Simmel, Bezalel	65828
Bamford, Parto	40006
Name	Avg. salary
Facello, Georgi	75388.94
Simmel, Bezalel	68854.50
Bamford, Parto	43030.29

or from the MariaDB prompt:

# mysql -u empadmin -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 10.1.14-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> source maxminavg.sql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

Summary

In this article we have explained how to use several MariaDB functions in order to refine result sets returned by SELECT statements. Once they have defined, multiple individual queries can be inserted in a script to execute it more easily and to reduce the risk of human error.

Do you have any questions or suggestions about this article? Feel free to drop us a note using the comment form below. We look forward to hearing from you!

Source