MySQL


Resetting a forgotten password...

...on Ubuntu Lucid. One link on this topic is http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html.

Conceptually, here's what I found I had to do:

  1. Get root.
  2. Stop mysqld.
  3. Start mysqld by hand and in the background in a special mode to grant rights to mess with passwords.
  4. Launch mysql command-line application.
  5. Fix the password.
  6. Bounce mysqld.
  7. Try it out.

Here's an illustration of some of what went on:

	[email protected]:~> sudo bash
	[email protected]:~> /etc/init.d/mysql stop
	[email protected]:~> /usr/sbin/mysqld --skip-grant-tables &
	[email protected]:~> /usr/bin/mysql -u root mysql
	Welcome to the MySQL monitor. Commands end with ; or \g.
	Your MySQL connection id is 1
	Server version: 5.1.41-3ubuntu12.6 Ubuntu)

	Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

	mysql> UPDATE user SET password=PASSWORD('snagglepuss') WHERE user='root'; FLUSH PRIVILEGES
	ERROR 1046 (3D000): No database selected
	Query OK, 0 rows affected (0.00 sec)
	mysql> exit
	Bye
	[email protected]:~> /etc/init.d/mysql start
	[email protected]:~> ps -ef | grep [m]ysql
	mysql    2751   2703  0 10:57 pts/1    00:00:00  /usr/sbin/mysqld --skip-grant-tables
	[email protected]:~> kill -9 2751
	[email protected]:~> /etc/init.d/mysql start
	[email protected]:~> /usr/bin/mysql -u root -p
	Enter password: snagglepuss
	Welcome to the MySQL monitor. Commands end with ; or \g.
	Your MySQL connection id is 36
	Server version: 5.1.41-3ubuntu12.6 Ubuntu)

	Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

	mysql> quit

Clearly, once you've got the root password, you can go back in and reset any common user's password. But, you've got to have root access.

Here's my experience from the next time this happened (March 2012):

$ sudo bash
$ /etc/init.d/mysql stop
$ mysqld_safe --skip-grant-tables &
$ mysql --user=root mysql
mysql> update user set password=PASSWORD('new-password') where user='root';
mysql> flush privileges;
mysql> exit;
$ ps -ef | grep [m]ysql
$ kill -9 (the pid associated with mysqld_safe)
$ /etc/init.d/mysql start
$ mysql -u root -p
Enter password: (new-password)

How to add a new user to MySQL

Here's how to set up for a common user (rather than root) to create, read, update and delete database tables. Here we start logged in and running the command-line utility as root and list the current users.

	mysql> select user from mysql.user;
	+------------------+
	| user             |
	+------------------+
	| root             |
	| debian-sys-maint |
	| root             |
	| root             |
	+------------------+
	4 rows in set (0.00 sec)

	mysql> create user 'russ'@'localhost' identified by 'test123';
	Query OK, 0 rows affected (0.00 sec)

	mysql> grant all on *.* to 'russ'@'localhost';
	Query OK, 0 rows affected (0.00 sec)

	mysql> select user from mysql.user;
	+------------------+
	| user             |
	+------------------+
	| root             |
	| debian-sys-maint |
	| root             |
	| russ             |
	| root             |
	+------------------+
	5 rows in set (0.00 sec)

	mysql> quit
	Bye

Later, ...

	[email protected]:~> mysql -u russ -p
	Enter password: test123
	Welcome to the MySQL monitor.  Commands end with ; or \g.
	Your MySQL connection id is 41
	Server version: 5.1.41-3ubuntu12.6 (Ubuntu)

	Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

	mysql> quit
	Bye

How to grant access to a database

Note that, even before a database is created, access to do so must be granted to the user that's going to do it. Also, granting all privileges to a user probably isn't what you want to do.

mysql> grant all privileges on database.* to 'user'@'localhost';
mysql> flush privileges;

How to change a user's password
mysql> use mysql;
mysql> update user set password=PASSWORD('new password') where User='username';
mysql> flush privileges;

However, as the GRANT statements were done using a different password, each will have to be redone.


How to set up MySQL on Ubuntu/Mint

Needed to do this from scratch recently after so many years away from it.

