Original MySQL database query and LVM backup restore learning Notes Records

One, the query statement type:
1) Simple Query
2) Multi-table query
3) Sub-query

4) Joint Query

1) Simple query:
SELECT * from Tb_name;
SELECT field1,field2 from Tb_name; projection
SELECT [DISTINCT] * from Tb_name WHERE qualification;

FROM clause: Relationship to query
table, multiple tables, other SELECT statement
WHERE clause: boolean relationship expression
And,or,not,between ... And...,like "[%: any character of any length; _: Any single character],rlike,in,is null,is not NULL
ORDER by Field_name {asc| DESC}
As field alias
LIMIT [offset,]count offset offset, count takes several
aggregates:
SUM (), MIN (), MAX (), AVG (), COUNT () GROUP BY group:
have: Filter [qualification]
SELECT AVG (age) from students group by Gender;
Elect COUNT (CID1) as persons,cid1 from students GROUP by CID1;
SELECT COUNT (CID1) as persons,cid1 from students GROUP by CID1 have persons>=2;

The order in which SQL statements are executed:
Start->from->where->group by->having->order by->select fieldn->limit- >end

2) Multi-table query:
Connection:
Cross Connection: Cartesian product
SELECT * from Students,courses;
Natural connection: Inner connection, outer link: Left Outer connection: ... Left JOIN ... On ..., right outer connection: ... Right JOIN ... On ..., self-connected
SELECT * from students,courses where students.cid1 = Courses.cid;
Select Name,cname from students,courses where students.cid1 = Courses.cid;
Select S.name,c.cname from students as s,courses as C where s.cid1 = C.cid;
SELECT S.name,c.cname from students as s left joins courses as C on S.CID1=C.CID1;
SELECT S.name,c.cname from students as s right joins courses as C on S.CID1=C.CID1;

3) Sub-query:
Using subqueries in comparison operations: Subqueries can only return a single value
In (): Using subqueries
Using subqueries in From
Select name from students where > (select AVG (age) from students);
Select name from students where-age-in (select-age from Tutors);
Select Name,age from (select Name,age from students) as T where T.age >= 20;

4) Joint Enquiry:
UNION:
(select Nmae,age from students) UNION (select Tname,age from tutors);

Second, backup and restore
Backup:
SELECT * into OUTFILE '/path/beifen.txt ' from tb_name [WHERE clause];
Recovery:
LOAD DATA INFILE '/path/beifen.txt ' into TABLE tb_name;

LVM snapshot backup and restore:
Premise:
1, the data file should be on the logical volume;
2. The volume group where this logical volume is located must have sufficient space to use the snapshot volume;
3. The data file and transaction log should be on the same logical volume;

Steps:
1. Open session, apply read lock, lock all tables:
Mysql> FLUSH TABLES with READ LOCK;
Mysql> FLUSH LOGS;
2, through another terminal, save the binary log file and related location information:
$mysql-uroot-p-E "SHOW MASTER status\g" >/path/master.info
3. Create a Snapshot Volume:
#lvcreate-l [size]-s-p r-n lv_name/path/source_lv
4. Release Lock:
Mysql> UNLOCK TABLES;
5. Mount the snapshot volume, backup:
#mount Lv_name/mnt-o RO
#cp-A./*/backup/
#rm-F/backup/mysql-bin.*
6. Delete the snapshot volume:
#lvremove--force Lv_name
7, incremental backup binary log [from the location of the Master.info file to start the backup, if there are other scrolling binary files can be backed up separately]:
#mysqlbinlog--start-post=107 mysql-bin.000003 mysql-bin.000004>/backup/incremental.sql
8. Restore Data:
#cp-a/bakcup/*/mysql/data/
#service mysqld Start
Mysql> set sql_log_bin=0;
Mysql> Source/backup/incremental.sql;
Mysql> set sql_log_bin=1;
mysql> SHOW MASTER status\g;

Extended:

There are three ways MySQL creates tables:

1) Create directly manually:

Mysql> CREATE TABLE Tb_name (FEILD1-N);

2) Create a new table based on other tables, the table created in this way does not copy the primary key, constraints, triggers, etc. of the original table and needs to be appended manually.

Mysql> CREATE TABLE Test_new as SELECT * from Test where 1=2; Where 1=2 only the table structure has no data, where 1=1 includes the table structure and the table data are copied together

3) A new table created from the original table, which uses the LIKE keyword to copy the structure of the original table, including the primary key, constraints, triggers, and so on.

Mysql> CREATE table test like tb_name;

Original MySQL database query and LVM backup restore learning Notes Records