Talk:MariaDB

From ArchWiki
Jump to navigation Jump to search

Two things that might be worth mentioning:

Password for DB root is limited to 79 characters (I was not aware of that as my password policy for databases was 80 characters after setting password I could not log in) If the one decides to reset the password then after everything is done but before loading services it might be worth killing mysql. I have done it simple way: ps ax | grep /usr/bin/mysqld and then kill it by PID (I guess there are better ways of doing this, I just appreciate this particular way). Hope this helps Gregosky (talk) 23:43, 29 August 2013 (UTC) Gregosky

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)

Can’t open and lock privilege tables

I think the troubleshooting case "If you run mysqld and the following error appears: Fatal error: Can’t open and lock privilege tables: Table ‘mysql.host’ doesn’t exist" is the default when installing on a host where mysql was never installed, isn't it? (At least for me it was.) If that's the case, I'd propose moving that to the very top (before "Start/enable mysqld.service"), because nearly everyone will have to do this. --Marian (talk) 11:55, 26 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)