mysql

发布日期: 2020-02-24

作者: 小明

文章字数: 3k

阅读时长: 15 分

阅读次数:

1. MySQL升级和降级

1.1 方式

1.1.1 INPLACE  就地 
在一台服务器上，原版本升级到新版本。
风险较大。

==建议：不管哪种方式升级，都应该先做备份。方便失败回退。==

1.1.2 Mergeing 迁移建议

备份迁移 
主从迁移

1.2 升级注意事项

a. 支持GA版本之间升级
b. 5.6--> 5.7  ,先将5.6升级至最新版，再升级到5.7
c. 5.5 ---> 5.7 ,先将5.5 升级至最新，再5.5---> 5.6最新，再5.6--->5.7 最新
d. 回退方案要提前考虑好，最好升级前要备份(特别是往8.0版本升级)。
e. 降低停机时间（停业务的时间）

2.INPLACE 升级过程原理

0. 备份原数据库数据
a. 安装新版本软件
b. 关闭原数据库（挂维护页）
c. 使用新版本软件 “挂” 旧版本数据启动(--skip-grant-tables ,--skip-networking)
d. 升级 ： 只是升级系统表。升级时间和数据量无关的。
e. 正常重启数据库。
f. 验证各项功能是否正常。
g. 业务恢复

1.4 5.6.46 —-> 5.7.28 Inplace 升级演练

a. 安装 新版本软件 
b. 停原库 
# 1. 快速关库功能关闭(优雅关闭)  
vim /data/3316/my.cnf 
## 添加以下配置
innodb_fast_shutdown=0 
[root@db01 data]# systemctl stop mysqld3316
c. 使用高版本软件挂低版本数据启动

1-停止数据库

[root@db01 data]# systemctl stop mysqld3316

2- 使用高版本软件挂低版本数据启动

[root@db01 data]# vim /data/3316/my.cnf 
[mysqld]
user=mysql
basedir=/data/app/mysql
datadir=/data/3316/data
socket=/tmp/mysql3316.sock
port=3316
server_id=16
innodb_fast_shutdown=0

3-启动

[root@db01 data]# /data/app/mysql/bin/mysqld_safe --defaults-file=/data/3316/my.cnf --skip-grant-tables --skip-networking &

4-升级

升级（升级到8.0可以省略）

d. 升级 （升级到8.0可以省略）
[root@db01 data]# /data/app/mysql/bin/mysql_upgrade -S /tmp/mysql3316.sock --force

5-重启数据库到正常状态

[root@db01 data]# mysqladmin -S /tmp/mysql3316.sock shutdown

修改启动脚本

修改以下内容 


[root@db01 data]# vim /etc/systemd/system/mysqld3316.service 

ExecStart=/data/app/mysql/bin/mysqld --defaults-file=/data/3316/my.cnf
-------------
[root@China ~] # cat /etc/systemd/system/mysqld3316.service 
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/data/app/mysql80/bin/mysqld --defaults-file=/data/3316/my.cnf
LimitNOFILE = 5000

6-启动数据库

[root@db01 data]# systemctl start mysqld3316 

# 连接查看
[root@db01 data]# mysql -S /tmp/mysql3316.sock
[root@db01 ~]# mysqlsh root:123@10.0.0.51:3306 -e "util.checkForServerUpgrade()"

3.-SQL 应用基础

1、SQL介绍

结构化查询语言
5.7 以后符合SQL92严格模式
通过sql_mode参数来控制

2、常用SQL分类

DDL：数据定义语言
DCL：数据控制语言
DML：数据操作语言
DQL：数据的查询语言

3、数据类型、表属性、字符集

3.1 数据类型

3.1.1 作用

影响到了SQL语句的执行行为。为了让数据库在存、取能够满足生活的常识、科学的逻辑，让这些数据有意义。
例子： 
    现实生活中常识：
        日期： 1000-9999 年 1-12 月 1-31日 
    科学逻辑：         
        除法：除数不能为0

