From ArchWiki
Jump to: navigation, 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

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)

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)