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

Backup

During the operation of the MySQL service instance, there was an unexpected power outage, hard disk damage, misoperation, server downtime, and so on. At this time, how to ensure that the database can be restored to the 'correct' state as much as possible?

For database managers, the easiest way to prevent data loss is to back up the original data regularly and create a copy of the data. However, the data is inconsistent with expectations, and then the data is restored using the backed-up data. For MySQL, there are three common ways to create a copy of data

The method of creating a copy of data

1. Data backup:

From the previous blog of MySQL Log system, we know that all the data in the database is in the form of files and stored on the hard disk, so we can directly back up all the files under the data directory of MySQL. Therefore, when opening the MySQL service, it is necessary to design the storage location of various data files and log files, so as to facilitate quick backup. In addition, when backing up, it is best to back up to other machines.

2. Use binary log:

The [data backup method] above belongs to physical backup, which is coarse-grained and cannot achieve more fine-grained data recovery, especially for systems with frequent updates. The binary log records all the update operations of the database. When the data is lost, the binary log can be redone through a full backup, which can complete the recovery based on the point-in-time or operation point, and then achieve a more fine-grained recovery of the database.

3. Database replication:

Database replication actually prevents data loss through binary logs, and data replication can achieve remote backup and recovery of the database.

Logical backup and physical backup

According to whether the copy files produced after backup can be edited, the backup method of MySQL can be divided into logical backup and physical backup.

1. Logical backup:

With logical backups, database administrators can usually view and edit the contents of the copy file directly. There are usually two situations in which copies are produced in a logical backup. Case 1: the copy is the SQL file, which contains crete table and a large number of inert statements in the SQL file. In case 2, the copy is a file with a specified delimiter, which can be imported by cutting the data with the specified delimiter when importing the database.

2. Physical backup:

Copies of data produced by physical backups are binary files and are often not editable, such as the binary log of a database.

Cold backup, warm backup, hot backup

During data backup, according to whether the MySQL service instance needs to be stopped or not, the data recovery of MySQL can be divided into cold backup, warm backup and hot backup.

1. Cold backup:

Cold backup refers to backing up data after stopping the operation of the MySQL service. This backup method is very simple, but it is not allowed in systems with busy services.

2. Warm backup: warm backup is between hot backup and cold backup. Warm backup allows the MySQL service instance to continue to run. During the backup period, warm backup uses the read lock mechanism to ensure that no new data is written during the backup.

Full backup, incremental backup

According to the degree of missing copy files, data backup can be divided into full backup and incremental backup.

1. Full backup:

A full backup is a complete backup of data that can restore the database to a correct state by relying solely on the copy file. "without the hot spare tool, a full backup may need to stop the MySQL service." At this point, MySQL will not be able to provide services, and full backups are rarely used in real business scenarios.

2. Incremental backup:

Incremental backup refers to the backup of updated data on the basis of full backup, which needs to be restored with the help of copy files produced by full backup. At present, MySQL does not provide a real incremental backup tool. Database managers can use hot backup tools to simulate incremental backups, or they can simulate incremental backups by re-executing update statements in binary logs.

3. Hot backup:

Hot backup is a method that does not need to stop the MySQL service instance from running and back up data. If the update operation of the database is more frequent, the backed up data may have changed during the data backup, so the implementation of hot backup is more complex.

First, backup common operation basic command.

1. Backup command mysqldump format

Format: mysqldump-h hostname-P port-u user name-p password-database database name > file name. Sql

First create a database and table

Mysql > create database mood;Query OK, 1 row affected (0.00 sec) mysql > use moodDatabase changedmysql > create table info (id int not null primary key auto_increment,name char (10) not null); Query OK, 0 rows affected (0.03 sec) mysql > insert into info-> (name) values ('zhangsan'), (' lisi'); Query OK, 2 rows affected (0.03 sec) Records: 2 Duplicates: 0 Warnings: 0mysql > select * from info +-+ | id | name | +-+-+ | 1 | zhangsan | | 2 | lisi | +-+-- + 2 rows in set (0.00 sec)

Use the tar command to back up