查看sql_mode

mysql> select @@sql_mode;
ONLY_FULL_GROUP_BY          :  针对group by 语句执行时的规范
STRICT_TRANS_TABLES         :  针对事务表启动严格模式
NO_ZERO_IN_DATE             ： 2010-00-10
NO_ZERO_DATE                ： 0000-00-00
ERROR_FOR_DIVISION_BY_ZERO  ： 除数不能为0
NO_AUTO_CREATE_USER         ： 是否自动创建用户
NO_ENGINE_SUBSTITUTION      :  建表是使用了一个不支持的存储引擎报错。

SQL 标准

SQL-89  \ SQL-92  \  SQL-99   \SQL03 .....

保证数据的准确性和标准性。

4-数据库对象属性介绍

库
    库名
    库属性：字符集、校对规则

表 
    表名 
    列 ：
        列名
        列属性：数据类型、约束、其他属性
    行
    表属性：引擎、字符集、校对规则、其他。

5-字符集

MySQL 中的 “密码本”
2.2.2 种类
utf8    not  UTF-8  
utf8mb4 is   UTF-8（万国码）

特点 : 
1. utf8   , 最大支持3字节的字符。不支持emoji字符
2. utf8mb4, 最大支持4字节的字符。支持emoji字符。

8.0之前，默认字符集latin1,8.0之后是utf8mb4。
我们建议使用utf8mb4.

2.2.3 设置方法
默认字符集参数
建库
建表

2.3 校对规则（排序规则）  
2.3.1 作用 
影响到数据的排序方式。
    ASCII码
a    97
b    98
c    99
d    100

A    65
B    66
C    67
D    68

2.3.2 查看
mysql> show collation;


2.4 列属性

6-数据类型

#  作用 
尽可能保证数据是有意义的。
#  种类 
## 数字类型
整数：
    tinyint   1字节  =  8位 = 00000000 - 11111111  = 2^8个  = 0  - 255     , -127-128    
    int       4字节  =  32位                       = 2^32个 = 0  - 2^32-1  ,-2^31-2^31-1 ,10位数
    bigint    8字节                                           0  - 2^64-1  ,-2^63-2^63-1 ,20位数
浮点数：
    decimal(m,n)

## 字符串类型
char(N)    : 
    N字符个数，最大不超过255
    定长的字符串类型。
    例如： char(10) ,最多存10个字符,只要10个字符以内，都10个字符长度的存储空间。剩余用空格填充。
varchar(M) ：
    M字符个数，最大不超过65535
    变长的字符串类型。会额外占用1-2字节存储字符长度。255字符之内，额外1字节，255以上，额外2字节
    例如： varchar(10),最多存10个字符,按需分配存储空间。
    abc 3             =4
    asdasdadasd 1000  =1002

enum('bj','sh','tj','heibei','henan',.....)
district enum('bj','sh','tj','heibei','henan',.....)

## 时间类型 
datetime    8字节 
timestamp   4字节 

DATETIME 
范围为从 1000-01-01 00:00:00.000000 至 9999-12-31 23:59:59.999999。

TIMESTAMP 
1970-01-01 00:00:00.000000 至 2038-01-19 03:14:07.999999。
timestamp会受到时区的影响

## 二进制类型
## json 数据类型

4-数据库降级

1-官方解释

降级：
官方解释： 
https://dev.mysql.com/doc/refman/5.7/en/downgrade-paths.html
Downgrade from MySQL 5.7 to 5.6 is supported using the logical downgrade method.

https://dev.mysql.com/doc/refman/5.7/en/downgrade-binary-package.html#downgrade-procedure-inplace
In-place downgrade is supported for downgrades between GA releases within the same release series.

5.7.28 —》 5.7.10 inplace downgrade ok

原环境：
5.7.28 二进制版本 + /data/3306/data

2. 安装 5.7.10 （低）二进制版本

/data/app/mysql5710

针对5728版本（高）进行处理工作

