Migrating Data

-mysqldump dump command

Mysqldump is a logical backup tool for Mysql. The backup principle is to connect to the Mysql database through the protocol and query the data to be backed up into the corresponding INSERT statement. When the data needs to be restored, it can be restored simply by executing these INSERT statements.

Common commands:

  • Exporting all databases
mysqldump -uroot -p123456 --all-databases  >/tmp/all.sql
Copy the code

  • Exporting a specified database
mysqldump -uroot -p123456 --databases  db  >/tmp/db.sql
Copy the code

  • Exporting a specified table
mysqldump -uroot -p123456 --databases  db  --tables a >/tmp/a.sql
Copy the code

  • Export data based on conditions
mysqldump -uroot -p123456 --databases db --tables a --where='id=1' >/tmp/a.sql
Copy the code

  • Only the table structure is exported
mysqldump -uroot -p123456 --no-data --databases db  >/tmp/db.sql
Copy the code

2. Import the file -source command

Using the source command, you can import the exported SQL file to a specified database.

Operation steps:

  • Use database;

  • Soucre has exported the SQL file.

Migration way of thinking

1. Migrate the historical data of database A to the HDFS for archiving.

2. Run the drop command to delete archived tables from database A.

3. Create A table on database A for database B migration.

4. All except unfinished orders in database B are migrated to database A;

5. The script deletes migrated data from database B.

O Ding Kai speak

1. Parameter introduction

In the Innodb storage engine, the innodb_file_per_table parameter is used to control how table data is stored.

When the parameter is set to OFF, all data is stored in a shared tablespace named ibData * under the default path. That is, all table data and index files of the database are stored in one file. The IBData * file does not shrink automatically when a table is dropped.

When the parameter is ON, each table is stored in a file with an.ibd suffix. In this way, each table has its own tablespace. You can use the drop table command to reclaim the tablespace.

Starting with Mysql 5.6.6, innodb_file_per_table defaults to ON.

2. Parameter Settings

To check the current status of the innodb_file_per_table parameter, run the show variables like ‘%per_table%’ command:

If you want to change the state of a parameter, you can dynamically change it to ON or OFF using SET GLOBAL, or permanently change it in my.cnf. Note that the mysqld service needs to be restarted if the modification takes effect in my.cnf.

Question: If the parameter is set to OFF, how will the tablespace be allocated after it is set to ON?

The answer is only for subsequent operations.

What does that mean? The data before the modification remains unchanged, that is, the original data is stored in the ibData * file, and the modified data is used in a separate tablespace.

Therefore, it is recommended to set this parameter to ON at the beginning.

The second day

Adin began a second time

Innodb uses a B+ tree structure to store data.

In the figure, P represents a page of data and R represents a row of data.

If we want to delete R2, InnoDB will only mark it as deleted. It will not actually free up the space occupied by the row, which means the hole will remain. This space can be used if subsequent inserts are between R1 and R3.

Assuming that R1, R2 and R3 records are deleted exactly, that is to say, the data on P1 page is deleted, then the space where P1 is will be marked as reusable. If the inserted data needs to use a new page, the P1 pit can be used.

So you might say, well, the data I inserted neatly avoids those places. What am I supposed to say? Slutty. This can cause a lot of wasted space, and if you delete a lot of data, the wasted space can be huge.

ALTER table XXX ENGINE = InnoDB;

Before version 5.5, the process for rebuilding a table looked like this:

The old table is then replaced with a temporary file, which enables the table to be rebuilt.

Pay attention to

1. Control migration speed to prevent online faults caused by master-slave delay;

2, Create a large table, use the following table to save about 50% of the space:

ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8
Copy the code

3, Use optimize Table to compress tables with enough space.

About the author

Author: Hello, I’m Lewu, a brick remover from BAT. From a small company to dachang, I gained a lot along the way. I want to share these experiences with people in need, so I created a public account “IT Migrant Workers”. Regular updates, I hope to help you.