一些命令老是忘记，好记性不如烂笔头。

mysql安装

ubuntu

1
2
3

apt-get install mysql-server
apt-get install mysql-client
apt-get install libmysqlclient-dev

默认密码

1

sudo cat /etc/msyql/debian.cnf

允许远程连接

ubuntu
1.改配置

1
2
3
4

sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
#找到bind-address = 127.0.0.1这一行
#改为bind-address = 0.0.0.0即可
service mysql restart

2.设置用户远程连接

1
2
3
4

use mysql;//切换使用mysql数据库
select user,host from user;   //查看用户是否开启远程
update user set host='%' where user='root';  //开启远程
flush privileges;//刷新权限

mysql8

MySql 8.0.11 换了新的身份验证插件（caching_sha2_password）, 原来的身份验证插件为（mysql_native_password）。而客户端工具Navicat Premium12 中找不到新的身份验证插件（caching_sha2_password），对此，我们将mysql用户使用的 登录密码加密规则 还原成 mysql_native_password，即可登陆成功。

1
2

select user,host,plugin,authentication_string from mysql.user;
alter user 'username'@'%' identified with mysql_native_password by 'password';

服务操作

1
2

service mysql start
service mysql stop

用户

创建用户

创建用户

1

create user 'username' identified by 'password';

修改密码

1

alter user 'username'@'%' identified by 'password';

删除用户

1

alter user 'username'@'%' identified by 'password';

权限

授权

1
2

grant select,insert,update,delete on dbname.*  to 'username'@'%' with grant option;
grant all privileges on tablename.*  to 'username'@'localhost' with grant option; #授权某用户对某数据库的所有操作权

查权

1

show grants for 'username';

撤权

1
2

revoke all privileges on *.* from  'username'@'%';
revoke all privileges on dbname.* from  'username'@'%';

数据库

创建数据库
判断原来不存在，则创建数据库，并指定编码格式和校验集：

1

create database if not exists dbname default character set utf8 collate utf8_general_ci;

更改数据库的字符和校验集

1

ALTER DATABASE dbname DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci

删除数据库

1

drop database dbname;

表

表结构

创建表

1
2
3
4
5
6
7

create table student(
xh int(4),
xm varchar(20),
sex char(2),
birthday date,
sal double(7,2)
);

修改字段

1
2
3

alter table student add (classid int(2));
alter table student modify (xm varchar(30));
alter table student drop column sal;

修改表名

1

rename student to stu;

表数据

添加数据

1

insert into student values(1,"张三",'男','01-5-05',10);

修改数据

1

update student set sex="女" where xh=1

删除数据

1
2

delete from student ;
delete from student where condition;

表查询

1
2
3
4
5
6

desc student;
insert into newtablename select * from student; #表复制
select count(*) from student;
select distinct xm from student;
select xm "姓名", sex "性别" from student; 使用别名
like操作符 # %表示多个字符，`_`表示单个字符

备份

导入

1
2
3
4

create database databasename;#注意数据库的编码
use databasename;
set names utf8;# 设置数据库编码
source absolutepath #数据库文件的位置

导出

表结构和数据

1
2

mysqldump -u username -p databasename > databasename.sql
input: password

表结构

1
2

mysqldump -u username -p -d databasename > databasename.sql
input: password

字符集和校验集

字符集

常用的字符集：

• gbk：一个汉字占用2个字节
• utf8：一个汉字占用3个字节
• utf8mb4：一个汉字占用4个字节

查看支持的字符集

1

show character set;

查看字符编码设置

1

show variables like 'character%';

设置编码字符集

1

set names 'utf8';

修改数据库字符集

1

alter database dbname character set xxx;

只修改表的字符集，影响后续该表新增列的默认定义，已有列的字符集不受影响。

1

alter table tablename character set xxx；

同时修改表字符集和已有列字符集，并将已有数据进行字符集编码转换。

1

alter table tablename convert to character set xxx;

校验集

COLLATE 是指在同一字符集内字符之间的比较规则；确定比较规则后，才能在一个字符集上定义什么是等价的字符，以及字符之间的大小关系

命名惯例：以对应的字符集名称开头；以_ci(表示大小写不敏感)、_cs(表示大小写敏感)或_bin(表示按编码值比较)结尾。例如：在字符序“utf8_general_ci”下，字符“a”和“A”是等价的；
查看支持的校验集

1

show collation;

查看当前字符集和校对规则设置

1

show variables like 'collation_%';

utf8_general_ci校对速度快，但准确度稍差
utf8_unicode_ci准确度高，但校对速度稍慢

DBA建议使用传统的utf8_general_ci

设置各种字符集

mysql的字符集和校对规则有四个级别的默认设置：服务器级、数据库级、数据表级、字段级。它们分别在不同的地方设置，作用也不相同。

服务器级

① 设置服务器级字符集与校队规则

可以在my.cnf中设置,会在mysql服务启动的时候确定

1
2

[mysqld]
character-set-server=utf8

或者在启动选项中指定

