I've used mariadb for years, in an embedded application running on a Rapsberry Pi. When I first built the system about 5 years ago, I learned just enough about mariadb administration to get it working, and make it do what I needed. It was actually quite simple, taking just a few hours, as it installed and ran with almost no changes in configuration. The process I have used all this time is this:
log into Debian as user "pi", with a password.
Install mariadb-server and mariadb-client, using "sudo apt -y install".
After installation, kill the daamon, and re-start it from the command line with the --skip-grant-tables option.
Run the below script, mysql_root_setup.sql, to set the root user privileges. It is run using:
sudo -user=root < mysql_root_setup.sql
mysql_root_setup.sql:
`
UPDATE mysql.user SET Grant_priv='Y', Super_priv='Y' WHERE User='root' ;
FLUSH PRIVILEGES ;
GRANT ALL ON . TO 'root'@'localhost' ;
FLUSH PRIVILEGES ;
SHOW GRANTS FOR 'root'@'localhost' ;
Quit
`
Kill the daemon, then restart with "service mysqld start"
Run the below script, mysql_user_setup.sql, to create the default user account and create the database. It is run using:
sudo -user=root < mysql_user_setup.sql
mysql_user_setup.sql:
`
`
GRANT ALL PRIVILEGES ON . TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*418CF519725AAA1BFF555A40A7A6DE18C406B1F2' WITH GRANT OPTION ;
FLUSH PRIVILEGES ;
CREATE USER 'pi'@'localhost' IDENTIFIED BY 'Raspberry' ;
UPDATE mysql.user SET Grant_priv='Y', Super_priv='Y' WHERE User='pi' ;
FLUSH PRIVILEGES ;
GRANT ALL ON . TO 'pi'@'localhost' ;
FLUSH PRIVILEGES ;
GRANT ALL PRIVILEGES ON . TO 'pi'@'localhost' IDENTIFIED BY PASSWORD '*418CF519725AAA1BFF555A40A7A6DE18C406B1F2' WITH GRANT OPTION ;
SHOW GRANTS FOR 'pi'@'localhost' ;
CREATE DATABASE qdtimerdata;
USE qdtimerdata
CREATE TABLE timerdata1 (
idx INT(10) NOT NULL AUTO_INCREMENT,
timer_range INT(1) NOT NULL,
timer_lane INT(1) NOT NULL,
match_num INT(11) NOT NULL,
timer_enabled BOOLEAN NOT NULL,
timer_val CHAR(5) NOT NULL,
timestamp TIMESTAMP NULL DEFAULT NOW() ON UPDATE NOW(),
PRIMARY KEY(idx)
) AUTO_INCREMENT=1;
Quit
`
The above has worked perfectly on the Raspberry Pi for years, on perhaps 100 systems. But, I now have to move to a new hardware platform, as the Raspberry Pi's are very hard to come by, and ver expenive. The new platform is a NanoPi-R5C running OpenWRT.
When I try to follow the same process everything works ok until it comes to running the two scripts. Obviously, the '-user=root" option has changed to "-u root", so I made that change. But, both scripts fail on the first line. The "root" script gives the error: "ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)"
Now, there is one difference I see between OpenWRT and Debian that MAY be significant. I don't know enough about mariadb to know for sure. On the RPi under Debian, I am logged in as a regular user, named pi, which has sudo rights. OpenWRT, by default, has only a root account. I can, of course, setup a 'pi' account, but I suspect there is something in the user configuration or rights that mariadb requires that I am missing.
Any idea how I can resolve the problems I'm having? Or where I CAN get helpl? I've been beating my head against the wall for two days, and made very little progress, and finding it very hard to find support for mariadb beyond web searches which have yielded little of value, and huge amounts of conflicting, or flat-out wrong, information. I'm getting desperate...
