Talk:MariaDB

Better way to reset root password

The current approach with --skip-grant-tables has several downsides:

• requires multiple restarts
• security is entirely disabled for a period
• the mysqld binary is run as the root user, which might cause permissions issues when new files, like a binary log, are created by that instance

Instead, I recommend using an init-file in the [mysqld] section of my.cnf that, when executed, creates a new account that has super privileges. This works because mysqld will run the commands in init-file during startup without any authentication.

Here is what propose:

# cat /etc/mysql/my.cnf

[mysqld]
datadir=/var/lib/mysql
log-bin

init-file=/var/lib/mysql/fixroot.sql # Add this line under mysqld section

[mysqld_safe]
log-error

Create a file that will GRANT ALL ON *.* ... WITH GRANT OPTION:

# cat /var/lib/mysql/fixroot.sql

grant all on *.* to rescueroot@localhost identified by 'hq24iuwf' with grant option;

This account does not currently exist:

# mysql -e'show grants for rescueroot@localhost'

ERROR 1141 (42000) at line 1: There is no such grant defined for user 'rescueroot' on host 'localhost'

After you restart mysqld.service, the account will exist:

# mysql -urescueroot -phq24iuwf -e'show grants for rescueroot@localhost\G'

*************************** 1. row ***************************
Grants for rescueroot@localhost: GRANT ALL PRIVILEGES ON *.* TO 'rescueroot'@'localhost' IDENTIFIED BY PASSWORD '*A0F3178DA7F087BCDF3B711E1146F31E2BD9E5DA' WITH GRANT OPTION

After verifying that the rescue account has ALL ON *.* ... WITH GRANT OPTION, remove the file /var/lib/mysql/fixroot.sql then comment out the init-file line in my.cnf.

With the new super user account, you can drop/create the existing root account(s), then drop the rescue account:

# mysql -urescueroot -phq24iuwf

mysql>

mysql> select user, host from mysql.user where user='root';

list of root users

Drop all of them:

mysql> drop user root@'127.0.0.1';

Query OK, 0 rows affected (0.00 sec)

mysql> drop user root@'::1';

Query OK, 0 rows affected (0.00 sec)

mysql> drop user root@localhost;

Query OK, 0 rows affected (0.00 sec)

Create a new root account:

mysql> grant all on *.* to root@localhost identified by 'MTMzzEsrxyH4sqx' with grant option;

Query OK, 0 rows affected (0.00 sec)

Remove the rescueroot account:

mysql> drop user rescueroot@localhost;

Query OK, 0 rows affected (0.00 sec)

Finally, update the /root/.my.cnf (if it exists) to house the new root password. This is useful for automated backups etc:

# cat /root/.my.cnf

[client]
password=MTMzzEsrxyH4sqx

Mikeg (talk) 01:15, 22 November 2014 (UTC)

Case sensitivity of tables

When importing a dump from my windows DB, I was quite surprised to see that my queries were no longer working. This is because on windows case sensitiveness isn't really a thing, and so my tables were lowercase whereas my queris were in Titlecase.

To fix this I had to add the following line in the my.conf file under the mysqld block:

lower_case_table_names=1

This line isn't there by default (not commented I mean), which was quite confusing

Caféhaine (talk) 16:10, 3 November 2018 (UTC)

/etc/mysql/my.cnf

I suggest adding in the main page some comments about checking /etc/mysql/my.cnf.pacnew

I suffered from a long A start job is running for ... at boot, and it was all due to an old and extensive my.cnf

--Nicoadamo (talk) 18:41, 24 January 2019 (UTC)

That's not specific to MariaDB, see System maintenance#Deal promptly with new configuration files. -- Lahwaacz (talk) 10:06, 26 January 2019 (UTC)

The Wiki entry talks about config files being split and residing in my.cnf.d but it still only reads /etc/my.cnf and nothing is read from /etc/my.cnf.d I am using version 10.4.7 with unmodified startup files. Vario (talk) 12:18, 9 September 2019 (UTC)

Unable to run mysql_upgrade

Another thing to bear in mind is that the command mysql_upgrade -u root -p can't handle a password with special character '.

--Nicoadamo (talk) 15:43, 26 January 2019 (UTC)

