MySQL数据库压力测试

/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --user=mysql --initialize-insecure 初始化数据库
/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql & 启动数据库
/usr/local/mysql/bin/ /mysql/my3306/mysql.sock -p 登录数据库

alter user ‘root’@‘localhost’ identified by ‘lndx_123’;
flush privileges; MySQL按照提示修改密码

/usr/local//mysql/bin/mysql -u test -h 135.129.12.49 -P3306 -p123 用户登录mysql
create user ‘zhaopeng’@’%’ identified by ‘123’; 创建用户
grant all privileges on . to ‘zhaopeng’@’%’ with grant option; 赋予全部权限
flush privileges; 提交

create database sbtest; 创建sbtest数据库

压力测试，测试mysql服务器性能并得到一张性能曲线图:
sysbench /usr/local/share/sysbench/oltp_read_write.lua --mysql_storage_engine=innodb --table-size=100 --tables=20 --mysql-db=sbtest --mysql-user=root --mysql-password=lndx_123 --mysql-port=3306 --mysql-host=127.0.0.1 --threads=4 --time=60 --report-interval=2 --db-ps-mode=disable --events=0 --db-driver=mysql prepare
sysbench /usr/local/share/sysbench/oltp_read_write.lua --mysql_storage_engine=innodb --table-size=100 --tables=20 --mysql-db=sbtest --mysql-user=root --mysql-password=lndx_123 --mysql-port=3306 --mysql-host=127.0.0.1 --threads=4 --time=60 --report-interval=2 --db-ps-mode=disable --events=0 --db-driver=mysql run

select user,host from mysql.user;查看创建用户

show processlist; 查看数据库进程

select * from information_schema.innodb_trx\G

/usr/local/mysql/bin/mysql -u root -S /mysql/my3306//mysql.sock -p123 < world.sql 导入数据库
show databases; 查看导入数据库

usr employees;
alter table employees add index idx_hire(emp_no,hire_date); 给表插入索引
show create table employees; 查看表插入的索引

插入任意一条数据进入表employees，并且查询出来
insert into employees values(135199,‘2021-01-02’,‘zhaopeng’,‘Facello’,‘M’,‘2021-04-07’);
select * from employees where emp_no like ‘135%’;

/usr/local/sysbench-master/src/sysbench /usr/share/sysbench/oltp_read_write.lua --mysql_storage_engine=innodb --table-size=100 --tables=20 --mysql-db=sbtest --mysql-user=root --mysql-password=lndx_123 --mysql-port=3306 --mysql-host=127.0.0.1 --threads=64 --time=60 --report-interval=2 --db-ps-mode=disable --events=0 --db-driver=mysql run