Difference between revisions of "Talk:MySQL"

From ArchWiki
Jump to: navigation, search
m (I added topic to discussion to indicate there might be some information missing in related article.)
m (Installation)
 
(15 intermediate revisions by 9 users not shown)
Line 1: Line 1:
Two things that might be worth mentioning:
+
== 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)
 
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:
 
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:
Line 7: Line 11:
 
[[User:Gregosky|Gregosky]] ([[User talk:Gregosky|talk]]) 23:43, 29 August 2013 (UTC)
 
[[User:Gregosky|Gregosky]] ([[User talk:Gregosky|talk]]) 23:43, 29 August 2013 (UTC)
 
Gregosky
 
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.
 +
[[User:Axper|axper]] ([[User talk: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 {{ic|.service}}, but no official rule/recommendation has been decided on yet. If you disagree or want to add to the discussion, please do!
 +
:--[[User:Lonaowna|Lonaowna]] ([[User talk:Lonaowna|talk]]) 20:03, 7 April 2014 (UTC)
 +
 +
::Alright, so we are keeping it. [[User:Axper|axper]] ([[User talk: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:
 +
 +
{{hc|# cat /etc/mysql/my.cnf|<nowiki>
 +
[mysqld]
 +
datadir=/var/lib/mysql
 +
log-bin
 +
 +
init-file=/var/lib/mysql/fixroot.sql # Add this line under mysqld section
 +
 +
[mysqld_safe]
 +
log-error</nowiki>}}
 +
 +
Create a file that will GRANT ALL ON *.*  ... WITH GRANT OPTION:
 +
 +
{{hc|# cat /var/lib/mysql/fixroot.sql|
 +
grant all on *.* to rescueroot@localhost identified by 'hq24iuwf' with grant option;}}
 +
 +
This account does not currently exist:
 +
 +
{{hc|# 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 {{ic|mysqld.service}}, the account will exist:
 +
 +
{{hc|# 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 {{ic|/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:
 +
 +
{{hc|# mysql -urescueroot -phq24iuwf|
 +
mysql>}}
 +
{{hc|mysql> select user, host from mysql.user where user<nowiki>=</nowiki>'root';|
 +
list of root users}}
 +
 +
Drop all of them:
 +
 +
{{hc|mysql> drop user root@'127.0.0.1';|
 +
Query OK, 0 rows affected (0.00 sec)}}
 +
{{hc|mysql> drop user root@'::1';|
 +
Query OK, 0 rows affected (0.00 sec)}}
 +
{{hc|mysql> drop user root@localhost;|
 +
Query OK, 0 rows affected (0.00 sec)}}
 +
 +
Create a new root account:
 +
 +
{{hc|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:
 +
 +
{{hc|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:
 +
 +
{{hc|# cat /root/.my.cnf|
 +
<nowiki>[client]
 +
password=MTMzzEsrxyH4sqx</nowiki>}}
 +
[[User:Mikeg|Mikeg]] ([[User talk: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: <code>Fatal error: Can’t open and lock privilege tables: Table ‘mysql.host’ doesn’t exist</code>" 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. --[[User:Marian|Marian]] ([[User talk:Marian|talk]]) 11:55, 26 November 2014 (UTC)
 +
 +
== Installation ==
 +
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.
 +
 +
[[User:Theking2|Theking2]] ([[User talk:Theking2|talk]]) 09:49, 15 May 2015 (UTC)
 +
 +
:I think you need to use the {{ic|-p}} option to use a password: {{ic|mysqladmin -u root -p}} -- [[User:Lahwaacz|Lahwaacz]] ([[User talk:Lahwaacz|talk]]) 09:58, 15 May 2015 (UTC)

Latest revision as of 10:23, 15 May 2015

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
[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)

Installation

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)