https://dev.mysql.com/doc/refman/5.7/en/downgrading-to-previous-series.html

set sql_mode='STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' ;
set global sql_mode='STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' ;
select @@sql_mode;                              
ALTER TABLE mysql.proc MODIFY definer char(77) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '';
ALTER TABLE mysql.event MODIFY definer char(77) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '';
ALTER TABLE mysql.tables_priv MODIFY Grantor char(77) COLLATE utf8_bin NOT NULL DEFAULT '';
ALTER TABLE mysql.procs_priv MODIFY Grantor char(77) COLLATE utf8_bin NOT NULL DEFAULT '';

3. 优雅的关闭5.7.28（高）。

set global innodb_fast_shutdown=0 ;
mysql --execute="SET GLOBAL innodb_fast_shutdown=0"
mysqladmin  shutdown

[root@db01 /data] # mysqladmin  -S /tmp/mysql3308.sock  shutdown

4. 删除ib_logfile*

防止冲突
[root@db01 mysql5710]# rm -rf /data/3306/data/ib_logfile*

5. 替换配置文件、环境变量（替换成低版本）

[root@db01 mysql5710]# vim /etc/my.cnf
[mysqld]
user=mysql
basedir=/data/app/mysql5710
#basedir=/data/app/mysql56
datadir=/data/3306/data
socket=/tmp/mysql.sock
[mysql]
socket=/tmp/mysql.sock

[root@db01 mysql5710]# vim /etc/profile
export PATH=/data/app/mysql5710/bin:$PATH

[root@db01 mysql5710]# source /etc/profile

db01_5.7 [(none)]>select version();
+-----------+
| version() |
+-----------+
| 5.7.10    |
+-----------+
1 row in set (0.00 sec)

db01_5.7 [(none)]>

[root@db01 mysql5710]# mysql -V
mysql  Ver 14.14 Distrib 5.7.10, for linux-glibc2.5 (x86_64) using  EditLine wrapper

6. 启动数据库

[root@db01 mysql5710]# /etc/init.d/mysqld start 
Starting MySQL.. SUCCESS! 

   全路径启动

7. 执行upgrade

[root@db01 /data] # /data/app/mysql5710/bin/mysql_upgrade   --force 
[root@db01 mysql5710]# mysql_upgrade --force

[root@db01 /data] # /data/app/mysql5710/bin/mysql_upgrade   -S /tmp/mysql3308.sock   --force 
Checking server version.
Running queries to upgrade MySQL server.
Checking system database.
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.engine_cost                                  OK
mysql.event                                        OK
mysql.func                                         OK
mysql.general_log                                  OK
mysql.gtid_executed                                OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.innodb_index_stats                           OK
mysql.innodb_table_stats                           OK
mysql.ndb_binlog_index                             OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.server_cost                                  OK
mysql.servers                                      OK
mysql.slave_master_info                            OK
mysql.slave_relay_log_info                         OK
mysql.slave_worker_info                            OK
mysql.slow_log                                     OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK
The sys schema is already up to date (version 1.5.2).
Checking databases.
sys.sys_config                                     OK
Upgrade process completed successfully.
Checking if update is needed.

5-数据库5.7-5.6降级

0. 恢复5.7.28 环境

0. 恢复5.7.28 环境 
[root@db01 data]# pkill mysqld
[root@db01 data]# rm -rf /data/3306/data/*
# 恢复配置文件
[root@db01 data]# vim /etc/my.cnf
[mysqld]
user=mysql
basedir=/data/app/mysql
#basedir=/data/app/mysql56
datadir=/data/3306/data
socket=/tmp/mysql.sock
[mysql]
socket=/tmp/mysql.sock

# 恢复环境变量 
export PATH=/data/app/mysql/bin:$PATH
[root@db01 data]# source /etc/profile

[root@db01 data]# mysql -V
mysql  Ver 14.14 Distrib 5.7.28, for linux-glibc2.12 (x86_64) using  EditLine wrapper

