This tutorial will guide you with the installation of the MariaDB server on the Ubuntu 22.04 Linux system.

1. Configure Repository

The MariaDB packages are available in default Ubuntu repositories. Also, MariaDB provides an official repository to install the latest version on Ubuntu systems.

In order to configure MariaDB Apt repository, open a terminal with a sudo privileged account and run the following commands.

sudo apt install software-properties-common dirmngr apt-transport-https 
sudo apt-key adv --fetch-keys 'https://mariadb.org/mariadb_release_signing_key.asc' 
sudo add-apt-repository 'deb [arch=amd64,arm64,ppc64el] https://atl.mirrors.knownhost.com/mariadb/repo/10.8/ubuntu jammy main' 

The above commands will create a repository file under /etc/apt/sources.list.d directory.

2. Installing MariaDB on Ubuntu

Next, execute the following commands to install the MariaDB server and client on your Ubuntu system.

sudo apt update 
sudo apt install mariadb-server 

Press ‘y’ and hit Enter, when prompted for the confirmation.

Once the installation is finished, execute the mysql_secure_installation script to secure MariaDB server and set a password for root account.

sudo sudo mysql_secure_installation 

Follow the on-screen instructions to complete the security wizard.

3. Manage MariaDB Service

MariaDB creates a systemd configuration file to manage its service. Use the following commands to stop, start, or restart the MariaDB service.

sudo systemctl start mariadb 
sudo systemctl stop mariadb 

To view the current status of MariaDB service, type:

sudo systemctl status mariadb 

4. Connect to MariaDB

You can get a MariaDB shell to create users, databases, and other related activities. To connect shell, type:

mysql -u root -p 

Type the MariaDB root user password you set with the mysql_secure_installation command.

Output:
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 19
Server version: 10.8.3-MariaDB-2ubuntu1 Ubuntu 22.04

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

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

MariaDB [(none)]>

Conclusion

This tutorial helped you to install and configure the MariaDB server on the Ubuntu system. Now you can create databases for your awesome applications. We strongly recommended creating a separate user account for the databases.

The post How to Install MariaDB on Ubuntu 22.04 appeared first on TecAdmin.

In this article you will learn to find accounts in a MySQL server and remote unnecessary user accounts.

View Current Users

Connect to the existing MySQL server using administrative privileged account.

mysql -u root -p 

Next, list all the available user account in MySQL server. Default database named “mysql” contains system related details including user account under “user” table.

SELECT User,Host FROM mysql.user; 

List out the account with hostname to delete or all unused accounts no longer required.

Drop/Delete MySQL User

Use DROP USER statement is used to delete one or more accounts from MySQL. It also removes privilege rows for the account from all grant tables. You must have specified username along with hostname as per showing in above screenshot.

For example, to remove user “dummy” with host “localhost”, execute below query:

DROP USER 'dummy'@'localhost';

The above command drop only those user from MySQL maches both username and hostname.

Let’s use another example to delete user “myuser” with hostname “%”, execute below query.

DROP USER 'myuser'@'%';

That’s it. Repeat the same command to remove more accounts no longer required.

Conclusion

This tutorial describes you to how to remove user accounts from MySQL database server.

The post How to Delete A MySQL User Account appeared first on TecAdmin.

This tutorial will help you to install the MariaDB on Ubuntu 20.04 LTS system.

Prerequisites

• Running Ubuntu 20.04 system
• Login access with root or sudo privileged account

Step 1 – Setup Repository

The Default packages repositories contain old versions of the MariaDB server. So, it will be good to use the official repository, which provides the latest version of packages.

First, add the package signing key to your system by running commands:

sudo apt install software-properties-common
sudo apt-key adv --fetch-keys 'https://mariadb.org/mariadb_release_signing_key.asc'

Then, create a apt configuration file on your Ubuntu system. To create file run command:

sudo nano /etc/apt/sources.list.d/mariadb.list

Add the below snippet

# MariaDB 10.4 Repository
deb [arch=amd64] http://nyc2.mirrors.digitalocean.com/mariadb/repo/10.4/ubuntu focal main
deb-src http://nyc2.mirrors.digitalocean.com/mariadb/repo/10.4/ubuntu focal main

