Ssh remote connection to the MySQL database character set in linux and prevent garbled characters, sshmysql

Ssh remote connection to the MySQL database character set in linux and prevent garbled characters, sshmysql
Lab environment:

Local windows 8.1

Remote Connection Tool SecureCRT 7.3

CentOS 6.7 x86_64-bit Linux, kernel version 2.6.32-573

Mysql version mysql-5.5.32

1.1 MySQL database character set Introduction

Character Set is a set of character symbols, encoding rules, and comparison rules.

MySQL database CHARACTER set includes two concepts: CHARACTER set (CHARACTER) and COLLATION. Character set is used to define the storage method of MySQL Data strings, and the collation is used to define the method of comparing strings.

1.1.1 in the Internet environment, the character sets frequently used by MySQL include

Common Character sets	Length of a Chinese character	Description
GBK	2 bytes	It is not an international standard and has good support for Chinese environments.
UTF-8	3 bytes	We recommend that you use this character set in a mix of Chinese and English environments.
Latin1	1 byte	Default Character Set of MySQL
Utf8mb4	4 bytes	UTF-8 Unicode for mobile Internet

1.1.2 how does MySQL select an appropriate character set?

1) if the processing of a variety of text, published to countries in different languages, should select the Unicode Character Set, For mysql is the UTF-8 (each Chinese Character 3 bytes), more suitable for English less Chinese

2) If you only need to support Chinese characters, and the data size is large and the performance requirements are high, you can select GBK (fixed length, each Chinese Character occupies dual byte, and English also occupies dual byte ), more suitable for a large number of operations, comparative sorting, fixed-length character sets, high performance

3) to process mobile Internet services, you may need to use the utf8mb4 character set.

Teacher suggestion: no special requirements, please select UTF-8

1.1.3 view character sets supported by the current MySQL System

Mysql> show character set;

1.2 view the character set currently used by the System

Mysql> show variables like 'character _ set % ';

 

To avoid garbled characters, you must unify the above character sets.

1.3 change the character set of the client 1.3.1 after entering the database

Set names gbk;

Change the character set (1, 2, and 5) of the three clients to gbk.

Enter the following three commands:

Set character_set_client = gbk;

Set character_set_results = gbk;

Set character_set_connection = gbk;

1.3.2 change the character set of the client when logging on to the database.

[Root @ db02 ~] # Mysql-uroot-poldboy123-S/data/3306/mysql. sock -- default-character = gbk;

Mysql> show variables like 'character _ set % ';

 

1.3.3 modify the configuration file my. cnf

Valid permanently.

[Client]

Default-character-set = gbk

Note: In case of multiple instances, this step modifies the character set and/etc/my. cnf

1.4 change server Character Set 1.4.1 modify configuration file my. cnf

[Mysqld]

Default-character-set = utf8 # Applicable to versions 5.1 and earlier

Character-set-server = uft8 # Applicable to 5.5

It takes effect permanently after the service is restarted.

This modification affects 3rd and 6th character sets, which is equivalent to modifying

Character_set_database

Character_set_server

1.4.2 specify the server character set during compilation

-DDEFAULT_CHARSET = utf8 \

-DDEFAULT_COLLATION = utf8_general_ci \

-DEXTRA_CHARSETS = gbk, gb2312, utf8, ascii \

1.4.3 modify the character set of the database using the command line

We do not recommend that you modify the data in a database. The following command does not take effect on the existing data and affects the updated data.

Mysql> alter database oldboy character set latin1 collate = latin1_swedish_ci;

Mysql> show create database oldboy \ G

1.4.4 modify the character set of a table using the command line

It is not recommended to modify a table with data. The following command is invalid for the existing data and affects the updated data.

Mysql> alter table oldboy. test character set latin1;

Mysql> show create table oldboy. test \ G

1.5 prevent garbled characters and unify character sets when creating a table store in other places 1.5.1

Database creation

Create database oldboy_utf8 default character set utf8 collate utf8_general_ci

Create a table

Create table 'student '(

'Id' int (4) not null auto_increment,

'Name' char (20) not null,

Primary key ('id '),

) ENGINE = InnoDB AUTO_INCREMENT = 10 default charset = utf8

1.5.2 Program

The code of the program should be unified with the database table, and the UTF-8 has no signature.

1.5.3 linux Server

Cat/etc/sysconfig/i18n

$ LANG character set must be consistent with the database

1.5.4 Character Set of the Connection Tool CRT

Change the default Character Set of SecureCRT to UTF-8.

1.5.5 how to execute SQL statements in the database

1) operation habits

Try not to insert data directly in the MySQL Command Line (influenced by the SSH client) and put the SQL statement into the file.

2) SQL File Format

Unified use of "uft8 without signature"

3) File Import Method

You can use source to execute the SQL file in the MySQL command line.

Command to import data mysql-uroot-poldboy123 oldboy <test. SQL

4) set the client character set when importing SQL statements

Add set names utf8 to the SQL file;

Or mysql-uroot-poldboy123 oldboy -- default-character-set = uft8 <test. SQL

1.6 change the character set of an existing database

Idea: export the table structure and data of the original database, delete the original database, create a new database, and import the table structure and data into the new database.

1.6.1 export table structure

Mysqldump-uroot-poldboy123 -- default-character-set = latin1-d dbname> alltable. SQL

-- Default-character-set = uft8 indicates to connect to the utf8 character set, and-d indicates to only export the table structure.

1.6.2 edit table structure statement

Table Structure alltable. SQL changes all latin1 strings to utf8 (you can replace them with sed)

1.6.3 export data

Make sure that the database is no longer updated and all data is exported (without the table structure)

Mysqldump-uroot-poldboy123 -- quick -- no-create-info -- extended-insert -- default-character-set = latin1 dbname> alldata. SQL

Parameter description:

-- Quick is used to dump large tables, force mysqldump to retrieve data from one row on the server instead of all rows, and CACHE the data to the memory before output.

-- No-create-info: do not CREATE a create TABLE statement

-- Extended-insert: Use the multi-row INSERT syntax that includes several VALUES lists. This makes the file smaller, IO smaller, and data import faster.

-- Default-character-set = latin1 export data according to the original character set. In this way, all Chinese characters in the exported file are visible and will not be saved as garbled characters.

1.6.4 modify my. cnf Configuration

Adjust the character set of the client and server, and restart and take effect.

1.6.5 create a database through utf8

Delete the original database, and then create database dbname default charset utf8;

1.6.6 import table structure

The imported table structure of the Modified Character Set

Mysql-uroot-poldboy123 dbname <alltable. SQL

1.6.7 import data

Mysql-uroot-poldboy123 dbname <alldata. SQL

PS: when selecting a directory character set, it is better to be greater than or equal to the source character set (larger font). Otherwise, unsupported data may be lost.