Shulou(Shulou.com)06/01 Report--

The main contents that you need to know about the operation database at the application level are as follows:

Install, start and connect to the database, modify the management password of the database, backup and restore of the database, full backup, incremental backup, physical backup, logical backup of the database with high availability: optimization of cluster database: optimization of database operating system hardware layer, optimization of database software configuration level, optimization of sql statement level, optimization of data index level.

-

Install the database:

You can download the compiled installation package on the official website, environment centos

Yum installation: mysql-community

Download the npm package first

Install the npm package: rpm-Uvh mysql57-community-release-el7-9.noarch.rpm

Install mysql:yum install mysql-community-server

Start mysql:systemctl start mysqld

Change the root user password after initial installation

After starting the service, the root user will create a temporary password to store in / var/log/mysqld.log, which can be accessed through the

Grep 'temporary password' / var/log/mysqld.log to view

Then log in using mysql-uroot-p

If the temporary password has a special symbol when logging in, you can enclose 'myPassword'' in single quotation marks.

And then

ALTER USER 'root'@'localhost' IDENTIFIED BY' MyNewPass4passport'

Refresh permission: flush privileges

-

Authorize a user to access the database

Grant all privileges on database. Table to 'username' @ 'ip' identified by' password'

Grant all privileges on. To 'root'@'%' identified by' password'/ /% percent sign indicates any address

-

Backup and restore

Use mysqldump to back up:

Backup using sql format: mysqldump-- all-databases > my.sql

Mysqldump-- databases db1 db2 > my.sql

Reload data through a sql file: mysql

< my.sql 或者mysql>

Source my.sql

Back up data using delimiters:

Mysqldump-tab=/tmp db1 saves two files in two formats, one txt and one sql

Parameter:-- fields-terminated-by=str,--fields-enclosed-by=char,--fields-optionally-enclosed-by=char,--fields-escaped-by=char,--lines-terminated-by=str

Restore backup files in the form of delimiters:

Mysql db1use db1

Mysql > load data infile 't1.txt' into table T1

How to copy a database

Mysqldump db1 > dump.sql

Mysql db2

< dump.sql 不要再mysqldump中使用--database，这会导致use db1的语句存在dump文件中，从而覆盖db2的数据库名。 如何复制一个服务器的数据库到另外一个服务器： mysqldump --databases db1 >

Dump.sql

On server 2: mysql > dump.sql

Use-- databases because it contains create database statements

How to back up stored procedures (stored procedures, functions, triggers, events)

Plus the parameter-- events-- routines (stored procedures and functions)-- triggers

How to store definitions and data separations:

-- no-data does not copy table data,-- no-create-info:

Upgrade compatibility Test:

Mysqldump-all-databases-no-data-routines-events > dump-defs.sql

Mysql in the upgraded server

< dump-defs.sql 使用二进制日志进行时间点恢复（增量恢复）： 查看二进制日志列表：show binary logs 查看二进制日志的名字：show master status 恢复二进制日志：mysqlbinlog mylog | mysql-uroot -p 查看二进制日志信息：mysqlbinlog mylog | more 如果要处理多个二进制日志，每个文件单独一条语句时一种危险的做法，应该在一条语句中执行 mysqlbinlog log1 log2 log3 | mysql -uroot -p 或者使用追加的方式： mysqlbinlog log1 >

My.sql

Mysqlbinlog log2 > > my.sql

Then use: mysql-uroot-p-e "source my.sql"

You can use-- skip-gtids to ignore global transactions

Recovery point in time:

Use-- start-datetime and-- stop-datetime to specify the recovery time:

Mysqlbinlog-- stop-datetime= "2017-04-20 9:59:59" mylog | mysql-uroot-p

Mysqlbinlog-- start-datetime= "2017-04-20 9:59:59" mylog | mysql-uroot-p

Use-- start-position and-- stop-position to specify the location of the restore:

Mysqlbinlog-- stop-position=154 mylog | mysql-uroot-p

Mysqlbinlog-- start-position=209 mylog | mysql-uroot-p

Master-slave configuration

The principle is to use binary logs, one master and two slaves. Before saving the data, the master server will write a binlog record, which contains records of data operations. This record will be synchronized to the slave server, and then the slave server will start the SQL thread to synchronize these records into the database.

Operation procedure:

1: configure the my.cnf files of the master and slave servers respectively, and add server-id=1,server-id=2,server-id=3....

Add log-bin=mylog to the primary node and open the binary file

2: add users for replication on the primary node

