Raspberry PI (raspberry pie) installation of MariaDB / MySQL database

Introduction: This article mainly explains how to install MariaDB / MySQL database on raspberry PI (raspberry pie) system.

For image download, domain name resolution and time synchronization, please click Alibaba open source mirror station

1, Update system

Update the system you need to install by running the following command. It may take different time depending on the network.

sudo apt-get update

The output content of the updated system is:

root@raspberrypi:~# sudo apt-get update
Hit:1 http://archive.raspberrypi.org/debian buster InRelease
Get:2 http://raspbian.raspberrypi.org/raspbian buster InRelease [15.0 kB]
Get:3 http://raspbian.raspberrypi.org/raspbian buster/main armhf Packages [13.0 MB]
Fetched 13.0 MB in 19s (694 kB/s)
Reading package lists... Done
root@raspberrypi:~#

2, Install database

The installation can be performed automatically through the following conditions:

sudo apt-get install mariadb-server

3, Set security

When the server installation is complete, run the following command to set security for your database.

sudo mysql_secure_installation

The main purpose is to set your root password, root remote access permission, whether anonymous access is allowed, whether remote access is allowed, etc.
If you just test using this database locally, you don't have to set this.
But we still suggest you set it.

4, Set up a separate user that can be accessed remotely

You can set up a single user with sufficient root privileges to perform all operations and remote access.
You can also use the root user for configuration, but we do not recommend that you use the root user.
On the server you installed, log in to the server using mysql.
Then execute the following command:

CREATE USER 'honeymoose'@'%' IDENTIFIED BY '12345678';
GRANT USAGE ON *.* TO 'honeymoose'@'%';
GRANT ALL PRIVILEGES ON *.* TO 'honeymoose'@'%' IDENTIFIED BY '12345678' WITH GRANT OPTION; 
FLUSH PRIVILEGES;

Note: the above command is used to authorize the database after you log in to the server with root.

The purpose of executing the above SQL is to create a user and empower the user.

5, Modify configuration to allow remote access

If you connect remotely now When you use MariaDB / MySQL, you will receive an error connecting to 10061.
According to our previous tips, this is because of the binding problem.
In the old version of MySQL, modify the / etc/mysql/my.cnf file, and the modification contents are the same.
If the version of your service does not have this file, you need to modify: / etc/mysql/mariadb.conf.d/50-server.cnf for the new version of the server This file.
Comment out ' bind-address

If necessary, try restarting the server.
The command to restart the server is:

root@raspberrypi:~# service mariadb restart

The command to check whether the database server process is running is:

service mariadb status

If the server outputs the following information, indicating that the server is already running, you can try to connect remotely using the Client.

root@raspberrypi:~# service mariadb status
● mariadb.service - MariaDB 10.3.22 database server
   Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
   Active: active (running) since Sun 2020-04-05 10:57:14 EDT; 11s ago
     Docs: man:mysqld(8)
           https://mariadb.com/kb/en/library/systemd/
  Process: 1691 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, status=0/SUCCESS)
  Process: 1692 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
  Process: 1694 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= ||   VAR=`/usr/bin/galera_recovery`; [ $? -eq 0 ]   && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1 (code=exited, status=0/SUCCESS
  Process: 1771 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
  Process: 1773 ExecStartPost=/etc/mysql/debian-start (code=exited, status=0/SUCCESS)
 Main PID: 1740 (mysqld)
   Status: "Taking your SQL requests now..."
    Tasks: 31 (limit: 4035)
   Memory: 47.7M
   CGroup: /system.slice/mariadb.service
           └─1740 /usr/sbin/mysqld
Apr 05 10:57:13 raspberrypi systemd[1]: Starting MariaDB 10.3.22 database server...
Apr 05 10:57:14 raspberrypi mysqld[1740]: 2020-04-05 10:57:14 0 [Note] /usr/sbin/mysqld (mysqld 10.3.22-MariaDB-0+deb10u1) starting as process 1740 ...
Apr 05 10:57:14 raspberrypi systemd[1]: Started MariaDB 10.3.22 database server.
Apr 05 10:57:14 raspberrypi /etc/mysql/debian-start[1775]: Upgrading MySQL tables if necessary.
Apr 05 10:57:14 raspberrypi /etc/mysql/debian-start[1778]: /usr/bin/mysql_upgrade: the '--basedir' option is always ignored
Apr 05 10:57:14 raspberrypi /etc/mysql/debian-start[1778]: Looking for 'mysql' as: /usr/bin/mysql
Apr 05 10:57:14 raspberrypi /etc/mysql/debian-start[1778]: Looking for 'mysqlcheck' as: /usr/bin/mysqlcheck
Apr 05 10:57:14 raspberrypi /etc/mysql/debian-start[1778]: This installation of MySQL is already upgraded to 10.3.22-MariaDB, use --force if you still need to run mysql_upgrade
Apr 05 10:57:14 raspberrypi /etc/mysql/debian-start[1786]: Checking for insecure root accounts.
Apr 05 10:57:14 raspberrypi /etc/mysql/debian-start[1790]: Triggering myisam-recover for all MyISAM tables and aria-recover for all Aria tables

In addition, you can view the ports on which MariaDB runs:

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3306  |
+---------------+-------+
1 row in set (0.004 sec)
MariaDB [(none)]>

Reference link: https://www.cwiki.us/questions/57938848

This article is transferred from: Raspberry PI (raspberry pie) installation of MariaDB / MySQL database - alicloud developer community

Keywords: Database MySQL MariaDB

Added by dewed on Fri, 03 Dec 2021 18:26:54 +0200

Programming VIP