CentOS 7x安装Mysql8.0.x

其实官网有教程的,我这里记录下来,是因为要写一个自动化安装脚本,虽然不经常用到,但是还是写出来比较好。

Mysql8.0仅支持CentOS 7x系统下安装,反正6.5的测试过一次,但是安装成功,启动失败,原因是缺少依赖。

Mysql8.0相信只会安装到64位的机器上。

  • 首先,下载mysql8.0的压缩包:

# wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.18-linux-glibc2.12-x86_64.tar.xz
# wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.18-el7-x86_64.tar.gz
# wget http://mysql.mirror.kangaroot.net/Downloads/MySQL-8.0/mysql-8.0.18-el7-x86_64.tar.gz
wget http://ftp.ntu.edu.tw/MySQL/Downloads/MySQL-8.0/mysql-8.0.18-el7-x86_64.tar.gz

我测试了最后一个,下载速度最快,所以贴上来好了。

  • 解压缩

tar -xzvf mysql-8.0.18-el7-x86_64.tar.gz
mv mysql-8.0.18-el7-x86_64/* /alidata/server/mysql
mkdir -p /alidata/server/mysql/data
ln -s /alidata/server/mysql-8.0.18 /usr/local/mysql
chmod 777 /alidata/server/mysql
chmod 777 /alidata/server/mysql/data

  • 权限配置

groupadd mysql
useradd -g mysql -s /sbin/nologin mysql
chown -R mysql:mysql /alidata/server/mysql/
chown -R mysql:mysql /alidata/server/mysql/data/
chown -R mysql:mysql /alidata/log/mysql
chmod -R 777 /alidata/server/mysql/support-files
chmod -R 777 /alidata/server/mysql/bin
\cp -f /alidata/server/mysql/support-files/mysql.server /etc/init.d/mysqld
sed -i ‘s#^basedir=$#basedir=/alidata/server/mysql#’ /etc/init.d/mysqld
sed -i ‘s#^datadir=$#datadir=/alidata/server/mysql/data#’ /etc/init.d/mysqld

  • 输出my.cnf文件

cat > /etc/my.cnf <<END
[client]
port=3306
default-character-set=utf8
[mysqld]
port = 3306
socket = /tmp/mysql.sock
default_authentication_plugin=mysql_native_password
skip-external-locking
log-error=/alidata/log/mysql/error.log
character-set-server=utf8
default-storage-engine=INNODB
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

log-bin=mysql-bin
binlog_format=mixed
server-id = 1

innodb_buffer_pool_size=512M
innodb_flush_log_at_trx_commit=1
innodb_lock_wait_timeout=120
innodb_log_buffer_size=4M
innodb_log_file_size=256M
interactive_timeout=120
join_buffer_size=2M
key_buffer_size=32M
max_allowed_packet=16M
max_connections=100
max_heap_table_size=64M
myisam_max_sort_file_size=64G
myisam_sort_buffer_size=32M
read_buffer_size=512kb
read_rnd_buffer_size=4M
server_id=1
skip-external-locking=on
sort_buffer_size=256kb
table_open_cache=256
thread_cache_size=16
tmp_table_size=64M
wait_timeout=120

[mysql]
default-character-set=utf8
END

  • 最后,安装并启动

/alidata/server/mysql/bin/mysqld –initialize –user=mysql
chmod 755 /etc/init.d/mysqld
/etc/init.d/mysqld start

*需要注意:

mysql8.0安装成功后,会把初始密码写到log-error对应的文件中,我这里设置的路径是/alidata/log/mysql/error.log,打开这个文件,找到密码,并使用:mysql -uroot -p

登陆终端,把密码修改了,不然的话登陆上去,也操作不了其他。

修改密码的代码:

#alter user ‘root’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘bsiidno6gH0′;
#flush privileges

最后,贴出整个shell代码:

#!/bin/bash

yum install -y libaio

ifubuntu=$(cat /proc/version | grep ubuntu)
if14=$(cat /etc/issue | grep 14)

if [ `uname -m` == "x86_64" ];then
machine=x86_64
else
machine=i686
fi
if [ $machine == "x86_64" ];then
  rm -rf mysql-8.0.18-el7-x86_64
  if [ ! -f mysql-8.0.18-el7-x86_64.tar.gz ];then
#   wget http://zy-res.oss-cn-hangzhou.aliyuncs.com/mysql/mysql-5.6.21-linux-glibc2.5-x86_64.tar.gz
#   wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.18-linux-glibc2.12-x86_64.tar.xz
#   wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.18-el7-x86_64.tar.gz
#   wget http://mysql.mirror.kangaroot.net/Downloads/MySQL-8.0/mysql-8.0.18-el7-x86_64.tar.gz
    wget http://ftp.ntu.edu.tw/MySQL/Downloads/MySQL-8.0/mysql-8.0.18-el7-x86_64.tar.gz
  fi
  tar -xzvf mysql-8.0.18-el7-x86_64.tar.gz
  mv mysql-8.0.18-el7-x86_64/* /alidata/server/mysql
  mkdir -p /alidata/server/mysql/data
  ln -s /alidata/server/mysql-8.0.18 /usr/local/mysql
  chmod 777 /alidata/server/mysql   
  chmod 777 /alidata/server/mysql/data

else
  echo 'unsupport machine i686'
fi

if [ "$ifubuntu" != "" ] && [ "$if14" != "" ];then
   mv /etc/mysql/my.cnf /etc/mysql/my.cnf.bak
fi

groupadd mysql
useradd -g mysql -s /sbin/nologin mysql
chown -R mysql:mysql /alidata/server/mysql/
chown -R mysql:mysql /alidata/server/mysql/data/
chown -R mysql:mysql /alidata/log/mysql
chmod  -R 777 /alidata/server/mysql/support-files
chmod  -R 777 /alidata/server/mysql/bin
\cp -f /alidata/server/mysql/support-files/mysql.server /etc/init.d/mysqld
sed -i 's#^basedir=$#basedir=/alidata/server/mysql#' /etc/init.d/mysqld
sed -i 's#^datadir=$#datadir=/alidata/server/mysql/data#' /etc/init.d/mysqld
cat > /etc/my.cnf <<END
[client]
port=3306
default-character-set=utf8
[mysqld]
port            = 3306
socket          = /tmp/mysql.sock
default_authentication_plugin=mysql_native_password
skip-external-locking
log-error=/alidata/log/mysql/error.log
character-set-server=utf8
default-storage-engine=INNODB
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

log-bin=mysql-bin
binlog_format=mixed
server-id       = 1

innodb_buffer_pool_size=512M
innodb_flush_log_at_trx_commit=1
innodb_lock_wait_timeout=120
innodb_log_buffer_size=4M
innodb_log_file_size=256M
interactive_timeout=120
join_buffer_size=2M
key_buffer_size=32M
max_allowed_packet=16M
max_connections=100
max_heap_table_size=64M
myisam_max_sort_file_size=64G
myisam_sort_buffer_size=32M
read_buffer_size=512kb
read_rnd_buffer_size=4M
server_id=1
skip-external-locking=on
sort_buffer_size=256kb
table_open_cache=256
thread_cache_size=16
tmp_table_size=64M
wait_timeout=120

[mysql]
default-character-set=utf8


END

/alidata/server/mysql/bin/mysqld --initialize --user=mysql
chmod 755 /etc/init.d/mysqld
/etc/init.d/mysqld start

#mysql password see @/alidata/log/mysql/error.log
#and must by alter root password.
#
#alter user 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'bsiidno6gH0';
#flush privileges
#use mysql mysql
#update user set user.Host='%'where user.User='root';
#export PATH=$PATH:/alidata/server/mysql/bin
#
#/alidata/server/php/bin/php -f ./res/init_mysql.php

官方文档:

https://dev.mysql.com/doc/refman/8.0/en/installing.html

mysql主从复制选项和变量

在主服务器和每个从服务器上,必须设置 server_id系统变量以建立唯一的复制ID。对于每个服务器,应选择一个介于1到2 32 − 1之间的唯一正整数,并且每个ID必须与其他任何复制主服务器或从属正在使用的每个ID不同。范例:server-id=3

有关在主服务器上用于控制二进制日志记录的选项。

下表描述了用于控制复制主服务器的启动选项。与复制相关的系统变量将在本节后面讨论。

  • --show-slave-auth-info
    Property Value
    Command-Line Format --show-slave-auth-info[={OFF|ON}]
    Type Boolean
    Default Value OFF

    SHOW SLAVE HOSTS在主服务器上 的输出中,显示以 --report-user和 --report-password选项开头的从站的从站用户名和密码 。

复制主机上使用的系统变量

以下系统变量用于复制原版或由复制原版使用:

  • auto_increment_increment
    Property Value
    Command-Line Format --auto-increment-increment=#
    System Variable auto_increment_increment
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies Yes
    Type Integer
    Default Value 1
    Minimum Value 1
    Maximum Value 65535

    auto_increment_increment 和auto_increment_offset 用于主机到主复制的使用,并且可以用于控制其操作 AUTO_INCREMENT的列。这两个变量都具有全局值和会话值,并且每个变量都可以采用1到65535(含)之间的整数值。将这两个变量之一的值设置为0会导致其值设置为1。尝试将这两个变量的值设置为大于65535或小于0的整数会导致其值设置为65535。尝试设定auto_increment_increment或 的值 auto_increment_offset 非整数值会产生错误,并且变量的实际值保持不变。

    注意

    auto_increment_increment 还支持与NDB表一起使用 。

    从MySQL 8.0.18开始,设置此系统变量的会话值不再是受限制的操作。

    在服务器上启动组复制时,值 auto_increment_increment更改为的值 group_replication_auto_increment_increment,默认为7,而 auto_increment_offset更改为服务器ID。停止组复制时,将还原更改。仅当auto_increment_increment 且auto_increment_offset 每个更改的默认值均为1时,才进行并还原这些更改 。如果已将其更改为默认值,则组复制不会更改它们。从MySQL 8.0开始,当组复制处于只有一个服务器写入的单主模式下时,系统变量也不会被修改。

    auto_increment_increment 并auto_increment_offset 影响AUTO_INCREMENT列行为,如下所示:

    • auto_increment_increment 控制连续列值之间的间隔。例如:
      mysql> SHOW VARIABLES LIKE 'auto_inc%';
      +--------------------------+-------+
      | Variable_name            | Value |
      +--------------------------+-------+
      | auto_increment_increment | 1     |
      | auto_increment_offset    | 1     |
      +--------------------------+-------+
      2 rows in set (0.00 sec)
      
      mysql> CREATE TABLE autoinc1
          -> (col INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
        Query OK, 0 rows affected (0.04 sec)
      
      mysql> SET @@auto_increment_increment=10;
      Query OK, 0 rows affected (0.00 sec)
      
      mysql> SHOW VARIABLES LIKE 'auto_inc%';
      +--------------------------+-------+
      | Variable_name            | Value |
      +--------------------------+-------+
      | auto_increment_increment | 10    |
      | auto_increment_offset    | 1     |
      +--------------------------+-------+
      2 rows in set (0.01 sec)
      
      mysql> INSERT INTO autoinc1 VALUES (NULL), (NULL), (NULL), (NULL);
      Query OK, 4 rows affected (0.00 sec)
      Records: 4  Duplicates: 0  Warnings: 0
      
      mysql> SELECT col FROM autoinc1;
      +-----+
      | col |
      +-----+
      |   1 |
      |  11 |
      |  21 |
      |  31 |
      +-----+
      4 rows in set (0.00 sec)
    • auto_increment_offset 确定AUTO_INCREMENT列值的起点 。考虑以下条件,假设这些语句与描述中给出的示例在同一会话中执行 auto_increment_increment
      mysql> SET @@auto_increment_offset=5;
      Query OK, 0 rows affected (0.00 sec)
      
      mysql> SHOW VARIABLES LIKE 'auto_inc%';
      +--------------------------+-------+
      | Variable_name            | Value |
      +--------------------------+-------+
      | auto_increment_increment | 10    |
      | auto_increment_offset    | 5     |
      +--------------------------+-------+
      2 rows in set (0.00 sec)
      
      mysql> CREATE TABLE autoinc2
          -> (col INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
      Query OK, 0 rows affected (0.06 sec)
      
      mysql> INSERT INTO autoinc2 VALUES (NULL), (NULL), (NULL), (NULL);
      Query OK, 4 rows affected (0.00 sec)
      Records: 4  Duplicates: 0  Warnings: 0
      
      mysql> SELECT col FROM autoinc2;
      +-----+
      | col |
      +-----+
      |   5 |
      |  15 |
      |  25 |
      |  35 |
      +-----+
      4 rows in set (0.02 sec)

      当的值 auto_increment_offset 大于 auto_increment_increment的值时,auto_increment_offset 将忽略的值 。

    如果更改了这些变量中的任何一个,然后将新行插入到包含AUTO_INCREMENT列的表中 ,则结果似乎违反直觉,因为AUTO_INCREMENT计算该系列 值时不考虑该列中已经存在的任何值,并且下一个插入的值是该系列中的最小值大于该AUTO_INCREMENT列中的最大现有值 。该序列的计算如下:

    auto_increment_offsetN× auto_increment_increment

    其中,N是[1、2、3,…]系列中的正整数值。例如:

    mysql> SHOW VARIABLES LIKE 'auto_inc%';
    +--------------------------+-------+
    | Variable_name            | Value |
    +--------------------------+-------+
    | auto_increment_increment | 10    |
    | auto_increment_offset    | 5     |
    +--------------------------+-------+
    2 rows in set (0.00 sec)
    
    mysql> SELECT col FROM autoinc1;
    +-----+
    | col |
    +-----+
    |   1 |
    |  11 |
    |  21 |
    |  31 |
    +-----+
    4 rows in set (0.00 sec)
    
    mysql> INSERT INTO autoinc1 VALUES (NULL), (NULL), (NULL), (NULL);
    Query OK, 4 rows affected (0.00 sec)
    Records: 4  Duplicates: 0  Warnings: 0
    
    mysql> SELECT col FROM autoinc1;
    +-----+
    | col |
    +-----+
    |   1 |
    |  11 |
    |  21 |
    |  31 |
    |  35 |
    |  45 |
    |  55 |
    |  65 |
    +-----+
    8 rows in set (0.00 sec)

    显示auto_increment_increment 并auto_increment_offset 生成5 + N×10 系列的值 ,即[5、15、25、35、45,…]。出现在col之前的列 中的最大值INSERT是31,而该AUTO_INCREMENT 序列中的下一个可用值是35,因此col从该点开始的插入值 和结果如SELECT 查询所示。

    不可能将这两个变量的作用限制在一个表中。这些变量控制MySQL服务器上所有中所有AUTO_INCREMENT列 的行为。如果设置了其中一个变量的全局值,则其影响将持续到通过设置会话值更改或覆盖全局值,或者直到重新启动mysqld为止。如果设置了本地值,则新值将影响 会话期间当前用户在其中插入新行的所有表的列,除非在该会话期间更改了值。 AUTO_INCREMENT

    默认值为 auto_increment_increment1。请参见 mysql第17.5.1.1节“复制和AUTO_INCREMENT”

  • auto_increment_offset
    Property Value
    Command-Line Format --auto-increment-offset=#
    System Variable auto_increment_offset
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies Yes
    Type Integer
    Default Value 1
    Minimum Value 1
    Maximum Value 65535

    此变量的默认值为1。如果保留其默认值,并且在多主模式下在服务器上启动组复制,则会将其更改为服务器ID。有关更多信息,请参见的描述 auto_increment_increment

    注意

    auto_increment_offset还支持与NDB表一起使用。

    从MySQL 8.0.18开始,设置此系统变量的会话值不再是受限制的操作。

  • immediate_server_version
    Property Value
    Introduced 8.0.14
    System Variable immediate_server_version
    Scope Session
    Dynamic Yes
    SET_VAR Hint Applies No
    Type Integer

    供复制内部使用。该会话系统变量保存服务器的MySQL服务器发行版号,该服务器是复制拓扑中的直接主服务器(例如,80014对于MySQL 8.0.14服务器实例)。如果此即时服务器的发行版不支持会话系统变量,则该变量的值将设置为0(UNKNOWN_SERVER_VERSION)。

    变量的值从主机复制到从机。通过识别这些信息,从属服务器可以通过识别所涉及的版本之间在何处发生语法更改或语义更改并进行适当的处​​理,来正确处理来自较旧版本的主服务器的数据。该信息还可以在组复制环境中使用,在该环境中,复制组的一个或多个成员的版本比其他成员的版本高。可以在每个交易的二进制日志中查看变量的值(作为 Gtid_log_event,或 Anonymous_gtid_log_event (如果服务器上未使用GTID),则可能有助于调试跨版本复制问题。

    设置此系统变量的会话值是受限制的操作。会话用户必须具有 REPLICATION_APPLIER特权(请参见第17.3.3节“复制特权检查”)或足以设置受限制的会话变量的特权(请参见第5.1.9.1节“系统变量特权”)。但是,请注意,该变量并非供用户设置。它是由复制基础结构自动设置的。

  • original_server_version
    Property Value
    Introduced 8.0.14
    System Variable original_server_version
    Scope Session
    Dynamic Yes
    SET_VAR Hint Applies No
    Type Integer

    供复制内部使用。此会话系统变量保存最初提交事务的服务器的MySQL Server版本号(例如, 80014对于MySQL 8.0.14服务器实例)。如果此原始服务器的发行版不支持会话系统变量,则该变量的值将设置为0(UNKNOWN_SERVER_VERSION)。请注意,当原始服务器设置发行版号时,如果复制拓扑中的直接服务器或任何其他中介服务器不支持会话系统变量,那么该变量的值将重置为0,因此不复制其值。

    变量的值的设置和使用方式与 immediate_server_version 系统变量相同。如果该变量的值与immediate_server_version 系统变量的值相同 ,则仅将后者记录在二进制日志中,并指示原始服务器版本是相同的。

    在组复制环境中,查看更改日志事件(这些事件是新成员加入组时每个组成员排队的特殊事务)用组成员的服务器版本对事务进行标记。这样可确保加入成员知道原始供体的服务器版本。因为排队等待特定视图更改的视图更改日志事件在所有成员上都具有相同的GTID,所以仅在这种情况下,同一GTID的实例可能具有不同的原始服务器版本。

    设置此系统变量的会话值是受限制的操作。会话用户必须具有 REPLICATION_APPLIER特权(请参见第17.3.3节“复制特权检查”)或足以设置受限制的会话变量的特权(请参见第5.1.9.1节“系统变量特权”)。但是,请注意,该变量并非供用户设置。它是由复制基础结构自动设置的。

  • rpl_semi_sync_master_enabled
    Property Value
    Command-Line Format --rpl-semi-sync-master-enabled[={OFF|ON}]
    System Variable rpl_semi_sync_master_enabled
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type Boolean
    Default Value OFF

    控制是否在主服务器上启用半同步复制。要启用或禁用插件,请将此变量 分别设置为ONOFF(或1或0)。默认值为 OFF

    仅当安装了主端半同步复制插件时,此变量才可用。

  • rpl_semi_sync_master_timeout
    Property Value
    Command-Line Format --rpl-semi-sync-master-timeout=#
    System Variable rpl_semi_sync_master_timeout
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type Integer
    Default Value 10000

    以毫秒为单位的值,用于控制主机在超时并恢复为异步复制之前,等待提交等待从属服务器进行确认的时间。默认值为10000(10秒)。

    仅当安装了主端半同步复制插件时,此变量才可用。

  • rpl_semi_sync_master_trace_level
    Property Value
    Command-Line Format --rpl-semi-sync-master-trace-level=#
    System Variable rpl_semi_sync_master_trace_level
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type Integer
    Default Value 32

    主服务器上的半同步复制调试跟踪级别。定义了四个级别:

    • 1 =常规级别(例如,时间功能故障)
    • 16 =详细程度(更多详细信息)
    • 32 =净等待级别(有关网络等待的更多信息)
    • 64 =功能级别(有关功能进入和退出的信息)

    仅当安装了主端半同步复制插件时,此变量才可用。

  • rpl_semi_sync_master_wait_for_slave_count
    Property Value
    Command-Line Format --rpl-semi-sync-master-wait-for-slave-count=#
    System Variable rpl_semi_sync_master_wait_for_slave_count
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type Integer
    Default Value 1
    Minimum Value 1
    Maximum Value 65535

    主机在继续之前必须为每个事务接收的从机确认数。默认情况下 rpl_semi_sync_master_wait_for_slave_count 为1,表示半同步复制在收到单个从属确认后继续进行。对于此变量的较小值,性能最佳。

    例如,如果 rpl_semi_sync_master_wait_for_slave_count is为2,则2个从属必须在rpl_semi_sync_master_timeout 半同步复制配置的超时时间继续之前确认事务的接收 。如果在超时期间较少的从服务器确认接收到事务,则主服务器将恢复为正常复制。

    注意

    此行为还取决于 rpl_semi_sync_master_wait_no_slave

    仅当安装了主端半同步复制插件时,此变量才可用。

  • rpl_semi_sync_master_wait_no_slave
    Property Value
    Command-Line Format --rpl-semi-sync-master-wait-no-slave[={OFF|ON}]
    System Variable rpl_semi_sync_master_wait_no_slave
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type Boolean
    Default Value ON

    控制主服务器是否等待由配置的超时时间rpl_semi_sync_master_timeout 到期,即使从服务器的计数下降到小于超时期间配置 的从服务器的数量也是如此 rpl_semi_sync_master_wait_for_slave_count 。

    当的值 rpl_semi_sync_master_wait_no_slave是 ON(默认值),则允许用于从属计数下降到小于 rpl_semi_sync_master_wait_for_slave_count 在超时期限内。只要有足够多的从服务器在超时期限到期之前确认该事务,半同步复制就会继续。

    当的值 rpl_semi_sync_master_wait_no_slave是 OFF,如果从计数降到比配置数量较少 rpl_semi_sync_master_wait_for_slave_count 期间由配置的超时时间段在任何时候 rpl_semi_sync_master_timeout,主回复到正常的复制。

    仅当安装了主端半同步复制插件时,此变量才可用。

  • rpl_semi_sync_master_wait_point
    Property Value
    Command-Line Format --rpl-semi-sync-master-wait-point=value
    System Variable rpl_semi_sync_master_wait_point
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type Enumeration
    Default Value AFTER_SYNC
    Valid Values

    AFTER_SYNC

    AFTER_COMMIT

    此变量控制半同步复制主服务器在将状态返回给提交事务的客户端之前等待事务接收的从属确认的时间点。这些值是允许的:

    • AFTER_SYNC(默认):主服务器将每个事务写入其二进制日志,而从服务器则将二进制日志同步到磁盘。主机在同步后等待从机对交易收据的确认。收到确认后,主服务器将事务提交到存储引擎,并将结果返回给客户端,然后客户端可以继续进行。
    • AFTER_COMMIT:主服务器将每个事务写入其二进制日志,而从服务器将其同步到二进制日志,然后将事务提交到存储引擎。提交后,主机等待从机对交易收据的确认。收到确认后,主机将结果返回给客户端,然后客户端可以继续进行。

    这些设置的复制特征如下:

    • 使用AFTER_SYNC,所有客户端可以同时看到已提交的事务:从属服务器确认了该事务并提交给了主服务器上的存储引擎之后。因此,所有客户端都可以在主服务器上看到相同的数据。如果主服务器发生故障,则所有在主服务器上提交的事务都将复制到从服务器(保存到其中继日志中)。由于从属服务器是最新的,因此主服务器崩溃和故障转移到从属服务器是无损的。但是请注意,在这种情况下无法重新启动主服务器,必须将其丢弃,因为其二进制日志中可能包含未提交的事务,这些事务在二进制日志恢复后被外部化时会与从服务器发生冲突。
    • 使用AFTER_COMMIT,仅在服务器提交到存储引擎并接收到从属确认后,发出事务的客户端才会获得返回状态。在提交之后和从属确认之前,其他客户端可以在提交客户端之前看到提交的事务。如果出现故障,导致从服务器无法处理事务,则在主服务器崩溃并故障转移到从服务器的情况下,此类客户端可能会看到相对于其在主服务器上看到的数据丢失的情况。

    仅当安装了主端半同步复制插件时,此变量才可用。

    由于增加了 rpl_semi_sync_master_wait_point 在MySQL 5.7,创建一个版本的兼容性约束,因为它增加了半同步接口版本:服务器为MySQL 5.7和更高不要从旧版本的半同步复制插件的工作,也没有从旧版本的服务器与半同步复制插件工作适用于MySQL 5.7和更高版本。

原文:https://dev.mysql.com/doc/refman/8.0/en/replication-options-master.html

mysqldump备份数据库命令

1、备份命令

格式:mysqldump -h主机名 -P端口 -u用户名 -p密码 –database 数据库名 > 文件名.sql

例如: mysqldump -h 192.168.1.100 -p 3306 -uroot -ppassword –database cmdb > /data/backup/cmdb.sql

2、备份压缩

导出的数据有可能比较大,不好备份到远程,这时候就需要进行压缩

格式:mysqldump -h主机名 -P端口 -u用户名 -p密码 –database 数据库名 | gzip > 文件名.sql.gz

例如: mysqldump -h192.168.1.100 -p 3306 -uroot -ppassword –database cmdb | gzip > /data/backup/cmdb.sql.gz

3、备份同个库多个表

格式:mysqldump -h主机名 -P端口 -u用户名 -p密码 –database 数据库名 表1 表2 …. > 文件名.sql

例如 mysqldump -h192.168.1.100 -p3306 -uroot -ppassword cmdb t1 t2 > /data/backup/cmdb_t1_t2.sql

4、同时备份多个库

格式:mysqldump -h主机名 -P端口 -u用户名 -p密码 –databases 数据库名1 数据库名2 数据库名3 > 文件名.sql

例如:mysqldump -h192.168.1.100 -uroot -ppassword –databases cmdb bbs blog > /data/backup/mutil_db.sql

5、备份实例上所有的数据库

格式:mysqldump -h主机名 -P端口 -u用户名 -p密码 –all-databases > 文件名.sql

例如:mysqldump -h192.168.1.100 -p3306 -uroot -ppassword –all-databases > /data/backup/all_db.sql

6、备份数据出带删除数据库或者表的sql备份

格式:mysqldump -h主机名 -P端口 -u用户名 -p密码 –add-drop-table –add-drop-database 数据库名 > 文件名.sql

例如:mysqldump -uroot -ppassword –add-drop-table –add-drop-database cmdb > /data/backup/all_db.sql

7、备份数据库结构,不备份数据

格式:mysqldump -h主机名 -P端口 -u用户名 -p密码 –no-data 数据库名1 数据库名2 数据库名3 > 文件名.sql

例如:mysqldump –no-data –databases db1 db2 cmdb > /data/backup/structure.sql

mysql执行delete大数据时建议的方法

一张表可以存10几亿条的数据,特别是硬件上报的日志,特别多,而且还很多没有什么用的,某些数据库(本人试验mysql5.1)在批量删除数据时会拖慢数据库,本来跟删除的表无关,但还是拖慢了,也许是服务器的问题。

建议的方式:

  1. 创建一张备份表
  2. 如果删除的数据大于存留的数据,那么将存留的数据拷贝到备份表去
  3. 使用TRUNCATE TABLE命令,直接清空原表的数据
  4. 将备份表的数据拷贝到原表中
  5. 使用TRUNCATE TABLE命令清空备份表的数据

CREATE TABLE tb_device_report_log_bak SELECT * FROM tb_device_report_log_2 WHERE 1;
INSERT INTO tb_device_report_log_bak SELECT * FROM tb_device_report_log_2 a WHERE a.msgId!=10009;
TRUNCATE tb_device_report_log_2;
SELECT count(1) as zs ,msgId FROM `tb_device_report_log_2` WHERE 1 group by msgId order by zs desc;
INSERT INTO tb_device_report_log_2 SELECT * FROM tb_device_report_log_bak a WHERE 1;
TRUNCATE tb_device_report_log_bak;

SELECT count(1) as zs ,msgId FROM `tb_device_report_log_2` WHERE 1 group by msgId order by zs desc;

这行命令我是用来对比数据的,ok了再TRUNCATE表。

 

 

 

 

 

MySQL8.0新特性

1. 默认字符集由latin1变为utf8mb4

在8.0版本之前,默认字符集为latin1,utf8指向的是utf8mb3,8.0版本默认字符集为utf8mb4,utf8默认指向的也是utf8mb4。
注:在Percona Server 8.0.15版本上测试,utf8仍然指向的是utf8mb3,与官方文档有出入。

Warning | 3719 | 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |

2. MyISAM系统表全部换成InnoDB表

系统表全部换成事务型的innodb表,默认的MySQL实例将不包含任何MyISAM表,除非手动创建MyISAM表。

# MySQL 5.7
mysql> select distinct(ENGINE) from information_schema.tables;
+--------------------+
| ENGINE             |
+--------------------+
| MEMORY             |
| InnoDB             |
| MyISAM             |
| CSV                |
| PERFORMANCE_SCHEMA |
| NULL               |
+--------------------+
6 rows in set (0.00 sec)
 
# MySQL 8.0
mysql> select distinct(ENGINE) from information_schema.tables;
+--------------------+
| ENGINE             |
+--------------------+
| NULL               |
| InnoDB             |
| CSV                |
| PERFORMANCE_SCHEMA |
+--------------------+
4 rows in set (0.00 sec)

3. 自增变量持久化

在8.0之前的版本,自增主键AUTO_INCREMENT的值如果大于max(primary key)+1,在MySQL重启后,会重置AUTO_INCREMENT=max(primary key)+1,这种现象在某些情况下会导致业务主键冲突或者其他难以发现的问题。自增主键重启重置的问题很早就被发现(https://bugs.mysql.com/bug.php?id=199),一直到8.0才被解决,8.0版本将会对AUTO_INCREMENT值进行持久化,MySQL重启后,该值将不会改变。

4. DDL原子化

InnoDB表的DDL支持事务完整性,要么成功要么回滚,将DDL操作回滚日志写入到data dictionary 数据字典表 mysql.innodb_ddl_log 中用于回滚操作,该表是隐藏的表,通过show tables无法看到。通过设置参数,可将ddl操作日志打印输出到mysql错误日志中。

mysql> set global log_error_verbosity=3;
mysql> set global innodb_print_ddl_logs=1;
mysql> create table t1(c int) engine=innodb;
 
# MySQL错误日志:
2018-06-26T11:25:25.817245+08:00 44 [Note] [MY-012473] [InnoDB] InnoDB: DDL log insert : [DDL record: DELETE SPACE, id=41, thread_id=44, space_id=6, old_file_path=./db/t1.ibd]
2018-06-26T11:25:25.817369+08:00 44 [Note] [MY-012478] [InnoDB] InnoDB: DDL log delete : by id 41
2018-06-26T11:25:25.819753+08:00 44 [Note] [MY-012477] [InnoDB] InnoDB: DDL log insert : [DDL record: REMOVE CACHE, id=42, thread_id=44, table_id=1063, new_file_path=db/t1]
2018-06-26T11:25:25.819796+08:00 44 [Note] [MY-012478] [InnoDB] InnoDB: DDL log delete : by id 42
2018-06-26T11:25:25.820556+08:00 44 [Note] [MY-012472] [InnoDB] InnoDB: DDL log insert : [DDL record: FREE, id=43, thread_id=44, space_id=6, index_id=140, page_no=4]
2018-06-26T11:25:25.820594+08:00 44 [Note] [MY-012478] [InnoDB] InnoDB: DDL log delete : by id 43
2018-06-26T11:25:25.825743+08:00 44 [Note] [MY-012485] [InnoDB] InnoDB: DDL log post ddl : begin for thread id : 44
2018-06-26T11:25:25.825784+08:00 44 [Note] [MY-012486] [InnoDB] InnoDB: DDL log post ddl : end for thread id : 44

来看另外一个例子,库里只有一个t1表,drop table t1,t2; 试图删除t1,t2两张表,在5.7中,执行报错,但是t1表被删除,在8.0中执行报错,但是t1表没有被删除,证明了8.0 DDL操作的原子性,要么全部成功,要么回滚。

# MySQL 5.7
mysql> show tables;
+---------------+
| Tables_in_db |
+---------------+
| t1            |
+---------------+
1 row in set (0.00 sec)
mysql> drop table t1, t2;
ERROR 1051 (42S02): Unknown table 'db.t2'
mysql> show tables;
Empty set (0.00 sec)
 
# MySQL 8.0
mysql> show tables;
+---------------+
| Tables_in_db |
+---------------+
| t1            |
+---------------+
1 row in set (0.00 sec)
mysql> drop table t1, t2;
ERROR 1051 (42S02): Unknown table 'db.t2'
mysql> show tables;
+---------------+
| Tables_in_db |
+---------------+
| t1            |
+---------------+
1 row in set (0.00 sec)

5. 参数修改持久化

MySQL 8.0版本支持在线修改全局参数并持久化,通过加上PERSIST关键字,可以将修改的参数持久化到新的配置文件(mysqld-auto.cnf)中,重启MySQL时,可以从该配置文件获取到最新的配置参数。
例如执行:
set PERSIST expire_logs_days=10 ;
系统会在数据目录下生成一个包含json格式的 mysqld-auto.cnf 的文件,格式化后如下所示,当 my.cnf 和 mysqld-auto.cnf 同时存在时,后者具有更高优先级。

{
    "Version": 1,
    "mysql_server": {
        "expire_logs_days": {
            "Value": "10",
            "Metadata": {
                "Timestamp": 1529657078851627,
                "User": "root",
                "Host": "localhost"
            }
        }
    }
}

6. 新增降序索引

MySQL在语法上很早就已经支持降序索引,但实际上创建的仍然是升序索引,如下MySQL 5.7 所示,c2字段降序,但是从show create table看c2仍然是升序。8.0可以看到,c2字段降序。

# MySQL 5.7
mysql> create table t1(c1 int,c2 int,index idx_c1_c2(c1,c2 desc));
Query OK, 0 rows affected (0.03 sec)
mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `c1` int(11) DEFAULT NULL,
  `c2` int(11) DEFAULT NULL,
  KEY `idx_c1_c2` (`c1`,`c2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
 
# MySQL 8.0
mysql> create table t1(c1 int,c2 int,index idx_c1_c2(c1,c2 desc));
Query OK, 0 rows affected (0.06 sec)
mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `c1` int(11) DEFAULT NULL,
  `c2` int(11) DEFAULT NULL,
  KEY `idx_c1_c2` (`c1`,`c2` DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
1 row in set (0.00 sec)

再来看看降序索引在执行计划中的表现,在t1表插入10万条随机数据,查看select * from t1 order by c1 , c2 desc;的执行计划。从执行计划上可以看出,5.7的扫描数100113远远大于8.0的5行,并且使用了filesort。

DELIMITER ;;
CREATE PROCEDURE test_insert ()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i<100000
DO
insert into t1 select rand()*100000, rand()*100000;
SET i=i+1;
END WHILE ;
commit;
END;;
DELIMITER ;
CALL test_insert();
 
# MySQL 5.7
mysql> explain select * from t1 order by c1 , c2 desc limit 5;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+--------+----------+-----------------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows   | filtered | Extra                       |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+--------+----------+-----------------------------+
|  1 | SIMPLE      | t1    | NULL       | index | NULL          | idx_c1_c2 | 10      | NULL | 100113 |   100.00 | Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+--------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)
 
# MySQL 8.0
mysql> explain select * from t1 order by c1 , c2 desc limit 5;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | index | NULL          | idx_c1_c2 | 10      | NULL |    5 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

降序索引只是对查询中特定的排序顺序有效,如果使用不当,反而查询效率更低,比如上述查询排序条件改为 order by c1 desc, c2 desc,这种情况下,5.7的执行计划要明显好于8.0的,如下:

# MySQL 5.7
mysql> explain select * from t1  order by c1 desc , c2 desc limit 5;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | index | NULL          | idx_c1_c2 | 10      | NULL |    5 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)
 
# MySQL 8.0
mysql> explain select * from t1 order by c1 desc , c2 desc limit 5;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+--------+----------+-----------------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows   | filtered | Extra                       |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+--------+----------+-----------------------------+
|  1 | SIMPLE      | t1    | NULL       | index | NULL          | idx_c1_c2 | 10      | NULL | 100429 |   100.00 | Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+--------+----------+-----------------------------+
1 row in set, 1 warning (0.01 sec)

7. group by 不再隐式排序

mysql 8.0 对于group by 字段不再隐式排序,如需要排序,必须显式加上order by 子句。

# 表结构
mysql> show create table tb1\G
*************************** 1. row ***************************
       Table: tb1
Create Table: CREATE TABLE `tb1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `group_own` int(11) DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
1 row in set (0.00 sec)
 
# 表数据
mysql> select * from tb1;
+----+------+-----------+
| id | name | group_own |
+----+------+-----------+
|  1 | 1    |         0 |
|  2 | 2    |         0 |
|  3 | 3    |         0 |
|  4 | 4    |         0 |
|  5 | 5    |         5 |
|  8 | 8    |         1 |
| 10 | 10   |         5 |
+----+------+-----------+
7 rows in set (0.00 sec)
 
# MySQL 5.7
mysql> select count(id), group_own from tb1 group by group_own;
+-----------+-----------+
| count(id) | group_own |
+-----------+-----------+
|         4 |         0 |
|         1 |         1 |
|         2 |         5 |
+-----------+-----------+
3 rows in set (0.00 sec)
 
# MySQL 8.0.11
mysql> select count(id), group_own from tb1 group by group_own;
+-----------+-----------+
| count(id) | group_own |
+-----------+-----------+
|         4 |         0 |
|         2 |         5 |
|         1 |         1 |
+-----------+-----------+
3 rows in set (0.00 sec)
 
# MySQL 8.0.11显式地加上order by进行排序
mysql> select count(id), group_own from tb1 group by group_own order by group_own;
+-----------+-----------+
| count(id) | group_own |
+-----------+-----------+
|         4 |         0 |
|         1 |         1 |
|         2 |         5 |
+-----------+-----------+
3 rows in set (0.00 sec)

8. JSON特性增强

MySQL 8 大幅改进了对 JSON 的支持,添加了基于路径查询参数从 JSON 字段中抽取数据的 JSON_EXTRACT() 函数,以及用于将数据分别组合到 JSON 数组和对象中的 JSON_ARRAYAGG() 和 JSON_OBJECTAGG() 聚合函数。

在主从复制中,新增参数 binlog_row_value_options,控制JSON数据的传输方式,允许对于Json类型部分修改,在binlog中只记录修改的部分,减少json大数据在只有少量修改的情况下,对资源的占用。

9. redo & undo 日志加密

增加以下两个参数,用于控制redo、undo日志的加密。
innodb_undo_log_encrypt
innodb_undo_log_encrypt

10. innodb select for update跳过锁等待

select … for update,select … for share(8.0新增语法) 添加 NOWAIT、SKIP LOCKED语法,跳过锁等待,或者跳过锁定。
在5.7及之前的版本,select…for update,如果获取不到锁,会一直等待,直到innodb_lock_wait_timeout超时。

在8.0版本,通过添加nowait,skip locked语法,能够立即返回。如果查询的行已经加锁,那么nowait会立即报错返回,而skip locked也会立即返回,只是返回的结果中不包含被锁定的行。

# session1:
mysql> begin;
mysql> select * from t1 where c1 = 2 for update;
+------+-------+
| c1   | c2    |
+------+-------+
|    2 | 60530 |
|    2 | 24678 |
+------+-------+
2 rows in set (0.00 sec)
 
# session2:
mysql> select * from t1 where c1 = 2 for update nowait;
ERROR 3572 (HY000): Statement aborted because lock(s) could not be acquired  immediately and NOWAIT is set.
mysql> select * from t1 where c1 = 2 for update skip locked;
Empty set (0.00 sec)

11. 增加SET_VAR语法

在sql语法中增加SET_VAR语法,动态调整部分参数,有利于提升语句性能。

  • select /*+ SET_VAR(sort_buffer_size = 16M) */ id from test order id ;
  • insert /*+ SET_VAR(foreign_key_checks=OFF) */ into test(name) values(1);

