Learning notes for various MySQL videos

Open binary log (binary log content: Change the operation of the database)

In the/ETC/MYSQL/MY.CNF

Remove the comment from the Log_bin line to open the following is the saved path

The following expire_logs_days is how long, MySQL automatically deletes the binary log

Max_binlog_size is the largest binary log size

Note that the changes just after restarting MySQL will take effect.

Shell>sudo service MySQL Stop

Shell>sudo service MySQL Start

Then log in to MySQL

A binary log is generated after each login

My binary log is specifically

mysql-bin.000001 (MySQL here for host name)

Can be used in MySQL: Show BINARY logs to view

See what you need in the shell: for example

mysqlbinlog/var/log/mysql/mysql-bin.0000002

To view

Delete all log files (a new binary log file is also generated):

Mysql>reset MASTER;

To delete the specified log file:

Mysql>purge MASTER LOGS to ' mysql-bin.0000002 ';

To is to the meaning so above that sentence for delete mysql-bin.0000001

can also be partially deleted based on time

: Mysql>purge MASTER LOGS before ' 20150531 ';

Recovering data from binary logs:

Shell>mysqlbinlog--stopdatetime= "2015-05-31 10:18:15"/var/log/mysql/mysql-bin.000002|mysql-u root-p

Where time is the time in the binary log

Pause binary log:

Mysql>set Sql_log_bin=off;

To restore a binary log:

Mysql>set Sql_log_bin=on;

"Effect of flush logs: More than one new log file will be generated"

So the instructions above will be used when the binary log is opened.

Turn on the error log (suffix. err)

Also in My.cnf, let Log_error's comments be canceled.

To view the path where the error log is located

Mysql>show variables like ' log_error '

To delete the error log:

Shell>mysqladmin-u root-p Flush-logs

Or:

Mysql>flush logs

To start the Universal query log (common query log: User-to-database steps):

In the same vein [mysqld]

Riga: Log

Delete/actually re-create:

Mysqladmin-u root-p Flush-logs

Start slow query log (slow query log content: Queries that execute longer than long_query_time or queries that do not use an index)

Start set slow query log: The same as the previous

Add in MY.CNF: log-slow-queries (use its default data path without specifying a path later)

Regenerate slow query log files

Shell>mysqladmin-u root-p Flush-logs

Mysql>flush logs

"Slow query log is related to performance optimization"

Parsing query statements

1) explain [extended] Select Select_options

2) describe (can be abbreviated as DESC) Select Select_options

View information for a table [show create table name]

When is the index useless?

1) Use the like query and% at the beginning

2) Multiple-column indexes are queried directly with the second field, and the index does not work

Example: Create index index_id_price on fruits (f_id,f_price);

Explain select * from fruits where f_price=5.2;

The index in this SELECT statement does not work.

3) When a query statement that uses the OR keyword is not before the index column

To refine a subquery:

Sub-query is not efficient, use JOIN query + Index to replace sub-query

Optimizing the database structure

1) Break up tables with many fields into multiple tables: separate frequently used fields into new tables

2) Increase the intermediate table: for tables that often require joint queries

3) Increase the redundancy field reasonably:

4) Optimize the speed of Insert record: BULK INSERT, use load Data infile Bulk Import, disable index on insert (the value of set parameter is OK), disable uniqueness check before inserting

Parse table: Analyze table name, read-only lock added during parsing

Checklist: Check table table name, read-only lock added during check

Tuning table: Optimize table name, optimizing fragmentation, adding read-only locks during optimization

Optimize the server:

Optimization parameters: See show variables for explanations, remember to restart the MySQL service after configuration

Available? + Command to view commands

MySQL built-in string functions

Concat () connection

LCase () Turns lowercase

UCase () turn into uppercase

Replace (STR,SEARCH_STR,REPLACE_STR) replacement

SUBSTRING (str,position,length) Take substring

Space (count) generates spaces

LTrim () go to the left space

Repeat (string,count) repeat Count times

MySQL built-in math functions

BIN () decimal goto binary

Ceiling () rounding up

Floor () Rounding down

Max ()

Min ()

sqrt () Open square

Random values in rand () 0-1

MySQL built-in date function

Curdate ()

Curtime ()

Time

Week (date) Return date is the first week

Year (date)

DateDiff (EXP1,EXP2) Two days difference between dates

MySQL Preprocessing statement example (for optimization)

mysql> prepare STMT1 from ' select * from t where id>? ';

Mysql> set @i=1;

Mysql> execute STMT1 using @i;

mysql> drop prepare STMT1;

Example of MySQL transaction processing (InnoDB exclusive, for optimization)

Mysql> set autocommit=0;

Mysql> Delete from t where id=11;

Mysql> savepoint p1;

Mysql> Delete from t where id=10;

