Original link: https://wsgzao.github.io/post/mysql/
foreword
Every time you have a need, you need to search Google. If there are too many repetitions, you should record the commonly used mysql and mysqldump commands you actually use for reference. In addition, the official also announced that MySQL 5.7 will end its life cycle on October 21, 2023. Everyone Also start to promote the upgrade of MySQL 8.0 and above.
update history
May 17, 2023 – First draft
Read the original text – https://wsgzao.github.io/post/mysql/
mysql create user and login authorization
- Public cloud needs to set data security ACL whitelist policy
- Intranet environments need to set authorized access policies
1
2
3
4
|
# Log in
mysql -h connection address -P port -u username -p password
mysql -h<host> -P3306 -u<user> -p
|
Login parameter description
- The capital letter P stands for the port, and the lowercase letter p stands for the password.
- When entering the user name and password, you need to enter them directly after -u and -p without spaces.
- After -p, you don’t need to enter the password, but enter it after executing the command to prevent the password from being displayed in plain text.
MySQL 8.0 has new security requirements. You cannot create users and authorize them at one time like previous versions. You need to create users first and then perform authorization operations.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
twenty one
twenty two
twenty three
twenty four
|
# Create a new user
# Among them, username is a user-defined user name; host is the login domain name, when host is '%', it means any IP, when it is localhost, it means the local machine, or fill in the specified IP address; paasword is the password
create user 'username'@'host' identified by 'password';
# Authorize the user
# Among them, *.* the first * means all databases, the second * means all data tables, if you don’t want to authorize all, then write the corresponding * as the corresponding database or data table; username is the specified user; % is the user login domain name
grant all privileges on *.* to 'username'@'%' with grant option;
# Refresh permissions after authorization
flush privileges;
# revoke authorization
#Take back permissions (not including empowerment permissions)
REVOKE ALL PRIVILEGES ON *.* FROM user_name;
REVOKE ALL PRIVILEGES ON user_name.* FROM user_name;
# Take back authorization permissions
REVOKE GRANT OPTION ON *.* FROM user_name;
# Refresh the permissions after the operation
flush privileges;
# delete users
drop user username@'login address';
|
Mysql export and import common commands
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
twenty one
|
# Database export
mysqldump -uroot -h192.168.1.101 -p dbtest > dbtest.sql
# database import
mysql -uroot -h192.168.1.101 -p dbtest < /tmp/dbtest.sql --default-character-set=utf8mb4
# data table copy
CREATE TABLE new_table SELECT * FROM old_table;
# table rename
RENAME TABLE old_table TO new_table;
# export the entire database
mysqldump -u username -p database name> exported file name
# Export a table in the database
mysqldump -u username -p database name table name> exported file name
# Export a database structure (-d no data)
mysqldump -u username -p -d database name> exported file name
mysqldump -u root -p test > test.sql
mysqldump -u root -p test users > test_users.sql
mysqldump -u root -p -d test > test.sql
|
reference article
mysql — The MySQL Command-Line Client
mysqldump — A Database Backup Program
Connect to the RDS MySQL instance through the client or the command line
This article is transferred from: https://wsgzao.github.io/post/mysql/
This site is only for collection, and the copyright belongs to the original author.