1. The background

Typically during development, we need to create an account and grant access to a database. This article describes the operation method.

2. Create a user and assign the user rights

Create a user

CREATE USER 'zyf'@'%' identified by 'zyf';
Copy the code
  • The CREATE USER keyword is used to CREATE a USER
  • The @ symbol is preceded by the user name and followed by the host name. Usually locaohost refers to the local machine.
  • Note: % indicates a host name that means “login from anywhere”, meaning remote login is enabled.

Grant Operation Rights

GRANT SELECT,INSERT,UPDATE,DELETE ON zoo.* to 'zyf'@'%';
Copy the code
  • The GRANT keyword is used to GRANT permissions
  • Followed by the SELECT, INSERT, UPDATE, DELETE refers to can add and DELETE methods.
  • ON followed by the user name and host address

3. Backup and restore

Backing up the Database

mysqldump -u root -p zoo > backup2021-06-24.sql
Copy the code
  • The mysqldump keyword is used to back up the database
  • It’s followed by the user name and the database name
  • “>” after a greater than sign follows the file name

Restoring the Database

Create database zoo2; Mysql mysql zoo2 < backup2021-06-24.sql -u root -pCopy the code
  • Note that when restoring data, use the mysql keyword instead of mysqldump

4. MySQL logs

MySQL has four types of logs:

  • Error log: Records a problem with the MySQL service
  • Query logs: Records SQL statements connected and executed by clients
  • Slow query log: records the query that takes a long time to execute
  • Binary log: Statements that record all changed data

5. Extension

Check the location of the MySQL database file

mysql> show global variables like "%datadir%";
Copy the code

END