1-初始化数据

从新安装的软件

mysqld --initialize-insecure --user=mysql --basedir=/data/app/mysql --datadir=/data/3306/data

2-启动数据库

[root@db01 data]# /etc/init.d/mysqld start
Starting MySQL.Logging to '/data/3306/data/db01.err'.
 SUCCESS! 

 mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.28    |
+-----------+
1 row in set (0.00 sec)

3-安装5.6.46二进制版本软件

/data/app/mysql56

4-处理5.7.28高版本数据

set sql_mode='STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' ;
set global sql_mode='STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' ;
select @@sql_mode;   
ALTER TABLE mysql.proc MODIFY definer char(77) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '';
ALTER TABLE mysql.event MODIFY definer char(77) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '';
ALTER TABLE mysql.tables_priv MODIFY Grantor char(77) COLLATE utf8_bin NOT NULL DEFAULT '';
ALTER TABLE mysql.procs_priv MODIFY Grantor char(77) COLLATE utf8_bin NOT NULL DEFAULT '';
ALTER TABLE mysql.tables_priv MODIFY User char(16) NOT NULL default '';
ALTER TABLE mysql.columns_priv MODIFY User char(16) NOT NULL default '';
ALTER TABLE mysql.user MODIFY User char(16) NOT NULL default '';
ALTER TABLE mysql.db MODIFY User char(16) NOT NULL default '';
ALTER TABLE mysql.procs_priv MODIFY User char(16) binary DEFAULT '' NOT NULL;
ALTER TABLE mysql.user ADD Password char(41) character set latin1
collate latin1_bin NOT NULL default '' AFTER user;
UPDATE mysql.user SET password = authentication_string WHERE
LENGTH(authentication_string) = 41 AND plugin = 'mysql_native_password';
UPDATE mysql.user SET authentication_string = '' WHERE
LENGTH(authentication_string) = 41 AND plugin = 'mysql_native_password';
ALTER TABLE mysql.help_category ENGINE='MyISAM' STATS_PERSISTENT=DEFAULT;
ALTER TABLE mysql.help_keyword ENGINE='MyISAM' STATS_PERSISTENT=DEFAULT;
ALTER TABLE mysql.help_relation ENGINE='MyISAM' STATS_PERSISTENT=DEFAULT;
ALTER TABLE mysql.help_topic ENGINE='MyISAM' STATS_PERSISTENT=DEFAULT;
ALTER TABLE mysql.time_zone ENGINE='MyISAM' STATS_PERSISTENT=DEFAULT;
ALTER TABLE mysql.time_zone_leap_second ENGINE='MyISAM' STATS_PERSISTENT=DEFAULT;
ALTER TABLE mysql.time_zone_name ENGINE='MyISAM' STATS_PERSISTENT=DEFAULT;
ALTER TABLE mysql.time_zone_transition  ENGINE='MyISAM' STATS_PERSISTENT=DEFAULT;
ALTER TABLE mysql.time_zone_transition_type ENGINE='MyISAM' STATS_PERSISTENT=DEFAULT;
ALTER TABLE mysql.plugin ENGINE='MyISAM' STATS_PERSISTENT=DEFAULT;
ALTER TABLE mysql.servers ENGINE='MyISAM' STATS_PERSISTENT=DEFAULT;
ALTER TABLE mysql.user MODIFY plugin CHAR(64) COLLATE utf8_bin
DEFAULT 'mysql_native_password';
DROP DATABASE sys;

5. 逻辑备份5.7.28数据

[root@db01 /data] # mysqldump -S /tmp/mysql3308.sock   -A >/tmp/full.sql
[root@db01 ~]# mysqldump -A >/tmp/full.sql

6. 初始化一套5.6.46的空环境

[root@db01 ~]# vim /etc/profile
export PATH=/data/app/mysql56/bin:$PATH

