MySQL - 《Linux》 - 极客文档

https://dev.mysql.com/downloads/mysql/

备份恢复
数据库备份(在命令行执行)
mysqldump —lock-all-tables -udevelop -p databasename > ~/sqlfiles/databasename.sql
数据库还原(在mysql中执行)
use databasename
source ~/sqlfiles/databasename.sql

启停数据库
cd mysql/bin
./mysqladmin -u develop -p shutdown # 停止
./mysqld_safe & # 启动

多版本共存 (8.0+5.7)
8.0安装
下载安装包
wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.26-linux-glibc2.12-x86_64.tar.xz

# 新建用户
groupadd mysql
useradd mysql8 -g mysql
su - mysql8
cp -f /download/mysql-8.0.23-linux-glibc2.12-x86_64.tar.xz ./
xz -dk mysql-8.0.23-linux-glibc2.12-x86_64.tar.xz
tar xvf mysql-8.0.23-linux-glibc2.12-x86_64.tar
rm -f mysql-8.0.23-linux-glibc2.12-x86_64.tar.xz
rm -f mysql-8.0.23-linux-glibc2.12-x86_64.tar
mv mysql-8.0.23-linux-glibc2.12-x86_64/ mysql-8.0.23
cd mysql-8.0.23
mkdir temp logs

在mysql8用户根目录下新建.my.cnf文件
vi ~/.my.cnf
mysql查找配置文件的顺序如下，如果需要安装多个mysql版本，只能使用~/.my.cnf
/etc/my.cnf > /etc/mysql/my.cnf > /usr/etc/my.cnf > ~/.my.cnf
所以，请检查前3个位置，如果存在则需要删除！
~/.my.cnf 不要写错了，不是my.cnf，不是.my.conf，是.my.cnf ！该文件是隐藏文件，需要用ls -a才能看到

[client]
port = 3386
socket = /home/mysql8/mysql-8.0.23/temp/mysql.sock
default_character_set = utf8mb4
[mysql]
auto-rehash
default_character_set = utf8mb4
[mysqld]
port = 3386
user = mysql8
character_set_server = utf8mb4
collation_server = utf8mb4_zh_0900_as_cs
basedir = /home/mysql8/mysql-8.0.23
datadir = /home/mysql8/mysql-8.0.23/data
tmpdir = /home/mysql8/mysql-8.0.23/temp
slave_load_tmpdir = /home/mysql8/mysql-8.0.23/temp
socket = /home/mysql8/mysql-8.0.23/temp/mysql.sock
pid_file = /home/mysql8/mysql-8.0.23/temp/mysqld.pid
log_error = /home/mysql8/mysql-8.0.23/logs/mysql_runtime.log
server_id = 1
binlog_format = ROW
log_bin = /home/mysql8/mysql-8.0.23/logs/binlog
general_log = 0
general_log_file = /home/mysql8/mysql-8.0.23/logs/mysql_general.log
# init_connect = 'SET collation_connection = utf8mb4_zh_0900_as_cs'
init_connect = 'SET NAMES utf8mb4'
default_time_zone = '+08:00'
key_buffer_size = 16M
max_allowed_packet = 128M
max_connections = 500
sysdate_is_now = 1
lower_case_table_names = 1
# 慢查询
slow_query_log = 1
long_query_time = 1
min_examined_row_limit = 100
slow_query_log_file = /home/mysql8/mysql-8.0.23/logs/mysql_slow.log
# MySQL X 是MySQL 5.7发布的类似MongoDB服务的插件
mysqlx = 0
mysqlx_port = 3388
mysqlx_socket = /home/mysql8/mysql-8.0.23/temp/mysqlx.sock
explicit_defaults_for_timestamp = 1
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO
# min_examined_row_limit # 对于查询扫描行数小于此参数的SQL将不会记录到慢查询日志中
# lower_case_table_names = 1 # 1表示大小写不敏感
# max_connections # 最大连接数
# explicit_defaults_for_timestamp = 1 # 设置为1才符合标准SQL模式, timestamp不会在update时自动更新为当前时间
# skip_name_resolve = 1 # 禁用DNS主机名查找加快响应速度, 设置后只能用IP而不能用域名, 只能用127.0.0.1而不能用localhost
[mysqldump]
quick
max_allowed_packet = 16M
# quick 支持较大的数据库转储

