...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:

	russ@tuonela:~> sudo bash
	root@tuonela:~> /etc/init.d/mysql stop
	root@tuonela:~> /usr/sbin/mysqld --skip-grant-tables &
	root@tuonela:~> /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
	root@tuonela:~> /etc/init.d/mysql start
	root@tuonela:~> ps -ef | grep [m]ysql
	mysql    2751   2703  0 10:57 pts/1    00:00:00  /usr/sbin/mysqld --skip-grant-tables
	root@tuonela:~> kill -9 2751
	root@tuonela:~> /etc/init.d/mysql start
	root@tuonela:~> /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)

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, ...

	russ@tuonela:~> 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

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;

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.

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 russ@localhost
    -> 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 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!

[root@localhost etc]# ps -ef | grep [m]ysql
[root@localhost etc]# systemctl start mariadb.service
[root@localhost 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...
[root@localhost 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)]>

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 russ@localhost |
+-------------------------------------------------------------------------------------------------------------+
| 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 russ@localhost |
+-------------------------------------------------------------------------------------------------------------+
| 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 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 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='"' ...

username needs privileges, to perform these actions.

1. Export the database to a dump/back-up file:

   # mysqldump -u username -p --opt database-name > database-name.sql
   

2. Secure the dump/back-up file and make it smaller. This command will solicit a password from you by which the data will be hash-encrypted:

   # zip --encrypt database-name.zip database-name.sql
   

3. Copy the zipped and encrypted file elsewhere (another host):

   # scp database-name.zip user@hostname:destination-path
   

4. Unzip this file to its full, unencrypted self:

   # unzip -P password database-name.zip
   

5. Import the database to the new server:

   # mysql -u username -p database-name < database-name.sql
   

6. Validate imported data: You're a bit on your own here, but you could run SELECT queries with COUNTs simultaneously on both the old and new databases, etc. Stuff like:

SELECT table_name, table_rows
    FROM schema.tables
    WHERE table_schema = database-name;