MySQL installation on Ubuntu 18.04
Basic installation is an easy process and requires typing just a couple of commands
$ sudo apt update
Answer the questions provided and your basic installation is finished. Now you need to make sure your can access your database with username/password pair.
$ sudo mysql
This will take you in MySQL prompt where you need to set a password for the
root user with commands provided below
mysql> SELECT user,authentication_string,plugin,host FROM mysql.user;
NOTE: Make sure to replace password with your own secure password.
Exit a shell
mysql> FLUSH PRIVILEGES;
and check you are able to connect with a password created
$ mysql -u root -p
Let’s create a database so we can connect to it with a help of any client and use it within our applications
mysql> CREATE DATABASE [database-name];
Now suppose that you have to migrate between instances and create a database dump which can be transfered as a file and then restored on a new machine. mysqldump will help as to accomplish this.
$ which mysqldump
-u is an argument for your username and
-p is for password (there is no spaces between keys and their values)
Now from our target machine we can copy a remote dump file and restore it within current MySQL installation
$ scp -r [remote-user]@[remote-ip]:[remote-path] .
You need to provide username and remote ip address as well as a path on remote machine to the backup file.
. at the end of a command means copy to current directory. Resulting command might look like this
scp -r firstname.lastname@example.org:/home/ubuntu/db_22_01_19.dump .
Having a dump locally allows us to restore database from a file.
$ mysql -u root -p [database-name] < db_22_01_19.dump
To make sure restore procedure has been completed successfully you might want to open MySQL shell once again and check content for our database is in place
mysql> show databases;
That’s it. Be vigilant and don’t forget to periodically create backups of your database not to loose important data.