When you hire a new developer to manage MySQL databases then you may need to grant specific permission to manage those databases such as deleting or modifying the information. In that case, it is essential for your to know how to grant specific privileges to the MySQL user account.

In this post, we will show you how to create a MySQL user and grant specific permissions

Prerequisites

• A Linux VPS with MySQL server installed.
• Access to the root user account (or access to an admin account with root privileges)

Log in to the Server & Update the Server OS Packages

First, log in to your Debian 10 server via SSH as the root user:

ssh root@IP_Address -p Port_number

You will need to replace ‘IP_Address’ and ‘Port_number’ with your server’s respective IP address and SSH port number. Additionally, replace ‘root’ with the username of the admin account if necessary.

Before starting, you have to make sure that all OS packages installed on the server are up to date. You can do this by running the following commands:

apt-get update -y
apt-get upgrade -y

Create a New MySQL User

First, you will need to connect to the MySQL shell using the MySQL root user. You can connect it using the following command:

mysql -u root -p

You will be asked to provide your MySQL root password. Once you are connected to the MySQL shell, you should see the following output:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.25-0ubuntu0.20.04.1 (Ubuntu)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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, create a new MySQL user with the following command:

mysql> CREATE USER 'username'@'localhost' IDENTIFIED BY 'userpassword';

Where:

• username is the name of the MySQL user you want to create.
• userpassword is the password of the MySQL user.
• localhost is a host from where you want to connect to MySQL.

You will need to replace the localhost with the remote server IP address if you want to connect the MySQL from the remote server. In that case, you can create a new MySQL user with the following command:

mysql> CREATE USER 'username'@'192.168.0.100' IDENTIFIED BY 'userpassword';

• 192.168.0.100 is the IP address of the remote server.

Grant Privileges to a MySQL User Account

There are multiple types of permissions available in MySQL that you can provide to the MySQL user account. Some of the most commonly used permissions are shown below:

• ALL PRIVILEGES: – This will allow MySQL users to run any query on the specified database.
• CREATE: – This will allow MySQL users to create databases and tables.
• DELETE: – This will allow MySQL users to delete rows from the table.
• DROP: – This will allow MySQL users to drop databases and tables.
• INSERT: – This will allow MySQL users to insert rows to a specific table.
• SELECT: – This will allow MySQL users to read a database.
• UPDATE: – This will allow MySQL users to update table rows.
• GRANT OPTION: – This will allow MySQL users to grant or remove other users’ privileges.

To grant all privileges to the MySQL user account on a specific database, run the following command:

mysql> GRANT ALL PRIVILEGES ON dbname.* TO 'username'@'localhost';

To grant all privileges to the MySQL user on a specific table from a database, run the following command:

mysql> GRANT ALL PRIVILEGES ON dbname.tablename TO 'username'@'localhost';

To grant multiple privileges like, SELECT, INSERT, DELETE to the MySQL user on a specific database, run the following command:

mysql> GRANT SELECT, INSERT, DELETE ON dbname.* TO username@'localhost';

You will need to run the flush privileges command for the changes to take effect.

mysql> FLUSH PRIVILEGES;

View MySQL User Account Privileges

If you want to view the privileges assigned to the MySQL user account, run the following command:

mysql> SHOW GRANTS FOR 'username'@'localhost';

You should see the following output:

+--------------------------------------------------------------+
| Grants for username@localhost                                |
+--------------------------------------------------------------+
| GRANT USAGE ON *.* TO `username`@`localhost`                 |
| GRANT ALL PRIVILEGES ON `dbname`.* TO `username`@`localhost` |
+--------------------------------------------------------------+
2 rows in set (0.00 sec)

Remove MySQL User Account Privileges

You can use the REVOKE command to remove the permission of the MySQL user from the specific database or table.

For example, to remove ALL PRIVILEGES from the database, run the following command:

mysql> REVOKE ALL ON *.* FROM 'username'@'localhost';

Next, you will need to run the flush privileges command for the changes to take effect.

mysql> FLUSH PRIVILEGES;

Of course, you don’t have to manage MySQL server if you use one of our Managed Hosting services, in which case you can simply ask our expert Linux admins to manage the MySQL server for you. They are available 24/7 and will take care of your request immediately.

If you liked this post on How to Create a New User and Grant Privileges in MySQL, please share it with your friends on the social networks using the buttons on the left or simply leave a reply below. Thanks.

The post How To Create a New User and Grant Permissions in MySQL appeared first on LinuxHostSupport.

In this article, we will show you how to install Open Source Social Network on an Ubuntu 18.04 server.

At the time of writing this article, the latest stable version of Open Source Social Network is 5.0, and it requires:

• PHP version 5.6, 7.0 or 7.1
• MySQL 5 or higher
• Apache
• mod_rewrite Apache module
• PHP cURL
• PHP Mcrypt s
• PHP GD Extension
• PHP ZIP Extension
• JSON Support
• XML Support
• PHP setting allow_url_fopen should be enabled

If you don’t have a LAMP stack set up, don’t worry – we’ll be showing you how to install that as well.

1. Connect to your server

Before we begin, you need to connect to your server via SSH as the root user. To do this, use the following command:

ssh root@IP_Address -p port_number

of course, you will need to replace IP_Address and port_number with your actual server IP address and SSH port number.

Once logged in, make sure that your server is up-to-date by running the following commands:

sudo apt update
sudo apt upgrade

2. Install LAMP

In order to run Open Source Social Network, it needs a web server, PHP, and a database server. That’s why we chose the LAMP stack. In this step, we will install Apache, PHP, and MariaDB to fulfill the requirements of this application.

Execute the following command to install Apache2:

sudo apt install apache2

Once the Apache2 web service is installed on the server, we can use the commands below to start, stop and enable the service.

sudo systemctl stop apache2.service
sudo systemctl start apache2.service
sudo systemctl enable apache2.service

To confirm that Apache2 is installed properly, we can open a web browser and type the server IP address or domain name (we assume it is pointed to your server) – we should be able to view the Apache2 ‘Default Page’.

Now that our Apache installation is finished, let’s install MariaDB, an open-source equivalent of MySQL. Use the following command to install MariaDB on your server:

sudo apt install mariadb-server mariadb-client

Once MariaDB is installed on your server, you can use the commands below so that you can stop, start and enable the MariaDB service.

sudo systemctl stop mariadb.service
sudo systemctl start mariadb.service
sudo systemctl enable mariadb.service

The next command is optional. You can run the command if you want to secure the MariaDB server by disallowing remote root access, remove the test database and create a root password.

sudo mysql_secure_installation

When prompted, answer the questions below by following the guide.

Enter current password for root (enter for none): Just press the [Enter] key, there is no password set by default
Set root password? [Y/n]: Y
New password: Enter your password
Re-enter new password: Repeat your password
Remove anonymous users? [Y/n]: Y
Disallow root login remotely? [Y/n]: Y
Remove test database and access to it? [Y/n]: Y
Reload privilege tables now? [Y/n]: Y

Restart the MariaDB server, so the changes will take effect.

sudo systemctl restart mariadb.service

Next, we will install PHP version 7.1 from Ondřej Surý’s repository.

Please note that OSSN does not support PHP 7.2 yet. With running these commands below, you will add Ondřej Surý’s PPA.

sudo apt-get install software-properties-common
sudo add-apt-repository ppa:ondrej/php
sudo apt update

In order to install PHP 7.1 together with the modules that are required for running the OSSN, please run this command:

sudo apt install php7.1 php7.1-mysql php7.1-curl php7.1-json php7.1-cgi libapache2-mod-php7.1 php7.1-mcrypt php7.1-xmlrpc php7.1-gd php7.1-mbstring php7.1 php7.1-common php7.1-xmlrpc php7.1-soap php7.1-xml php7.1-intl php7.1-cli php7.1-ldap unzip php7.1-zip wget php7.1-readline php7.1-imap php7.1-tidy php7.1-recode php7.1-sq php7.1-intl -y

Once the installation is complete, check that all installed services work properly. If so, you can proceed to the next step.

3. Create and Configure Database on MariaDB

Now we need to create a database for OSSN. Log in to the MariaDB server with the command:

sudo mysql -u root -p

Then type the password you created in the previous step to sign in. Once you are in the MariaDB shell, you can use the following command and create a database called ossn_db for the OSSN application.

CREATE DATABASE ossn_db;

Then create a database user called ossn_user and replace Str0n9Pas$worD with your own password.

CREATE USER 'ossn_user'@'localhost' IDENTIFIED BY 'Str0n9Pas$worD';

To grant the user ossn_user with full access to the database ossn_db, run the command:

GRANT ALL ON ossn_db.* TO 'ossn_user'@'localhost' IDENTIFIED BY 'Str0n9Pas$worD' WITH GRANT OPTION;

Now we can use flush privileges operation to reload the grant tables and after that, we can exit from the MariaDB shell.

FLUSH PRIVILEGES;
EXIT;

4. Install OSSN

Use the command below to change the directory to /opt and download the Open Source Social Network.

cd /opt && wget https://www.opensource-socialnetwork.org/download_ossn/latest/build.zip

unzip the content and move the files to the Apache2 default root directory:

unzip build.zip 
sudo mv ossn /var/www/html/ossn

Create an OSSN data directory by running the commands:

sudo mkdir /var/www/html/ossn_data

