Content

Introductory

When operating a website, it is important to be able to make a fresh backup of the database at any time, or even to restore it if there is anything to do with the page. Of course a web hosting control panels most of them offer such features by default, but it doesn’t hurt to be able to control things related to the database yourself, especially if it’s not just a backup or restore task, but any other database operation that needs to be done.

This tutorial covers the mysql and mysqldump commands on how to make them more secure.

Introducing mysql and mysqldump commands

You can use the mysql command to give direct SQL statements to MySQL server, so you can easily create various scripts to bundle your SQL commands or automate your database work. The mysqldump command is a dedicated database backup tool to fine-tune the standards, character encoding, etc. of your backup SQL file.

Let's see some examples!

The mysql command

In the example below, I a Debian 8 (Jessie) is the perfect server for 1.1 version I enter a mysql command in console mode a Drupal test page database and query the users table:

Here you can see that the mysql command prompts for the specified user password and then enters console mode. Then, after selecting the database, commands can be sent to the MySQL server.

This is nice and good, but what if you don't want to run SQL commands interactively, but need to run something on a cron timer, for example, or just need another program to start an SQL sequence?

There is a solution to this. You will also need to enter the password on the command line and the string or SQL file that contains the SQL statements as input:

Here I gave the server the necessary SQL statement from a command line (which I now intentionally did with a \ for better clarity), which I executed, and redirected the output of the command to a file, which can then be listed to see the above output in the same way. So the syntax for the mysql command is as follows:

echo [bemeneti SQL parancs] | mysql -u [felhasználónév] -p[jelszó] [adatbázis] > [kimeneti SQL fájl]

For larger SQL statements, you can read from a file:

cat [bemeneti SQL parancsfájl] | mysql -u [felhasználónév] -p[jelszó] [adatbázis] > [kimeneti SQL fájl]

Essentially the same. And if no output is needed because, for example, we just change something in a table, then it's even simpler:

mysql -u [felhasználónév] -p[jelszó] [adatbázis] < [bemeneti SQL parancsfájl]

(The -p option is followed by a database password without spaces!)

And the Bash due to its versatility, it could still be crafted and varied in many ways as the situation demands.

The mysqldump command

A mysqldump command syntax is similar, let's look at an example:

Here I first listed the directory to see that there was nothing before it, then with mysqldump I saved the database of the entire Drupal test page to a file which, after a new listing, appears to be about 6 megabytes in size and then the first Listing 20's line, I looked into the file to see the result. Well, she put in a full page database backup.

So the syntax of the mysqldump command looks like this:

mysqldump -u [felhasználónév] -p[jelszó] [adatbázis] > [kimeneti SQL fájl]

The use of the two commands is roughly the same, and their switches had to be used the same way in this case.

However, here is the beauty flaw in the whole thing, which is also the essence of today's article: For both commands, the passwords are put on the command line, which is very insecure. For example, for a longer mysql command, listing the processes immediately shows the running command and all its parameters, including the database user's password. Or, if you need to put together a shell script that requires database operations, the passwords look the same.

This is not the best solution, sooner or later someone may come across our passwords. How then can we securely manage databases from the command line?

Secure use of database management commands

Fortunately, there is a way to handle these commands securely, so you can use them in our scripts.

The solution is to use defaults files. In short, the point is to store usernames and passwords in a separate file, and then protect them from being read by another user with the appropriate permission setting. Then we only need to specify this defaults file for the commands that perform mysql and mysqldump and other database operations, so our passwords will not appear anywhere.

A defaults file is a plain text file that can be divided into several sections, which allows you to configure multiple programs at once.

Debian also includes the following file by default: /etc/mysql/debian.cnf, in which it is recommended to set the database root user and its password.

The following template illustrates the database access settings for mysql and mysqldump:

[mysql]
# Ezt a csoportot csak a mysql parancs olvassa.
# Itt adhatjuk meg a parancs számára az adatbázis hozzáférési beállításokat
host     = hoszt
user     = felhasználó
password = 'jelszó'
database = adatbázisnév


[mysqldump]
# Ezt pedig a mysqldump olvassa
# Ez nem támogatja az adatbázis nevet, 
#ezért azt külön kell megadni a parancs használatakor
host     = hoszt
user     = felhasználó
password = 'jelszó'

# Így tehát mindkét programmal működik: 
# A mysql-el is és a mysqldump-al is

Passwords work without apostrophe unless the password contains special characters. Therefore, it is advisable to always use it.

I created a subdirectory named "mysql" at the root of the webpage's document in the private directory and created a file called "testpage.cnf" in which I set the test page database access according to the above template.

Then you need to set the appropriate permissions to chmod command:

chmod go-rwx tesztoldal.cnf

vagy

chmod 600 tesztoldal.cnf

This removes all permissions from the group and others. From now on, only the owner of the file can see it.

After that, let's repeat our very first example where we went into console mode and listed the users table:

It enters nicely without getting away from your password. Also, since I have given the name of the database in the file, it will be selected in one step so there is no need for the "use [database name]" command.

Using the mysql command has become even simpler as you only need to enter one parameter:

mysql --defaults-file=[defaults_fájl_elérési_útja]

Or, if you don't want to go into the console but need to run an SQL script immediately, you need to add it to your input:

mysql --defaults-file=[defaults_fájl_elérési_útja] < [sql_bemeneti_fájl]

So you immediately execute it and then quit.

The same is true with mysqldump, but only the database name must be entered here because it is not supported in the defaults file:

mysqldump --defaults-file=[defaults_fájl_útvonala] [adatbázisnév] > [kimeneti_sql_fájl]

Conclusion

So by using the defaults file, you can easily secure your work on databases from the command line and not have to worry about unauthorized access to your passwords. Not to mention that it also makes our job easier, as we don’t have to hack access data during every database operation.