How to back up mysql/mariadb database as mysql root when using unix socket for sql access in a bash script?

Question

I am running Ubunutu Server 20.04. I have secured mariadb by using the unix socket for authentication, which means, I need to enter sudo mysql to be able to access mysql as root.

1) If I enter

mysql -u root -pMYROOTPASSWORD

to access mysql as root user, I get the following error:

ERROR 1698 (28000): Access denied for user 'root'@'localhost'

I am trying to run a command that backs up mysql database using the mysql root user. The syntax I would like to use is...

mysqldump -u root -p MyDb > MyDb.sql

2) If I (as my home user) enter

mysqldump -u root -p MyDb > MyDb.sql

I get the Enter password: prompt, and then I have to enter my unix password which returns the following error:

mysqldump: Got error: 1698: "Access denied for user 'root'@'localhost'" when trying to connect

3) If I then enter

sudo mysqldump -u root -p MyDb > MyDb.sql

I again get the Enter password: prompt, to which this time, I enter a BAD UNIX PASSWORD. This actually still creates a file named MyDb.sql in my current directory. How can a database be backed up by using a bad unix password? Why do I not get an error when entering the wrong sudo password for an sql root backup? How is this database getting backed up and what are the repercussions of using a bad sudo password for this backup command?

4) If I enter

sudo mysqldump -u root -p MyDb > MyDb.sql

I get the Enter password: prompt, to which this time, I enter a VALID UNIX SUDO PASSWORD. This once again successfully creates a file named MyDb.sql in my current directory. However, I have no idea if this file is a valid sql-root-user DB Backup file, since mysqldump seems to create a backup with any password whatsoever.

5) The problem with #3 and #4, is that there is no way to tell which root password the command

sudo mysqldump -u root -p MyDb > MyDb.sql

actually requires and if its valid. Does it require my unix sudo password, my mysql root password (which I think is disabled or invalid since I am using unix socket authentication for mysql root), or any random password?

6) The reason this is so important, is that I intend to create a backup script with this command, and then run the script in a crontab to automate backups. That being said, since sqlbackup doesn't show weather a password is valid or not, I don't know the proper password that I should enter in the backup script. This brings me to my questions.

Question1: Which password should I be using to create a bash backup script of a mysql database using the mysql root user?

Question2: The mysqldump --help command indicate the password option as

 -p, --password[=name]
                      Password to use when connecting to server. If password is
                      not given it's solicited on the tty.

Does that mean that my backup script syntax should look like this?:

sudo mysqldump -u root -p'My_Unix_Sudo_Password' MyDb > MyDb.sql

this?:

sudo mysqldump -u root -p'MySql_Possibly_Disabled_or_Invalid_Root_Password' MyDb > MyDb.sql

or this?:

sudo mysqldump -u root -p'It_Doesnt_Matter_the_Password_They_are_all_valid_somehow' MyDb > MyDb.sql

Question3: Should I be running the backup script as the root crontab user, or as the regular crontab user?

Question4 Is there an easy way to verify and check that a backup sql database is valid without having to fully restore and test it?

Question5 If the syntax I have for putting a sql backup command into a bash backup script is incorrect, then what is the proper syntax (including a password for automated backups) for creating an sql backup script that backs up databases as the root mysql user, and excludes the password prompt in the command for automatic running of the script?

Thanks for any help!

Answer 1

You seem to be trying to come up with multiple solutions to the same problem.

When SO_PEERCRED authentication is in place, passwords are pretty much irrelevant. When MySQL is NOT using SO_PEERCRED then it will use its own internal password store (unless you have explicitly configured it to do soething else). SO_PEERCRED authentication can only be used with connections via a filesystem socket. Where the host is not explicitly specified, or the literal string "localhost" is used, MySQL clients will use the filesystem socket.

With SO_PEERCRED, MySQL expects that it has a user configured with the same name as the OS user connecting. Out of the box, the default configuration on MySQL is to only allow the root OS user to connect via SO_PEERCRED. This is implemented by a 'root@localhost' user in the mysql.users table.

There are potentially MANY different combinations you can use to achieve your objective. But it is good practice to reserve your root account for tasks which can only be performed by root. And backing up the database is not one of these. So I would recommend creating a new user in MySQL with appropriate permissions.

If you create an OS user with the same name, you will be able to use that account to run mysqlbackup locally using the SO_PEERCRED mechanism.

Alternatively you can set a password for the MySQL account and if you connect via a network socket rather than a filesystem socket you need to provide the password to connect. While MySQL clients interpret "localhost" as meaning use the filesystem socket, "127.0.0.1" will connect to the network port (typically 3306).

It is usually better for security not to leave passwords on your filesystem in clear text, hence the SO_PEERCRED authentication route is safer.