或者在编译时指定(源码安装mysql)

② 查看服务器级的字符集和校队规则

1
2

show variables like 'character_set_server';
show variables like 'collation_set_server';

数据库级

数据库的字符集和校对规则可以在创建表的时候指定，也可以创建数据库后通过alter database命令进行修改，如果数据库中已经存在数据，则修改字符集不会对原有记录造成影响，原有的记录依然按照以前的字符集存放
① 在创建数据库时指定字符集和校队规则

② 修改已存在的数据库的字符集和校队规则

③ 查看数据库字符集和校队规则

1
2

show variables like 'character_set_database';
show variables like 'collation_database';

数据表级

表的字符集和校对规则可以在创建表的时候指定，也可以后期通过alter table命令进行修改，如果表中原来已有记录，则修改字符集不会对原有记录造成影响，原有的记录依然按照以前的字符集存放

① 创建数据表时指定字符集

1
2
3

create table test_tablename (
id int not null,
)default charset=utf8;

② 修改数据表的字符集

③ 查看数据表的字符集

列字符集和校队规则

mysql可以定义列级别的字符集和校对规则，主要是针对相同的表不同字段需要使用不同的字符集的情况，应该说遇到这种情况的概率比较低，这只是mysql提供给我们一个灵活设置的手段
列字符集可以在创建表时指定，也可以在后期通过修改表来调整，如果在创建表的时候没有指定字符集和校队规则，则默认使用表的字符集和校对规则

连接字符集和校对规则

上面四种设置方式仅仅是针对于数据在数据库中保存时使用的字符集和校对规则，而对于实际的应用程序来说，还存在客户端和服务器之间交互的字符集和校对规则的设置
对于客户端和服务器的交互操作，mysql提供了三个不同的参数：
character_set_client 、 character_set_connection、character_set_results,分别代表客户端、连接和返回结果的字符集，通常情况下，这三个设置应该是相同的，才可以确保用户写入的数据被正确读出
可以通过一个命令同时设置这三个参数

这个命令可以同时修改这三个参数的值，或者在配置文件my.cnf中设置

1
2

[mysql]
default-character-set=utf8

一个提交的分解

1. 客户端发起 查询

2. 服务器使用character_set_client变量作为客户端发送的查询中使用的字符集。

3. 服务器拿到 查询 后用将character_set_client 编码方式转为 character_set_connection对应的校验规则为collation_connection， (如果查询是文字字符串，也就是他们有某种字符格式的引介词 例如_utf8,如果是列值，校验规则将不依靠collation_connection)

4. 服务器执行查询的结果 将会 按照 character_set_results 编码方式返回查询结果到客户端。包括结果数据，例如列值和结果元数据（如列名）

字符和校验集设置

修改my.cnf

1
2
3
4
5
6
7
8
9
10
11
12
13

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8


[mysqld]
init_connect='SET collation_connection = utf8_general_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_general_ci
skip-character-set-client-handshake

数据

数据存储位置

1

sudo service MySQL stop#迁移前必须先停止mysql

创建mysql 存放的 目标文件夹 一般 默认的 mysql 存储目录在 /var/lib中
文件的权限 为 700 且 用户和用户组 都为 mysql,接下来的创建 文件夹必须和这个一样:

我这里挂载盘/mnt/data 可以迁移到这里，并建一个文件夹:

1

sudo mkdir mysql

修改所属用户 和所属用户组为 msyql

1

sudo chown -vR  mysql:mysql  /mnt/data/mysql/

修改权限

1

sudo chmod -vR  700 /mnt/data/mysql/

迁移文件

进入 /var/lib 建议使用cp命令 防止万一出现问题

使用su命令 切换到 root 因为 权限 为 700

为了保证 目录权限和属性 和以前的一致 ,加 av 后缀

1

cp -av /var/lib/mysql/* /mnt/data/mysql/

exit;退出root

修改mysql的配置文件:

1

sudo vim  /etc/mysql/mysql.conf.d/mysqld.cnf

修改 datadir 为 /mnt/data/mysql/

以上mysql的配置修改完毕
配置AppArmor访问控制

1

vim  /etc/apparmor.d/tunables/alias                  # 配置别名规则

文件最后添加一行

1

alias /var/lib/mysql/  ->  /mnt/mysql/,

重启AppArmor服务

1

service  apparmor restart

启动mysql

1

/etc/init.d/mysql start

查看mysql新目录的值

1
2

mysql -uroot -p
select @@datadir;

表数据大小

Navicat导入SQL文件时报错：MySql 错误 Err [Imp] 1153 - Got a packet bigger than 'max_allowed_packet' bytes

查了一下，原来是MySQL默认读取执行的SQL文件最大为16M，我这个SQL文件260M，所以执行不过去

解决方法：

在MySQL安装目录下找到文件my.cnf，搜索[mysqld]，在其下面添加一句话

1

max_allowed_packet=400M

mysql8

参考文章：
MYSQL字符集与校对规则

FROM :blog.cfyqy.com | Author:cfyqy