MySQL 8.0 OCP认证精讲视频、环境和题库之四 多实例启动 缓存、事务、脏读

一、配置第一个mysqld服务

    1、编辑选项文件，指定以下选项：
    [mysqld]
        basedir=/mysql80
        datadir=/mysql80/data1
        socket=/mysql80/data1/mysqld.sock
        pid-file=/mysql80/data1/mysqld.pid
        log-error=/mysql80/data1/mysqld.log
    2、初始化
        /mysql80/bin/mysqld --defaults-file=/mysql80/mysqld.cnf --initialize
    3、启动mysqld服务
        /mysql80/bin/mysqld--defaults-file=/mysql80/mysqld.cnf&
    4、查看初始密码，登录，修改出初始密码    
        mysqi -uroot -p'As%&sT9xX/wp' -h127.0.0.1 -P3307
        mysql>alter user user() identifiedby 'Ora_123';
        
    5、创建一个统一的用户，比如 multi_user ,密码相同，并指定shutdown权限
        mysql>create user multi_user@localhost identifiedby 'Ora_123';
        mysql>grant shutdown on *.* to  multi_user@localhost;
    6、 关闭mysqld
        /mysql80/bin/mysqldadmin -u multi_user -h 127.0.0.1 -P 3307 shutdown
    7、 屏蔽掉mysqld选
        #[mysqld]
        #basedir=/mysgl80
        #datadir=/mysql8o/datal
        socket=/mysql80/datal/mysqld.sock
        #pid-file=/mysql80/data1/mysqld.pid
        #log-error=/mysql80/data1/mysqld.log
        #port=3307

二、配置第二个mydqld服务 

    重复上述步骤1-7，指定不同的配置；

三、配置第三个mydqld服务 

    重复上述步骤1-7，指定不同的配置；
    
    #[mysqld]
    #basedir=/mysgl80
    #datadir=/mysql8o/datal
    #socket=/mysql80/datal/mysqld.sock
    #pid-file=/mysql80/data1/mysqld.pid
    #log-error=/mysql80/data1/mysqld.log
    #port=3307
    
    #[mysqld]
    #basedir=/mysql80
    #datadir=/mysql80/data2
    #socket=/mysql80/data2/mysgld.sock
    #pid-file=/mysql80/data2/mysqld.pid
    #log-error=/mysql80/data2/mysqld.log
    #port=3308
    
    #[mysqld]
    #basedir=/mysql80
    #datadir=/mysql80/data3
    #socket=/mysql80/data3/mysgld.sock
    #pid-file=/mysql80/data3/mysqld.pid
    #log-error=/mysql80/data3/mysqld.log
    #port=3309

四、取消前面的#；

    [mysqld]
    basedir=/mysgl80
    datadir=/mysql8o/datal
    socket=/mysql80/datal/mysqld.sock
    pid-file=/mysql80/data1/mysqld.pid
    log-error=/mysql80/data1/mysqld.log
    port=3307
    
    [mysqld]
    basedir=/mysql80
    datadir=/mysql80/data2
    socket=/mysql80/data2/mysgld.sock
    pid-file=/mysql80/data2/mysqld.pid
    log-error=/mysql80/data2/mysqld.log
    port=3308
    
    [mysqld]
    basedir=/mysql80
    datadir=/mysql80/data3
    socket=/mysql80/data3/mysgld.sock
    pid-file=/mysql80/data3/mysqld.pid
    log-error=/mysql80/data3/mysqld.log
    port=3309
    
    [mysql_miti]
     mysqld=/mysql80/bin/mysqld
     mysqladmin=/mysql80/bin/mysqladmin
     user= multi_user

五、修改选项名称

 
    [mysqld1]
    basedir=/mysgl80
    datadir=/mysql8o/datal
    socket=/mysql80/datal/mysqld.sock
    pid-file=/mysql80/data1/mysqld.pid
    log-error=/mysql80/data1/mysqld.log
    port=3307
    
    [mysqld]
    basedir=/mysql80
    datadir=/mysql80/data2
    socket=/mysql80/data2/mysgld.sock
    pid-file=/mysql80/data2/mysqld.pid
    log-error=/mysql80/data2/mysqld.log
    port=3308
    
    [mysqld3]
    basedir=/mysql80
    datadir=/mysql80/data3
    socket=/mysql80/data3/mysgld.sock
    pid-file=/mysql80/data3/mysqld.pid
    log-error=/mysql80/data3/mysqld.log
    port=3309
    
    [mysql_miti]
     mysqld=/mysql80/bin/mysqld
     mysqladmin=/mysql80/bin/mysqladmin
     user= multi_user