12. 支持不可见索引

使用INVISIBLE关键字在创建表或者进行表变更中设置索引是否可见。索引不可见只是在查询时优化器不使用该索引,即使使用force index,优化器也不会使用该索引,同时优化器也不会报索引不存在的错误,因为索引仍然真实存在,在必要时,也可以快速的恢复成可见。

# 创建不可见索引
create table t2(c1 int,c2 int,index idx_c1_c2(c1,c2 desc) invisible );
# 索引可见
alter table t2 alter index idx_c1_c2 visible;
# 索引不可见
alter table t2 alter index idx_c1_c2 invisible;

13. 支持直方图

优化器会利用column_statistics的数据,判断字段的值的分布,得到更准确的执行计划。

可以使用 ANALYZE TABLE table_name [UPDATE HISTOGRAM on col_name with N BUCKETS |DROP HISTOGRAM ON clo_name] 来收集或者删除直方图信息。

直方图统计了表中某些字段的数据分布情况,为优化选择高效的执行计划提供参考,直方图与索引有着本质的区别,维护一个索引有代价。每一次的insert、update、delete都会需要更新索引,会对性能有一定的影响。而直方图一次创建永不更新,除非明确去更新它。所以不会影响insert、update、delete的性能。

# 添加/更新直方图
mysql> analyze table t1 update histogram on c1, c2 with 32 buckets;
+--------+-----------+----------+-----------------------------------------------+
| Table  | Op        | Msg_type | Msg_text                                      |
+--------+-----------+----------+-----------------------------------------------+
| db.t1 | histogram | status   | Histogram statistics created for column 'c1'. |
| db.t1 | histogram | status   | Histogram statistics created for column 'c2'. |
+--------+-----------+----------+-----------------------------------------------+
2 rows in set (2.57 sec)
 