[root@db01 ~]# source /etc/profile
[root@db01 ~]# mysql -V
mysql  Ver 14.14 Distrib 5.6.46, for linux-glibc2.12 (x86_64) using  EditLine wrapper

[root@db01 ~]# mv /etc/my.cnf /etc/my.cnf.bak
mv: overwrite ‘/etc/my.cnf.bak’? y

[root@db01 data]# rm -rf /data/3316/data/*

6.1-初始化 5.6

[root@db01 data]# /data/app/mysql56/scripts/mysql_install_db  --user=mysql --basedir=/data/app/mysql56 --datadir=/data/3316/data
[root@db01 data]# systemctl start mysqld3316

7. 恢复备份数据到5.6.46中

[root@db01 data]# mysql -S /tmp/mysql3316.sock 
mysql> source /tmp/full.sql

把 /etc/my.cnf 的移动走

6-8.0的新功能

mysql-shell工具，8.0以后，可以调用这个命令，升级之前的预检查。

[root@db01 ~]# mysqlsh root:123@10.0.0.51:3306 -e "util.checkForServerUpgrade()"

例如： 5.7.28 升级至 8.0.18 版本

1. 下载 8.0.18 版本的 mysql-shell，并安装

[root@db01 app]# yum install -y mysql-shell-8.0.18-1.el7.x86_64.rpm

2. 恢复5728环境

[root@db01 data]# pkill mysqld
[root@db01 data]# rm -rf /data/3306/data/*

3.恢复配置文件

[root@db01 data]# vim /etc/my.cnf
[mysqld]
user=mysql
basedir=/data/app/mysql
#basedir=/data/app/mysql56
datadir=/data/3306/data
socket=/tmp/mysql.sock
[mysql]
socket=/tmp/mysql.sock

4.恢复环境变量

export PATH=/data/app/mysql/bin:$PATH
[root@db01 data]# source /etc/profile

[root@db01 data]# mysql -V
mysql  Ver 14.14 Distrib 5.7.28, for linux-glibc2.12 (x86_64) using  EditLine wrapper

5.初始化数据

mysqld --initialize-insecure --user=mysql --basedir=/data/app/mysql --datadir=/data/3306/data

6.启动数据库

[root@db01 data]# /etc/init.d/mysqld start
Starting MySQL.Logging to '/data/3306/data/db01.err'.
 SUCCESS! 

mysql> grant all on *.* to root@'10.0.1.%' identified by '123';

7. 预检查

检查

mysqlsh root:123@10.0.0.51:3306 -e "util.checkForServerUpgrade()"
[root@db01 ~] # mysqlsh root:123@10.0.1.51:3308 -e "util.checkForServerUpgrade()"
-----------
[root@db01 ~] # mysqlsh root:123@10.0.1.51:3308 -e "util.checkForServerUpgrade()"
WARNING: Using a password on the command line interface can be insecure.
The MySQL server at 10.0.1.51:3308, version 5.7.28 - MySQL Community Server
(GPL), will now be checked for compatibility issues for upgrade to MySQL
8.0.18...

1) Usage of old temporal type
  No issues found

2) Usage of db objects with names conflicting with new reserved keywords
  No issues found

3) Usage of utf8mb3 charset
  No issues found

转载规则

《MySQL升级和降级》由小明采用知识共享署名 4.0 国际许可协议进行许可。

第五章SQL 常识

1. SQL 常识1.1 SQL的介绍属于关系型数据库产品中专用的语句。结构化查询语句。 MySQL 1.2 SQL 标准 SQL-89 \ SQL-92 \ SQL-99 \SQL03 ..... 1.3 sql_mod

2020-02-26 mysql

leetcode mysql

Nginx多策略流量分发

Nginx多策略流量分发1、场景描述在实际生产环境中，流量分发有很多情况，下面主要讲讲以下两种流量分发场景：新版本上线，为了保证新版本稳定性，需要用线上的流量的引入，对新版本进行真实流量测试。如果新版本上线有问题，为降低影响范围，我们对

2020-02-24 nginx

nginx