MySQL Database common operations

Original address

Server operation (CMD)

Turn on the server (you must ensure that MySQL is a Windows service): Net start mysql56 (this is the same name in the system process)

To see if a process table exists: Mysqld.exe process (present)

Shut down the server (you must ensure that MySQL is a Windows service): net stop mysql56
To see if a process table exists: Mysqld.exe process (not present)
Client Login operation
Cmd
1. Login server: Mysql-uroot-p123-hlocalhost//-hlocalhost can be omitted, he is the default
-U: followed by user name
-P: followed by password
-H: followed by IP
2. Exit the server: Exit or quit

Backup and Recovery

Database Export SQL Script

Mysqldump–u User name –p password database name > generated script file path
For example: mysqldump-uroot-p123 Mydb1>c:\mydb1.sql (same as Mysql.exe and Mysqld.exe in bin directory)
Note, do not score number, do not log in to MySQL, run directly under CMD
Note that the generated script file does not contain the CREATE DATABASE statement

Execute SQL Script
The first way

Mysql-u User name-p password Database < script file path
For example:

Delete the MYDB1 library first, and then recreate the MYDB1 library mysql-uroot-p123 mydb1

MySQL storage blob packet too large

Com.mysql.jdbc.PacketTooBigException:Packet for query is too large (9802817 > 1048576). You can change this value on the server by setting the Max_allowed_packet ' variable.

Set in My.ini, add max_allowed_packet=10m under [mysqld], for example:

[Mysqld]
max_allowed_packet=64m

Coding

View MySQL database encoding

SHOW VARIABLES like ' char% ';

Code interpretation

Character_set_client:mysql Use this code to interpret the data sent by the client, for example, the code is UTF8, then if the client sends the data is not UTF8, then there will be garbled character_set_ Results:mysql will convert the data to this encoding, and then send to the client, for example, the code is UTF8, then if the client does not use UTF8 to interpret, then there will be garbled
Other encodings are supported in Chinese, which means they cannot be used Latin1

Console garbled problem

garbled when inserting or modifying:

The GBK is used by default because of CMD, and character_set_client is not the reason for GBK. We just need to make the two codes the same.
Because the code to change the cmd is inconvenient, so we go to set Character_set_client to GBK.

The queried data is garbled:
This is because Character_set_results is not GBK, and CMD uses GBK for the default reason. We just need to make the two codes the same.
Because the code to change the cmd is inconvenient, so we go to set Character_set_results to GBK. To set the statement for a variable:
Set CHARACTER_SET_CLIENT=GBK;
Set CHARACTER_SET_RESULTS=GBK;
Note that setting a variable is only valid for the current connection, and when you exit the window, log in to MySQL again, and you need to set the variable again.
For once and for all, you can set it in My.ini:
You can set the DEFAULT-CHARACTER-SET=GBK.

Specify the default encoding
We have specified a default encoding of UTF8 when we installed MySQL, so we do not need to specify the encoding again when we create the database and create the table.
For once and for all, you can set it in My.ini:
You can set the Character-set-server=utf8.

character_set_client | Utf8–> MySQL treats the data passed by our client as utf8! One is to pass the UTF8 to it, and the second is if we pass the GBK, then we need to modify this variable to GBK
character_set_connection | Utf8
Character_set_database | Utf8
Character_set_results | utf8–> MySQL sends the data to the client is UTF8. One is the client with UTF8 encoding, the second is if the client uses GBK to encode, then need to modify this variable to GBK.
Character_set_server | Utf8
Character_set_system | Utf8

Original address

MySQL Database common operations