Reset the root password: Update

Since we're looking for a better way to document how to reset the root MariaDB password, why not also suggest /bin/mysqladmin from mariadb-clients, which (if you already have no-password access for your root linux user) makes it as easy as:

# mysqladmin --user=root password 'hunter2'

Zebouski (talk) 14:48, 23 August 2019 (UTC)

skip-networking

I don't see this in the latest version of the server.cnf. Should the words be changed to "add" instead of uncomment, or does it belong in a different file, or maybe it is the default now?

—This unsigned comment is by KeithCu (talk) 01:24, 19 December 2019‎. Please sign your posts with ~~~~!

Feel free to make the changes yourself! See this for more information. Terry tibbles (talk) 06:37, 31 March 2020 (UTC)

Mistakes in "MySQL binary logs are taking up huge disk space"

It references a my.cnf file which no longer exists. It also says to uncomment parameter "log-bin=mysql-bin" but I don't see that parameter.

It mentions these parameters:

expire_logs_days = 10
max_binlog_size  = 100M

But it doesn't say what file / section to put it in. Server.cnf has a confusing "galera" section with a logging parameter so I'm not sure if that's where I should put them.

Fixing these would make it easier for newcomers. KeithCu (talk) 21:56, 21 December 2019 (UTC)

"Using UTF8MB4" is misleading

First it states, that mariadb already uses utf8mb4 by default and then proceeds to explain how to edit the settings to get the same result. Shouldn't we add a clause like: 'To check your current settings [...] SHOW VARIABLES LIKE "character_set_server".', or something like that?

—This unsigned comment is by Awebb (talk) 12:01, 10 June 2020‎. Please sign your posts with ~~~~!

This was optional on older releases of MariaDB, you may want to add a note explaining it's not needed anymore for new installations.

Francoism (talk) 12:57, 10 June 2020 (UTC)

mysql_secure_installation

Should be moved out of security and up to configuration where it was in the past. You install mariadb and then run # mysql_secure_installation before the system will start. It should not be squirreled away 1/3 way down the page under security. That makes the config and startup section read like a train-wreck. Technical documentation should read top-down, and not be obscured by links popping to various parts of the page. When you see links to necessary information that break up the flow of the document, you need to refactor to remove them. David C. Rankin, J.D.,P.E. -- Rankin Law Firm, PLLC (talk) 19:06, 19 March 2021 (UTC)

I can CONFIRM this should be moved because I just spent the last 3 days trying to figure out what was wrong with my installation using every troubleshooting guide around just to find that running that one command was necessary even though none of the install instructions mentioned it at all. Mind you, I'm not a n00b to MYSQL but if install instructions don't explicitly mention something, I tend to skip my old patterns thinking something has gone obsolete. Apparently it has not.

Ehrichweiss (talk) 15:51, 2 October 2022 (UTC)

I had the same confusion after reading installation section: is _secure_installation script not needed anymore?

The Note is moved up before service start and make it clear what is done. See MariaDB&diff=762003&oldid=761278 Diff

Maybe we should move security section up as first section of configuration. In my opinion, security are the most important configurations that should be done first. --Fengchao (talk) 23:36, 23 December 2022 (UTC)

10.4 to 10.5 upgrade crash: Headsup

The suggested solution to move /var/lib/mysql/ib_logfile out of the way resulted in the following error for me:

[ERROR] InnoDB: File ./ib_logfile0 was not found
[ERROR] InnoDB: Plugin initialization aborted with error Generic error
[Note] InnoDB: Starting shutdown...
[ERROR] Plugin 'InnoDB' init function returned error.
[ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
[ERROR] Unknown/unsupported storage engine: InnoDB
[ERROR] Aborting

I had to create an empty ib_logfile0 (using touch /var/lib/mysql/ib_logfile0) to fix the issue. If it's safe to do so, the article can be updated.

--Bady (talk) 05:10, 6 July 2022 (UTC)

Page already updated. The MariaDB#10.4 to 10.5 upgrade crash: "InnoDB: Upgrade after a crash is not supported. The redo log was created with MariaDB 10.4.x" section need some rewording though. --Fengchao (talk) 23:13, 23 December 2022 (UTC)