Installing MySQL on Ubuntu Linux 20.04

So you wanna install MySQL server on your Ubuntu Linux 20.04 box right? No problem, another super duper easy install:

• apt install mysql-server

That will install the latest version in the Ubuntu repositories, that being 8.0.2. IIRC the version numbering has changed and went from 5.7 to the new 8.x.x version numbering system. This has prompted some in the industry to get paranoid with Oracle as MySQL versions are closing in on Oracle DB versions and there’s been rumors of Oracle taking away the open source license for MySQL, merging the Oracle DB and making it a single commercial closed source product. However, I believe they are just that, rumors. This has prompted some dump MySQL and has been a boon to MariaDB installations as people jump ship hoping to stay with free open source licensing but honestly this is all conjecture and probably a needless conspiracy at the moment, with some personal insight, so I can confidently say its nothing to worry about. MariaDB is a drop in replacement for MySQL and if you’re worried, you can look for one of the many excellent articles out there on installing MariaDB instead of MySQL.

So you’ve just installed your mysql-server package and you’re wondering what to do now. Well, the first thing you want to do before you do anything with MySQL is run:

• mysql_secure_installation

That command will ask a series of questions to secure the database. I’ll paraphrase the questions and give the answers I chose to use here:

• Validate Password Component N
• Enter Root Password Self Explanatory
• Remove Anon Users Y
• Disallow remote root login Y
• remove test db and access Y
• Reload Privilege Tables Y

Most of that should be self explanatory but I’ll got into each. I always choose these same set of answers on 98% of MySQL installs and I’ll try to give some reasoning but won’t go into too much detail. First off, I chose No for the validate password component because that’s more of an issue if multiple users will be interacting with the database. Basically it checks to make sure you’re choosing good passwords and can be a pain in the ass if only one or two users ever log into the database. Enter root password is self explanatory. You’re choosing a root password, but it’s not going work anyways because of something called auth_socket (I’ll touch on more details about this below). Y for remove anonymous users because it’s good practice. Y for disallow remote root login (to the database). Y to remove test databases and access. Finally, Y for reloading the privilege tables and make sure all these settings take. If you’d like to know more about these leave a comment below and I’ll try to better justify my reasons for choosing the options that I did but *most* installations do the same so unless you know or need a different answer than what I chose, that will be in your specific requirements.

Let’s talk about that auth_socket thing now. When you are logged in as the system root user, MySQL will automagically log you in as the database root user when you call MySQL without options. That auth_socket thing is the reason you don’t have to enter passwords when configuring or creating databases and users when you ARE logged in as the Linux root user. In some non default setups this may not be desired. For example you may actually want to be able to login as the root user from across a network connection for backups or replication purposes. In that case you will want to have answered N when mysql_secure_installation asks disallow root network logins to the database. Additionally you will have to change MySQL’s authentication type to a password type other than auth_socket. For example you will want to change it to mysql_native_password or caching_sha2_password. That can be performed by:

• update mysql.user set plugin = ‘mysql_native_password’ where user = ‘root’;
• update mysql.user set plugin = ‘auth_socket’ where user = ‘root’; # Revert to default behavior

Now I’m one of those guys who always uses lower case even when interacting with MySQL. A) It’s ok, and B) I’m a hardcore Unix/Linux administrator and I don’t believe in capital letters 😉 Let me step back for a moment and say more likely than not you won’t need to run that command if you believe you’ll be using MySQL’s default behavior. I just wanted to add that in here because it seems to be an answer to a question people are always asking about MySQL.

That was probably way too geeky and detailed for most people, and probably boring AF* for you veteran admins. I’m writing most of my articles geared towards intermediate users, and in that vein I believe knowing this information will be useful to know for future reference. More likely than not you won’t have to mess with the authentication settings unless it’s a requirement.

Now for most people needing the MySQL subsystem the default behavior is usually just fine so you can get away with the simple apt install mysql-server for most cases.

That’s all I wanted to say about installing MySQL on Ubuntu 20.04, but before I leave I will go over the commands to quickly add a user to MySQL as well as the commands to set the password for that user. Additionally I will go over the command for creating a database. Plus I will show the commands to delete a user and delete a database. These are the items most people need to know how to do and if you’re an intermediate Linux administrator, these few commands are good to commit to memory as they are the most common administration commands used on a regular basis.

• create user ‘user’@’localhost’ identified by ‘password’;
• create database appdb;
• grant all privileges on appdb.* to ‘user’@’localhost’;
• drop ‘user’@’localhost’;
• drop database appdb;
• flush privileges;

I will go over these few commands quickly as well but they should be self explanatory. The important thing is going to be memorizing the syntax. The create user and create database commands are self explanatory. The grant command is giving a specific user (user@localhost) full access to a specific database (appdb). The drop commands are the commands to delete a user and delete a database. Be careful with drop commands. They are final. I mean you can recreate the user and database, but there’s no coming back from the drop commands once they are issued. I have the flush privileges command separated from the other commands to let you know that you “flush” privileges after creating and granting, or after dropping, among other things.

Okay, that will do it for this the MySQL thing. I’ve already written articles on Apache 2 and PHP installation and this was the final article component for creating a full LAMP stack. What’s a LAMP stack? It’s a framework for running applications. For example, like WordPress. It’s the WordPress tutorial I’ve been really wanting to write but I needed to get articles out on Apache 2 and PHP first. Now with MySQL out of the way you have the AMP part and of course Linux is the L in LAMP.

One final command I want to add is how to fully backup the database:

• mysqldump –all-databases > database.mysql

That mysqldump command dumps the entire database to the file database.mysql. I’ll leave it up the reader to research how to load database.mysql back into a MySQL database, unless I get an overwhelming amount of people who can’t find the answer to that on Google 😉

I hope this helped someone and maybe even taught you a few commands. All errors are mine so if you catch one, let me know in a comment and I will fix or re-educate myself on the issue and make corrections.

Please visit my Patreon page if you’re so inclined or make a donation. Or you can click the coffee cup in the bottom right corner of the screen and Buy Me A Coffee. Thank you!

*AF – as fuck