MySQL database account creation, authorization, data export and import operation examples

MySQL database account creation, authorization, data export and import operation examples

This article describes the examples of creating accounts, authorization, data export and import operations in MySQL database. Share with you for your reference, the details are as follows:

1. Account creation and authorization

grant all privileges on *.* to 'yangxin'@'%' identified by 'yangxin123456' with grant option;
  • privileges: means all permissions are granted to the user. You can also specify specific permissions, such as: SELECT, CREATE, DROP, etc.
  • on: indicates which databases and tables these permissions are effective for. The format is database name.table name. Writing "*" here means all databases and all tables. If I want to specify that permissions be applied to the user table in the test library, I can write: test.user
  • to: The user to whom the permission is granted. Format: "user name"@"login IP or domain name". % means there is no restriction and you can log in from any host. For example: "yangxin"@"192.168.0.%", which means that the user yangxin can only log in in the 192.168.0 IP segment
  • identified by: specifies the user's login password
  • with grant option: allows users to grant their permissions to other users

2. Check user permissions

show grants for root;

3. Reclaim permissions

revoke create on *.* from 'yangxin';

4. Delete User

drop user test;

5. Change password

1> Update mysql.user table

# Before mysql5.7 update mysql.user set password=password('123456') where user='root';
# After mysql5.7 update mysql.user set authentication_string=password('123456') where user='root';

2> Use the set password command

grammar:

set password for 'user name'@'login address'=password('password')

3>mysqladmin

Syntax: mysqladmin -u username -p old password password new password

Remember to refresh permissions:

flush privileges;

Data export:

1. Export structure but not data

mysqldump -d database name -uroot -p > xxx.sql

2. Export data but not structure

mysqldump -t database name -uroot -p > xxx.sql

3. Export data and table structure

mysqldump database name -uroot -p > xxx.sql

4. Export the structure of a specific table

mysqldump -uroot -p -B database name --table table name> xxx.sql
#mysqldump [OPTIONS] database [tables]

Import data:

mysql -uroot -p database name < file name

or:

Then select the database to be imported:

#use database;
#source /tmp/xxx.sql

Readers who are interested in more MySQL-related content can check out the following topics on this site: "Summary of MySQL Index Operation Skills", "Summary of MySQL Common Functions", "Summary of MySQL Log Operation Skills", "Summary of MySQL Transaction Operation Skills", "Summary of MySQL Stored Procedure Skills" and "Summary of MySQL Database Lock-Related Skills".

I hope this article will be helpful to everyone's MySQL database design.

You may also be interested in:
  • Pycharm remotely connects to the server and runs and debugs
  • Pycharm remote connection server debugging tensorflow cannot load problem
  • The whole process of Pycharm connecting to the remote server and remote debugging
  • Pycharm synchronous remote server debugging method steps
  • mysql create database, add users, user authorization practical method
  • Pycharm remote debugging and MySQL database authorization issues

<<:  How to implement web stress testing through Apache Bench

>>:  Detailed steps to install Sogou input method on Ubuntu 20.04

Recommend

Introduction to ApplicationHost.config (IIS storage configuration area file)

For a newly created website, take ASP.NET MVC5 as...

Detailed installation process of nodejs management tool nvm

nvm nvm is responsible for managing multiple vers...

Using puppeteer to implement webpage screenshot function on linux (centos)

You may encounter the following problems when ins...

Linux kernel device driver advanced character device driver notes

/****************** * Advanced character device d...

Writing Snake Game with Native JS

This article shares the specific code of writing ...

Three ways to implement animation in CSS3

This is a test of the interviewee's basic kno...

How to lock a virtual console session on Linux

When you are working on a shared system, you prob...

MySQL 8.0.17 installation graphic tutorial

This article shares with you the MySQL 8.0.17 ins...

jQuery realizes the effect of theater seat selection and reservation

jQuery realizes the effect of theater seat select...

Detailed explanation of the cache implementation principle of Vue computed

Table of contents Initialize computed Dependency ...

Basic usage examples of listeners in Vue

Table of contents Preface 1. Basic usage of liste...

How to add interface listening mask in Vue project

1. Business Background Using a mask layer to shie...

Tudou.com front-end overview

1. Division of labor and process <br />At T...

Detailed explanation of MySQL data grouping

Create Group Grouping is established in the GROUP...