创建数据库

cd bin
# 初始化
./mysqld --initialize
# 查看临时密码
grep 'temporary password' ../logs/mysql_runtime.log
# 启动服务
./mysqld_safe &
# 登录mysql
./mysql -u root -p
# 输入临时密码
# 修改管理员密码
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'my0000sql';

创建用户

-- 创建用户
CREATE USER 'develop'@'%' IDENTIFIED WITH mysql_native_password BY 'dev0000lop';
-- 赋予权限
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER,RELOAD,SHUTDOWN,PROCESS,FILE,REFERENCES,INDEX,
    SHOW DATABASES,SUPER,CREATE TEMPORARY TABLES,LOCK TABLES,EXECUTE,REPLICATION SLAVE,REPLICATION CLIENT,
    CREATE VIEW,SHOW VIEW,CREATE ROUTINE,ALTER ROUTINE,CREATE USER,EVENT,TRIGGER 
    ON *.* TO 'develop'@'%';
-- 刷新权限
FLUSH PRIVILEGES;

创建数据库实例

CREATE DATABASE IF NOT EXISTS qdbp_test DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_zh_0900_as_cs;
-- 修改字符集
ALTER DATABASE qdbp_test DEFAULT CHARACTER SET utf8mb4 COLLATE=utf8mb4_zh_0900_as_cs;

排序字符集COLLATE = utf8mb4_zh_0900_as_cs 才能实现中文按拼音排序

5.7安装
下载安装包
wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.35-linux-glibc2.12-x86_64.tar.gz

# 新建用户
# groupadd mysql
useradd mysql5 -g mysql
su - mysql5
cp -f /download/mysql-5.7.32-linux-glibc2.12-x86_64.tar.gz ./
tar zxvf mysql-5.7.32-linux-glibc2.12-x86_64.tar.gz
rm -f mysql-5.7.32-linux-glibc2.12-x86_64.tar.gz
mv mysql-5.7.32-linux-glibc2.12-x86_64/ mysql-5.7.32
cd mysql-5.7.32
mkdir temp logs

在mysql8用户根目录下新建.my.cnf文件
vi ~/.my.cnf

[client]
port = 3356
socket = /home/mysql5/mysql-5.7.32/temp/mysql.sock
default_character_set = utf8mb4
[mysql]
auto-rehash
default_character_set = utf8mb4
[mysqld]
port = 3356
user = mysql5
character_set_server = utf8mb4
collation_server = utf8mb4_unicode_ci
basedir = /home/mysql5/mysql-5.7.32
datadir = /home/mysql5/mysql-5.7.32/data
tmpdir = /home/mysql5/mysql-5.7.32/temp
slave_load_tmpdir = /home/mysql5/mysql-5.7.32/temp
socket = /home/mysql5/mysql-5.7.32/temp/mysql.sock
pid_file = /home/mysql5/mysql-5.7.32/temp/mysqld.pid
log_error = /home/mysql5/mysql-5.7.32/logs/mysql_runtime.log
server_id = 1
binlog_format = ROW
log_bin = /home/mysql5/mysql-5.7.32/logs/binlog
general_log = 0
general_log_file = /home/mysql5/mysql-5.7.32/logs/mysql_general.log
# init_connect = 'SET collation_connection = utf8mb4_unicode_ci'
init_connect = 'SET NAMES utf8mb4'
default_time_zone = '+08:00'
key_buffer_size = 16M
max_allowed_packet = 128M
max_connections = 500
sysdate_is_now = 1
lower_case_table_names = 1
# 慢查询
slow_query_log = 1
long_query_time = 1
min_examined_row_limit = 100
slow_query_log_file = /home/mysql5/mysql-5.7.32/logs/mysql_slow.log
explicit_defaults_for_timestamp = 1
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
# min_examined_row_limit # 对于查询扫描行数小于此参数的SQL将不会记录到慢查询日志中
# lower_case_table_names = 1 # 1表示大小写不敏感
# max_connections # 最大连接数
# explicit_defaults_for_timestamp = 1 # 设置为1才符合标准SQL模式, timestamp不会在update时自动更新为当前时间
# skip_name_resolve = 1 # 禁用DNS主机名查找加快响应速度, 设置后只能用IP而不能用域名, 只能用127.0.0.1而不能用localhost
[mysqldump]
quick
max_allowed_packet = 16M
# quick 支持较大的数据库转储

