How do i start mysql database in linux?

Question

While the are many ways to create databases, system administrators benefit from knowing how to create a MySQL database in Linux via the command line. Knowing more than one way to accomplish a task provides flexibility in case the usual way you perform it is not an option. Use this tutorial to create a MySQL database via the command line.

Table of Contents Show

Prerequisites
Create MySQL Database on Linux using Command Line
View All MySQL Databases
Wrapping Up
Linux databases
How to set up a database in Linux
Create a first database
Create a new database user with permissions
How do I start MySQL on Linux?
How do I start MySQL database?
How do I start and stop MySQL in Linux?
How do I start MySQL from command line?

Prerequisites

Server running CentOS or AlmaLinux.
Root-level access to the server.

Create MySQL Database on Linux using Command Line

Step 1:

Log into the MySQL server from the command line with the following command, specifying the user root with the -u flag, and prompt for a password using the -p flag. Enter your current password once prompted to complete the login.

You can change the MySQL password for the root user or any other user in the database via the command line.

A prompt displays like the one below once you log in.

Step 2:

To create a database with the name tutorial_database, type the following command.

CREATE DATABASE tutorial_database;

If a database of the same name already exists, the system will not create a new database, and you will receive this error.

ERROR 1007 (HY000): Can't create database 'tutorial_database'; database exists

You can avoid this error by using the following command. It only creates the database tutorial_database if a database of that name does not already exist.

CREATE DATABASE IF NOT EXISTS tutorial_database;

View All MySQL Databases

Use the following command to view the database you've created.

Here is the resulting output.

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
| tutorial_database  |
+--------------------+
4 rows in set (0.00 sec)

Wrapping Up

Creating a MySQL database from the Linux command line is an easy process. System administrators would do well to have this process in their toolkit to add flexibility to their workflow.

Every Linux admin must install and set up a database at some point. This can include deploying a dynamic website, such as for WordPress, or storing data for web applications as well as customer, client and employee records. Databases are crucial for every type of business.

Linux presents the best platform for databases. It's a reliable and open source OS with plenty of pre-made databases already available for installation. Of these pre-made databases, MySQL database server is the most popular.

Linux databases

The databases available for Linux range from small, embedded tools such as SQLite to powerful relational databases such as MySQL and even NoSQL databases for big data. Some of the more popular databases available for Linux include:

PostgreSQL
MariaDB
IBM Db2
Oracle Database
Firebird
Amazon Relational Database Service
Redis
Ingres
Hadoop Distributed File System

MySQL represents a complete relational database management system, which means it includes everything necessary for the storage and retrieval of data. It uses the SQL query language, so anyone familiar with that language should be able to use this database easily. Even for those unfamiliar with the SQL language, SQL is designed to be easy to learn and readable.

How to set up a database in Linux

To install and set up a MySQL database on Linux -- specifically, Ubuntu Server 20.04 -- start by logging into Ubuntu Server and install MySQL with the command:

sudo apt-get install mysql-server -y

Once the installation completes, start and enable the server with the command:

sudo systemctl enable --now mysql

MySQL should now be installed and running. However, the MySQL admin account comes without a password, which presents a security issue. To secure the MySQL installation, issue the command:

sudo mysql_secure_installation

The system first asks if you want to enable the "Validate Password" component, which requires all accounts to use very strong passwords. Type Y to enable this component; otherwise, hit any other key. Even if you don't enable this component, you can still use strong passwords.

When you secure your MySQL installation, you should see the following, which asks whether MySQL requires passwords of a certain strength.

After this step, the system prompts you to type and verify a password for the MySQL admin user. Do that and then answer Y for the remaining questions:

Remove anonymous users?
Disallow root login remotely?
Remove test database and access to it?
Reload privilege tables now?

Once you have the MySQL database server secured, you can create your first database.

Create a first database

In this tutorial, create a database named techtarget. In order to do that, log in to the MySQL console with the command:

sudo mysql -u root -p

The options are:

-u, which defines a user (in this case, the root user)
-p, which prompts for a password

If prompted for the sudo password, type that first and then the MySQL root user password when prompted. You will then find yourself at the MySQL console.

From the MySQL console, you can create your first Linux database.

The MySQL console is ready for you to create your first database.

To create the techtarget database, issue the command:

CREATE DATABASE techtarget;

To verify the database's creation, issue the command:

show databases;

You should see techtarget listed (Figure 3).

With the show databases; command, you should see all available MySQL databases.

Create a new database user with permissions

Next, create a user with permission to access the new database. In this example, create the user techtargetuser with the command:

CREATE USER 'techtargetuser'@'localhost' IDENTIFIED BY 'PASSWORD';

PASSWORD represents a strong or unique password.

When you create a new user, the user doesn't automatically have access to the database. For example, when you install WordPress, using the techtarget database and the MySQL admin user presents a security risk. The techtarget database and the techtargetuser account helps mitigate that risk when you give the user access to the database.

Give the user access to the database with the command:

GRANT ALL ON techtarget.* To 'techtargetuser'@'localhost' WITH GRANT OPTION;

This command grants all permissions on the techtarget database -- and any or all tables, using .* -- to techtargetuser on the hosting machine, with the ability to give other users any necessary privileges -- handled by GRANT OPTION.

With that in place, when you install WordPress, configure the database details as such:

Database: techtarget
Database user: techtargetuser

Once you install a database, you can use your database server to work with all types of applications and services. In most cases, the applications that require MySQL -- such as WordPress and Nextcloud -- populate those databases during the installation, so you don't have to manually add tables and data.

How do I start MySQL on Linux?

Set Up a MySQL Database on Linux.

Install a MySQL server. ... .

Configure the database server for use with Media Server: ... .

Add the MySQL bin directory path to the PATH environmental variable by running the command: export PATH=$PATH:binDirectoryPath. ... .

Start the mysql command-line tool..

How do I start MySQL database?

Windows – Start and Stop Server.

Open 'Run' Window by using Win key + R..

Type 'services.msc'.

Now search for MySQL service based on the version that is installed..

Click on 'stop', 'start' or 'restart' the service option..

How do I start and stop MySQL in Linux?

To Start or Stop MySQL.

To start MySQL: On Solaris, Linux, or Mac OS, use the following command: Start: ./bin/mysqld_safe --defaults-file= install-dir /mysql/mysql.ini --user= user. ... .

To stop MySQL: On Solaris, Linux, or Mac OS, use the following command: Stop: bin/mysqladmin -u root shutdown -p..

How do I start MySQL from command line?

Launch the MySQL Command-Line Client. To launch the client, enter the following command in a Command Prompt window: mysql -u root -p . The -p option is needed only if a root password is defined for MySQL. Enter the password when prompted.

kode mysql Restart MySQL Debian MySQL Tutorial