# 删除直方图
mysql> analyze table t1 drop histogram on c1, c2;
+--------+-----------+----------+-----------------------------------------------+
| Table  | Op        | Msg_type | Msg_text                                      |
+--------+-----------+----------+-----------------------------------------------+
| db.t1 | histogram | status   | Histogram statistics removed for column 'c1'. |
| db.t1 | histogram | status   | Histogram statistics removed for column 'c2'. |
+--------+-----------+----------+-----------------------------------------------+
2 rows in set (0.13 sec)

14. 新增innodb_dedicated_server参数

能够让InnoDB根据服务器上检测到的内存大小自动配置innodb_buffer_pool_size,innodb_log_file_size,innodb_flush_method三个参数。

15. 日志分类更详细

在错误信息中添加了错误信息编号[MY-010311]和错误所属子系统[Server]

# MySQL 5.7
2018-06-08T09:07:20.114585+08:00 0 [Warning] 'proxies_priv' entry '@ root@localhost' ignored in --skip-name-resolve mode.
2018-06-08T09:07:20.117848+08:00 0 [Warning] 'tables_priv' entry 'user mysql.session@localhost' ignored in --skip-name-resolve mode.
2018-06-08T09:07:20.117868+08:00 0 [Warning] 'tables_priv' entry 'sys_config mysql.sys@localhost' ignored in --skip-name-resolve mode.
 
 
# MySQL 8.0
2018-06-21T17:53:13.040295+08:00 28 [Warning] [MY-010311] [Server] 'proxies_priv'  entry '@ root@localhost' ignored in --skip-name-resolve mode.
2018-06-21T17:53:13.040520+08:00 28 [Warning] [MY-010330] [Server] 'tables_priv'  entry 'user mysql.session@localhost' ignored in --skip-name-resolve mode.
2018-06-21T17:53:13.040542+08:00 28 [Warning] [MY-010330] [Server] 'tables_priv'  entry 'sys_config mysql.sys@localhost' ignored in --skip-name-resolve mode.

