How to setup a MySQL server on Ubuntu

Posted on September 5, 2024

MySQL is a popular open-source relational database management system (RDBMS) that allows you to store, manage, and retrieve data in a structured and organized way. Here’s a brief overview:

Key Features:

  • Relational database: MySQL stores data in tables with well-defined relationships between them.
  • SQL support: MySQL uses Structured Query Language (SQL) for querying and managing data.
  • Cross-platform: MySQL runs on multiple operating systems, including Windows, Linux, and macOS.
  • High performance: MySQL is optimized for high-speed data retrieval and storage.
  • Scalable: MySQL can handle large volumes of data and scale horizontally (add more servers) or vertically (increase server power).

Common Use Cases:

  • Web applications: MySQL is a popular choice for web applications, especially those built with PHP, Python, and Ruby.
  • Content management systems: MySQL is used by popular CMS platforms like WordPress, Joomla, and Drupal.
  • E-commerce platforms: MySQL is used by e-commerce platforms like Magento and WooCommerce.
  • Data analysis: MySQL can be used for data analysis and business intelligence applications.

Installation

Prerequisites

  • Ubuntu installation (any version)
  • Sudo privileges

1) Install MySQL Server

Open a terminal and run the following command:

sudo apt update
sudo apt install mysql-server

2) Secure MySQL Installation

Run the included security script to restrict access and set a root password:

sudo mysql_secure_installation

Follow the prompts to:

  • Set a root password
  • Remove anonymous users
  • Disallow root login remotely
  • Remove test database
  • Reload privilege tables

3) Start and Enable MySQL Service

Start the MySQL service and enable it to start automatically on boot:

sudo systemctl start mysql
sudo systemctl enable mysql

4) Log in to MySQL shell

Log in to the MySQL shell as the root user:

sudo mysql -u root -p

Enter the root password you set earlier if it was entered in Step-2, else press ENTER.

5) Set MySQL root password (if not prompted in Step-2):

In the MySQL shell, set the password using this:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '<your-mysql-password';
FLUSH PRIVILEGES;
EXIT;

6) Create a New User and Database (optional)

Create a new user and database for your application:

CREATE USER 'newuser'@'%' IDENTIFIED BY 'password';
CREATE DATABASE newdatabase;
GRANT ALL PRIVILEGES ON newdatabase.* TO 'newuser'@'%';
FLUSH PRIVILEGES;

Replace newuser, password, and newdatabase with your desired values.

7) Configure MySQL (optional)

Edit the MySQL configuration file to adjust settings like character encoding, buffer sizes, or network settings:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

8) Restart MySQL Service

Restart the MySQL service to apply changes:

sudo systemctl restart mysql

That’s it! You now have a running MySQL server on your Ubuntu system.

Default Port for MySQL: 3306

Leave a Reply