创建数据库

cd bin
# 初始化
./mysqld --initialize
# 查看临时密码
grep 'temporary password' ../logs/mysql_runtime.log
# 启动服务
./mysqld_safe &
# 登录mysql
./mysql -u root -p
# 输入临时密码
# 修改管理员密码
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'my0000sql';

创建用户

-- 创建用户
CREATE USER 'develop'@'%' IDENTIFIED WITH mysql_native_password BY 'dev0000lop';
-- 赋予权限
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER,RELOAD,SHUTDOWN,PROCESS,FILE,REFERENCES,INDEX,
    SHOW DATABASES,SUPER,CREATE TEMPORARY TABLES,LOCK TABLES,EXECUTE,REPLICATION SLAVE,REPLICATION CLIENT,
    CREATE VIEW,SHOW VIEW,CREATE ROUTINE,ALTER ROUTINE,CREATE USER,EVENT,TRIGGER 
    ON *.* TO 'develop'@'%';
-- 刷新权限
FLUSH PRIVILEGES;

创建数据库实例

CREATE DATABASE IF NOT EXISTS qdbp_test DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_general_ci;

MySQL 5.7 没有 utf8mb4_zh_0900_as_cs

Windows安装

cd F:\tools\mysql.8.0.23\bin
# 初始化 (记住临时密码)
mysqld --initialize --user=mysql --console
# 安装服务
.\mysqld install mysq.8.0
# 启动服务
net start mysq.8.0
# 停止服务
net stop mysq.8.0

字符集说明
MySQL中的utf8是阉割版，最长只有3字节，当遇到4字节编码时 (emoji或复杂汉字)，会导致存储异常。
从5.5.3 开始，MySQL开始用 utf8mb4 编码来实现完整的 UTF-8，其中 mb4 表示 most bytes 4。

character_set_client = 客户端请求数据的字符集
character_set_connection = 从客户端接收到数据, 然后传输的字符集
character_set_database = 默认数据库的字符集, 无论默认数据库如何改变, 都是这个字符集; 如果没有默认数据库, 那就使用 character_set_server指定的字符集, 这个变量建议由系统自己管理, 不要人为定义。
character_set_filesystem = 把操作系统上的文件名转化成此字符集, 即把 character_set_client转换character_set_filesystem,  默认binary是不做任何转换的
character_set_results = 结果集的字符集
character_set_server = 数据库服务器的默认字符集
character_set_system = 存储系统元数据的字符集, 总是 utf8, 不需要设置

查询排序字符集：SHOW COLLATION WHERE CHARSET = ‘utf8mb4’;
修改字符集：ALTER TABLE t_charset_test CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_zh_0900_as_cs;
ai表示accent insensitivity，即“不区分音调”。也就是说，排序时e，è，é，ê和ë之间没有区别。
ci表示case insensitivity，即“不区分大小写”。也就是说，排序时a和A没有区别。

其他命令
当 skip_name_resolve = 1 则给java连接的用户只能用127.0.0.1不能用localhost; 远程用户’xxdb’@’%’
DROP USER name@host 删除用户
REVOKE ALL ON db. FROM user@host; // 收回权限
SHOW GRANTS FOR root; // 查看权限
*查看环境变量
SHOW SESSION VARIABLES LIKE ‘%collation%’;
SHOW GLOBAL VARIABLES LIKE ‘%collation%’;