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

JavaScript to achieve a simple countdown effect

This article example shares the specific code of ...

7 interview questions about JS this, how many can you answer correctly

Preface In JavaScript, this is the function calli...

Using Vue3 (Part 1) Creating a Vue CLI Project

Table of contents 1. Official Documentation 2. Cr...

Tutorial on installing MySQL 8.0.11 using RPM on Linux (CentOS7)

Table of contents 1. Installation preparation 1. ...

Detailed description of the life cycle of React components

Table of contents 1. What is the life cycle 2. Lo...

Analysis and summary of the impact of MySQL transactions on efficiency

1. Database transactions will reduce database per...

How to install MySQL database on Ubuntu

Ubuntu is a free and open source desktop PC opera...

CSS controls the spacing between words through the letter-spacing property

letter-spacing property : Increase or decrease th...

Quickly solve the problem of slow startup after Tomcat reconfiguration

During the configuration of Jenkins+Tomcat server...

Interpretation of 17 advertising effectiveness measures

1. 85% of ads go unread <br />Interpretatio...

Difference between querySelector and getElementById methods in JS

Table of contents 1. Overview 1.1 Usage of queryS...