Mysql5.7 yum安装
下载rpm包
wget http://repo.mysql.com/mysql57-community-release-el7-8.noarch.rpm
安装rpm包
rpm -ivh mysql57-community-release-el7-8.noarch.rpm
yum安装
yum install mysql-server
service mysql start
grep "password" /var/log/mysqld.log
alter user 'root'@'localhost' identified by '***';
update mysql.user set host='%' where host='localhost';
grant all on *.* to root@'%' identified by '***';
flush privileges;
批量导入数据
load data infile '/data/import200.txt' into table password fields terminated by ':' lines terminated by '\n' (password, sha);
跳过指定行数,场景:批量导入报错,再次导入跳过之前入库的数据
load data infile '/data/import200.txt' into table password fields terminated by ':' lines terminated by '\n' ignore 100 lines (password, sha);
忽略错误
load data infile '/data/import200.txt' ignore into table password fields terminated by ':' lines terminated by '\n' (password, sha);
加快批量导入数据的速度
导入前ALTER TABLE...DISABLE KEYS
导入后ALTER TABLE...ENABLE KEYS
创建索引
CREATE INDEX part_of_sha1 ON password (sha1(10));
修改innodb配置,提高导入数据性能
innodb_flush_log_at_trx_commit=0或2
innodb_autoextend_increment=128
innodb_log_buffer_size=16
innodb_log_file_size=128
查看数据大小
select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from TABLES where table_schema='alltext' and table_name='password';
SELECT TABLE_NAME,DATA_LENGTH+INDEX_LENGTH,TABLE_ROWS,concat(round((DATA_LENGTH+INDEX_LENGTH)/1024/1024,2), 'MB') as data FROM TABLES WHERE TABLE_SCHEMA='alltext';
在一个耗时的查询语句中增加超时设置
select /*+ max_execution_time(1000)*/ * from tablea where email like "%dd_ii.com"
SET STATEMENT max_execution_time=1000 for SELECT * FROM `tablea` WHERE `email` LIKE '%@bb-ii.com' ORDER BY `id` LIMIT 20