实验环境:
操作系统:CentOS release 6.8 (Final)
CPU:4 Intel(R) Xeon(R) CPU E5-2680 v3 @ 2.50GHz
2核4线程
内存:8G
磁盘:阿里云普通云盘
实验表:x_link_prod数据库下base_charge表
数据行:3210346
实验工具
MySQL自带数据导入导出命令:
Mysqldump
Load
实验方法
方法1:(9分钟左右,操作时间,不包含检查时间)
- 将base_charge的表结构和数据分别导出,得到base_charge_schema.sql和base_charge_data.sql两个文件。
- 修改表结构文件(base_charge_schema.sql):添加需要增加的字段。
- 分别依次将表结构和数据文件,导入数据库(x_link_prod)
- 检查转换后的base_charge的表结构和数据量,添加完成。
注:表结构和数据是要分开导出,如果放在一个文件,文件很大,修改时,很可能会卡死。
方法2:(6分钟左右,操作时间,不包含检查时间)
- 查看base_charg创建命令,添加需要增加的字段,创建中间表b_c_bak。
- 使用命令:mysqldump将base_charge的表结构和数据分别导出,得到base_charge_schema.sql和base_charge_data.txt两个文件。
- 使用load命令将数据文件.txt导入b_c_bak表中。
- 检查b_c_bak和base_charge数据是否一致。
- 删除base_charge,将b_c_bak重新命名为base_charge。字段添加完成。
实验结果:
方法2比方法1消耗时间更短、更安全些。
导入数据后再创建索引比先创建索引再导入数据快一点。(可能是表中数据量不够大,快的不够明显)
方法的缺点:都需要停应用程序。
详细实验过程
方法一:实验过程
- #表结构导出
[root@app-1 sql]# time mysqldump -uroot -ppassword --no-data --complete-insert x_link_prod base_charge > base_charge_schema.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
real 0m0.041s
user 0m0.006s
sys 0m0.003s
- #表数据导出
[root@app-1 sql]# time mysqldump -uroot -ppassword --no-create-info --complete-insert x_link_prod base_charge > base_charge_data.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
real 0m24.927s
user 0m20.228s
sys 0m3.385s
注:标红出参数要有,否则导入数据时会报错。
- #表结构修改
[root@app-1 sql]# time vim base_charge_schema.sql
#添加内容:
# `longitude` VARCHAR(50) DEFAULT NULL COMMENT '经度',
#`latitude` VARCHAR(50) DEFAULT NULL COMMENT '纬度',
real 0m19.092s
user 0m0.052s
sys 0m0.007s
- #导入表结构
[root@app-1 sql]# time mysql -uroot -ppassword x_link_prod < base_charge_schema.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
real 0m0.410s
user 0m0.009s
sys 0m0.001s
#数据导入(导入前,表已有索引)
[root@app-1 sql]# time mysql -uroot -ppassword x_link_prod < base_charge_data.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
real 7m43.833s
user 0m19.932s
sys 0m1.709s
- 检查表结构和数据量是否一致
##################################################################
total 8m30s 不包含检查的时间,但是还是要检查一下,查看数据是否完整。
##################################################################
#实验:导入数据时,表没有索引的情况下,导入的速度。
#数据导入(此时表只有:主键索引)
[root@app-1 sql]# time mysql -uroot -ppassword x_link_prod < base_charge_data.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
real 4m18.724s
user 0m19.932s
sys 0m1.657s
#创建索引
#(2m51s)
ALTER TABLE base_charge ADD INDEX index_merchant_code (merchant_code),
ADD UNIQUE `merchant_code` (`merchant_code`,`out_trade_no`),
ADD INDEX `index_store_code` (`store_code`),
ADD INDEX `index_out_trade_no` (`out_trade_no`),
ADD INDEX `index_trade_type` (`trade_type`,`product_code`),
ADD INDEX `index_state` (`state`),
ADD INDEX `index_institution_code` (`instition_code`),
ADD INDEX `idx_base_charge_created_at` (`created_at`),
ADD INDEX `idx_oper_id` (`operator_id`,`trade_type`,`created_at`),
ADD INDEX `idx_oper_amount` (`operator_id`,`created_at`,`state`,`total_amount`);
数据导入时间+创建索引时间 比 有索引时导入数据快了30s
方法二:实验过程
- #创建中间表b_c_bak
CREATE TABLE `b_c_bak` (
。。。 。。。
##增加的字段
`longitude` VARCHAR(50) DEFAULT NULL COMMENT '经度',
`latitude` VARCHAR(50) DEFAULT NULL COMMENT '纬度',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=INNODB AUTO_INCREMENT=4140339 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
- #将base_charge的数据和表结构导出
[root@app-1 sql]# time mysqldump -uroot -ppassword x_link_prod base_charge --tab="/var/lib/mysql-files"
mysqldump: [Warning] Using a password on the command line interface can be insecure.
real 0m34.988s
user 0m0.004s
sys 0m0.010s
注:参数--tab的值需要注意。需要查看你的mysql变量secure_file_priv所指的路径,如何—tab所指的路径与secure_file_priv不一致将报错,数据无法导出。(MySQL的安全机制)
命令:SHOW VARIABLES LIKE 'secure_file_priv';
- #数据导入,要进入mysql命令窗。(此时表:b_c_bak没有创建索引)
mysql> load data local infile '/var/lib/mysql-files/base_charge.txt' into table b_c_bak _at`, `updated_at`, `deleted_at`, `subject`, `body`, `remark`, `attach`, `state`, `trade`, `merchant_code`, `store_code`, `operator_id`, `total_amount`, `pay_amount`, `discounttrade_no`, `notify_url`, `product_code`, `instition_code`, `agent_id`, `timeout_express`no`, `channel_trade_no`, `real_merchant_id`, `client_ip`, `ref_id`, `merchant_id`, `stord`, `institution_product_code`, `merchant_fee`, `checking_state`, `trans_code`, `trans_mde`, `rate_amount`, `voice_msg_status`, `pre_rate_amount`, `payer_id`, `source`, `accoun_type`, `qrcode_id`, `merchant_remarks`);
Query OK, 3210346 rows affected (2 min 31.22 sec)
注:#MySQL8.0.11 ERROR 1148 (42000): The used command is not allowed with this MySQL version需要在服务端和客户端设置变量:SET GLOBAL local_infile=ON;
在MySQL的配置文件/etc/my.cnf中增加
[mysqld]
local_infile=ON
[client]
local_infile=ON
#添加索引:
(2 min 17 sec)
ALTER TABLE b_c_bak ADD INDEX index_merchant_code (merchant_code),
ADD UNIQUE `merchant_code` (`merchant_code`,`out_trade_no`),
ADD INDEX `index_store_code` (`store_code`),
ADD INDEX `index_out_trade_no` (`out_trade_no`),
ADD INDEX `index_trade_type` (`trade_type`,`product_code`),
ADD INDEX `index_state` (`state`),
ADD INDEX `index_institution_code` (`instition_code`),
ADD INDEX `idx_base_charge_created_at` (`created_at`),
ADD INDEX `idx_oper_id` (`operator_id`,`trade_type`,`created_at`),
ADD INDEX `idx_oper_amount` (`operator_id`,`created_at`,`state`,`total_amount`);
- 检查表结构和数据量是否一致。
- 删除base_charge,将表b_c_bak重新命名为:base_charge
DROP TABLE base_charge;
ALTER TABLE b_c_bak RENAME TO base_charge;
2s左右
字段添加完成。
##############################################################################
总时间 6m左右 不包含检查的时间,但是还是要检查一下,查看数据是否完整。
##############################################################################
#上边的实验导入数据前没有索引
#下边,实验有索引的情况下,导入数据速度。
#有索引导入
创建表b_c_bak时增加索引。
导入命令与上相同
消耗时间:
(5 min 43.85 sec)
导入数据后创建索引,比导入数据前创建索引快乐1分钟
错误汇总
错误1:
[root@app-1 sql]# time mysqldump -uroot -ppassword test_gravity T2 --tab="/data/sql"
mysqldump: Got error: 1290: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement when executing 'SELECT INTO OUTFILE'
解决方法:将参数--tab 和secure-file-priv所指的路径设置一致即可。
原因:
参数secure-file-priv是用来限制LOAD DATA, SELECT … OUTFILE, and LOAD_FILE()传到哪个指定目录的。
当secure_file_priv的值为null ,表示限制mysqld 不允许导入|导出
当secure_file_priv的值为/var/lib/mysql-files/,表示限制mysqld 的导入|导出只能发生在/var/lib/mysql-files/目录下
当secure_file_priv的值没有具体值时,表示不对mysqld 的导入|导出做限制
错误2:
向数据库Load数据时,报如下错误
ERROR 1148 (42000): The used command is not allowed with this MySQL version
需要客户端打开:local_infile
解决方法:
在MySQL的配置文件/etc/my.cnf中增加
[mysqld] #服务端配置
local_infile=ON
[client] #客户端配置
local_infile=ON
重启mysql服务
原因:
local_infile服务器变量指示能否使用load data local infile命令。
该变量为OFF时,禁用客户端的load data local infile命令
在mysql8.0.11版本,该变量默认为OFF。