MySQL

I hate MySQL root password, but I don't know how to completely get rid of root password, as PostgreSQL does.

Install MySQL (interactive)

sudo apt -y install mysql-server
sudo mysql_secure_installation
sudo mysql
  • config file: /etc/mysql/mysql.conf.d/mysqld.cnf
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
binlog_expire_logs_seconds      = 60

Restart MySQL

sudo /etc/init.d/mysql restart
sudo /etc/init.d/mysql status

Never ask me for root password again!!! (interactive)

mysql_config_editor set --login-path=client --host=localhost --user=root --password

now it is possible to use mysql without password

mysql

Create user and database

mysql
mysql -u root -p
CREATE DATABASE {{ database.value }} CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE USER '{{ username.value }}'@'localhost' IDENTIFIED BY '{{ password.value }}';
GRANT ALL PRIVILEGES ON {{ database.value }}.* TO '{{ username.value }}'@'localhost';
FLUSH PRIVILEGES;
exit

Manage the database

mysql
mysql {{ database.value }}
mysql -u root -p
# Change database
use {{ database.value }}
# Show tables
SHOW TABLES;
# Quit
\q

Backup MySQL database {{ database.value }}

mysqldump --lock-all-tables --databases {{ database.value }} | bzip2 -c > {{ filename }}
mysqldump -u root -p --lock-all-tables --databases {{ database.value }} | bzip2 -c > {{ filename }}

Restore MySQL database {{ database.value }}

bunzip2 < {{ filename }} | mysql {{ database.value }}
bunzip2 < {{ filename }} | mysql -u root -p {{ database.value }}

Connect MySQL from another machine (optional)

1) Listen to 0.0.0.0

sudo {{ texteditor.value }} /etc/mysql/mysql.conf.d/mysqld.cnf
bind-address = 0.0.0.0

2) Create root user that can remotely access

mysql
mysql -u root -p
CREATE USER 'root'@'%' IDENTIFIED BY '{{ password.value }}';
GRANT ALL ON *.* TO 'root'@'%';
GRANT USAGE ON  *.* TO 'root'@'%' WITH GRANT OPTION;
flush privileges;
exit

3) Restart MySQL

sudo /etc/init.d/mysql restart

Windows Desktop MySQL Management Tool

SQLyog Community Edition