- MySQL skip-grants-tables Tips
- MySQL employees-schema 导入测试
- MySQL ICP(Index Condition Pushdown) 测试
- MySQL analysis_binlog 解析binary log测试
- Referece
MySQL 8.0 Study 014 Tips
学习系列
- MySQL MySQL skip-grants-tables Tips
- MySQL employees-schema 导入测试
- MySQL ICP(Index Condition Pushdown) 测试
- MySQL analysis_binlog 解析binary log测试
MySQL skip-grants-tables Tips
防火墙什么的均正常但是无法被远程访问到。简单的使用客户端登录报错。
ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (111)
查看教程/端口绑定/
[root@ol8mysql01 ~]# ps -ef | grep mysql
root 5415 1 0 Mar02 pts/0 00:00:00 /bin/sh bin/mysqld_safe --defaults-file=/root/sandboxes/ee_8_0_32/my.sandbox.cnf
root 5778 5415 0 Mar02 pts/0 00:09:38 /opt/mysql/8.0.32/bin/mysqld --defaults-file=/root/sandboxes/ee_8_0_32/my.sandbox.cnf --basedir=/opt/mysql/8.0.32 --datadir=/root/sandboxes/ee_8_0_32/data --plugin-dir=/opt/mysql/8.0.32/lib/plugin --user=root --log-error=/root/sandboxes/ee_8_0_32/data/msandbox.err --pid-file=/root/sandboxes/ee_8_0_32/data/mysql_sandbox8032.pid --socket=/tmp/mysql_sandbox8032.sock --port=8032
root 27945 5297 0 16:04 pts/0 00:00:00 grep --color=auto mysql
[root@ol8mysql01 ~]# lsof -i:8032
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 5778 root 22u IPv4 32282 0t0 TCP *:pro-ed (LISTEN)
[root@ol8mysql01 ~]# ss -nltp | grep 8032
LISTEN 0 151 0.0.0.0:8032 0.0.0.0:* users:(("mysqld",pid=5778,fd=22))
LISTEN 0 70 *:18032 *:* users:(("mysqld",pid=5778,fd=20))
[root@ol8mysql01 ~]#
下面端口如果是0
的话,而且配置了skip-grants-tables
的话,可能报连接错误。
mysql> show variables like 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port | 8032 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'skip_net%';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| skip_networking | OFF |
+-----------------+-------+
1 row in set (0.00 sec)
mysql>
参数情况:
[root@ol8mysql01 ee_8_0_32]# ps -ef | grep mysql
root 5415 1 0 Mar02 pts/0 00:00:00 /bin/sh bin/mysqld_safe --defaults-file=/root/sandboxes/ee_8_0_32/my.sandbox.cnf
root 5778 5415 0 Mar02 pts/0 00:09:43 /opt/mysql/8.0.32/bin/mysqld --defaults-file=/root/sandboxes/ee_8_0_32/my.sandbox.cnf --basedir=/opt/mysql/8.0.32 --datadir=/root/sandboxes/ee_8_0_32/data --plugin-dir=/opt/mysql/8.0.32/lib/plugin --user=root --log-error=/root/sandboxes/ee_8_0_32/data/msandbox.err --pid-file=/root/sandboxes/ee_8_0_32/data/mysql_sandbox8032.pid --socket=/tmp/mysql_sandbox8032.sock --port=8032
root 27980 5297 0 16:14 pts/0 00:00:00 grep --color=auto mysql
[root@ol8mysql01 ee_8_0_32]# find /opt/mysql/8.0.32 -name mysqld
/opt/mysql/8.0.32/bin/mysqld
[root@ol8mysql01 ee_8_0_32]# /opt/mysql/8.0.32/bin/mysqld --verbose --help | grep skip-network
--skip-networking Don't allow connection with TCP/IP
skip-networking FALSE
[root@ol8mysql01 ee_8_0_32]# /opt/mysql/8.0.32/bin/mysqld --verbose --help | grep skip-grant
--skip-grant-tables Start without grant tables. This gives all users FULL
skip-grant-tables FALSE
[root@ol8mysql01 ee_8_0_32]#
Because starting the server with --skip-grant-tables disables authentication checks, the server also disables remote connections in that case by enabling skip_networking.
因为使用 skip-grants-tables 参数禁用了权限表,这种情况下 mysql 会默认开启 skip-networking 来禁用远程连接。目的是为了安全。
配置 skip-grants-tables 引起无法远程连接 mysql 服务端的故障,解决方法也是非常的简单注释重启。
MySQL employees-schema 导入测试
MySQL官方文档中关于此employees-schema
数据库参考如下
MySQL employees-schema 导入和确实
ER 结构图如下:
[root@ol8mysql01 vagrant]# unzip test_db-master.zip
Archive: test_db-master.zip
d01cb62fcfa4671773f167480df174d8ce4316c1
creating: test_db-master/
inflating: test_db-master/Changelog
inflating: test_db-master/README.md
inflating: test_db-master/employees.sql
inflating: test_db-master/employees_partitioned.sql
inflating: test_db-master/employees_partitioned_5.1.sql
creating: test_db-master/images/
inflating: test_db-master/images/employees.gif
inflating: test_db-master/images/employees.jpg
inflating: test_db-master/images/employees.png
inflating: test_db-master/load_departments.dump
inflating: test_db-master/load_dept_emp.dump
inflating: test_db-master/load_dept_manager.dump
inflating: test_db-master/load_employees.dump
inflating: test_db-master/load_salaries1.dump
inflating: test_db-master/load_salaries2.dump
inflating: test_db-master/load_salaries3.dump
inflating: test_db-master/load_titles.dump
inflating: test_db-master/objects.sql
creating: test_db-master/sakila/
inflating: test_db-master/sakila/README.md
inflating: test_db-master/sakila/sakila-mv-data.sql
inflating: test_db-master/sakila/sakila-mv-schema.sql
inflating: test_db-master/show_elapsed.sql
inflating: test_db-master/sql_test.sh
inflating: test_db-master/test_employees_md5.sql
inflating: test_db-master/test_employees_sha.sql
inflating: test_db-master/test_versions.sh
[root@ol8mysql01 vagrant]#cd test_db-master
[root@ol8mysql01 test_db-master]# pwd
/vagrant/test_db-master
[root@ol8mysql01 test_db-master]# # mysql -S /tmp/mysql_sandbox8033.sock < employees.sqlINFO
CREATING DATABASE STRUCTURE
INFO
storage engine: InnoDB
INFO
LOADING departments
INFO
LOADING employees
INFO
LOADING dept_emp
INFO
LOADING dept_manager
INFO
LOADING titles
INFO
LOADING salaries
data_load_time_diff
00:00:45
[root@ol8mysql01 test_db-master]#
[root@ol8mysql01 test_db-master]# time mysql -S /tmp/mysql_sandbox8033.sock -t < test_employees_sha.sql
+----------------------+
| INFO |
+----------------------+
| TESTING INSTALLATION |
+----------------------+
+--------------+------------------+------------------------------------------+
| table_name | expected_records | expected_crc |
+--------------+------------------+------------------------------------------+
| departments | 9 | 4b315afa0e35ca6649df897b958345bcb3d2b764 |
| dept_emp | 331603 | d95ab9fe07df0865f592574b3b33b9c741d9fd1b |
| dept_manager | 24 | 9687a7d6f93ca8847388a42a6d8d93982a841c6c |
| employees | 300024 | 4d4aa689914d8fd41db7e45c2168e7dcb9697359 |
| salaries | 2844047 | b5a1785c27d75e33a4173aaa22ccf41ebd7d4a9f |
| titles | 443308 | d12d5f746b88f07e69b9e36675b6067abb01b60e |
+--------------+------------------+------------------------------------------+
+--------------+------------------+------------------------------------------+
| table_name | found_records | found_crc |
+--------------+------------------+------------------------------------------+
| departments | 9 | 4b315afa0e35ca6649df897b958345bcb3d2b764 |
| dept_emp | 331603 | d95ab9fe07df0865f592574b3b33b9c741d9fd1b |
| dept_manager | 24 | 9687a7d6f93ca8847388a42a6d8d93982a841c6c |
| employees | 300024 | 4d4aa689914d8fd41db7e45c2168e7dcb9697359 |
| salaries | 2844047 | b5a1785c27d75e33a4173aaa22ccf41ebd7d4a9f |
| titles | 443308 | d12d5f746b88f07e69b9e36675b6067abb01b60e |
+--------------+------------------+------------------------------------------+
+--------------+---------------+-----------+
| table_name | records_match | crc_match |
+--------------+---------------+-----------+
| departments | OK | ok |
| dept_emp | OK | ok |
| dept_manager | OK | ok |
| employees | OK | ok |
| salaries | OK | ok |
| titles | OK | ok |
+--------------+---------------+-----------+
+------------------+
| computation_time |
+------------------+
| 00:00:47 |
+------------------+
+---------+--------+
| summary | result |
+---------+--------+
| CRC | OK |
| count | OK |
+---------+--------+
real 0m46.895s
user 0m0.017s
sys 0m0.000s
[root@ol8mysql01 test_db-master]#
MySQL ICP(Index Condition Pushdown) 测试
Index Condition Pushdown (ICP)是MySQL 5.6 版本中的新特性,是一种在存储引擎层使用索引过滤数据的一种优化方式。
- 当关闭ICP时,index 仅仅是data access 的一种访问方式,存储引擎通过索引回表获取的数据会传递到MySQL Server 层进行where条件过滤。
- 当打开ICP时,如果部分where条件能使用索引中的字段,MySQL Server 会把这部分下推到引擎层,可以利用index过滤的where条件在存储引擎层进行数据过滤,而非将所有通过index access的结果传递到MySQL server层进行where过滤.
优化效果:ICP能减少引擎层访问基表的次数和MySQL Server 访问存储引擎的次数,减少io次数,提高查询语句性能。
关闭索引下推可以使用如下命令
set optimizer_switch='index_condition_pushdown=off';
测试
创建employees first_name/last_name的组合索引
mysql [localhost:8032] {msandbox} ((none)) > use employees;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql [localhost:8032] {msandbox} (employees) > show tables;
+----------------------+
| Tables_in_employees |
+----------------------+
| current_dept_emp |
| departments |
| dept_emp |
| dept_emp_latest_date |
| dept_manager |
| employees |
| salaries |
| titles |
+----------------------+
8 rows in set (0.00 sec)
mysql [localhost:8032] {msandbox} (employees) > show index from employees;
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| employees | 0 | PRIMARY | 1 | emp_no | A | 299246 | NULL | NULL | | BTREE | | | YES | NULL |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.01 sec)
mysql [localhost:8032] {msandbox} (employees) > desc employees;
+------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| emp_no | int | NO | PRI | NULL | |
| birth_date | date | NO | | NULL | |
| first_name | varchar(14) | NO | | NULL | |
| last_name | varchar(16) | NO | | NULL | |
| gender | enum('M','F') | NO | | NULL | |
| hire_date | date | NO | | NULL | |
+------------+---------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql [localhost:8032] {msandbox} (employees) > create index idx_emp_first_lastname on employees(first_name,last_name);
Query OK, 0 rows affected (2.35 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql [localhost:8032] {msandbox} (employees) > show index from employees;
+-----------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-----------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| employees | 0 | PRIMARY | 1 | emp_no | A | 299246 | NULL | NULL | | BTREE | | | YES | NULL |
| employees | 1 | idx_emp_first_lastname | 1 | first_name | A | 1311 | NULL | NULL | | BTREE | | | YES | NULL |
| employees | 1 | idx_emp_first_lastname | 2 | last_name | A | 276828 | NULL | NULL | | BTREE | | | YES | NULL |
+-----------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0.01 sec)
mysql [localhost:8032] {msandbox} (employees) >
mysql [localhost:8032] {msandbox} (employees) > select * from employees limit 10000,2;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 20001 | 1962-05-16 | Atreye | Eppinger | M | 1990-04-18 |
| 20002 | 1955-12-25 | Jaber | Brender | M | 1988-01-26 |
+--------+------------+------------+-----------+--------+------------+
2 rows in set (0.00 sec)
mysql [localhost:8032] {msandbox} (employees) >
测试
- 查询语句: MySQL的最左前缀原则, first_name 可以使用索引,last_name采用了like 模糊查询,不能使用索引。
[root@ol8mysql01 msb_8_0_32]# ./use
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.32 MySQL Community Server - GPL
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql [localhost:8032] {msandbox} ((none)) > use employees;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql [localhost:8032] {msandbox} (employees) > set profiling=1;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql [localhost:8032] {msandbox} (employees) > select * from employees where first_name='Jaber' and last_name like '%der';
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 20002 | 1955-12-25 | Jaber | Brender | M | 1988-01-26 |
| 17961 | 1963-07-09 | Jaber | Schieder | M | 1985-12-18 |
+--------+------------+------------+-----------+--------+------------+
2 rows in set (0.00 sec)
mysql [localhost:8032] {msandbox} (employees) > select * from employees where first_name='Jaber' and last_name like '%der';
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 20002 | 1955-12-25 | Jaber | Brender | M | 1988-01-26 |
| 17961 | 1963-07-09 | Jaber | Schieder | M | 1985-12-18 |
+--------+------------+------------+-----------+--------+------------+
2 rows in set (0.00 sec)
mysql [localhost:8032] {msandbox} (employees) > select * from employees where first_name='Jaber' and last_name like '%der';
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 20002 | 1955-12-25 | Jaber | Brender | M | 1988-01-26 |
| 17961 | 1963-07-09 | Jaber | Schieder | M | 1985-12-18 |
+--------+------------+------------+-----------+--------+------------+
2 rows in set (0.00 sec)
mysql [localhost:8032] {msandbox} (employees) > show profiles;
+----------+------------+----------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+----------------------------------------------------------------------------+
| 1 | 0.00084375 | select * from employees where first_name='Jaber' and last_name like '%der' |
| 2 | 0.00070300 | select * from employees where first_name='Jaber' and last_name like '%der' |
| 3 | 0.00097475 | select * from employees where first_name='Jaber' and last_name like '%der' |
+----------+------------+----------------------------------------------------------------------------+
3 rows in set, 1 warning (0.00 sec)
mysql [localhost:8032] {msandbox} (employees) > explain select * from employees where first_name='Jaber' and last_name like '%der';
+----+-------------+-----------+------------+------+------------------------+------------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+------------------------+------------------------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | employees | NULL | ref | idx_emp_first_lastname | idx_emp_first_lastname | 58 | const | 264 | 11.11 | Using index condition |
+----+-------------+-----------+------------+------+------------------------+------------------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
mysql [localhost:8032] {msandbox} (employees) > set optimizer_switch='index_condition_pushdown=off';
Query OK, 0 rows affected (0.00 sec)
mysql [localhost:8032] {msandbox} (employees) > select * from employees where first_name='Jaber' and last_name like '%der';
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 20002 | 1955-12-25 | Jaber | Brender | M | 1988-01-26 |
| 17961 | 1963-07-09 | Jaber | Schieder | M | 1985-12-18 |
+--------+------------+------------+-----------+--------+------------+
2 rows in set (0.00 sec)
mysql [localhost:8032] {msandbox} (employees) > select * from employees where first_name='Jaber' and last_name like '%der';
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 20002 | 1955-12-25 | Jaber | Brender | M | 1988-01-26 |
| 17961 | 1963-07-09 | Jaber | Schieder | M | 1985-12-18 |
+--------+------------+------------+-----------+--------+------------+
2 rows in set (0.00 sec)
mysql [localhost:8032] {msandbox} (employees) > select * from employees where first_name='Jaber' and last_name like '%der';
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 20002 | 1955-12-25 | Jaber | Brender | M | 1988-01-26 |
| 17961 | 1963-07-09 | Jaber | Schieder | M | 1985-12-18 |
+--------+------------+------------+-----------+--------+------------+
2 rows in set (0.00 sec)
mysql [localhost:8032] {msandbox} (employees) > show profiles;
+----------+------------+------------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+------------------------------------------------------------------------------------+
| 1 | 0.00084375 | select * from employees where first_name='Jaber' and last_name like '%der' |
| 2 | 0.00070300 | select * from employees where first_name='Jaber' and last_name like '%der' |
| 3 | 0.00097475 | select * from employees where first_name='Jaber' and last_name like '%der' |
| 4 | 0.00111750 | explain select * from employees where first_name='Jaber' and last_name like '%der' |
| 5 | 0.00021700 | set optimizer_switch='index_condition_pushdown=off' |
| 6 | 0.00297800 | select * from employees where first_name='Jaber' and last_name like '%der' |
| 7 | 0.00226100 | select * from employees where first_name='Jaber' and last_name like '%der' |
| 8 | 0.00251200 | select * from employees where first_name='Jaber' and last_name like '%der' |
+----------+------------+------------------------------------------------------------------------------------+
8 rows in set, 1 warning (0.00 sec)
mysql [localhost:8032] {msandbox} (employees) > explain select * from employees where first_name='Jaber' and last_name like '%der';
+----+-------------+-----------+------------+------+------------------------+------------------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+------------------------+------------------------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | employees | NULL | ref | idx_emp_first_lastname | idx_emp_first_lastname | 58 | const | 264 | 11.11 | Using where |
+----+-------------+-----------+------------+------+------------------------+------------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql [localhost:8032] {msandbox} (employees) > set profiling=0;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql [localhost:8032] {msandbox} (employees) >
- ICP 开启时的执行计划 含有 Using index condition 标示 ,表示优化器使用了ICP对数据访问进行优化。
- last_name 的like ‘%der’条件可以通过索引字段last_name 进行过滤,在存储引擎内部通过与where条件的对比,直接过滤掉不符合条件的数据。
- 该过程不回表,只访问符合条件的1条记录并返回给MySQL Server ,有效的减少了io访问和各层之间的交互。
- ICP 关闭时的执行计划显示use where.
ICP的使用限制
- 当sql需要全表访问时,ICP的优化策略可用于range, ref, eq_ref, ref_or_null 类型的访问数据方法 。
- 支持InnoDB和MyISAM表。
- ICP只能用于二级索引,不能用于主索引。
- 并非全部where条件都可以用ICP筛选。如果where条件的字段不在索引列中,还是要读取整表的记录到server端做where过滤。
- ICP的加速效果取决于在存储引擎内通过ICP筛选掉的数据的比例。
- 5.6 版本的不支持分表的ICP 功能,5.7 版本的开始支持。
- 当sql 使用覆盖索引时,不支持ICP 优化方法。
MySQL analysis_binlog 解析binary log测试
analysis_binlog 工具利用内存换取时间,加上可以并行分析多个文件,所以能做到高效解析 binlog,但是这可能也是缺点。 不建议在生产环境服务器使用,否则容易将内存打满。
dbdeployer 安装最新MySQL 8.0.32
# wget https://github.com/datacharmer/dbdeployer/releases/download/v1.71.0/dbdeployer-1.71.0.linux.tar.gz
# tar xzvf dbdeployer-1.71.0.linux.tar.gz
# chmod +x dbdeployer-1.71.0.linux
# mv dbdeployer-1.71.0.linux /usr/local/bin/dbdeployer
# mkdir -p ~/sandboxes/{mysql_package,mysql_base}
# dbdeployer defaults update sandbox-binary $HOME/sandboxes/mysql_base
# dbdeployer defaults show
# scp mysql-commercial-8.0.32-linux-glibc2.12-x86_64.tar.xz ol8mysql:/root
# mkdir /opt/mysql/mysql_ee -p
# dbdeployer --sandbox-binary=/opt/mysql/mysql_ee unpack mysql-commercial-8.0.32-linux-glibc2.12-x86_64.tar.xz
# dbdeployer deploy single 8.0.32 --sandbox-binary=/opt/mysql/mysql_ee --sandbox-directory=ee_8_0_32 --bind-address=0.0.0.0 --port=8032 --remote-access='%' --native-auth-plugin --gtid --my-cnf-options="skip_name_resolve" --pre-grants-sql="create user lin@'%' identified with mysql_native_password by 'mysql';grant all on *.* to lin@'%' with grant option;flush privileges;"
# mysql -ulin -P8032 -pmysql
Client connect:
# mysql -ulin -pmysql -hol8mysql01 -P8032 -e "select version()";
/opt/mysql/mysql_ee/8.0.32/bin/mysql -ulin -P8032 -p
[root@ol8mysql ~]# hostname
ol8mysql
[root@ol8mysql ~]# cat /proc/cpuinfo | grep -i process
processor : 0
processor : 1
[root@ol8mysql ~]# free -m
total used free shared buff/cache available
Mem: 1972 663 521 16 787 1145
Swap: 4095 0 4095
[root@ol8mysql ~]# mysql -ulin -hol8mysql -P8032 -pmysql -e "select version();"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------------+
| version() |
+-------------------+
| 8.0.32-commercial |
+-------------------+
[root@ol8mysql ~]#
sysbench 工具安装
Create test database:
mysql -ulin -pmysql -hol8mysql -P8032 -e "create database if not exists mstest";
script:
#!/bin/bash
# Ref:https://gitee.com/mo-shan/script/blob/master/sysbench.sh
mysql_conne_route_info=(lin mysql ol8mysql 8032)
threads_num=4
db_name="mstest"
table_num=10
table_size=100000
export PATH="/root/sysbench-master/src:${PATH}"
case ${1} in
"--prepare" | "-p")
sysbench /root/sysbench-master/src/lua/oltp_read_only.lua --mysql-host=${mysql_conne_route_info[2]} \
--mysql-port=${mysql_conne_route_info[3]} --mysql-db=${db_name} --mysql-user=${mysql_conne_route_info[0]} \
--mysql-password=${mysql_conne_route_info[1]} --table_size=${table_size} --tables=${table_num} \
--threads=${threads_num} --events=500000 --report-interval=10 --time=60 prepare
;;
"--cleanup" | "-c")
sysbench /root/sysbench-master/src/lua/oltp_read_only.lua --mysql-host=${mysql_conne_route_info[2]} \
--mysql-port=${mysql_conne_route_info[3]} --mysql-db=${db_name} --mysql-user=${mysql_conne_route_info[0]} \
--mysql-password=${mysql_conne_route_info[1]} --table_size=${table_size} --tables=${table_num} \
--threads=${threads_num} --events=500000 --report-interval=10 --time=60 cleanup
;;
"--run" | "-r")
sysbench /root/sysbench-master/src/lua/oltp_update_non_index.lua --mysql-host=${mysql_conne_route_info[2]} \
--mysql-port=${mysql_conne_route_info[3]} --mysql-db=${db_name} --mysql-user=${mysql_conne_route_info[0]} \
--mysql-password=${mysql_conne_route_info[1]} --table_size=${table_size} --tables=${table_num} \
--threads=${threads_num} --events=500000 --report-interval=10 --time=60 run
;;
"--help" | "-h" | *)
echo -e "\n\t\033[33mUsage: bash ${0} [--prepare|-p|--run|-r|--cleanup|-c|--help|-h]\033[0m\n"
echo -e "\t\t\033[34m[--prepare|-p] \t\tPrepare test data to MySQL.\033[0m"
echo -e "\t\t\033[34m[--run|-r] \t\tStart stress test to MySQL.\033[0m"
echo -e "\t\t\033[34m[--cleanup|-c] \t\tCleaning up test data from MySQL.\033[0m\n"
exit 1
;;
esac
Test 测试
mysql -ulin -pmysql -hol8mysql -P8032
flush binary logs;
show binary logs;
mysql> show binary logs;
+------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000001 | 8748 | No |
| mysql-bin.000002 | 220 | No |
| mysql-bin.000003 | 190916435 | No |
| mysql-bin.000004 | 197 | No |
+------------------+-----------+-----------+
4 rows in set (0.01 sec)
mysql>
压力测试:
[root@ol8mysql01 ~]# bash sysbench.sh -r
sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3)
Running the test with following options:
Number of threads: 4
Report intermediate results every 10 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 10s ] thds: 4 tps: 458.71 qps: 458.71 (r/w/o: 0.00/458.71/0.00) lat (ms,95%): 15.00 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 4 tps: 422.69 qps: 422.69 (r/w/o: 0.00/422.69/0.00) lat (ms,95%): 15.83 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 4 tps: 402.63 qps: 402.63 (r/w/o: 0.00/402.63/0.00) lat (ms,95%): 16.12 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 4 tps: 416.88 qps: 416.88 (r/w/o: 0.00/416.88/0.00) lat (ms,95%): 16.12 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 4 tps: 397.71 qps: 397.71 (r/w/o: 0.00/397.71/0.00) lat (ms,95%): 17.01 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 4 tps: 423.54 qps: 423.54 (r/w/o: 0.00/423.54/0.00) lat (ms,95%): 15.55 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 0
write: 25227
other: 0
total: 25227
transactions: 25227 (420.37 per sec.)
queries: 25227 (420.37 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
Throughput:
events/s (eps): 420.3673
time elapsed: 60.0118s
total number of events: 25227
Latency (ms):
min: 3.05
avg: 9.51
max: 32.61
95th percentile: 16.12
sum: 239892.24
Threads fairness:
events (avg/stddev): 6306.7500/20.63
execution time (avg/stddev): 59.9731/0.00
[root@ol8mysql01 ~]#
analysis_binlog
参考: analysis_binlog
git clone https://gitee.com/mo-shan/analysis_binlog.git
bin/analysis_binlog
#将这里的mysqlbinlog_path改成mysqlbinlog工具的绝对路径,否则可能会因版本太低导致错误
(1)更改mysqlbinlog路径
(2)更改analysis_binlog家目录路径
chmod +x bin/analysis_binlog
echo "export PATH=$(pwd)/bin:${PATH}" >> ${HOME}/.bashrc
示例:
-bfile: 指定binlog文件, 支持多个文件并行分析, 多个文件用逗号相隔, 需要并行分析时请结合-w参数使用
-w : 指定并行数, 当需要分析多个binlog文件时该参数有效, 默认是1
-t : 指定显示结果的格式/内容, 供选选项有"detail|simple". 当指定detail的时候结果较为详细, 会打印详细的分析过程, 消耗时间也不直观, simple只做了统计工作
-s : 指定排序规则, 供选选项有"insert|update|delete". 默认会把统计结果做一个排序, 按照表的维度统计出insert update delete的次数, 并按照次数大小排序(默认insert)
Logs:
[root@ol8mysql01 ~]# mysql -ulin -pmysql -hol8mysql -P8032 -e "show binary logs"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000001 | 8748 | No |
| mysql-bin.000002 | 220 | No |
| mysql-bin.000003 | 0 | No |
| mysql-bin.000004 | 190916622 | No |
| mysql-bin.000004 | 190916622 | No |
| mysql-bin.000005 | 17275206 | No |
+------------------+-----------+-----------+
[root@ol8mysql01 ~]# scp ol8mysql:/root/sandboxes/ee_8_0_32/data/mysql-bin.000005 /tmp
root@ol8mysql's password:
mysql-bin.000005 100% 16MB 64.5MB/s 00:00
[root@ol8mysql01 ~]#
analysis_binlog -bfile=/tmp/mysql-bin.000005 -w=2
# analysis_binlog -bfile=/tmp/mysql-bin.000005 -w=2
[2023-03-02 14:20:34] [WARN] [192.168.3.110] Version : v_1.3
[2023-03-02 14:20:34] [INFO] [192.168.3.110] THREAD_1:Analysing --> /tmp/mysql-bin.000005
[2023-03-02 14:20:37] [INFO] [192.168.3.110] THREAD_1:Analysis completed --> /tmp/mysql-bin.000005. Analyze the results in this file '/root/analysis_binlog/res/mysql-bin.000005.res'
# # cat /root/analysis_binlog/res/mysql-bin.000005.res
Table First Time Last Time Type affe(row) Ins(s) Upd(s) Del(s) Total(s)
mstest.sbtest10 230302 13:28:09 230302 13:29:09 DML 0 0 2607 0 2607
mstest.sbtest4 230302 13:28:09 230302 13:29:09 DML 0 0 2589 0 2589
mstest.sbtest5 230302 13:28:09 230302 13:29:09 DML 0 0 2584 0 2584
mstest.sbtest8 230302 13:28:09 230302 13:29:09 DML 0 0 2563 0 2563
mstest.sbtest1 230302 13:28:09 230302 13:29:09 DML 0 0 2539 0 2539
mstest.sbtest6 230302 13:28:09 230302 13:29:09 DML 0 0 2531 0 2531
mstest.sbtest7 230302 13:28:09 230302 13:29:09 DML 0 0 2522 0 2522
mstest.sbtest2 230302 13:28:09 230302 13:29:09 DML 0 0 2512 0 2512
mstest.sbtest3 230302 13:28:09 230302 13:29:09 DML 0 0 2499 0 2499
mstest.sbtest9 230302 13:28:09 230302 13:29:09 DML 0 0 2492 0 2492
Table First Time Last Time Type affe(row) Ins(s) Upd(s) Del(s) Trans(total)
The total 230302 13:28:09 230302 13:29:09 DML 0 0 25438 25438
Table First Time Last Time Type affe(row) Ins(T) Upd(T) Del(T) Trans(total)
Transaction 230302 13:28:09 230302 13:29:09 DML 0 0 25438 0 25438
#
#拷贝一份继续解析成sql文件,并行的话 加上-w=2
[root@ol8mysql ~]# analysis_binlog -mpath=/opt/mysql/mysql_ee/8.0.32/bin/mysqlbinlog -bfile=/tmp/mysql-bin.000005 --binlog2sql --save-way=table
[2023-03-02 14:23:53] [WARN] [192.168.3.110] Version : v_1.3
[2023-03-02 14:23:53] [INFO] [192.168.3.110] THREAD_1:Analysing --> /tmp/mysql-bin.000005
[2023-03-02 14:24:01] [INFO] [192.168.3.110] THREAD_1:Analysis completed --> /tmp/mysql-bin.000005
[root@ol8mysql ~]#
[root@ol8mysql table]# pwd
/root/analysis_binlog/res/table
[root@ol8mysql table]# cd ..
[root@ol8mysql res]# pwd
/root/analysis_binlog/res
[root@ol8mysql res]# ls -tlr
total 16
-rw-r--r-- 1 root root 2408 Mar 2 13:05 mysql-bin.000004.res
-rw-r--r-- 1 root root 2408 Mar 2 13:05 mysql-bin.000003.res
-rw-r--r-- 1 root root 2408 Mar 2 14:20 mysql-bin.000005.res
drwxr-xr-x 3 root root 4096 Mar 2 14:23 table
[root@ol8mysql res]# cd table
[root@ol8mysql table]# ls -tlr
total 13848
drwxr-xr-x 2 root root 6 Mar 2 13:10 big
-rw-r--r-- 1 root root 1386910 Mar 2 14:24 mysql-bin.000005_mstest.sbtest9.log
-rw-r--r-- 1 root root 1426557 Mar 2 14:24 mysql-bin.000005_mstest.sbtest8.log
-rw-r--r-- 1 root root 1403572 Mar 2 14:24 mysql-bin.000005_mstest.sbtest7.log
-rw-r--r-- 1 root root 1408695 Mar 2 14:24 mysql-bin.000005_mstest.sbtest6.log
-rw-r--r-- 1 root root 1438066 Mar 2 14:24 mysql-bin.000005_mstest.sbtest5.log
-rw-r--r-- 1 root root 1440863 Mar 2 14:24 mysql-bin.000005_mstest.sbtest4.log
-rw-r--r-- 1 root root 1390803 Mar 2 14:24 mysql-bin.000005_mstest.sbtest3.log
-rw-r--r-- 1 root root 1397978 Mar 2 14:24 mysql-bin.000005_mstest.sbtest2.log
-rw-r--r-- 1 root root 1413137 Mar 2 14:24 mysql-bin.000005_mstest.sbtest1.log
-rw-r--r-- 1 root root 1453572 Mar 2 14:24 mysql-bin.000005_mstest.sbtest10.log
[root@ol8mysql table]#
Referece
参考:
Have a good work&life! 2023/02 via LinHong