六、调用mysqld_multi命令，对多个mysqld服务统一地进行启动，关闭，查看状态 

七、修改bug

     /usr/local/mysql/bin/mysqld_multi有Bug
    # vi /usr/local/mysql/bin/mysqld_multi

    sub defaults_for_group
    {
      my ($group) = @_;

      return () unless $my_print_defaults_exists;

      my $com= join ' ', 'my_print_defaults', @defaults_options, $group;
      my @defaults = `$com`;
      chomp @defaults;
      return @defaults;
    }

    改 my $com= join ' ', 'my_print_defaults -s', @defaults_options, $group;
    

八、缓存buffer_pool

join_buffer_size
sort_buffer_size    
mysql> show variables like '%sort%';
    +--------------------------------+---------------------+
    | Variable_name                  | Value               |
    +--------------------------------+---------------------+
    | innodb_disable_sort_file_cache | OFF                 |
    | innodb_ft_sort_pll_degree      | 2                   |
    | innodb_sort_buffer_size        | 1048576             |
    | max_length_for_sort_data       | 4096                |
    | max_sort_length                | 1024                |
    | myisam_max_sort_file_size      | 9223372036853727232 |
    | myisam_sort_buffer_size        | 8388608             |
    | sort_buffer_size               | 262144              |
    +--------------------------------+---------------------+
    8 rows in set (0.00 sec)

mysql> show variables like 'innodb_buffer_pool_size';
    +-------------------------+-----------+
    | Variable_name           | Value     |
    +-------------------------+-----------+
    | innodb_buffer_pool_size | 134217728 |   
    +-------------------------+-----------+
    1 row in set (0.00 sec)

    mysql> 
    #innodb_buffer_pool_size | 134217728 生产环境偏小。
mysql> show variables like '%buffer_pool%';
        +-------------------------------------+----------------+
    | Variable_name                       | Value          |
    +-------------------------------------+----------------+
    | innodb_buffer_pool_chunk_size       | 134217728      |
    | innodb_buffer_pool_dump_at_shutdown | ON             |
    | innodb_buffer_pool_dump_now         | OFF            |
    | innodb_buffer_pool_dump_pct         | 25             |
    | innodb_buffer_pool_filename         | ib_buffer_pool |
    | innodb_buffer_pool_instances        | 1              |
    | innodb_buffer_pool_load_abort       | OFF            |
    | innodb_buffer_pool_load_at_startup  | ON             |
    | innodb_buffer_pool_load_now         | OFF            |
    | innodb_buffer_pool_size             | 134217728      |
    +-------------------------------------+----------------+
    10 rows in set (0.00 sec)
    
buffer_pool的设置
    变量innodb_buffer_pool_size：指定buffer_size的大小 。
    变量innodb_buffer_pool_chunk_size：在调整buffer_pool大小限制时的基本单位；
    当增加或减少innodb_buffer_pool_size时，操作是chunk_size执行的。区块大小由innodb_buffer_pool_Chunk_size配置选项定义，默认值为128M=134217728b。
    
    innodb_buffer_pool_size大小必须始终等于或等于innodb_Buffer_pool_chunk_size*innodb_Buffer_pool_instances的倍数。
    mysql> show variables like '%innodb_buffer_pool_instances%';
    +------------------------------+-------+
    | Variable_name                | Value |
    +------------------------------+-------+
    | innodb_buffer_pool_instances | 1     |
    +------------------------------+-------+
    1 row in set (0.00 sec)
    
    如果将innodb_buffer_pool_size配置为不等于innodb_buffer_pool_chunk_size*innodb_uffer_pool_instances或其整数倍的值，
    则缓冲池大小将自动调整为等于innodd_buffer_pool_chunk_size*innodb_pool_instances的值或其倍数。
    假设
    innodb_buffer_pool_size设置为8G，innodl_buffer_poor_instances设置为16。innodb_buffer_pool_chunk_size为128M，为默认值。
    可以判断
     innodd_buffer_pool_chunk_size*innodb_pool_instances=128M*16=  2^7*2^4=2*2^10=2G
    ( innodb_buffer_pool_size=8)/(innodd_buffer_pool_chunk_size*innodb_pool_instances=2G)=4倍
    因此innodb_buffer_pool_size-8G是有效值

    8G是一个有效的innodb_buffer_pool_size值，因为8G是innodb_buffer_pool_instance=16*innodb_uffer_pool_chunk_size=128M的倍数，即2G。
    
    
    再假设
    在本例中，innodb_buffer_pool_size设置为9G（刚才是8G），innodl_buffer_pool_instances设置为16。innodb_buffer_pool_chunk_size为128M，为默认值。
    innodb_buffer_poor_instances*innodb_buffer_pool_chunk_size=16*128M=2G ，9G/2g=4.5倍(访问时跨chunk访问，所以慢)
    因此mysql向上取整调整为10G。
    
    innodb_buffer_pool_instances    其值为>=0,<=64
    innodb_buffer_pool被划分为的区域数。对于innodb_buffer_pool在1GB 字节范围内的系统，将缓冲池划分为单独的实例,
    可以通过减少不同线程读取和写入缓存页面时的争用来提高并发性。此选项仅在将innodb_buffer_pool_size设置为1GB或更大时生效。
    缓冲池的总大小在所有缓冲池中划分。为了获得最佳效率，请指定innodb_buffer_pool_instances和innodb_buffer_pool_size的组合，以便每个缓冲池实例至少为1GB。
    
    On all other platforms, the default value is 8 when innodb_buffer_pool_size is greater than or equal to 1GB. Otherwise, the default is 1.
    
    
