Install MySQL on Ubuntu 16.04
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