How to Install and Use MySQL on Ubuntu 16.04
Synonyms:
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
$[username]$
$[password]$
$[database]$
mysql
mysql -u root -p
CREATE DATABASE $database$ CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE USER '$username$'@'localhost' IDENTIFIED BY '$password$';
GRANT ALL PRIVILEGES ON $database$.* TO '$username$'@'localhost';
FLUSH PRIVILEGES;
exit
Manage the database
$[database]$
mysql
mysql $database$
mysql -u root -p
# Change database
use $database$
# Show tables
SHOW TABLES;
# Quit
\q
Backup MySQL database $database$
mysqldump --lock-all-tables --databases $database$ | bzip2 -c > $filename$
mysqldump -u root -p --lock-all-tables --databases $database$ | bzip2 -c > $filename$
Restore MySQL database $database$
bunzip2 < $filename$ | mysql $database$
bunzip2 < $filename$ | mysql -u root -p $database$
Connect MySQL from another machine (optional)
$[texteditor]$
1) Listen to 0.0.0.0
sudo $texteditor$ /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$';
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