Mysql database import and export test

Today, when preparing the test environment, you need to migrate data from the old database to the new server. Since I was not familiar with Mysql before, my first reaction was to have a function similar to the database import and export function in SQL server. However, I did not find similar functions in Mysql Workbeach, so I had to use python to implement them.

The general steps are as follows:

The code is as follows:	Copy code
1. Obtain all tables in the old database. 2. Obtain the field information, field name, and type of each table. 3. Construct a table creation statement 4. Construct a full table query statement, select ...... 5. Create a table under the new database 6. Insert data from the old database to the new database.

Although this script can complete database migration, it is hard to say its efficiency. I will not introduce the reason. I can guess from this step that multiple loop traversal and string concatenation operations will waste a lot of time.

After completing the above process. I started to think that mysql, as a very mature database system, cannot have such basic functions. So I started using google and finally found ~ The method can be completed in 2 minutes. You can use mysqldump to complete the above operations.

Steps for using mysqldump:

1. Export the entire database

Import the entire database to a file. Command: mysqldump-u username-h host-p password database name> exported file name
For example: (the file is stored in the current directory)

     

2. Create a database on the new server, pbserver

3. Import database

A. Log on to the mysql console: mysql-u username-h 10.20.143.84-p password

B. Import the data in the above file:

The code is as follows:	Copy code
Use Database Source d: \ pbserver. SQL

        

Attached some information about mysqldump
1. Explanation of mysqldump backup restoration and mysqldump import and export statements:
Mysqldump backup:

Mysqldump-u username-p password-h host Database a-w "SQL condition"-lock-all-tables> path

Case:

Mysqldump-uroot-p1234-hlocalhost db1 a-w "id in (select id from B)"-lock-all-tables> c: \ aa.txt

Restore mysqldump:

Mysqldump-u username-p password-h host Database <path

Case:

Mysql-uroot-p1234 db1 <c: \ aa.txt

Mysqldump export by conditions:

Mysqldump-u user name-p password-h host Database a-where "condition statement"-no-create table> path

Mysqldump-uroot-p1234 dbname a-where "tag = '88 '"-no-create-info> c: \ a. SQL

Mysqldump:

Mysqldump-u username-p password-h host Database <path

Case:

Mysql-uroot-p1234 db1 <c: \ a.txt

Mysqldump:

Mysqldump-u username-p password-h host database table

Case:

Mysqldump-uroot-p sqlhk9 a-no-data

2. Some main parameters of mysqldump
-Compatible = name
It tells mysqldump that the exported data will be compatible with the database or the old MySQL server. The values can be ansi, mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb, no_key_options, no_tables_options, and no_field_options. Separate them with commas. Of course, it does not guarantee full compatibility, but is as compatible as possible.
-Complete-insert,-c
The exported data adopts the complete INSERT method containing the field name, that is, all values are written in one row. This can improve the insertion efficiency, but may be affected by the max_allowed_packet parameter, resulting in insertion failure. Therefore, you need to use this parameter with caution. At least I do not recommend this parameter.
-Default-character-set = charset
Specifies the character set used for data export. If the data table does not use the default latin1 character set, this option must be specified during data export. Otherwise, garbled characters will occur after data is imported again.
-Disable-keys
Tell mysqldump to add/* at the beginning and end of the INSERT statement /*! 40000 alter table table disable keys */; and /*! 40000 alter table table enable keys */; statement, which greatly improves the speed of the insert statement because it re-creates the index after all data is inserted. This option is only applicable to MyISAM tables.
-Extended-insert = true | false
By default, mysqldump enables the-complete-insert mode, so if you do not want to use it, use this option to set its value to false.
-Hex-blob
Export binary string fields in hexadecimal format. This option is required if binary data exists. The affected field types include BINARY, VARBINARY, and BLOB.
-Lock-all-tables,-x
Before starting the export, submit a request to lock all tables in all databases to ensure data consistency. This is a global read lock and the-single-transaction and-lock-tables options are automatically disabled.
-Lock-tables
It is similar to-lock-all-tables, but instead of locking all tables in the database. This option is only applicable to MyISAM tables. For Innodb tables, you can use the-single-transaction option.
-No-create-info,-t
Only export data without adding the create table statement.
-No-data,-d
Only the database table structure is exported without exporting any data.
-Opt
This is just a quick option, it is equivalent to adding the-add-drop-tables-add-locking-create-option-disable-keys-extended-insert-lock-tables-quick-set-charset option at the same time. This option allows mysqldump to export data quickly and export data back quickly. This option is enabled by default, but can be disabled with-skip-opt. Note: If the-quick or-opt option is not specified when running mysqldump, the entire result set is stored in the memory. If you export a large database, problems may occur.
-Quick,-q
This option is useful when exporting large tables. It forces mysqldump to retrieve records from the server query and output them directly instead of caching all records into the memory.
-Routines,-R
Export stored procedures and user-defined functions.
-Single-transaction
This option submits a begin SQL statement before exporting data. BEGIN does not block any applications and ensures database consistency during export. It is only applicable to transaction tables, such as InnoDB and BDB.
This option and the-lock-tables option are mutually exclusive, because lock tables will implicitly commit any pending transactions.
To export a large table, use the-quick option.
-Triggers
Export the trigger at the same time. This option is enabled by default. Use-skip-triggers to disable it.
For details about other parameters, see the manual. I usually use the following SQL to back up the MyISAM table:
/Usr/local/mysql/bin/mysqldump-uyejr-pyejr"
-Default-character-set = utf8-opt-extended-insert = false"
-Triggers-R-hex-blob-x db_name> db_name. SQL
Use the following SQL to back up the Innodb table:
/Usr/local/mysql/bin/mysqldump-uyejr-pyejr"
-Default-character-set = utf8-opt-extended-insert = false"
-Triggers-R-hex-blob-single-transaction db_name> db_name. SQL
In addition, if you want to implement online backup, you can also use the-master-data parameter, as shown below:
/Usr/local/mysql/bin/mysqldump-uyejr-pyejr"
-Default-character-set = utf8-opt-master-data = 1"
-Single-transaction-flush-logs db_name> db_name. SQL
It only requests the lock table at the beginning, then refreshes the binlog, and then adds the change master statement to the exported file to specify the binlog location of the current backup, if you want to restore the file to slave, you can use this method.

1.2 restore
The file backed up with mysqldump is an SQL script that can be directly imported. There are two ways to import data.
Directly use the mysql client
For example:
/Usr/local/mysql/bin/mysql-uyejr-pyejr db_name <db_name. SQL
Use the SOURCE syntax (the experiment failed !!!)
In fact, this is not a standard SQL syntax, but a function provided by the mysql client, for example:
SOURCE/tmp/db_name. SQL;
Here, you must specify the absolute path of the file and the file must be read by the mysqld running user (such as nobody.