MySQL Installation Using Linux Generic Binary and Securing the Installationv1


Music Credit : YouTube Audio Library We are on a Linux Container running Ubuntu 14.04.2 LTS The hostname is ‘mysql-2’ As you can see, the ‘mysql’ client command is not installed on this System Neither do we have the MySQL Server Process (‘mysqld’) running on this System We don’t have a user by name ‘mysql’ in the System. As a first step, we’ll create a ‘mysql’ User in the OS Well, that created a group by name ‘mysql’ as well Let’s navigate to http://www.mysql.com website to download the MySQL generic binary for Linux Operating Platform We’ll download the Community Edition of the MySQL Server Let’s download the latest edition of MySQL Community Server. Since we are running a 64 bit OS, we’ll download the 64 bit version of the binary It’s over 600 MB in size… Download is completed. Let’s get back to the Terminal window of ‘mysql-2’ host We’ll have a look at the Table of Contents of the MySQL tar file using the ‘t’ option of the ‘tar’ command There are two files. The one that is of interest to us is the first one listed in the ‘ls’ command output Let’s now extract the downloaded file using the ‘x’ option of the ‘tar’ command Now let’s extract the MySQL binary file using tar command. Since it is zipped (.tar.gz), we’ll use the ‘zx’ option of ‘tar’ to unzip and extract The extraction is done and that completes the MySQL installation as well We’ll rename the directory (using ‘mv’ command) to more friendly and convenient name (and location) Ok. I’m a simple User. Let me use the ‘sudo’ command to run it as a Super User Let me supply my User Password All right, so the MySQL installation directory is ‘/mysql2’. In the language of MySQL configuration file ‘my.cnf’, that’s the ‘basedir’ One can see all the MySQL binaries (including the one that starts the MySQL Server process ‘mysqld’) is under the ‘bin’ directory MySQL binaries offers some some template files that could be customized to our site requirement. All such files for the Linux Generic Binary is located under the ‘support-files’ directory (for MySQL 5.7) What is highlighted is a template file for creating the MySQL Configuration file (my.cnf) The one that is being highlighted (mysql.server) is a script that could be used on Unix flavours to start and stop MySQL Server Let’s take a look at it quickly The script is well documented, making its usage self explanatory (should we wish to use it to start/stop MySQL Server Process in Linux) Unlike the MySQL installation from Ubuntu Repository, the installation from Linux Generic binaries did not (i) create a default ‘my.cnf’ file, (ii) did not initialize the database ‘mysql’ So we are creating a MySQL Configuration file (my.cnf) by hand. For now, we’ll put only the bare minimum server parameters that are required for the MySQL Server to start As you probably know, whatever is mentioned under the ‘mysqld’ section will be read during the MySQL Server startup process So in this case, our MySQL instance will have an ID of 2, it will create a Socket file ‘/mysql2/data/mysq2.sock’ (used by MySQL clients to connect to the MySQL Server running on the same host, provided the host is a Unix flavour)… …the port bound by the MySQL Server is 4000, the installation directory is ‘/mysql2/’ and the MySQL data will reside in ‘/mysql2/data’ directory (indicated by the ‘datadir’ Server parameter) So we are ready with a MySQL Configuration file that has bare minimum information required for the MySQL Server to start We don’t have the ‘data’ directory created yet. Let’s create an empty directory, which will be used by MySQL as its data directory We’ll now change the ownership of ‘/mysql2’ directory and files & directories beneath it to the user ‘mysql’ and group ‘mysql’ Remember, we need the ‘mysql’ database to be initialized. It was done automatically during the Repository based installation of MySQL. But for Installation by hand, we’ll have to initialize the MySQL Database manually Earlier version of MySQL had the command highlighted (mysql_install_db) to initialize the mysql database But with MySQL 5.7 the ‘mysql’ database can be initialized using the ‘initialize’ option of the ‘mysqld’ command (the same command that starts the MySQL Server Process) Just making sure that the ‘data’ directory (‘datadir’ in the ‘my.cnf’ file) is empty Ok, let’s initialize the ‘mysql’ database now using the ‘initialize’ option of the ‘mysqld’ command But of course, while doing it, we are pointing the command to read the Server Parameters (like ‘basedir’, ‘datadir’ ) from the ‘my.cnf’ file that we created earlier Instead of ‘initialize’ if we use ‘initialize-insecure’, it will not set a random password for the MySQL root user. But in this case, the initialization process will set a random MySQL root user password. It’s important to make note of it Oops, looks like some package is missing. Never mind, let’s install it from the Ubuntu repository Now it should go through Well, we can see that the database initialization process has set a random password for the MySQL root user. Let’s copy it to the clipboard Cool, the Data Directory of MySQL Server is now populated. There is a directory ‘mysql’ that corresponds to the ‘mysql’ database and in it we can see all the OS files that corresponds to the tables in the Database Now is the time to start the MySQL Server We just need to run the command ‘mysqld’ pointing it (using ‘defaults-file’ option) to the ‘my.cnf’ file that we created so that it creates a socket mentioned in the file, listens to port 4000, uses the Base Directory and Data Directory mentioned in there Well, it looks like the Server started with a problem And since we use an ‘&’ while running the command, the process is pushed to the background and we can continue to use this Terminal for other commands That confirms the ‘mysqld’ Server Process to be up and running Let’s set the OS PATH variable to use the binary location of MySQL installation directory as well. Convenient to run commands without its full path Let’s run the ‘mysql’ client command as MySQL ‘root’ user Since it is connecting to the MySQL server on the same host (default behaviour), it tries to connect to it using the default socket file name (/tmp/mysql.sock) and so it fails (because our socket file is different) So let’s run the ‘mysql’ client command again, but this time pointing it to the socket file using a parameter ‘socket’ Well that looks like the socket we mentioned in the ‘my.cnf’ file Obviously we can’t get into a Database as its top level administrator without supplying its password (remember the random password generated and the one we copied to the clipboard) So let’s modify the command to supply the password We are in! Since we got inside using the random password generated during the ‘mysql’ database initialization, we will not be allowed to perform any operation without changing the password first!! There are many ways to set/reset MySQL User password. But on this occasion, we’ll use a recommended post installation script to do it. That’ll give us an opportunity to secure our MySQL instance as well The script ‘mysql_secure_installation’ is meant exactly for that. It’s a post installation script that does a few things like setting/resetting root password, enable/disable remote root logins, enforce strong passwords, remove anonymous user etc. Since the script expects details like the socket, we have to pass it as a command argument. Foolishly, I’m pointing it to use the ‘my.cnf’ file Like any other MySQL client, ‘mysql_secure_installation’ also tried to connect to the MySQL server on the same host using the default socket ‘/tmp/mysql.sock’ We dont’ want to change the command. So let’s change the configuration file (my.cnf) and make it work While the ‘mysqld’ section in the ‘my.cnf’ file is meant for the MySQL Server, we could add a ‘client’ section in the same file to specify parameters that can be picked up by the MySQL client commands Now this entry for the socket that we are making now is going to be picked up by the MySQL clients (like ‘mysql’, ‘mysql_secure_installation’, ‘mysqladmin’ ) Now if we run the command again pointing to the ‘my.cnf’ file, it’ll read the parameters mentioned under the ‘client’ section (which in this case is socket information to connect to the MySQL Server on the same host) Let’s paste the random password that we copied after the ‘mysql’ database was initialized Rest is self explanatory We’ve secured our MySQL installation This time around, let’s ask the ‘mysql’ client command to pick up the ‘socket’ information from under the ‘client’ section of the ‘my.cnf’ file Now we are free to run any MySQL operations as we managed to reset the randomly generated password for the MySQL ‘root’ User Let’s shutdown the MySQL Server Process Na…not a graceful way of doing it Let’s use the ‘mysqladmin’ client command to shutdown the MySQL server process. The command, as we discussed before, will pick up the socket information from the ‘my.cnf’ file Thanks for watching!

6 Comments

  1. How to setting the defaults-filel of my.cnf , to connect mysql use the command " mysql -u root -p " instead of “ mysql –socket=/mysql/data/mysql.sock –user=root -p”?
    thx!

Leave a Reply

Your email address will not be published. Required fields are marked *