~ $ which mysql
~ $ sudo apt-get install mysql-client mysql-server mysqladmin
~ $ mysql -u root -p
Enter password: password
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 45
Server version: 5.5.47-0ubuntu0.14.04.1 (Ubuntu)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> grant usage on test.* to [email protected]
    -> identified by 'password';
Query OK, 0 rows affected (0.00 sec)

(in preparation for russ to create a database, do this:)
mysql> grant all on test_database.* to 'russ'@'localhost' identified by 'password';
mysql> flush privileges;

MariahDB: MySQL in use in Red Hat land...

MariahDB is schizophrenic: it's mariahdb at the package level, but mysql on other occasions, this is a bit confusing and painful. Installing it on Fedora 22:

# yum -y install mysql-server mysql

Is it running? How do I start it? How to get into it? Etc. Feel the schizophrenia!

[[email protected] etc]# ps -ef | grep [m]ysql
[[email protected]host etc]# systemctl start mariadb.service
[[email protected] etc]# !ps
ps -ef | grep [m]ysql
mysql    16250     1  0 11:02 ?        00:00:00 /bin/sh /usr/bin/mysqld_safe --basedir=/usr
mysql    16363 16250  0 11:02 ?        00:00:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib...
[[email protected] etc]# mysql -u root
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.0.23-MariaDB MariaDB Server

Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

Here are some command-line notes working with MySQL...
MariaDB [test_db]> create database test_db;
MariaDB [test_db]> grant usage on test_db.* to 'russ'@'localhost' identified by 'testpass';
MariaDB [test_db]> select user,host from mysql.user; (not illustrating output here)
MariaDB [test_db]> show grants for 'russ'@'localhost';
MariaDB [test_db]> show grants for 'russ'@'localhost';
+-------------------------------------------------------------------------------------------------------------+
| Grants for [email protected] |
+-------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'russ'@'localhost' IDENTIFIED BY PASSWORD
'*B27795960CB764DAA642A3A5D50E8CD5BE54F3D6' |
+-------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
MariaDB [test_db]> grant all privileges on test_db.* to 'russ'@'localhost' identified by 'testpass';
MariaDB [test_db]> flush privileges;
MariaDB [test_db]> show grants for 'russ'@'localhost';
+-------------------------------------------------------------------------------------------------------------+
| Grants for [email protected] |
+-------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'russ'@'localhost' IDENTIFIED BY PASSWORD
'*B27795960CB764DAA642A3A5D50E8CD5BE54F3D6' | | GRANT ALL PRIVILEGES ON `test_db`.* TO
'russ'@'localhost' |
+-------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Running a load script I see:

In facility.csv... ` text' --user=russ --password=testpass test_db '--execute=alter table `facility` \
   add column `ACTIVE_FLAG
'RROR 1166 (42000) at line 1: Incorrect column name 'ACTIVE_FLAG + mysqlimport --local '--fields-enclosed-by="' \
   --fields-terminated-by=, '--lines-terminated-by=>n' $'--columns=CSD_PATIENT_ID,IPID_FACILITY,IPID,ACTIVE_FLAG>r' \
   -u russ -ptestpass test_db .//facility.csv
mysqlimport:
Error: 1054, Unknown column 'ACTIVE_FLAG' in 'field list', when using table: facility

What happened was facility.csv had '\r' inside from DOS. I took them out, then dropped all the tables:

MariaDB [test_db]> drop table facility,insurance,patient,phones;
Query OK, 0 rows affected (0.00 sec)

Then ran my load script again which worked.


mysqlimport: Error 1045, Access denied...
mysqlimport: Error: 1045, Access denied for user 'russ'@'localhost' (using password: YES), when using table: facility

This happened from a bash script I was running to populate a database with date from a CSV file.

It turns out that MySQL's FILE privilege must be granted—and it's a global one.

mysql> grant file on *.* to 'russ'@'localhost';
mysql> flush privileges;

mysqlimport: Error 13, Can't get stat...
mysqlimport: Error: 13, Can't get stat of '/var/lib/mysql/insurance.csv' (Errcode: 2), when using table: insurance

This happened from a bash script I was running to populate a database with date from a CSV file.

What's going on here is that the file from which I'm trying to import is not in a subdirectory readable by the user the MySQL server is running as. Don't know why this is true, but using the --local option on mysqlimport solves this. Fragment from my script:

mysqlimport --local --fields-enclosed-by='"' ...