How to Install MySQL and Create Databases

/ / Tutorials

Installing and managing MySQL doesn’t have to be complicated. In just a few minutes, you can set up MySQL on your Linux server, create your first database, and even automate backups – all while ensuring your data stays safe and organized. 

What Is MySQL 

MySQL is a relational database management system (RDBMS) that uses Structured Query Language (SQL) to organize and manage data efficiently. It’s one of the most popular open-source database systems worldwide, widely used in web development, e-commerce, and business applications. 

Common Use Cases 

MySQL is a reliable choice for various applications, including: 

  • Web applications: Managing user authentication and storing website content (e.g., WordPress, Drupal). 
  • E-commerce platforms: Handling product catalogs, customer orders, and transactions (e.g., Magento, WooCommerce). 
  • Data analytics: Storing and analyzing structured data for business insights. 
  • Business tools: Tracking customer records, finances, and inventory. 

If you’ve ever logged into a website or completed an online purchase, there’s a good chance MySQL was running behind the scenes. 

Installing MySQL on Linux (Debian-Based Systems) 

Installing MySQL on Ubuntu or Debian-based systems is simple and quick. Follow these steps: 

  1. Update your packages and install MySQL: 
sudo apt-get update && sudo apt-get install mysql-server -y 
  1. Secure the installation: 
sudo mysql_secure_installation 
  1. Check the MySQL service status: 
sudo systemctl status mysql 

If the service is inactive, start it using: 

sudo systemctl start mysql  

At this point, MySQL should be running on your system. 

Creating a MySQL Database and Table 

Once MySQL is installed, you can create your first database and table. 

  1. Log in to MySQL: 
mysql -u root -p 
  1. Create a new database: 
CREATE DATABASE my_database; 
  1. Switch to that database and create a table: 
USE my_database; 
CREATE TABLE users ( 
    id INT AUTO_INCREMENT PRIMARY KEY, 
    name VARCHAR(100), 
    email VARCHAR(100) UNIQUE 
); 
  1. Insert data into the table: 
INSERT INTO users (name, email) VALUES ('Alice', '[email protected]'); 
  1. Retrieve the data: 
SELECT * FROM users; 

Congratulations – you’ve just created and populated your first MySQL database. 

Managing Users and Permissions 

For better security, avoid using the root account for daily database tasks. Instead, create a dedicated user. 

  1. Create a new user: 
CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'secure_password'; 
  1. Grant privileges: 
GRANT ALL PRIVILEGES ON my_database.* TO 'new_user'@'localhost'; FLUSH PRIVILEGES; 

This new user now has full access to my_database without exposing the root credentials. 

Backing Up and Restoring Databases 

Regular backups are essential to prevent data loss. 

  1. Create a backup: 
mysqldump -u root -p my_database > backup.sql 
  1. Restore a backup: 
mysql -u root -p my_database < backup.sql 

This command restores the data from the backup file. 

Automating Backups 

To simplify maintenance, you can automate MySQL backups with a simple script and a scheduled task. 

  1. Create a shell script: 
nano backup.sh 
  1. Add the following content: 
#!/bin/bash 
mysqldump -u root -p[password] my_database > /path/to/backup.sql 
  1. Schedule daily backups using Cron: 
crontab -e 

Then add this line: 

0 3 * * * /path/to/backup.sh 

Your server will now automatically back up your database every day at 3 a.m. 

Watch Our YouTube Video on MySQL 

You are more of a visual learner and prefer a visual walk through? We have a YouTube video for you, going through all the steps discussed in this article. 

Conclusion 

You’ve now installed MySQL, created and managed databases, configured user permissions, and set up automated backups. With these steps, your server is ready to handle data efficiently and securely. 

For more tutorials and hosting tips, explore other guides from Contabo – and keep your systems optimized and protected for every project. 

Scroll to Top