[root@localhost opt] # tar jvcf / opt/mysql-.tar.xz / usr/local/mysql/data/ [root@localhost opt] # lsallback.sql back1.sql mood.sql mysql-2018-09-02.tar.xz mysql-5.7.17 rh

Back up a single MySQL database to opt. (the warning that appears later is because I wrote the password after-P, and if I don't write it, I interact with it.)

[root@localhost] # mysqldump-uroot-pabc123 mood > / opt/mood.sqlmysqldump: [Warning] Using a password on the command line interface can be insecure.

Back up all databases on the server to opt.

[root@localhost] # mysqldump-uroot-pabc123-- all-databases > / opt/allback.sql mysqldump: [Warning] Using a password on the command line interface can be insecure.

Multiple databases are backed up at the same time (both mood and native mysql databases are backed up)

[root@localhost opt] # mysqldump-uroot-pabc123-databases mood mysql > / opt/mood-mysql.sqlmysqldump: [Warning] Using a password on the command line interface can be insecure.

Back up tables in the database

[root@localhost opt] # mysqldump-uroot-pabc123 mood info > / opt/info.sqlmysqldump: [Warning] Using a password on the command line interface can be insecure.

Backup table structure

[root@localhost opt] # mysqldump-uroot-pabc123-d mood info > / opt/info1.sqlmysqldump: [Warning] Using a password on the command line interface can be insecure.

Two ways to recover table data

1 execute in the database

Mysql > use moodDatabase changedmysql > drop table info;Query OK, 0 rows affected (0.01 sec) mysql > show tables;Empty set (0.01 sec) mysql > source / opt/info.sql;Query OK, 0 rows affected (0.00 sec) mysql > show tables;+-+ | Tables_in_mood | +-+ | info | +-+ 1 row in set (0.00 sec)

2 rewind the table data directly

[root@localhost opt] # mysql-uroot-pabc123 mood

< /opt/info.sql mysql: [Warning] Using a password on the command line interface can be insecure. 不进入数据库使用-e直接查看数据表 [root@localhost opt]# mysql -uroot -pabc123 -e 'use mood;show tables;select *from info;'mysql: [Warning] Using a password on the command line interface can be insecure.+----------------+| Tables_in_mood |+----------------+| info |+----------------++----+----------+| id | name |+----+----------+| 1 | zhangsan || 2 | lisi |+----+----------+ MySQL 索引 MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。 打个比方，如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。 索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。 创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。 实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。 上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。 建立索引会占用磁盘空间的索引文件。 普通索引创建索引 这是最基本的索引，它没有任何限制。它有以下几种创建方式： CREATE INDEX indexName ON mytable(username(length)); 如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。 修改表结构(添加索引)ALTER table tableName ADD INDEX indexName(columnName)创建表的时候直接指定CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX [indexName] (username(length)) );删除索引的语法DROP INDEX [indexName] ON mytable;唯一索引 它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式： 创建索引CREATE UNIQUE INDEX indexName ON mytable(username(length))修改表结构ALTER table mytable ADD UNIQUE [indexName] (username(length))创建表的时候直接指定CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, UNIQUE [indexName] (username(length)) );使用ALTER 命令添加和删除索引 有四种方式来添加数据表的索引： ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。 ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。 ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引，索引值可出现多次。 ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ，用于全文索引。 以下实例为在表中添加索引。 mysql>

ALTER TABLE testalter_tbl ADD INDEX (c)

You can also use the DROP clause in the ALTER command to delete the index. Try the following example to delete the index:

Mysql > ALTER TABLE testalter_tbl DROP INDEX c; uses the ALTER command to add and remove primary keys

A primary key can only work on one column. When adding a primary key index, you need to make sure that the primary key is not NOT NULL by default. Examples are as follows:

Mysql > ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;mysql > ALTER TABLE testalter_tbl ADD PRIMARY KEY (I)

You can also use the ALTER command to delete the primary key:

Mysql > ALTER TABLE testalter_tbl DROP PRIMARY KEY

You only need to specify PRIMARY KEY when deleting the primary key, but you must know the index name when deleting the index.

Display index information

You can use the SHOW INDEX command to list the relevant index information in the table. You can format the output information by adding\ G.

Try the following example:

Mysql > SHOW INDEX FROM table_name;\ G.

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.