mysql> show variables like 'innodb_buffer_pool_size';
    +-------------------------+-----------+
    | Variable_name           | Value     |
    +-------------------------+-----------+
    | innodb_buffer_pool_size | 134217728 |
    +-------------------------+-----------+
    1 row in set (0.00 sec)

    mysql> 

    mysql> select  134217728 /1024/1024;
    +----------------------+
    | 134217728 /1024/1024 |
    +----------------------+
    |         128.00000000 |   ###128MB
    +----------------------+
    1 row in set (0.00 sec)

    mysql> 
mysql> show variables like 'innodb_buffer_pool_chunk_size';
    +-------------------------------+-----------+
    | Variable_name                 | Value     |
    +-------------------------------+-----------+
    | innodb_buffer_pool_chunk_size | 134217728 |
    +-------------------------------+-----------+
    1 row in set (0.00 sec)
    
mysql> show variables like '%innodb_buffer_pool_instances%';
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| innodb_buffer_pool_instances | 1     |
+------------------------------+-------+
1 row in set (0.00 sec)

mysql> 

 实践

MySQL :: MySQL 8.0 Reference Manual :: 15.8.3.1 Configuring InnoDB Buffer Pool Size

变量innodb buffer pool size:指定buffer poole的大小
变量innodb_buffer_pool_chunk_size:在洞整buffer pool大小时的基本单位
变量innodb buffer pool_instances:把innodb buffer分成若干部分，分别管理，仅当innodb buffer_pool size的值大于1GB时生效
实验：buffer pool的设道
1、查看上述变量的值
2、通过set命令将innodb buffer pool size设置为129M,查看实际大小
3、修改选项文件，指定以下选项：
    innodb buffer pool chunk size=64M
    innodb_buffer_pool_size=256M
    重新启动nysqld
4、   通过set命令修改innodb buffer pool size为300M,查看实际大小
    

处理：

1 、

mysql> show variables like 'innodb_buffer_pool_size%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+
1 row in set (0.01 sec)

mysql> show variables like 'innodb_buffer_pool_chunk%';
+-------------------------------+-----------+
| Variable_name                 | Value     |
+-------------------------------+-----------+
| innodb_buffer_pool_chunk_size | 134217728 |
+-------------------------------+-----------+
1 row in set (0.00 sec)

mysql> select 134217728/1024/1024 ;
+---------------------+
| 134217728/1024/1024 |
+---------------------+
|        128.00000000 |
+---------------------+
1 row in set (0.00 sec)

2、

mysql> select 129*1024*1024;
+---------------+
| 129*1024*1024 |
+---------------+
|     135266304 |
+---------------+
1 row in set (0.00 sec)

mysql> set global innodb_buffer_pool_size=135266304;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> show variables like 'innodb_buffer_pool_size%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 268435456 |
+-------------------------+-----------+
1 row in set (0.00 sec)

mysql> select 268435456/1024/1024;
+---------------------+
| 268435456/1024/1024 |
+---------------------+
|        256.00000000 |     ##预期129MB  ，实际256MB，是向上取整数倍速128MB
+---------------------+
1 row in set (0.00 sec)
 

