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.
- 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,
|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,
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,
Confirm MySQL listening ports
MySQL runs on port 3306 by default. Check it with below command,
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.
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
mysql -u dev_database -p
Now, to disconnect or exit database use below command and hit enter,
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
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.
Related Article: You can refer related article to install and configure LAMP server
Thanks for visiting to Devopsarticle.com