Change the permissions and the ownership of the files:

sudo chown -R www-data:www-data /var/www/html/ossn/
sudo chmod 755 /var/www/html/ossn/
chown -R www-data:www-data /var/www/html/ossn_data

5. Configure Virtual Host for OSSN

Since we have Apache installed on your server, we can continue and show you how to create a virtual host for your domain that you want to use. We will use nano as our editor, but if you do not prefer nano, you can use any editor of your choice and create a new configuration file called ossn.conf

sudo nano /etc/apache2/sites-available/ossn.conf

Then paste the configuration from below into the file, and replace domain-name.com with your actual domain name.

<VirtualHost *:80>
     ServerAdmin admin@domain-name.com
     DocumentRoot /var/www/html/ossn
     ServerName domain-name.com
     
          Options FollowSymlinks
          AllowOverride All
          Require all granted

     ErrorLog ${APACHE_LOG_DIR}/error.log
     CustomLog ${APACHE_LOG_DIR}/access.log combined
</VirtualHost>

After you finish editing the file, save and close it.

Once you have configured the virtual host, you can enable it by executing the following commands.

Disable the default virtual host with:

sudo a2dissite 000-default

then, enable the OSSN virtual host:

sudo a2ensite ossn.conf

Also, you need to make sure that the mod_rewrite Apache module is enabled:

sudo a2enmod rewrite

Restart Apache for the changes to take effect.

sudo systemctl restart apache2.service

6. Access Open Source Social Network

Open your web browser and type your domain:
http://domain-name.com/
If you closely followed this article, you should be able to see the OSSN Installation wizard, as shown on the image below.

During the installation, you will create an administrator account that you can use it to manage the OSSN application. You can access the back-end by adding /administrator to the end of the URL.

http://domain-name.com/administrator

The administrator dashboard will look as shown on the following image.

Finally, you can access your http://domain-name.com and log in so you can create your first post.

Congratulations! You have successfully installed Open Source Social Network on your server.

In this article, we showed you how to install Apache2, PHP 7.1, MariaDB Database Server, created an OSSN database and of course installed Open Source Social Network. Now you can start creating your own private community and connect with your friends with this wonderful social media platform.

Of course, if you are one of our Managed Ubuntu Support customers, you don’t have to install Open Source Social Network on your Ubuntu 18.04 VPS – simply ask our admins, sit back, and relax. Our admins will install and configure Open Source Social Network on Ubuntu 18.04 for you immediately.

PS. If you liked this post about how to install Open Source Social Network on an Ubuntu 18.04 VPS, please share it with your friends on the social networks using the share buttons below, or simply leave a comment in the comments section. Thanks.

The post How to Install Open Source Social Network on Ubuntu 18.04 appeared first on LinuxHostSupport.

At LinuxHostSupport, we use a lot of open source tools to help our admins with their work, but we’ll focus on the best and those that will most likely be useful to other Linux admins too. We’ll list out some command line tools and open source software we use on a daily basis and the reason why we are using them. Note that we frequently try and test new tools, so our current setup may be different than the one written in this post. You may get an idea out of this. The way we use a certain software, script or application may not be the way it was intended to be used, but when you are a sysadmin you get creative and think out of the box.

Being responsible for so many servers is something that we as system administrators thrive on. However it sure knows to be stressful and among others things, it requires problem-solving skills, competence, and perseverance, the latter especially in situations where you are about to go berserk because you can’t seem to find a solution for a client’s unreachable website in the middle of a planned marketing campaign and sale.

During debugging there are many command line tools that can be used to narrow down the possible solutions for a problem. Of course, every system admin out there has his/her own set of preferred commands and tools of use and it is really a matter of personal choice. Having said that, in this post, we will share the tools that we mostly use on a daily basis when handling and troubleshooting system, network and DNS related problems.

The day to day tasks and responsibilities that every system admin has involve constantly monitoring the network and the systems that use the network, ensuring their stability and reliability in the process. We will use examples to explain a problem with a website, server or service and the tool that we use to debug and eventually solve the problem.

Command line tools

So let’s start with one of the basic commands for network troubleshooting, ping. Using the “ping” command is the best way to test connectivity between a source computer and a specified destination computer. Ping uses ICMP (Internet Control Message Protocol) to communicate to other devices. You can use ping in many ways, for example, if you want to check the IP address that LinuxHostSupport.com resolves to, you can use:

# ping linuxhostsupport.com

The output will show the IP address of the domain, the round trip time, the packets loss value and the approximate round trip time.

The traceroute command is a network troubleshooting utility which shows the number of hops taken to reach a destination while determining the packets traveling path. So if for example, you cannot open a certain website and you are sure that the website is online for others, you can use the traceroute command to check if there is any packet loss along the way.