Save file and close.

Step 2 – Install MariaDB on Ubuntu

The official MariaDB repository contains the latest version of MariaDB server packages.

Once you added the package repository, run the following commands to install MariaDB server packages and required dependencies.

sudo apt update
sudo apt install mariadb-server

The above command will install the MariaDB server, client tools, and other required packages on your system.

Step 3 – Post Installation Setup

MariaDB package provides a command-line utility to complete post-installation steps. This includes configuring a password for the root user and removing unnecessary databases from the default installation.

To run the MariaDB post installation wizard, type:

sudo mysql_secure_installation

A wizard will prompt for some inputs to set up the MariaDB root user password. Secure your MariaDB installation by removing anonymous users, disabling root login from a remote host, and removing test databases.

A preferred choice is to say yes (‘y’) for all the question:

• Enter current password for root (enter for none): [ JUST PRESS ENTER ]
• Set root password? [Y/n] y
• New password: [ ENTER A SECURE PASSWORD ]
• Re-enter new password: [ CONFIRM 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

Step 4 – Connect to MariaDB

MariaDB packages provide a command-line utility to connect database servers via the command line. Where you can completely manage your database server.

To connect MariaDB server run below command on terminal:

mysql -u root -p

Enter the password for the root account configured in the above step.

Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 57
Server version: 10.4.13-MariaDB-1:10.4.13+maria~focal-log mariadb.org binary distribution

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

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

MariaDB [(none)]>

Step 5 – Manage Mariadb Service

The latest version of Ubuntu systems uses systemd to manage services. You can use the following commands to manage the MariaDB service on your system.

sudo systemctl start mariadb         # To start service 
sudo systemctl stop mariadb          # To stop service 
sudo systemctl status mariadb        # To check status of service 
sudo systemctl restart mariadb       # To stop and then start service 

Step 6 – Create Database and User (Optional)

This is an optional step for you. The below commands help you to create a database, a user, and assign privilege to the user on the database.

To use the below commands, first, you must be connected to the MariaDB server via the command line.

CREATE DATABASE mydb;
CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'my_secure_password';
GRANT ALL ON mydb.* TO 'myuser'@'localhost';
FLUSH PRIVILEGES;

Conclusion

In this tutorial, you have learned about to install MariaDB on Ubuntu Linux system. Next, you can install phpMyAdmin to administer MySQL server via web based interface.

The post How to Install MariaDB 10.7 on Ubuntu 20.04 appeared first on TecAdmin.

Step 1 – Add MariaDB Yum Repository

First add MariaDB yum repository in our system. Create a new repo file /etc/yum.repos.d/mariadb.repo in your system and add below code as per your operating system and architecture.

sudo vi /etc/yum.repos.d/mariadb.repo

[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.4/rhel8-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

Step 2 – Install MariaDB on CentOS 8

Let’s use the following command to install MariaDB 10.4 in your system. This will also install other dependencies automatically.

sudo dnf makecache
sudo dnf install MariaDB-server MariaDB-client --disablerepo=AppStream

After installing MariaDB in your system start it’s service using the following command.

sudo systemctl start mysql.service

Step 3 – Secure MariaDB Install

You also need to secure your MariaDB installation using passwords and do some other changes. To do this run secure installation script from command line.

sudo /usr/bin/mysql_secure_installation

The secure installation script will ask for user input as some points, follow the installation as per below output showing, All user inputs are highlighted with red color.

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

Enter current password for root (enter for none):
OK, successfully used password, moving on...

Set root password? [Y/n] y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
 ... Success!

Remove anonymous users? [Y/n] y
 ... Success!

Disallow root login remotely? [Y/n] y
 ... Success!

Remove test database and access to it? [Y/n] y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reload privilege tables now? [Y/n] y
 ... Success!

Cleaning up...
Thanks for using MariaDB!

Step 4 – Working with MariaDB

After installing and completing the configuration, connect to a MariaDB server using the following command.

sudo mysql -u root -p

Also try to create a new database, user and assign privileges to a database.

## CREATE DATABASE
MariaDB [(none)]> CREATE DATABASE mydb;
 
## CREATE USER ACCOUNT
MariaDB [(none)]> CREATE USER 'dbuser'@'localhost' IDENTIFIED BY 'secret';
 
## GRANT PERMISSIONS ON DATABASE
MariaDB [(none)]> GRANT ALL ON mydb.* TO 'dbuser'@'localhost';
 
##  RELOAD PRIVILEGES
MariaDB [(none)]> FLUSH PRIVILEGES;

You may also required install phpMyAdmin to manage MariaDB using web interface, which provides easy way to work.

The post How to Install MariaDB 10.4 on CentOS 8 & RHEL 8 appeared first on TecAdmin.

This article will help you to Install MariaDB 10.4 on Debian 9 (Stretch) via apt package manager.

Step 1 – Prerequsiteis

You can find the MariaDB Apt configuration file from the official website. Import the package singin key for MaraiDB packages on your system.

sudo apt-get install software-properties-common dirmngr
sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xF1656F24C74CD1D8

After this, create a new Apt configuration file for MariaDB with the following command. You can use any text editor than vim.

sudo vim /etc/apt/sources.list.d/mariadb.list

# MariaDB 10.4 for Debain 9
deb [arch=amd64,i386,ppc64el] http://sfo1.mirrors.digitalocean.com/mariadb/repo/10.4/debian stretch main
deb-src http://sfo1.mirrors.digitalocean.com/mariadb/repo/10.4/debian stretch main

Step 2 – Install MariaDB on Debian 9

After adding the repository to your system and use the following commands to install MariaDB-server on your Ubuntu system. This will remove any previous version of MariaDB packages and install the latest packages on the system. Make sure you have proper backups of databases before running the below commands.

sudo apt update
sudo apt install mariadb-server

This will also install other required dependencies on your system.

While installing MariaDB using above command installer will prompt for MariaDB root account password. Enter a strong password as below.

This will also ask for confirm password. Enter the same as above.

Step 3 – Verify MariaDB Setup

After the successful installation of MariaDB on the Debian 9 (Stretch) system. The installer will also start service during the install process. You can verify service by running the below command.

MariaDB also creates a reference for service with name MySQL. You can change MariaDB with MySQL as per your choice.

sudo systemctl status mariadb

You will see the running status like:

Then connect to MariaDB shell by running below command. You need to enter the password configured in the above step.

mysql -u root -p

Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.1.26-MariaDB-0+deb9u1 Debian 9.1

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

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

MariaDB [(none)]>

Step 4 – Manage MariaDB Service

Sometimes you need to stop or start MariaDB service. Use the following commands to stop, start, check status and restart MariaDB service.

sudo systemctl stop mariadb.service      # To Stop MariaDB service 
sudo systemctl start mariadb.service     # To Start MariaDB service 
sudo systemctl status mariadb.service    # To Check MariaDB service status 
sudo systemctl restart mariadb.service   # To Stop then Start MariaDB service 

The post How To Install MariaDB on Debian 9 (Stretch) appeared first on TecAdmin.

Step 1 – Add MariaDB Yum Repository

First add MariaDB yum repository in our system. Create a new repo file /etc/yum.repos.d/mariadb.repo in your system and add below code as per your operating system and architecture.

For CentOS/RHEL – 7

[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.4/rhel7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

For CentOS/RHEL – 6

[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.4/rhel6-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

For Fedora – 30/29/28

Please change the version (red highlighted) in the below setting as per the version of Fedora you used. Click here to view all available repositories.

[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.4/fedora30-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

Step 2 – Install MariaDB Server

Let’s use the following command to install MariaDB 10.4 in your system. This will also install other dependencies automatically.

#### For CentOS/RHEL 7/6
sudo yum install MariaDB-server MariaDB-client

#### For Fedora
sudo  dnf install MariaDB-server MariaDB-client

After installing MariaDB in your system start it’s service using the following command.

## CentOS/RHEL 7 
sudo systemctl start mysql.service

## CentOS/Redhat 6 
sudo service mysql start

Step 3 – Secure MariaDB Install

You also need to secure your MariaDB installation using passwords and do some other changes. To do this run secure installation script from the command line.

sudo /usr/bin/mysql_secure_installation

The secure installation script will ask for user input as some points, follow the installation as per below output showing, All user inputs are highlighted with red color.

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

Enter current password for root (enter for none):
OK, successfully used password, moving on...

Set root password? [Y/n] y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
 ... Success!

Remove anonymous users? [Y/n] y
 ... Success!

Disallow root login remotely? [Y/n] y
 ... Success!

Remove test database and access to it? [Y/n] y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reload privilege tables now? [Y/n] y
 ... Success!

Cleaning up...
Thanks for using MariaDB!

Step 4 – Working with MariaDB

After installing and completing the configuration, connect to a MariaDB server using the following command.

sudo mysql -u root -p

Also try to create a new database, user and assign privileges to a database.

## CREATE DATABASE
MariaDB [(none)]> CREATE DATABASE mydb;
 
## CREATE USER ACCOUNT
MariaDB [(none)]> CREATE USER 'dbuser'@'localhost' IDENTIFIED BY 'secret';
 
## GRANT PERMISSIONS ON DATABASE
MariaDB [(none)]> GRANT ALL ON mydb.* TO 'dbuser'@'localhost';
 
##  RELOAD PRIVILEGES
MariaDB [(none)]> FLUSH PRIVILEGES;

You may also required install phpMyAdmin to manage MariaDB using web interface, which provides easy way to work.

The post How to Install MariaDB 10.4 on CentOS/RHEL 7/6 appeared first on TecAdmin.

rahul@tecadmin:~$ mysql -p mydb < mydb.sql

Enter password:
ERROR 2006 (HY000) at line 122732: MySQL server has gone away

Solution:

To solve this problem increase max_allowed_packet size in your mysql configuration. Edit configuration file my.cnf and add following value under [mysql] section.

  max_allowed_packet=64M

Set the value as per your requirement and restart MySQL service.

The post ERROR 2006 (HY000) at line XXX: MySQL server has gone away appeared first on TecAdmin.

/etc/my.cnf 
/etc/mysql/my.cnf 
$MYSQL_HOME/my.cnf 
[DATADIR]/my.cnf 
~/.my.cnf 

In case my.cnf file is available in more than one place, the system load each file and overrides configuration values.

The post How To Find MySQL my.cnf File Location appeared first on TecAdmin.

CREATE INDEX:-

This will creates index on mysql table. This will allow duplicate values also.

CREATE INDEX index_name ON table_name (COLUMN_NAME1, COLUMN_NAME2, ...)

CREATE UNIQUE INDEX:-

This will creates index on mysql table. With uses of UNIQUE keyword this will not allow duplicate values.

CREATE UNIQUE INDEX index_name ON table_name (COLUMN_NAME1, COLUMN_NAME2, ...)

1. CREATE INDEX Examples:-

For this example I have a table named Users with thousands of records in mysql database. The table structure is as following in screenshot.

Now use following command to create index named Users_Idx1 on table Users based on column username.

mysql> CREATE INDEX Users_Idx1 ON Users (username);

Use UNIQUE keyword with the query to create index with unique records.

mysql> CREATE UNIQUE INDEX Users_Idx1 ON Users (username);

2. SHOW INDEX Examples:-

Use following sql command to list all indexes for a mysql table. The below query will delete Users_Idx1 index from Users table in current database.

mysql> SHOW INDEX FROM Users;

3. DELETE INDEX Examples:-

Use following sql command to delete index on mysql table.

mysql>  ALTER TABLE Users DROP INDEX Users_Idx1;

The post How to Create, List or Drop Indexes on MySQL Table appeared first on TecAdmin.

This article will help you to how to enable or disable foreign key check in MySQL. Ever you faced foreign key error, Disable foreign key checks, do you work and enable it again.

Disable Foreign Key Checks

SET FOREIGN_KEY_CHECKS=0;

Enable Foreign Key Checks

SET FOREIGN_KEY_CHECKS=1;

Remember that, do not forgot to re-enable foreign key checks again after doing your work.

The post How to Temporarily Disable Foreign Key Checks in MySQL appeared first on TecAdmin.