Common MySQL database operations

Common MySQL database operations
Server operation (cmd)

Enable the server (ensure that mysql is a windows Service): net start mysql56 (the name is the same as that in the system process)

Check whether the mysqld.exe process exists in the progress table)

Disable the server (ensure that mysql is a windows Service): net stop mysql56
Check whether the process mysqld.exe exists in the process (does not exist)
Customer Login operation
Cmd
1. log on to the server: mysql-uroot-p123-hlocalhost //-hlocalhost can be omitted, which is the default
-U: followed by the user name
-P: Password followed
-H: followed by IP Address
2. exit the server: exit or quit

Backup and recovery

Database Export SQL script

Mysqldump-u user name-p Password Database Name> path of the generated script file
For example, mysqldump-uroot-p123 mydb1> C: \ mydb1. SQL (the same as mysql.exeand mysqld.exe are in the bin directory)
Note: Do not set a score. Do not log on to mysql and run it directly in cmd.
Note: The generated script file does not contain the create database statement.

Execute an SQL script
Method 1

Mysql-u username-p password database <script file path
For example:

First Delete the mydb1 database, and then recreate the mydb1 database mysql-uroot-p123 mydb1

MySQL stores blob data packets 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.

In my. ini, add max_allowed_packet = 10 M under [mysqld]. For example:

[Mysqld]
Max_allowed_packet = 64 M

Encoding

View MySQL database Encoding

Show variables like 'Char % ';

Encoding explanation

Character_set_client: MySQL uses this encoding to interpret the data sent from the client. For example, if the encoding is UTF8, if the data sent by the client is not UTF8, garbled character_set_results: mySQL converts the data to the encoding and sends it to the client. For example, if the encoding is UTF8, garbled characters will occur if the client does not use UTF8 for interpretation.
Other codes only support Chinese characters, that is, latin1 cannot be used.

Console garbled

Garbled characters occur during insertion or modification:

In this case, GBK is used by default in cmd, while character_set_client is not GBK. We only need to make the two codes the same.
It is inconvenient to modify the cmd encoding, so we can set character_set_client to GBK.

The queried data is garbled:
This is because character_set_results is not GBK, while cmd uses GBK by default. We only need to make the two codes the same.
Because it is inconvenient to modify the cmd encoding, we can set character_set_results to GBK. Set the variable statement:
Set character_set_client = gbk;
Set character_set_results = gbk;
Note: The setting variable is only valid for the current connection. After exiting the window, log on to mysql again and set the variable again.
In my. ini, you can set:
Set default-character-set = gbk.
　

Specify default encoding
We have specified the default encoding UTF8 when installing MySQL, so we do not need to specify the encoding again when creating a database or table.
In my. ini, you can set:
Set character-set-server = utf8.

Character_set_client | utf8-> mysql treats the data transmitted by our client as utf8! One is to pass utf8 to it, and the other is to change this variable to gbk if we pass gbk.
Character_set_connection | utf8
Character_set_database | utf8
Character_set_results | utf8-> all data sent from mysql to the client is utf8. First, the client uses utf8 encoding, and second, if the client uses gbk encoding, You need to modify this variable to gbk.
Character_set_server | utf8
Character_set_system | utf8