MySQL is a free and open source relational database management systemm and also one of the most popular ones. In this article, I am going to show you the full installation guide of MySQL on Ubuntu machine (test environment Ubuntu 18.04).
Introduction
In this project, we will walk throgh the whole steps required to install MySQL on Ubuntu machine. Also, we provide some solutions to problems you may encounter. Finally, we will talk about secure setting as well as user creation and privileges.
Download and install MySQL APT Repository
Basically, we will use Linux shell commands and apt for package management. To begin, we should first download apt repository for MySQL. If you don’t want to keep the file after completion of installation, you can firstly head to tmp directory or otherwise you have to remove the file on your own. Then, perform a curl operation to acquire the repository deb file. You can switch the version according to your demand.
curl -OL https://dev.mysql.com/get/mysql-apt-config_0.8.12-1_all.deb
After that, we will install the deb file using dpkg command. You may use regular expression or directly refer to the filename.
sudo dpkg -i mysql-apt-config_0.8.12-1_all.deb
You will see a new interface coming up and asking you to choose which product and version you want to install.
Finally, run apt-get update to update package list.
sudo apt-get update
If you encounter GPG error (saying the following signature couldn’t be verified because the public key is not available: NO_PUBKEY ….) such as below. You may run the following command to solve the problem and let your system accept the public key.
sudo apt-key adv --keyserver keyserver.ubuntu.com --recv-keys <PUBKEY>
Install MySQL
Run the command below to install mysql server.
sudo apt-get install mysql-server
You may then see a new interface coming up with several options for you to choose.
You should now have MySQL installed on your machine after steps above.
Secure Setting of MySQL
Subsequently, we need to perform some secure setting procedure before using MySQL. Run mysql_secure_installation to have the settings done
sudo mysql_secure_installation
Then you should be able to see the following requests.
- Asking you to type the root password set during installation.
2. Ask if you wish to set validate password component that can be used to check strength of passwords.
3. Choose the level of password validation policy.
4. Ask if you wish to remove anonymous user which should only be used in testing.
5. Ask if you allow remote connection for root user? Normally, root user can only access database through localhost connection.
6. Ask if you wish to remove “test” database which is meant for testing only.
7. Ask if you wish to reload privilege table immediately.
By now, you have finished mysql_secure_installation setting.
Connect to MySQL
Run the command below to connect to MySQL through command line interface with password set by you.
sudo mysql -u root -p
If you already setup new users, you may switch root to desired user name instead.
Create User
You may need to create users with different privilege. In this section, we are going to show you how to create users with command line interface. First, login to mysql console just like above. Then run the command below and fill in desired information.
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';GRANT ALL PRIVILEGES ON *.* TO ‘username’@’localhost’;FLUSH PRIVILEGES;
The first line in this example code is to create user and second is to grant privileges to the user(Here we grant all, please check here for detailed grant command). The last line of code is to make the change effective.
Things to Do Before Working with Python
If you are trying to interact with MySQL using Python programming language. Here is something you may need to do beforehand.
- Install libmysqlclient-dev package (To solve mysql_config not found problem)
sudo apt-get install libmysqlclient-dev
2. Install Python development package (To solve command gcc failed with exit status 1 problem), you may need to switch 3.8 to your Python version.
sudo apt-get install python3.8-dev
Conclusion
MySQL is a open source and really easy to use database management tool with good ecosystem in many fields. Throught the full guide of its installation, I hope the software could bring you even more convenience. Thanks for your time reading, hope this really helps.