3、

 systemctl stop  mysql

 vi /etc/my.cnf
 

[mysqld]
datadir=/data/mysql/data/
socket=/data/mysql/mysql.sock

innodb_buffer_pool_chunk_size=64M
innodb_buffer_pool_size=256M
systemctl start   mysql
 

mysql> show variables like 'innodb_buffer_pool_chunk_size' ;
+-------------------------------+----------+
| Variable_name                 | Value    |
+-------------------------------+----------+
| innodb_buffer_pool_chunk_size | 67108864 |
+-------------------------------+----------+
1 row in set (0.01 sec)

mysql> show variables like 'innodb_buffer_pool_size' ;
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 268435456 |
+-------------------------+-----------+
1 row in set (0.00 sec)

4、

mysql> set global innodb_buffer_pool_size=314572800;

mysql> show variables  like 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 335544320 |
+-------------------------+-----------+
1 row in set (0.00 sec)

mysql> select 335544320/1024/1024;
+---------------------+
| 335544320/1024/1024 |
+---------------------+
|        320.00000000 |     
+---------------------+
1 row in set (0.00 sec)

mysql> 
# /etc/my.cnf 中的chunk_size=64MB，300MB/64MB ~4.6875 ;向上取整=5 倍

所以 64MB*5=320MB；

九、脏读及刷新

变量innodb_max_dirty_pages_pct:
    innoDB在后台执行某些任务，包括从缓冲池中清除脏页。脏页是指那些已被修改但尚未写入磁盘上的数据文件的页。
    在MySQL 8.0中，缓冲池刷新是由页面清理线程执行的。页面清理线程的数量由innodb_page_cleaners变量控制，该变量的默认值为4。
    但是，如果页面清理线程的数量超过缓冲池实例的数量，innodb_page_cleaners会自动设置为与innodb_buffer_pool_instances相同的值。
    当脏页的百分比达到innodb_max_dirty_pages_pct_lwm变量定义的低水位标记值时，启动缓冲池刷新。默认的低水位标记是缓冲池页面的10%。innodb_max_dirty_pages_pct_lwm值为0将禁用这种早期刷新行为。
    innodb_max_dirty_pages_pct_lwm阈值的目的是控制缓冲池中脏页的百分比，并防止脏页的数量达到innodb_ax_dirty_pages_pct变量定义的阈值，该变量的默认值为90。如果缓冲池中脏页面的百分比达到InnoDB_max_dirty_pages_pct阈值，InnoDB会主动刷新缓冲池页面。
    配置innodb_max_dirty_pages_pct_lwm时，该值应始终低于innodb_ax_dirty_pages_pct值。

变量innodb_flush_neighbors：
    允许对缓冲池刷新行为进行微调：
innodb_flush_neighbors变量定义从缓冲池中刷新页面是否也会刷新同一范围中的其他脏页面。
默认设置0：
    将禁用innodb_flush_neighbors。相同范围内的脏页不会被刷新。对于寻道时间不是重要因素的非旋转存储（SSD）设备，建议使用此设置。
设置为1：
    将在同一范围内刷新连续的脏页。
设置为2：
    会在同一范围内刷新脏页。
    
innodb_LRU_scan_depth变量为每个缓冲池实例指定页面清理器线程在缓冲池LRU列表中搜索脏页面的深度。这是页面清理线程每秒执行一次的后台操作。
innodb_flush_neighbors和innodb_LRU_scan_depth变量主要用于写密集型工作负载。
小于默认值的设置通常适用于大多数工作负载。明显高于必要值的值可能会影响性能。只有在典型工作负载下有空闲I/O容量时，才考虑增加值。
相反，如果写密集型工作负载使I/O容量饱和，请降低该值，尤其是在缓冲池很大的情况下。
 

十、事务

MySQL :: MySQL 8.0 Reference Manual :: 13.3.7 SET TRANSACTION Statement

事务(transaction):一系列SQL的集合，整体执行；要 么全部成功，要么全部失败；
事务的属性：ACID
    A：原子性
    C: 一致性
    I：独立性、并发事务，隔离性
    D：持久性
事务的处理：
    commit;
    rollback;

事务的形式：
    1、多条DML的集合  insert  update delete  需要手工提交
    2、单条DDL        create  drop  alter  truncate 立即提交
    3、单条DCL        grant revoke  

变量autocommit：仅针对第一种事务

start TRANSACTION
update...
insert ...
delete ...
commit/rollback