MySQL 安装成功后，首次基本配置

MySQL 安装成功后，首次基本配置

登录MySQL后，使用 ？查看帮助，
mysql> ?

For information about MySQL products and services, visit:
http://www.mysql.com/
For developer information, including the MySQL Reference Manual, visit:
http://dev.mysql.com/
To buy MySQL Enterprise support, training, or other products, visit:
https://shop.mysql.com/

List of all MySQL commands:
Note that all text commands must be first on line and end with ‘;’
? (?) Synonym for `help’.
clear (c) Clear the current input statement.
connect (r) Reconnect to the server. Optional arguments are db and host.
delimiter (d) Set statement delimiter.
edit (e) Edit command with $EDITOR.
ego (G) Send command to mysql server, display result vertically.
exit (q) Exit mysql. Same as quit.
go (g) Send command to mysql server.
help (h) Display this help.
nopager (n) Disable pager, print to stdout.
notee (t) Don’t write into outfile.
pager (P) Set PAGER [to_pager]. Print the query results via PAGER.
print (p) Print current command.
prompt (R) Change your mysql prompt.
quit (q) Quit mysql.
rehash (#) Rebuild completion hash.
source (.) Execute an SQL script file. Takes a file name as an argument.
status (s) Get status information from the server.
system (!) Execute a system shell command.
tee (T) Set outfile [to_outfile]. Append everything into given outfile.
use (u) Use another database. Takes database name as argument.
charset (C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
warnings (W) Show warnings after every statement.
nowarning (w) Don’t show warnings after every statement.

For server side help, type ‘help contents’

每条语句最后的分号;表示结束 ，否则 MySQL 会认为你的语句不完整无法执行，会等待你继续输入，直到你给出分号;为止，MySQL 才会去执行你给的语句。

退出 MySQL
mysql> quit
Bye
[root@localhost /]#

客户端 和 MySQL 服务器 都是 192.168.231.*网段的，
[root@localhost /]# ifconfig
eth2 link encap:Ethernet HWaddr 00:0C:29:F7:1C:C2
inet addr:192.168.231.131 Bcast:192.168.231.255 Mask:255.255.255.0

使用 sql manager for MySQL 远程连接 数据库，失败，无法获取数据库列表，

这是因为，数据库当前还不允许远程登录数据库，
需要，登录MySQL，配置允许远程登录，
[root@localhost /]# mysql
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 7
Server version: 5.1.66 Source distribution
Copyright © 2000, 2012, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘h’ for help. Type ‘c’ to clear the current input statement.
mysql> show databases;
±-------------------+
| Database |
±-------------------+
| information_schema |
| mysql |
| test |
±-------------------+
3 rows in set (0.00 sec)
mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MySQL 是 系统数据库，配置远程登录，就是 *** 作更改系统数据库 MySQL，

mysql> select host,user from user;
±----------------------±-----+
| host | user |
±----------------------±-----+
| 127.0.0.1 | root |
| localhost | |
| localhost | root |
| localhost.localdomain | |
| localhost.localdomain | root |
±----------------------±-----+
查看当前，系统数据库中存在的用户，可以看到，host 列中没有，192.168.231.*网段的，所以客户端无法远程登录MySQL，
需要创建一个远程用户，达到远程登录MySQL的目的，
mysql> grant all privileges on *.* to 'root'@'192.168.231.%' identified by 'root' with grant option;
Query OK, 0 rows affected (0.00 sec)
mysql> select host,user from user; ±----------------------±-----+
| host | user |
±----------------------±-----+
| 127.0.0.1 | root |
| 192.168.231.% | root |
| localhost | |
| localhost | root |
| localhost.localdomain | |
| localhost.localdomain | root |
±----------------------±-----+
6 rows in set (0.00 sec)
mysql> show grants for 'root'@'192.168.231.%';
±-------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for [email protected].% |
±-------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON . TO ‘root’@‘192.168.231.%’ IDENTIFIED BY PASSWORD ‘*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B’ WITH GRANT OPTION |
±-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

创建完成，尝试使用Windows 客户端远程登录MySQL，
可以看到数据库名了，
如果出现长时间都无法列出数据名，是因为卡在主机名逆向解析这里了，
主机名逆向解析，是指，通过客户端的ip地址，去查找对应的主机名，
所以为了加快客户端远程连接MySQL，需要配置MySQL 配置文件 MY.CNF，来跳过主机名逆向解析这个过程，这样MySQL就不用去查找客户端ip地址对应的主机名了，直接看客户端的ip是否在自己的允许范围内，就可以允许/禁止 客户端远程访问MySQL了，

[root@localhost /]# vi /etc/my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links=0
skip-name-resolve

在 MySQLd里添加 skip-name-resolve
保存退出，
重启 MySQL 服务
[root@localhost /]# service mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]

处理客户端查询结果乱码问题，
字符集合 charset 通常会包含这个字符集合对应的国家内所有的字符，
比如 GB2312 这个字符集中 的 字符集合，就包含了所有的汉字，
请注意 字符集合 和 字符集 是不同的两样东西，
控制字符，通常不显示
打印字符，显示在显示器上的字符，
字符集合+编码=字符集（个人理解就是给所有的字符编上数字代号，这样用数字代号就能找到字符，就是用 数字代号唯一的确定一个字符）
字符序，collation ，区分大小写的规则，
ci，case-insensitive，对大小写不敏感，个人感觉c应该是caps
cs，case-sensitive，对大小写敏感，
bin，binary collation，二进制值比较大小，
capslock（大写锁定键）
Latin是ASCII的扩充版本，
GBK ，使用16位二进制标识一个汉字，
ASCII 128个字符，
Latin 256个字符
Unicode 包括了全世界的所有国家的字符，
Unicode 一个字符占2个字节，英文不足2个字节，前面的字节用0000 0000 补齐，
UTF8 ，一个英文使用一个字节存储，一个汉字使用3个字节存储，
单字节的符号，字节的第一位设为0，后面7位为这个符号的unicode码。因此对于英语字母，UTF-8编码和ASCII码是相同的。（转自
hz_chenwenbiao
UTF-8编码规则（转） ）
查看Windows系统支持的 编码格式，方法是，打开一个cmd命令行窗口，右击标题栏，点选属性，
选项 选项卡中 当前代码页，显示936就是 简体中文DBK，
命令可以查看，当前代码页，
C:UsersZW>chcp
活动代码页: 936

在MySQL中查看，支持的字符集，
mysql> show character set;
±---------±----------------------------±--------------------±-------+
| Charset | Description | Default collation | Maxlen |
±---------±----------------------------±--------------------±-------+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| dec8 | DEC West European | dec8_swedish_ci | 1 |
| cp850 | DOS West European | cp850_general_ci | 1 |
| hp8 | HP West European | hp8_english_ci | 1 |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
| latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
| swe7 | 7bit Swedish | swe7_swedish_ci | 1 |
| ascii | US ASCII | ascii_general_ci | 1 |
| ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
| sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |
| hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 |
| tis620 | TIS620 Thai | tis620_thai_ci | 1 |
| euckr | EUC-KR Korean | euckr_korean_ci | 2 |
| koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 |
| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |
| greek | ISO 8859-7 Greek | greek_general_ci | 1 |
| cp1250 | Windows Central European | cp1250_general_ci | 1 |
| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
| latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |
| armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
| ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |
| cp866 | DOS Russian | cp866_general_ci | 1 |
| keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 |
| macce | Mac Central European | macce_general_ci | 1 |
| macroman | Mac West European | macroman_general_ci | 1 |
| cp852 | DOS Central European | cp852_general_ci | 1 |
| latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |
| cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |
| cp1256 | Windows Arabic | cp1256_general_ci | 1 |
| cp1257 | Windows Baltic | cp1257_general_ci | 1 |
| binary | Binary pseudo charset | binary | 1 |
| geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 |
| cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |
| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |
±---------±----------------------------±--------------------±-------+

创建字符集的粒度，
host 服务器
database 数据库
table 表
column 列

查看MySQL 使用的各个字符集，使用命令
mysql> show variables like 'character_set';
Empty set (0.00 sec)

mysql> show variables like 'character_set_%';
±-------------------------±---------------------------+
| Variable_name | Value |
±-------------------------±---------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
±-------------------------±---------------------------+
8 rows in set (0.00 sec)

当更改了某个粒度的 字符集之后，已经存在的实体不受影响，只会影响新建的实体，
更改MySQL在Linux系统中的配置文件my.cnf，修改server默认字符集，
[root@localhost ~]# vi /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links=0
skip-name-resolve
default-character-set=utf8
[root@localhost ~]# service mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]

mysql> show variables like 'character_set_%';
±-------------------------±---------------------------+
| Variable_name | Value |
±-------------------------±---------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
±-------------------------±---------------------------+
8 rows in set (0.00 sec)
库的默认字符集
create database dbtest default charset latin1;
表的默认字符集
create table student(sid INT,sname char(10))default charset utf8;
列的字符集
create table students(sid INT,sname char(10),address CHAr(20) character set latin1)default charset utf8;

不要轻易更改列的字符集，因此可能出现乱码，
举例，
已存在的3条记录使用的是utf8存储，
随后更改成Latin1，
从第四条开始存取都使用Latin1字符集，
但已存在的3条记录仍是utf8字符集，所以再尝试读取已存在的3条记录的时候，使用的是Latin1字符集，因此会出现乱码，

告诉MySQL服务器，自己的字符集是什么？但实际上自己的字符集可能和通知给MySQL服务器的不一致，
set names utf8;

January the 30th 2022 Sunday