1. Linux

1.1. Uninstallation

Enter the following commands at a Command Line.

mysql -u root -p -e "SHOW DATABASES"
mysql -u root -p -e "DROP DATABASE test"
mysql -u root -p -e "DROP DATABASE performance_schema"
mysql -u root -p -e "DROP DATABASE information_schema"  <????>
mysql -u root -p -e "DROP DATABASE mysql"
mysql -u root -p -e "DROP DATABASE <db>"
mysql -u root -p -e "SHOW DATABASES"
sudo apt-get purge mysql-server-5.5
sudo apt-get purge mysql-server-core-5.5
sudo apt-get purge mysql-common
sudo apt-get autoremove
sudo rm -rf /etc/mysql

1.2. Installation

  • Uninstall MariaDB.

Enter the following commands at a Command Line.

sudo apt-get install mysql-server       # Level 1 password for MySQL administrative "root" user
sudo apt-get install php5-mysql         # PHP 5
sudo apt-get install php7.0-mysql       # PHP 7
sudo vdir /etc/mysql/debian.cnf
sudo cat /etc/mysql/debian.cnf
sudo mysqladmin -p status
sudo apt-get install mysql-server       # Level 1 password for MySQL administrative "root" user

1.3. Configuration

Contents of the /etc/mysql/conf.d/smru.cnf file.

[mysqld]
character-set-server = utf8
collation-server = utf8_unicode_ci
skip-character-set-client-handshake

Enter the following commands at a Command Line.

mysql -e "SHOW VARIABLES LIKE 'char%'; SHOW VARIABLES LIKE 'collation%'"
sudo service mysql restart
mysql -e "SHOW VARIABLES LIKE 'char%'; SHOW VARIABLES LIKE 'collation%'"

1.4. Configuration for smru user

Enter the following commands at a Command Line.

mysql -p -u root                                        # Level 1 password
SELECT Host, User, Password FROM mysql.user;
CREATE USER 'smru'@'localhost' IDENTIFIED BY '********';
GRANT ALL PRIVILEGES ON *.* TO 'smru'@'localhost';
FLUSH PRIVILEGES;
SELECT Host, User, Password FROM mysql.user;
\q

Replace all passwords by 8 asterisks in the ~delta/.mysql_history file.

Contents of the ~delta/.my.cnf file with the level 2 password.

[client]
user=smru
password="********"

Enter the following commands at a Command Line.

chmod 600 ~delta/.my.cnf
vdir ~delta/.my.cnf
mysqladmin status
mysql -e 'SELECT Host, User, Password FROM mysql.user'
mysql -e 'SHOW DATABASES'

1.5. Configuration for another user

Enter the following commands at a Command Line.

mysql -p -u root                                        # Level 1 password
SELECT Host, User, Password FROM mysql.user;
CREATE USER '<user>'@'localhost' IDENTIFIED BY '<pass>';
GRANT ALL PRIVILEGES ON *.* TO '<user>'@'localhost';
FLUSH PRIVILEGES;
SELECT Host, User, Password FROM mysql.user;
\q

Replace all passwords by 8 asterisks in the ~<user>/.mysql_history file.

Contents of the ~<user>/.my.cnf file with the level 2 password.

[client]
user=<user>
password="********"

Enter the following commands at a Command Line.

chmod 600 ~<user>/.my.cnf
vdir ~<user>/.my.cnf
mysqladmin status
mysql -e 'SELECT Host, User, Password FROM mysql.user'
mysql -e 'SHOW DATABASES'

2. Windows

3. Clear "root" user password

In case you need to clear the MySQL administrative "root" user password.

Enter the following commands at a Command Line.

mysqladmin -p -u root password ""

4. Copy database

Enter the following commands at a Command Line.

mysqldump -u root -p db > /tmp/copy-db.sql
mysql -u root -p -e "CREATE DATABASE copy-db"
mysql -u root -p copy-db < /tmp/copy-db.sql
mysqldump db > /tmp/copy-db.sql
mysql -e "CREATE DATABASE copy-db"
mysql copy-db < /tmp/copy-db.sql

5. Rename database

Enter the following commands at a Command Line.

mysqldump -u root -p old-db > old-db.sql
mysql -u root -p -e "CREATE DATABASE new-db"
mysql -u root -p new-db < old-db.sql
mysql -u root -p -e "DROP DATABASE old-db"

6. Useful commands

Enter the following commands at a Command Line.

mysql -e 'show databases'
mysql -e 'create database tmp'
mysql --local-infile tmp < import.sql
mysql -e 'show databases'
mysql tmp -e 'show tables'
mysql tmp -e 'show columns from douwe'
mysql tmp -e 'select * from douwe'
mysql tmp -e 'select authors, db, pages, recnr, volume, year from douwe'
mysql -p -u root

SHOW DATABASES;
SHOW TABLES;
SELECT * FROM mysql.user;
SELECT Host, User, Password FROM mysql.user;
SHOW GRANTS FOR 'root'@'localhost';
SHOW GRANTS FOR 'smru'@'localhost';
DROP TABLE douwe;

\q
mysql -p -u smru shokloun_db

SHOW TABLES;
SELECT * FROM tb_user;
SELECT * FROM zp_administrators;
\q