How to Manage MySQL Databases and Users from the Command Line

MySQL or MariaDB is the most popular open-source relational database system. MySQL server allows us to create numerous users and databases and grant appropriate privileges so that the users can access and manage databases.

In this post, I will explains how to use the command line to manage MySQL or MariaDB databases and users. I assuming that you already have MySQL server installed on your system and all the commands will be executed as root user.

To open the MySQL prompt, type the following command and enter the MySQL root user password when prompted:

# mysql -u root -p

Continue reading

How to Secure a SSH Server

Security is the first priority for server management. Although there were some vulnerabilities, OpenSSH is fairly secure by default. There are still some steps left that can be improved. In this post, I'll show you how to secure SSH Server.

Preparation

Backup the configuration file

Before we start making changes to our configuration, let's make a backup.

cp /etc/ssh/sshd_config /root/sshd_config

Deploy in small steps

While it makes sense to do a full deployment of your new SSH configuration to all systems, you might want to be careful. One example is that some older SSH clients can't use the newer key types. So have a look at the oldest Linux distributions that are used to get an idea on compatibility issues.

Use the SSH configuration test

If you make changes to your SSH configuration, it makes sense to restart the service. I strongly recommend to always check your configuration (sshd_config) first. This can be done by using the test mode flag. This additional step ensures the syntax and options are correct before you end up with a nonfunctioning service.

This command should not return any text or errors.

sshd -t

Continue reading

Manage Linux Users with Command passwd

Linux is a multi-user system, because it allows multiple people to use a computer and not affect each other's files, processes, preferences, etc. As there could be multiple users on the system, it is necessary to manage their authentication. In this post, I will show you how to manage users with command passwd.

The passwd command changes passwords for user accounts for basic use. A normal user may only change the password for their own account, while the superuser may change the password for any account.

[root@server ~]# passwd [options] <username>

Running the passwd command without any argument will ask for a change of password for the user logged in.

Continue reading

How to Recover MySQL Root Password

If you are a system administrator, you may have forgotten your mysql root password, don't worry, it happens to all of us. In this post, I will show you how to recover mysql root password for Linux system.

First, by running the following command to identify the version of MySQL or MariaDB, because we will use different commands to recover the root passwords:

$ mysql --version

After you've known your version of MySQL or MariaDB, following the steps to recover the root passowrd.

1. Stop the MySQL or MariaDB Service

$ systemctl stop mysql

2. Start the MySQL or MariaDB server without loading the grant tables

$ mysqld_safe --skip-grant-tables &

The ampersand & at the end of the command above will cause the program to run in the background , so you can continue to use the shell. When the --skip-grant-tables option is used, anyone can to connect to the MySQL or MariaDB server without a password and with all privileges granted.

Continue reading

Connect to a Server Using an SSH Key

In this post I will describe how to connect to remote server using SSH Key instead of password.

Prerequisites

Connect via PuTTY

putty, Connect to a Server Using an SSH Key

PuTTY is the most popular SSH client for Windows users, and it is available for all versions of Windows. Download and install it, and PuTTY is ready to use.

1. Enter the username and IP address (root@11.22.33.44, for example) in the Host Name field. If your server has a domain name, you may use that in place of the IP address.

2. Select SSH as the connection type.

Continue reading

How to Add SSH Keys to Remote Server

After generating ssh keys, we have to add public key to remote server before we use them. In this post, I will quickguide how to add ssh keys to remote server.

Add SSH Keys with ssh-copy-id Utility

The ssh-copy-id utility is pre-installed on most Linux distributions. macOS users can install it via Homebrew.

$ ssh-copy-id -i ~/.ssh/id_rsa.pub root@11.22.33.44
  • Specify the correct public key with the -i [path to public key] parameter.
  • Specify the username and server IP address (or domain name) as shown. For example, the root user at 11.22.33.44.

The utility will print some basic information and prompt for your password, enter your password and the utility will install ssh key to remote server.

Continue reading

How Do I Generate SSH Keys?

An SSH key allows you to log into your server without a password. This guide describes how to create SSH keys using a Linux or Mac system.

Create an SSH Key with OpenSSH

ssh-keygen generate ssh key

OpenSSH is standard and should be present on macOS and most Linux distributions. Follow these steps to create an SSH key with the OpenSSH utilities.

1. Generate your key with ssh-keygen using these parameters:

$ ssh-keygen -t rsa -b 4096 -C "Example comment"

Generate an RSA format key with the -t rsa parameter. For a more secure 4096-bit key, use the -b 4096 parameter. To enter a comment, use the -C [comment] parameter.

Continue reading