16. undo空间自动回收

  • innodb_undo_log_truncate参数在8.0.2版本默认值由OFF变为ON,默认开启undo日志表空间自动回收。
  • innodb_undo_tablespaces参数在8.0.2版本默认为2,当一个undo表空间被回收时,还有另外一个提供正常服务。
  • innodb_max_undo_log_size参数定义了undo表空间回收的最大值,当undo表空间超过这个值,该表空间被标记为可回收。

17. 增加资源组

MySQL 8.0新增了一个资源组功能,用于调控线程优先级以及绑定CPU核。
MySQL用户需要有 RESOURCE_GROUP_ADMIN权限才能创建、修改、删除资源组。
在Linux环境下,MySQL进程需要有 CAP_SYS_NICE 权限才能使用资源组完整功能。

[root@localhost~]# sudo setcap cap_sys_nice+ep /usr/local/mysql8.0/bin/mysqld
[root@localhost~]# getcap /usr/local/mysql8.0/bin/mysqld
/usr/local/mysql8.0/bin/mysqld = cap_sys_nice+ep

默认提供两个资源组,分别是USR_default,SYS_default

创建资源组:
create resource group test_resouce_group type=USER vcpu=0,1 thread_priority=5;
将当前线程加入资源组:
SET RESOURCE GROUP test_resouce_group;
将某个线程加入资源组:
SET RESOURCE GROUP test_resouce_group FOR thread_id;
查看资源组里有哪些线程:
select * from Performance_Schema.threads where RESOURCE_GROUP=’test_resouce_group';
修改资源组:
alter resource group test_resouce_group vcpu = 2,3 THREAD_PRIORITY = 8;
删除资源组 :
drop resource group test_resouce_group;