Mysql> savepoint p2;

mysql> rollback to P1; (the restore point behind the P2 is automatically invalidated)

{

mysql> rollback; Roll back to the most original restore point

Mysql> commit; commit.

}

Reflow auto_increment

Restore Auto_increment to 1

1) TRUNCATE table name;

2) ALTER TABLE name Auto_increment=1;

MySQL Optimization tips

1) Regular expression (returns 1 if matched to 0)

For example:

Mysql> Select "Linux is very good!" regexp "^very";

will return 0

Mysql> select name, email from t where email REGEXP "@163[.,]com$";

But the regular expression consumes resources

2) Extract random rows with rand ()

Example: Random fetch of three strips

Mysql> SELECT * FROM Stu Order by rand () limit 3;

3) The WITH rollup in the group by sentence can retrieve more packet aggregation information, but it cannot drink order by simultaneously

In fact, the benefits are not very obvious, personally think

4) GROUP BY statement Riga Bit_and (bit and), bit_or (bit or) function

For example:

Mysql> Select ID, bit_and (kind) from the T Group by ID;

5) use of MySQL Help

? % can be obtained so the command in mysql>

? reg%

? Contents view so Help information

General steps for optimizing SQL statements

1) Look at the slow query log or

Mysql> Show [Session|global] status;

Session (default) indicates the current connection

Global indicates that the database has been started since

2) Locating SQL statements that perform less efficiently

Look at the slow query log or

Explain or DESC

View the usage of the index: Show status like ' handler_read% ';

If the index is working, the value of Handler_read_key is high, which represents the number of times a row is read by the indexed value

A high value of handler_read_rnd_next means that queries run inefficiently, and Index remediation should be established.

Load infile and outfile are more efficient than mysqldump importing/exporting data

For MyISAM tables, it is faster to turn off non-unique index imports before importing

ALTER TABLE t1 disable keys;

Remember to open when you are finished importing

ALTER TABLE T1 enable keys;

It is best not to turn off uniqueness checks

{The syntax to turn off uniqueness checks

Set unique_checks=0;

Syntax for opening a uniqueness check

Set Unique_checks=1;

}

Optimization for InnoDB tables

1) data is saved in ascending order by primary key then import is fast.

2) closing the transaction mechanism

Do not use optimize table during peak site access, which consumes a lot of CPU

Optimize INSERT statement: Insert multiple values at one time

Optimize the GROUP BY statement: followed by the ORDER by null

Use fewer nested queries because the outer query cannot be indexed

Use enum to fix fields, Find Fast

Set Character Set

Set in MY.CNF.

View Character set: show character set;

Do not log in to MySQL (this experiment I did not succeed)

First turn off the MySQL service

Mysqld_safe--skip-grant-table--user=mysql&

Mysql-uroot

You can log in.

How to log in to MySQL without a socket (experiment not done)

With Mysql-u root-p test--protocol tcp-hlocalhost

can also log on successfully

When backing up data, add-l (read lock to ensure that the data is a full snapshot)

MySQL Master-slave Replication (no experiment)

1) Log in to MySQL database first

2) to set up authorized users from the server

For example:

Mysql> grant all slave on * * to [e-mail protected] identified by "Pass";

Modify the parameters of the master server

The Server-id of the master and slave servers cannot be identical

Get a snapshot of data consistency on the primary server

Mysql> flush tables with read lock;

Remember to unlock after backup

mysql> unlock tables;

Master-Slave error Resolution (no experiment)

Unable to sync from database (show slave status)

Workaround

: Mysql>slave stop;

Mysql> set global_slave_skip_counter=1;----2

mysql> slave start;

or perform a manual synchronization from the server (change the second step above)

Mysql> Change Master to

\

\

\ config and (show master status parameters are the same)

MySQL Partition

Range partition

Example code:

PARTITION by RANGE (store_id) (

PARTITION p0 valuesless THAN (6),

PARTITION P1 valuesless THAN (11),

PARTITION P2 valuesless THAN (16),

PARTITION P3 valuesless THAN (21),

);

List partition

For example:

PARTITION by LIST (store_id)

(

PARTITION Pnorth VALUES in (3,5,6,7),

PARTITION peast VALUES in (1,2,4,8),

);

Hash partition (random split data)

For example:

PARTITION by HASH (year (hired))

Partitions 4;

Key partition

Partition

For example

CREATE TABLE t2 (id int) Engine=myisam partition by hash (ID) partitions 4;

You can use show create TABLE t2 to view information for a table

InnoDB Table Partitioning: First set to exclusive table space

To add a sentence in the configuration:

Innodb_file_per_table=1

Then do partition syntax and MyISAM the same, do partition when creating table

Then insert the data.

Learning notes for various MySQL videos