From ArchWiki
Jump to navigation Jump to search

MySQL depricated pass prefix

When trying to import any database using mysql command or phpMyAdmin, I get this error: ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'Warning: Using unique option prefix pass instead of password is deprecated and w' at line 1

—This unsigned comment is by TuxLyn (talk) 26 May 2014. Please sign your posts with ~~~~!

Closing since it's an error from 2014. --Larivact (talk) 07:20, 5 January 2019 (UTC)

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

Change "mysqld.service" -> "mysqld"

In newer versions of systemd we can drop the ".service" part, it's easier to read/type. Also only first time systemd units are mentioned it should link to systemd article. axper (talk) 17:51, 7 April 2014 (UTC)

We had a similar discussion some time back at Talk:Beginners' guide#service suffix. Most people seem to be in favour of keeping the .service, but no official rule/recommendation has been decided on yet. If you disagree or want to add to the discussion, please do!
--Lonaowna (talk) 20:03, 7 April 2014 (UTC)
Alright, so we are keeping it. axper (talk) 20:13, 7 April 2014 (UTC)
Closing since resolved. --Larivact (talk) 07:21, 5 January 2019 (UTC)

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

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


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> select user, host from mysql.user where user='root';
list of root users

Drop all of them:

mysql> drop user root@'';
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

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 ‘’ 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)


The installation as described now is not working (anymore).

With these steps:

# zfs create -o recordsize=8k -o primarycache=metadata -o mountpoint=/srv/data zdata/data
# mysql_install_db --user=mysql --basedir=/usr --datadir=/srv/data
# systemctl start mysqld
# mysql_secure_installation

So a clean database was created but I ended up with a database I cannot access:

# mysqladmin -u root 

results in:

# mysqladmin: connect to server at 'localhost' failed
# error: 'Access denied for user 'root'@'localhost' (using password: NO)'

I'will try to find the initial password that mariadb is using during install.

Theking2 (talk) 09:49, 15 May 2015 (UTC)

I think you need to use the -p option to use a password: mysqladmin -u root -p -- Lahwaacz (talk) 09:58, 15 May 2015 (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:


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

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


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)


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 ~~~~!

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)