# 创建资源组
mysql>create resource group test_resouce_group type=USER vcpu=0,1 thread_priority=5;
Query OK, 0 rows affected (0.03 sec)

mysql> select * from RESOURCE_GROUPS;
+---------------------+---------------------+------------------------+----------+-----------------+
| RESOURCE_GROUP_NAME | RESOURCE_GROUP_TYPE | RESOURCE_GROUP_ENABLED | VCPU_IDS |  THREAD_PRIORITY |
+---------------------+---------------------+------------------------+----------+-----------------+
| USR_default         | USER                |                      1 | 0-3      |                0 |
| SYS_default         | SYSTEM              |                      1 | 0-3      |                0 |
| test_resouce_group  | USER                |                      1 | 0-1      |                5 |
+---------------------+---------------------+------------------------+----------+-----------------+
3 rows in set (0.00 sec)

# 把线程id为60的线程加入到资源组test_resouce_group中,线程id可通过Performance_Schema.threads获取
mysql> SET RESOURCE GROUP test_resouce_group FOR 60;
Query OK, 0 rows affected (0.00 sec)

# 资源组里有线程时,删除资源组报错
mysql> drop resource group test_resouce_group;
ERROR 3656 (HY000): Resource group test_resouce_group is busy.

# 修改资源组
mysql> alter resource group test_resouce_group vcpu = 2,3 THREAD_PRIORITY = 8;
Query OK, 0 rows affected (0.10 sec)
mysql> select * from RESOURCE_GROUPS;
+---------------------+---------------------+------------------------+----------+-----------------+
| RESOURCE_GROUP_NAME | RESOURCE_GROUP_TYPE | RESOURCE_GROUP_ENABLED | VCPU_IDS | THREAD_PRIORITY |
+---------------------+---------------------+------------------------+----------+-----------------+
| USR_default         | USER                |                      1 | 0-3      |               0 |
| SYS_default         | SYSTEM              |                      1 | 0-3      |               0 |
| test_resouce_group  | USER                |                      1 | 2-3      |               8 |
+---------------------+---------------------+------------------------+----------+-----------------+
3 rows in set (0.00 sec)

