Install,create,backup & restore MySQL on Ubuntu 20.04 LTS 6 easy steps

In this article you will be learn installations and configuration of MySQL on  latest Ubuntu 20.04 (Focal Fossa)

On Ubuntu, MySQL client and MySQL server are the two different packages available. You can either install the MySQL client package to connect to MySQL servers and you can install MySQL server to create your own database server.

Install,create,backup & restore MySQL on Ubuntu 20.04 LTS 6 easy steps

Highlights:

  • How to install MySQL Client
  • How to install MySQL Server
  • How to create a mysql database
  • How to backup mysql database
  • Restore mysql database backup
  • How to uninstall mysql completely

Lets see what requirements needs to perform above activity,

Sr.No  Descriptions  Requirements
1 Operating System Ubuntu 20.04
2 Packages mysql Client & mysql Server
3 Other Requirements Root or sudo user privileges

Excited? Lets start all with 6 easy steps

Step.1 Install MySQL Client

Open your terminal and use below command,

#sudo apt install mysql-client

Now, confirm installed package

Use below command to check it, and will see the results as below,

#mysql -V
mysql  Ver 8.0.19-0ubuntu4 for Linux on x86_64 ((Ubuntu))

Step.2 Install MySQL Server

If you want to build your own database server then you have to install “MySQL Server” package, once it installed you can access your database server locally or you can access it externally using MySQL client.

Use below commands to install package MySQL server

#sudo apt install mysql-server

Now, its very very important to secure mysql server, use below command for this,

#sudo mysql_secure_installation

Now, after used above command, it will ask you few questions, you can answered this questions and choose most appropriate and secured settings. Also set strong password for root along with disable remote login for root.

In case, you need to access MySQL server remotely then you have to allow it. By default for security reason MySQL database is accessible only from locally. Use below command to allow remotely access. It will open mysqld.cnf file

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

Then change the bind-address line to 0.0.0.0  instead of  127.0.0.1                   Then save the file.

Restart MySQL service

You can now restart and enable MySQL services to take effect and boot automatically, use below commends,

#sudo systemctl restart mysql
#sudo systemctl enable mysql

Use can see the status as in below images,

mysql_service_status

Confirm MySQL listening ports

MySQL runs on port 3306 by default. Check it with below command,

#ss -ltn

Allow MySQL port on firewall

Use below command to allow MySQL port 3306 in Ubuntu’s firewall.

#sudo ufw allow from any to any port 3306 proto tcp

Step.3 Create MySQL Database

To create MySQL database it’s required user permission, unless you just use the root account which not recommended, here we’ll create user and database. Use below command using root previlage to open MySQL.

#sudo mysql

Now, use below command to create new database, (replace dev_database with your database name)

mysql> CREATE DATABASE dev_database;

Now, we have to create a user with full previlage to access this database. use below command,

mysql> CREATE USER 'devopsarticle'@'%' IDENTIFIED BY 'dev@!12345';

Now, as you can see in above command, new database user name is devopsarticle and password for this user is dev@12345 you can choose yours accordingly. In above command we have used a wildcard % due to that you can login from anywhere using this user to database over the internet. If you wish to allow this user to be connect from specific location then you can add IP address instead of %  In case you just need to allow from locally then you can use localhost instead of %

Now, we have to provide full previlage for this user to manage database for that use below command,

mysql> GRANT ALL PRIVILEGES ON dev_database.* to devopsarticle@'%';

Finally, we have to save all this configurations and then exit. Use below commands,

mysql> FLUSH PRIVILEGES;
mysql> exit

Now, use below command to connect database, (It will ask you database user password)

In case your MySQL server is on other system then you have to specify host_name otherwise its not required. Once your entered database user password you will login to your MySQL database.

mysql -h host_name -u user -p

OR

mysql -u dev_database  -p

Now, to disconnect or exit database use below command and hit enter,

mysql> quit

Now, you have fully functional MySQL database server ready and you can connect it from anywhere.

Step4. Backup MySQL Database

You can take MySQL database backup using three methods, mysqldump is the default command used to take database backup.

Method.1 By using mysqldump command and login in to MySQL database. For this use below command, and replace dev_database with your one.

 mysql>mysqldump -u  -p dev_database > /path/to/store/your-MySQL-database-backup

Method.2 By using mysqldump, without login to MySQL.
In this method database backup will stored in present directory itself, no need to login to mysql. Replace devopsarticle with your_database_user_name and dev_database with your_database_name  

#mysqldump -u devopsarticle -p dev_database | gzip > dev_database.sql.gz

Method.3 Automatic Backup

Automatic MySQL backup is a utility program that is available in the Ubuntu repositories. This utility can be scheduled to automatically to do backups at regular intervals. Install it using below commands,

$ sudo apt-get install automysqlbackup

Now, to to leave things unchanged choose “no configuration” and use below command to perform auto backup

$ sudo automysqlbackup

The default location for auto backups is /var/lib/automysqlbackup

You can verify it using below command,

$ ls /var/lib/automysqlbackup

Here you can see, three directories daily, weekly and monthly containing respective gzipped SQL backup.

Configuration file for automysqlbackup is available at /etc/default/automysqlbackup

You can change configurations according to your need.

Step5. Restore MySQL Database

Use below command to restore any MySQL database backup.

$ mysql -u username -p database_name < backup_name.sql

Step6. Uninstall MySQL Completely

In case of we face issues with MySQL installation on Linux machine and if we simply remove MySQL packages and re-install it doesn’t fix the issue.  In that case, old settings may still exist on the server which again affects new installation. So, in this scenario first, uninstall MySQL completely from the system and erase all settings of old installation. For this follow the below steps

Note: Please do not use below steps if MySQL has any running databases, before doing this activity take a MySQL all databases backup as mentioned above.

  • Uninstall MySQL Packages:  As per your Linux distribution, use one of the followings commands,
sudo yum remove mysql mysql-server    #CentOS and RedHat systems 
sudo apt remove mysql mysql-server      #Ubuntu and Debian systems 
sudo dnf remove mysql mysql-server      #Fedora 22+ systems
  • Remove MySQL Directory:  Now, we have to remove MySQL data directory it by default located at /var/lib/mysql  In case you are not able to find it you can check it in my.cnf file with variable datadir  I will recommend instead of deleting just rename to keep data backup of existing.
$sudo mv /var/lib/mysql /var/lib/mysql_old_backup

$sudo mv /etc/mysql /etc/mysql_old_backup

Now, in case of you required to re install MySQL server and MySQL client follow the above steps again. You can use below commands to install on various Linux distributions.

$sudo yum install mysql-client    #CentOS and RedHat systems 
$sudo apt install mysql-client      #Ubuntu and Debian systems 
$sudo dnf install mysql-client      #Fedora 22+ systems
$sudo yum install mysql-server    #CentOS and RedHat systems 
$sudo apt install mysql-server      #Ubuntu and Debian systems 
$sudo dnf install mysql-server      #Fedora 22+ systems

 

Hope this article helpful for you, please like, share and subscribe to Devopsarticle.com

 

Reference:  You can refer below links for official documentations.

mysql.com

dev.mysql.com/doc/

 

Related Article: You can refer related article to install and configure LAMP server

What is LAMP? How to install and configure LAMP server

 

Thanks for visiting to Devopsarticle.com

Suresh Dike

Suresh Dike I am Suresh, working on Cloud, DevOps, Linux, Firewalls,Docker and Kubernetes. Believes in sharing the knowledge.

2 thoughts on “Install,create,backup & restore MySQL on Ubuntu 20.04 LTS 6 easy steps”

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.