Shulou(Shulou.com)05/31 Report--

This article mainly explains "transaction processing and data backup and recovery methods in MySQL". The explanation in this article is simple and clear and easy to learn and understand. Please follow the editor's train of thought to study and learn "transaction processing and data backup and recovery methods in MySQL".

Table of contents:

1. Transaction

2. Backup and restore

1. Transaction:

Transfer: an account to 100-"B account

Sql:

1. An account-100

2. B account + 100

If both 1 and 2 are completed, then we say that the transfer process is complete.

Once the two steps 1 and 2, one step is not completed, then we say turn to failure!

What shall I do?

Roll back (undo) the part that has been completed to eliminate the impact.

Is there such a feature in mysql?

The answer is yes! Use business!

Transaction: a collection of sql, either all executed successfully or none executed (the sql that has already been executed is to be rolled back), returning the state to before the sql execution.

Syntax:

Start a transaction:

Start transaction

Temporarily store the execution result of sql.

Submit:

Commit

If all sql are executed successfully, commit. Persist the execution result of sql to the database

Roll back:

Rollback

If there is a sql that failed to execute, you need to roll back. Returns the status to before the transaction starts.

Persistence: writes data to the database.

Whether commit or rollback, the transaction is closed. (you need to use the transaction and open it again)

Transactions, before commit, are not persisted to data. Therefore, no data changes can be seen in other connections. Transaction that takes effect only for the current link.

However, data updates are not seen in other connections.

After rollback, the impact of the previous sql has been eliminated.

When a transaction is not used before, a single sql will be persisted to the database as soon as it is executed.

Why:

Come to think of it, after the transaction is opened, we can persist it to the database after we commit it. That is, commit is automatic when no transaction is used.

Can automatic submission be turned off?

Yes!

We just need to set the autocommit variable to 0 (turn off autocommit).

Set autocommit = 0

At this time, you don't have to open the transaction, and you have to commit it manually every time in order to persist to the database.

Note: after commit, it cannot be closed automatically.

Remember to ask set autocommit=1 to turn on autocommit.

Note: transactions, like foreign keys, can only be used when the storage engine is innodb.

2. Backup and restore of data

1. Simple data backup (single table backup)

Through the select statement, the query data is written to a file and stored.

Format:

Select field list into outfile "file path" from table name

Such as:

SQL > select * into outfile'DVERGUR WAMP * WAMP'Universe WAMP 1510 Universe 0113 Lexi Bak.sql' from goods

At this point, the data that should be displayed on the screen is written to the file bak.sql, with each field separated by a tab and each line with a newline character. However, it can be defined. Fields are separated by,.

Such as:

SQL > select * into outfile'DVERGUR WAMP from goods www from goods php1510 max 0113max bak2.sql 'from goods ",

Restore of simple backup: (garbled problem)

# load data infile "D:/wamp/www/php1510/0113/bak.sql" into table copy_goods

two。 Using the data export tool, mysqldump outputs sql statements.

There is a mysqldump.exe tool under D:\ wamp\ bin\ mysql\ mysql5.5.24\ bin. This is not sql, it is used in cmd.

Format:

Mysqldump database server address port username password-B library name > backup file

If there is-B, there is a sql statement in sql to create the library

Arrow: > output redirection

Back up a library:

# mysqldump-hlocalhost-P3306-uroot-p zm0109 > D:/wamp/www/php1510/0113/zm0109.sql

Back up some tables under the library:

Format:

Mysqldump database server address port username password-B library name table name > backup file

# mysqldump-hlocalhost-P3306-uroot-p zm0109 goods student > D:/wamp/www/php1510/0113/zm01093.sql

Restore data:

1. Restore when you log in to the mysql client:

Source sql file

SQL > source D:/wamp/www/php1510/0113/zm01093.sql

2. Restore without logging in to the mysql client:

# mysql-uroot-p zm3

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.