For a website to be shown in your web browser, the DNS records for that domain have to be correct. The domain must be pointed to the server where the data for the website is hosted. So when troubleshooting an inaccessible website we use the dig command.

dig (domain information groper) is a network administration command-line tool for querying Domain Name System (DNS) name servers.

So, for example, to query a domain A record from the Google public DNS server, we will use:

# dig A www.example.com @8.8.8.8

The answer will provide us with information that will help us narrow down the possible reasons on why the website is offline. If for example the domain is not pointed correctly or at all, we will know that the problem is an inaccurate DNS record. Then we will take the required measures or inform the domain owner of the solution.

Another useful tool that most sysadmins use is: find. It is a Linux command that can be used to search for files or a specific text string in a file or files.

To search for a specific file the below command can be used:

# find ./rosehosting -name “linuxvps.txt”

or if you need to find hidden files:

# find ~ -type f -name ".*"

The find command can find usage in multiple scenarios and it helps sysadmins significantly speed up the search for a file, directory or string in files.

A VPS has its own dedicated resources. Monitoring the server resource usage can be accomplished using the top command which provides a dynamic real-time view of the running system. It displays system summary information with a list of processes or threads currently being managed by the kernel.

What happens if you type top in your command line interface? You will get a display similar as the one in the image below, of course with your server’s current resource usage and processes.

When in need of checking a server network connections (both incoming and outgoing), routing tables, and a number of network interfaces, the netstat command can be used. You can use this command in many scenarios for different problems or inspections. Let’s say that we need to see all active connections and the TCP and UDP ports on which the server is listening. We will type:

# netstat -anlp

The output will show us all the ports that the server services listen on and will list all the active connections.

In certain cases where we needed to access a website from a terminal, we used elinks. It is a free text-based console web browser for Unix-like operating systems that renders both frames and tables and is highly customizable.

And last but not least tcpdump. It is a great packet analyzer that can be used to display TCP/IP and other packets being transmitted or received over a network. With tcpdump we analyze the packets’ content on a network interface with matched flags and boolean expressions.

These commands that we listed above, albeit great are not the only ones that we and sysadmins over the world use on a daily basis. Also, their usage is just a glimpse of the possibilities they offer and due to the nature of this brief informative post, we covered only the basics.

A lot of code editors

Code editors. There are so many out there! Vim, Nano, Brackets, Atom, Visual Studio Code… Every editor is highly customizable and has lots of plugins available. We use different code editors – we can’t really pick a favorite since everyone uses a different editor for different purposes. Nevertheless, a more powerful and flexible code editor is a must-have for every sysadmin, and you have a lot of open source code editors to choose from. There are even some great browser based code editors.

Nagios

Working with tons of servers requires careful monitoring of their services and reliability. A handy, no scratch that, a very handy monitoring software that we use on a daily basis is Nagios. Nagios is a robust, open source and very powerful monitoring system that monitors systems, networks and infrastructure. It offers monitoring and alerting services for servers, switches, applications and services. Nagios watches what you’ll set it to monitor and then alerts you when things go wrong until the problem is resolved after which again, alerts for a second time that the problem is gone.

Few of Nagios standout features are:

• Simple installation;
• Flexibility
• Scalable;
• Easy to use and navigate interface;
• A plethora of plugins;
• Alert system and event handlers that help with problem-solving;
• Reporting system;
• Available data graphing plugins;
• Parallelized service checks;
• Flat-text formatted configuration files;
• Automatic log file rotation and much more…

Nagios is quite powerful and flexible, however, the learning curve can be high. The initial setup and configuration usually takes a lot of work, but once you get a hold of it and become familiar with how it works and what it can do for you, you’ll never stop using it.

Of course, there are some other great monitoring services such as Zabbix, Icinga, Cacti, Observium Sensu etc.

Ansible

Ansible is a simple but powerful automation tool written in python that automates provisioning, application deployment, and configuration management. With Ansible you do not need to deploy agents on the remote machines, all you need is ssh access to your servers. We are big fans of Ansible because of its simplicity, speed and easy learning curve.

Nextcloud

Sometimes you need to synchronize and share valuable files or maybe you just need your privacy. With Nextcloud, you can share files, calendars, contacts, mail and more on a self-hosted, private server. It’s highly configurable and you get to keep your privacy when you share data in your own cloud. We find great usage in Nextcloud and they are contsantly updating the software with lots cool new features.

What open source tools do you use? Leave a comment below, we’d love to hear about them. We may publish another similar post, as we use a lot of other apps and open source software.

PS. If you liked this post, please share it with your friends on the social networks using the buttons below or simply leave a comment. Thanks.

The post Open source sysadmin tools we use on a daily basis appeared first on LinuxHostSupport.