Download test library employees from MySQL official website_ Db-full-1.0.6.tar.bz2, two errors occurred during import
Error 1: error 1193 (HY000) at line 38: unknown system variable ‘storage_ engine’
[root@maomao employees_db]# mysql -t < employees.sql ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) [root@maomao employees_db]# mysql -uroot -p123456 -t < employees.sql mysql: [Warning] Using a password on the command line interface can be insecure. +-----------------------------+ | INFO | +-----------------------------+ | CREATING DATABASE STRUCTURE | +-----------------------------+ ERROR 1193 (HY000) at line 38: Unknown system variable 'storage_engine'
View your own database storage engine
(root@localhost:mysql.sock)[(none)]>show variables like '%engine%'; +----------------------------------+--------+ | Variable_name | Value | +----------------------------------+--------+ | default_storage_engine | InnoDB | | default_tmp_storage_engine | InnoDB | | disabled_storage_engines | | | internal_tmp_disk_storage_engine | InnoDB | +----------------------------------+--------+ 4 rows in set (0.00 sec)
Open the script editor and mark the “storage” in red in the following code snippet_ “Engine” to “default”_ storage_ engine”
DROP DATABASE IF EXISTS employees;
CREATE DATABASE IF NOT EXISTS employees;
USE employees;
SELECT 'CREATING DATABASE STRUCTURE' as 'INFO';
DROP TABLE IF EXISTS dept_emp,
dept_manager,
titles,
salaries,
employees,
departments;
set storage_engine = InnoDB;
-- set storage_engine = MyISAM;
-- set storage_engine = Falcon;
-- set storage_engine = PBXT;
-- set storage_engine = Maria;
select CONCAT('storage engine: ', @@storage_engine) as INFO;
Import again, success
[root@maomao employees_db]# mysql -uroot -p123456 -t < employees.sql mysql: [Warning] Using a password on the command line interface can be insecure. +-----------------------------+ | INFO | +-----------------------------+ | 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 | +------------------+
Error 2: after importing successfully, enter the database to view and find that all table data is empty
(root@localhost:mysql.sock)[employees]>show tables; +---------------------+ | Tables_in_employees | +---------------------+ | departments | | dept_emp | | dept_manager | | employees | | salaries | | titles | +---------------------+ 6 rows in set (0.00 sec) (root@localhost:mysql.sock)[employees]>select count(*) from departments; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec) (root@localhost:mysql.sock)[employees]>select count(*) from titles; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec)
Solution: log in to the database employees in the directory where the file is located, manually execute the script employees.sql, and finally mark the statement in red
[root@maomao employees_db]# ls Changelog employees.sql load_employees.dump README employees_partitioned2.sql load_departments.dump load_salaries.dump test_employees_md5.sql employees_partitioned3.sql load_dept_emp.dump load_titles.dump test_employees_sha.sql employees_partitioned.sql load_dept_manager.dump objects.sql cat employees.sql CREATE TABLE salaries ( emp_no INT NOT NULL, salary INT NOT NULL, from_date DATE NOT NULL, to_date DATE NOT NULL, KEY (emp_no), FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE, PRIMARY KEY (emp_no, from_date) ); SELECT 'LOADING departments' as 'INFO'; source load_departments.dump ; SELECT 'LOADING employees' as 'INFO'; source load_employees.dump ; SELECT 'LOADING dept_emp' as 'INFO'; source load_dept_emp.dump ; SELECT 'LOADING dept_manager' as 'INFO'; source load_dept_manager.dump ; SELECT 'LOADING titles' as 'INFO'; source load_titles.dump ; SELECT 'LOADING salaries' as 'INFO'; source load_salaries.dump ;
The specific operation is as follows
[root@maomao employees_db]# mysql -uroot -p123456 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 425 Server version: 5.7.22-log MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. 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. (root@localhost:mysql.sock)[(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 (root@localhost:mysql.sock)[employees]>SELECT 'LOADING departments' as 'INFO'; (root@localhost:mysql.sock)[employees]>source load_departments.dump ; (root@localhost:mysql.sock)[employees]>SELECT 'LOADING employees' as 'INFO'; ......
After the execution, it is found that the data has been imported successfully
(root@localhost:mysql.sock)[employees]>select count(*) from employees; +----------+ | count(*) | +----------+ | 300024 | +----------+ 1 row in set (0.06 sec)