# 把资源组里的线程移出到默认资源组USR_default
mysql> SET RESOURCE GROUP USR_default FOR 60;
Query OK, 0 rows affected (0.00 sec)

# 删除资源组
mysql> drop resource group test_resouce_group;
Query OK, 0 rows affected (0.04 sec)

18. 增加角色管理

角色可以认为是一些权限的集合,为用户赋予统一的角色,权限的修改直接通过角色来进行,无需为每个用户单独授权。

# 创建角色
mysql> create role role_test;
Query OK, 0 rows affected (0.03 sec)
 
# 给角色授予权限
mysql> grant select on db.* to 'role_test';
Query OK, 0 rows affected (0.10 sec)
 
# 创建用户
mysql> create user 'read_user'@'%' identified by '123456';
Query OK, 0 rows affected (0.09 sec)
 
# 给用户赋予角色
mysql> grant 'role_test' to 'read_user'@'%';
Query OK, 0 rows affected (0.02 sec)
 
# 给角色role_test增加insert权限
mysql> grant insert on db.* to 'role_test';
Query OK, 0 rows affected (0.08 sec)
 
# 给角色role_test删除insert权限
mysql> revoke insert on db.* from 'role_test';
Query OK, 0 rows affected (0.10 sec)
 
# 查看默认角色信息
mysql> select * from mysql.default_roles;
+------+-----------+-------------------+-------------------+
| HOST | USER      | DEFAULT_ROLE_HOST | DEFAULT_ROLE_USER |
+------+-----------+-------------------+-------------------+
| %    | read_user | %                 | role_test         |
+------+-----------+-------------------+-------------------+
1 row in set (0.00 sec)
 
# 查看角色与用户关系
mysql> select * from mysql.role_edges;
+-----------+-----------+---------+-----------+-------------------+
| FROM_HOST | FROM_USER | TO_HOST | TO_USER   | WITH_ADMIN_OPTION |
+-----------+-----------+---------+-----------+-------------------+
| %         | role_test | %       | read_user | N                 |
+-----------+-----------+---------+-----------+-------------------+
1 row in set (0.00 sec)
 
# 删除角色
mysql> drop role role_test;
Query OK, 0 rows affected (0.06 sec)
12314