sequoiadb四颗牙P 第四节 参考

1) 切换到 sdbadmin 用户；


2）创建 MySQL 实例 myinst 并创建 company 数据库以及 employee 数据表( id INT, name VARCHAR(128), age INT )；
cd /opt/sequoiasql/mysql
/opt/sequoiasql/mysql/bin/sdb_sql_ctl addinst myinst -D database/3306/
mysql -h127.0.0.1 -uroot
create database company;
use company;
CREATE TABLE employee 
(
id INT , 
name VARCHAR(128), 
age INT
);


3）在 employee 数据表创建普通索引 name_Index，字段为 name ；
create index name_Index on employee(name);


4）查看 SequoiaDB 是否有 name_Index 这个索引；
show index from employee;


5）在 employee 插入信息（id: 10001, name: 'Georgi', age: 48）；
INSERT INTO employee (id,name, age) VALUES (10001,"Georgi", 48); 


6）开始 MySQL 事务,插入数据(id: 10002, name: 'Bezalel', age: 21),然后查询 employee 表信息,最后提交事务;
begin;
INSERT INTO employee (id,name, age) VALUES (10002,"Bezalel", 21); 
select * from employee;
commit;


7）开始 MySQL 事务,插入数据(id: 10003, name: 'lazhu', age: 22),然后查询 employee 表信息,不提交然后回滚事务,在进行表信息查询；
begin;
INSERT INTO employee (id,name, age) VALUES (10003,"lazhu", 22); 
select * from employee;
rollback;
select * from employee;


8）修改实例下的配置文件（database/3306/auto.cnf），加入导入导出路径参数配置（secure_file_priv）；
cat >> /opt/sequoiasql/mysql/database/3306/auto.cnf << EOF
secure_file_priv = "/opt/sequoiasql/tmp"
EOF
mkdir /opt/sequoiasql/mysql/tmp
bin/sdb_sql_ctl restart myinst
9）导出 employee 表数据至 /opt/sequoiasql/tmp/employee_export.csv下；
use company;
SELECT * FROM employee   
INTO OUTFILE '/opt/sequoiasql/tmp/employee_export.csv'   
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY '\r\n';


10）新建 employee_import_test 表( id INT, name VARCHAR(128), age INT )并将刚刚导出的数据进行导入到 employee_import_test 表；
CREATE TABLE employee_import_test 
(
id INT , 
name VARCHAR(128), 
age INT
);


LOAD DATA INFILE '/opt/sequoiasql/mysql/tmp/employee_export.csv'
INTO TABLE employee_import_test
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY '\r\n'; 


11）对 employee 表进行备份,备份路径为 /opt/sequoiasql/tmp/employee.sql；
/opt/sequoiasql/mysql/bin/mysqldump -h 127.0.0.1 -u root   company employee > /opt/sequoiasql/tmp/employee.sql


12）清空 employee 表数据并执行备份恢复操作；


truncate table employee;
SOURCE /opt/sequoiasql/mysql/tmp/employee_import_test.sql;