.::: Backup User User in MariaDB MySQL & restore user user using mysql dump :::.

 1. create user
create user01, user02, user03

CREATE USER 'user01'@'localhost' IDENTIFIED BY 'user';
GRANT ALL PRIVILEGES ON *.* TO 'user01'@'localhost' WITH GRANT OPTION;

CREATE USER 'user02'@'localhost' IDENTIFIED BY 'user';
GRANT ALL PRIVILEGES ON *.* TO 'user02'@'localhost' WITH GRANT OPTION;

CREATE USER 'user03'@'localhost' IDENTIFIED BY 'user';
GRANT ALL PRIVILEGES ON *.* TO 'user03'@'localhost' WITH GRANT OPTION;

FLUSH PRIVILEGES;

check user
select @@hostname, user, host, password, authentication_string from mysql.user;
 

2. test login user
[root@teguhth mysql]# mysql -uuser01 -puser
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 10.5.21-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> exit
Bye
[root@teguhth mysql]# mysql -uuser02 -puser
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 10.5.21-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> exit
Bye
[root@teguhth mysql]# mysql -uuser03 -puser
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 6
Server version: 10.5.21-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> exit
Bye
[root@teguhth mysql]#

3. backup user user
mysqldump -u root -p mysql > user_backup.sql

[root@teguhth tmp]# pwd
/root/tmp
[root@teguhth tmp]# ls
[root@teguhth tmp]# mysqldump -u root -p mysql > user_backup.sql
Enter password:
[root@teguhth tmp]# ls -l
total 2440
-rw-r--r-- 1 root root 2494682 Sep 15 08:34 user_backup.sql
[root@teguhth tmp]#

4. drop user & check again
drop user 'user01'@'localhost';
drop user 'user02'@'localhost';
drop user 'user03'@'localhost';
select @@hostname, user, host, password, authentication_string from mysql.user;

MariaDB [(none)]> drop user 'user01'@'localhost';
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> drop user 'user02'@'localhost';
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> drop user 'user03'@'localhost';
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> select @@hostname, user, host, password, authentication_string from mysql.user;
+------------+-------------+-----------+-------------------------------------------+-------------------------------------------+
| @@hostname | User        | Host      | Password                                  | authentication_string                     |
+------------+-------------+-----------+-------------------------------------------+-------------------------------------------+
| teguhth    | mariadb.sys | localhost |                                           |                                           |
| teguhth    | root        | localhost | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| teguhth    | mysql       | localhost | invalid                                   | invalid                                   |
| teguhth    |             | localhost |                                           |                                           |
| teguhth    | teguh       | %         | *EB39BF97E50561CC0C8AA79DC4F49D572679286F | *EB39BF97E50561CC0C8AA79DC4F49D572679286F |
| teguhth    | teguh       | localhost | *EB39BF97E50561CC0C8AA79DC4F49D572679286F | *EB39BF97E50561CC0C8AA79DC4F49D572679286F |
| teguhth    | root        | %         | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| teguhth    | backupuser  | %         | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| teguhth    | backupuser  | localhost | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| teguhth    | sysadmin    | localhost | *4ACFE3202A5FF5CF467898FC58AAB1D615029441 | *4ACFE3202A5FF5CF467898FC58AAB1D615029441 |
| teguhth    | sysadmin    | %         | *4ACFE3202A5FF5CF467898FC58AAB1D615029441 | *4ACFE3202A5FF5CF467898FC58AAB1D615029441 |
+------------+-------------+-----------+-------------------------------------------+-------------------------------------------+
11 rows in set (0.002 sec)

MariaDB [(none)]>
 

5. restore user

mysql -u root -p mysql < user_backup.sql

[root@teguhth tmp]# mysql -u root -p mysql < user_backup.sql
Enter password:
[root@teguhth tmp]#

6. check user after restore

[root@teguhth tmp]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 21
Server version: 10.5.21-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> select @@hostname, user, host, password, authentication_string from mysql.user;
+------------+-------------+-----------+-------------------------------------------+-------------------------------------------+
| @@hostname | User        | Host      | Password                                  | authentication_string                     |
+------------+-------------+-----------+-------------------------------------------+-------------------------------------------+
| teguhth    | mariadb.sys | localhost |                                           |                                           |
| teguhth    | root        | localhost | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| teguhth    | mysql       | localhost | invalid                                   | invalid                                   |
| teguhth    |             | localhost |                                           |                                           |
| teguhth    | teguh       | %         | *EB39BF97E50561CC0C8AA79DC4F49D572679286F | *EB39BF97E50561CC0C8AA79DC4F49D572679286F |
| teguhth    | user01      | localhost | *D5D9F81F5542DE067FFF5FF7A4CA4BDD322C578F | *D5D9F81F5542DE067FFF5FF7A4CA4BDD322C578F |
| teguhth    | teguh       | localhost | *EB39BF97E50561CC0C8AA79DC4F49D572679286F | *EB39BF97E50561CC0C8AA79DC4F49D572679286F |
| teguhth    | root        | %         | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| teguhth    | backupuser  | %         | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| teguhth    | backupuser  | localhost | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| teguhth    | user03      | localhost | *D5D9F81F5542DE067FFF5FF7A4CA4BDD322C578F | *D5D9F81F5542DE067FFF5FF7A4CA4BDD322C578F |
| teguhth    | user02      | localhost | *D5D9F81F5542DE067FFF5FF7A4CA4BDD322C578F | *D5D9F81F5542DE067FFF5FF7A4CA4BDD322C578F |
| teguhth    | sysadmin    | localhost | *4ACFE3202A5FF5CF467898FC58AAB1D615029441 | *4ACFE3202A5FF5CF467898FC58AAB1D615029441 |
| teguhth    | sysadmin    | %         | *4ACFE3202A5FF5CF467898FC58AAB1D615029441 | *4ACFE3202A5FF5CF467898FC58AAB1D615029441 |
+------------+-------------+-----------+-------------------------------------------+-------------------------------------------+
14 rows in set (0.001 sec)

MariaDB [(none)]>
 

 

7. test login before grant <after lab, enough run only FLUSH PRIVILEGES;

GRANT ALL PRIVILEGES ON *.* TO 'user01'@'localhost' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'user02'@'localhost' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'user03'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'user03'@'localhost' WITH GRANT OPTION;
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'user01'@'localhost' WITH GRANT OPTION;
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'user02'@'localhost' WITH GRANT OPTION;
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]>

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.002 sec)

MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'user03'@'localhost' WITH GRANT OPTION;
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'user01'@'localhost' WITH GRANT OPTION;
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'user02'@'localhost' WITH GRANT OPTION;
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]>
MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]>

8. test login lagi

[root@teguhth tmp]# mysql -uuser01 -puser
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 18
Server version: 10.5.21-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> exit
Bye
[root@teguhth tmp]# mysql -uuser02 -puser
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 19
Server version: 10.5.21-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> exit
Bye
[root@teguhth tmp]# mysql -uuser03 -puser
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 20
Server version: 10.5.21-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> exit
Bye
[root@teguhth tmp]#