Grant replication slave on. To 'repl'@'%' identified by' myPassword'

View the status of the primary node: show master status; now displays the log file name and position

3: open port 3306 to each server:

Firewall-cmd-zone=public-add-port=3306/tcp-permanent

Firewall-cmd-reload

4: enter on each slave node

Change master to

Master_host=' master node ip', / / comma should have

Master_user='repl'

Master_password='myPassword'

Master_log_file='mylog.000001', / / the file name here is the file name viewed from the primary node.

Master_log_pos=154; / / the location here is seen from the primary node.

Use start slave; to launch after success

Then view the slave node information: show slave status\ G, if

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

If both are yes, it is normal, if not, there may be incorrect configuration, check the port, log_pos location and so on.

Specific configuration my.cnf of master-slave replication

Server-id=1

Location of log-bin=/var/log/mysql/mysql-bin / / binary log

Binlog-do-db=test / / Libraries requiring synchronization

Binlog-ignore-db=mysql / / libraries that do not log, that is, libraries that do not need to be synchronized

Log-slave-updates / / enable log function on slave node

Sync_binlog=1 / / write the log file to the hard disk once after a log write operation, which is the safest but the least efficient. The default is 0.

# auto_increment / / controls the behavior of self-adding columns

Optimize

Single table optimization:

Field:

1: try to use tinyint,smallint,medium_int as an integer type instead of INT, non-negative plus UNSIGNED

2:varchar length allocates only the space needed

3: use enumerations or integers instead of string types

4: use timestamp instead of datetime

5: it is recommended that the field of a single table be less than 20

6: use integers to save ip

Index:

1: consider indexing on columns of where and order by

2: fields with sparse value distribution are not suitable for indexing

3: do not use character fields as primary keys

4: without foreign keys, the constraint is guaranteed by the program

5: without unique, the constraint is guaranteed by the program.

Ways to optimize the execution efficiency of sql statements:

1: try to select smaller columns

2: index the fields that are frequently used in where

Avoid using * in the 3:select clause

4: avoid using computations, not, in, and operations on index columns

5: use limit 1 when only one row of data is needed

6: ensure that the data of the form does not exceed 200W and separate the table at the right time

Without column operation, any operation on the column will result in a table scan, and the operation will be moved to the right of the equal sign when querying. The sql statement is as simple as possible, a sql can only be run on one cpu, and the large statement breaks down the small statement. The efficiency of changing or to in,or is n, the efficiency of in is the level of log (n), and the number of in is suggested to be less than 200.

-

Row subquery: multiple columns can be used as criteria

Select * from T1 where (col1,col2) in (select col3,col4 from T2)

Exist: if the subquery returns, the data will be returned. If the subquery has no content, it will not be returned.

Optimize subquery:

1: to use a subquery statement that can affect the number or order of subquery rows is to let the subquery statement filter multipoint data

2: replace join connections with subqueries

3: move external statements into internal subqueries

SELECT FROM t1

WHERE S1 IN (SELECT S1 FROM T1 UNION ALL SELECT S1 FROM T2)

Replace:

SELECT FROM t1

WHERE S1 IN (SELECT S1 FROM T1) OR S1 IN (SELECT S1 FROM T2)

Another example is:

SELECT (SELECT column1 + 5 FROM T1) FROM T2

Replace

SELECT (SELECT column1 FROM T1) + 5 FROM T2

4: replace the related subquery with a row query:

SELECT FROM t1

WHERE (column1,column2) IN (SELECT column1,column2 FROM T2)

Replace:

SELECT FROM t1

WHERE EXISTS (SELECT * FROM T2 WHERE t2.column1=t1.column1

AND t2.column2=t1.column2)

5: using not (a = any ()) is better than an all ()

6: it is better to use x = any (table containing (1 or 2)) than to use x = 2

7: using = any is better than exists

8: if the record returns only one row, using = is better than in

Explain query Analyzer

The content of explain:

Id select_type table type possible_keys key key_len ref rows Extra

Id: execute the number, if there is no nesting or subquery, only a unique select, then each line displays 1

Select_type: displays the query type of this line:

Simple: simple subqueries, excluding subqueries and union

Primary: contains union or subquery

Subquery: subquery

Derived: derived tabl

Union:

Union result: anonymous temporary table

Dependent union:

Subquery:

Dependent subquery:

Table: the table name or alias of the disaster relief visit of the corresponding bank

Type: access type:

All: full table scan

Index: scan the table by index instead of line by line

Range: range scan, and the key column shows which index is used. Use =, >, "=,"

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.