The version used in this article is 5.5.62 or 5.7.29. Apt is used for direct installation.

The installation

  • Run sudo apt-get install mysql-server
  • Check whether the installation is successful: sudo netstat – tap | grep mysql
  • Service after the start port query: sudo netstat anp | grep mysql

Skip the MySQL password authentication process and reset the password

  • Into the vim/etc/my CNF or/etc/mysql/mysql. Conf. D/mysqld. CNF
  • Add skip-grant-tables on any line after [mysqld] to skip password validation
  • MySQL restart :sudo service MySQL restart /etc/init.d/ MySQL restart
  • Enter mysql to change the password
  • Update user set authentication_string=password(” oden123 “) where user= “root”;
  • Run the Flush PRIVILEGES command
  • Log in using the password :mysql -u root -p
  • Edit my.cnf to remove the added content and restart MySQL

Creating a New user

  • The create user ‘oden’ @ ‘localhost’;
  • Set password for ‘oden’ @ ‘localhost’ = password(‘ 123456 ‘);

Configure new user rights

  • Grant all PRIVILEGES on testDb.* to ‘oden’ @ ‘localhost’ : Grant all privileges on testDb to oden

Configure to allow remote access

  • Change bind-address = 0.0.0.0 in my.cnf or mysqld. CNF
  • Update user set host= ‘%’ where user= ‘root’;
  • Grant all PRIVILEGES on *.* to ‘root’ @ ‘%’ identified by ‘ode123’ WITH Grant OPTION;
    • All the PRIVILEGES that gives all permissions to the specified users, here also can be replaced by giving a specific permissions, such as: the select, insert, update, delete, create, drop, etc., the specific permission to use “, “half Angle between commas
  • flush privileges

Configuration management

  • After the installation is complete, the following directories will be created after the installation
    • Database directory: /var/lib/mysql/
    • Configuration files: /usr/share/mysql (commands and configuration files), /etc/mysql (e.g. My.cnf)
    • Related commands: /usr/bin(mysqladmin mysqldump) and /usr/sbin
    • Start script: /etc/init.d/mysql

other

  • After a new mysql user is created or a password is changed, flush PRIVILEGES must be used to refresh the MYSQL system privileges table. Otherwise, access is denied.

Cli command

  • Use dbName: Select a database

uninstall

  • To delete the mysql data file :sudo rm /var/lib/mysql.-r
  • Run the sudo rm /etc/mysql.-r command to delete the mysql configuration file
  • Uninstall mysql automatically (including server and client) :
    • sudo apt-get autoremove mysql* –purge
    • sudo apt-get remove apparmor
  • In the terminal check MySQL dependencies: DPKG — list | grep MySQL
  • Delete dependencies: sudo apt-get remove dbconfig-mysql
  • If there are problems with the reinstallation, restarting the server here may solve the problem

Common commands

  • sudo service mysql restar
  • sudo service mysql start
  • sudo service mysql stop
  • sudo service mysql status
  • View the users and permissions: SELECT DISTINCT CONCAT (‘ User: ‘ ‘ ‘, the User, ‘ ‘ ‘@’ ‘ ‘, the host, ‘ ‘ ‘. ‘) AS query FROM mysql.user; Selsec user,host from mysql.user;
  • Open port :sudo ufw allow 3306
  • Disable the firewall by running the sudo ufw disable command
  • Run the sudo ufw enable command to enable the firewall
  • To check the firewall status, run sudo ufw status
  • The configuration file path varies according to the version
    • 5.5.62: / etc/my CNF
    • 5.7.29: / etc/mysql/mysql. Conf. D/mysqld. CNF