MariaDB is an open source relational database management system. It was originally designed as a backward compatible binary binary replacement for MySQL.
MariaDB is developed and maintained by the original developers of MySQL and the open source community.
This guide explains how to install and secure MariaDB Ubuntu 20.04(WSL)
1. Basic conditions
The installation environment for this article is Ubuntu20.04 on WSL (Windows Subsystem for Linux). We assume that you have a root user or administrative access with sudo privileges.
2. Install MariaDB
sudo apt update
sudo apt -y install mariadb-serverAfter the installation is complete, the MariaDB service will not start automatically, please use the following command to start the MariaDB service:
sudo service mysql start3. Configure MariaDB
The MariaDB service has a script called mysql_secure_installation, which allows you to easily improve the security of the database server.
Execute the script without parameters:
sudo mysql_secure_installationThe script will prompt you to enter the root password. Since you have not yet set the root password, just press " Enter " here.
Enter current password for root (enter for none):
At the next prompt, you will be asked to set the password of the MySQL root user, enter n :
Set root password? [Y/n] n
On Ubuntu, the auth_socket plugin will authenticate the MariaDB root user by default. The plug-in works by checking whether the local system user calling the client program matches the specified MariaDB username.
Next, you will be asked to delete anonymous users, restrict root user access to the local computer, delete the test database and reload the privilege table. You can modify it according to your preferences:
# sudo mysql_secure_installation
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!
In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.
Enter current password for root (enter for none):
OK, successfully used password, moving on...
Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.
# 修改root用户密码
Set root password? [Y/n] n
... skipping.
By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.
# 删除匿名用户
Remove anonymous users? [Y/n] y
... Success!
Normally, root should only be allowed to connect from 'localhost'. This
ensures that someone cannot guess at the root password from the network.
# 禁止root远程登录
Disallow root login remotely? [Y/n] n
... skipping.
By default, MariaDB comes with a database named 'test' that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.
# 删除测试数据库和访问权限
Remove test database and access to it? [Y/n] n
... skipping.
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
# 现在重新加载特权表
Reload privilege tables now? [Y/n] y
... Success!
Cleaning up...
All done! If you've completed all of the above steps, your MariaDB
installation should now be secure.
Thanks for using MariaDB!4. Log in as root
To interact with MariaDB server from the command line, use the mysql client utility or its alias mariadb. This tool is installed as a dependency of the MariaDB server package.
The auth_socket plug-in authenticates users who connect from localhost through a Unix socket file. This means that you cannot authenticate as the root user by providing a password.
To log in to the MariaDB server as the root user (note: it is no longer necessary to enter the -u root -p parameter in the current mode), enter:
sudo mysqlYou will be provided with MariaDB shell as shown below:
# sudo mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 54
Server version: 10.3.31-MariaDB-0ubuntu0.20.04.1 Ubuntu 20.04
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)]>
MariaDB [(none)]> quit
Bye5. Create a super user
On Ubuntu systems running MariaDB, by default, the root MariaDB user is set to use the unix_socket plugin instead of using a password for authentication. Since the server uses the root account to perform tasks such as log rotation and starting and stopping the server, it is best not to change the authentication details of the root account. Instead, the package maintainer recommends creating a separate administrative account for password-based access.
If you want to use an external program (such as phpMyAdmin) to log in to the MariaDB server as the root user, you have two options.
The first is to change the authentication method from auth_socket to mysql_native_password. You can do this by running the following command:
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'very_strong_password';
FLUSH PRIVILEGES;The second recommended option is to create a new dedicated administrative user who can access all databases:
GRANT ALL PRIVILEGES on *.* TO 'admin'@'%' IDENTIFIED BY 'very_strong_password' WITH GRANT OPTION;
SET PASSWORD FOR admin=PASSWORD('very_strong_password');
FLUSH PRIVILEGES;
QUIT;You can use any name you want to name the administrative user, but make sure to use a strong password. The complete example is as follows:
# sudo mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 43
Server version: 10.3.31-MariaDB-0ubuntu0.20.04.1 Ubuntu 20.04
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)]> GRANT ALL PRIVILEGES on *.* TO 'admin'@'%' IDENTIFIED BY 'very_strong_password' WITH GRANT OPTION;
RD FOQuery OK, 0 rows affected (0.000 sec)
R admiMariaDB [(none)]> SET PASSWORD FOR admin=PASSWORD('very_strong_password');
Query OK, 0 rows affected (0.000 sec)
ES;
Q
MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> QUIT;
ByeYou can use the mysqladmin tool (a client that can run administrative commands) to test the new user. The following mysqladmin command connects to MariaDB as the admin user and returns the version number after prompting the user for a password:
sudo mysqladmin -u admin -p versionYou will receive output similar to the following:
# sudo mysqladmin -u admin -p version
Enter password:
mysqladmin Ver 9.1 Distrib 10.3.31-MariaDB, for debian-linux-gnu on x86_64
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Server version 10.3.31-MariaDB-0ubuntu0.20.04.1
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/run/mysqld/mysqld.sock
Uptime: 10 min 31 sec
Threads: 6 Questions: 140 Slow queries: 0 Opens: 38 Flush tables: 1 Open tables: 31 Queries per second avg: 0.2216, MariaDB use
- MariaDB management and basic configuration
https://segmentfault.com/a/1190000040671147 - MariaDB user and permission management
https://segmentfault.com/a/1190000040671500 - MariaDB database command line guide
https://segmentfault.com/a/1190000040671849 - MariaDB import test database employees
https://segmentfault.com/a/1190000040671972
7. Other links
- Install MariaDB on the Ubuntu native system:
https://blog.csdn.net/fenglailea/article/details/107857403 - How To Install MariaDB on Ubuntu 20.04:
https://www.digitalocean.com/community/tutorials/how-to-install-mariadb-on-ubuntu-20-04-quickstart - How To Import and Export Databases in MySQL or MariaDB:
https://www.digitalocean.com/community/tutorials/how-to-import-and-export-databases-in-mysql-or-mariadb - An Introduction to Queries in MySQL:
https://www.digitalocean.com/community/tutorials/introduction-to-queries-mysql - How to Manage MySQL Databases and Users from the Command Line
https://linuxize.com/post/how-to-manage-mysql-databases-and-users-from-the-command-line/
ubuntu
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用。你还可以使用@来通知其他用户。