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

This article mainly explains "how to use mysql dump". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Now let the editor take you to learn how to use mysql dump.

Configure mysql on the client side

Mysqldump is a client-side tool that backs up data directly to the local location. Configuring mysql on the client side is very simple, which is done in two steps:

1. Go to the server's bin directory and copy mysql.exe and mysqldump.exe directly to the local location. For example, I copy them to C:\ mysql

2. Configure the environment variable and add C:\ mysql directly to the Path.

II. Mysqldump

1. Export all the databases under a user, and here I export all the databases under root

Mysqldump-uroot-p-- all-databases > C:\ Users\ QuJian\ Desktop\ a.dmp then prompts you for the root password and will proceed to the backup process.

Explanation: mysqldump is executed under the operating system interface, which needs to be kept in mind. Mysqldump is a backup command, telling mysql that I want to export data,-u indicates which export user to connect to,-p user password,-- all-databases means to export all databases under this user, > indicates the location of the backup export, here I export him to C:\ Users\ QuJian\ Desktop (desktop), the backup file name is a.dmp, this suffix can be not only dmp, but also .text, .sql and so on. Suffix names are not required, but they are all sql script files. When you open this backup file with Notepad++, you will find that the backup file of the library can be divided into the following parts: create database judgment statement-delete table-create table-lock table-disable index-insert data-enable index-unlock table.

2. Export some databases under the user

Mysqldump-uroot-p-- databases oa tsshop > C:\ Users\ QuJian\ Desktop\ a.dmp

Explanation:-- database indicates which library to export, where oa and tsshop libraries are exported

3. Export some tables

Mysqldump-uroot-p-- database tsshop-- tables himall_active > C:\ Users\ QuJian\ Desktop\ a.dmp

Explain:-- which tables are exported by the table parameter table name, and open the backup file with Notepad++, you can find that the backup file consists of: delete table-create table-import data.

4. Conditional table export

Mysqldump-uroot-p-- database tsshop-- tables himall_active-- where='id=1' > C:\ Users\ QuJian\ Desktop\ a.dmp

Mysqldump-uroot-p-database tsshop-tables himall_active-where= "name=' Zhang San'" > C:\ Users\ QuJian\ Desktop\ a.dmp

Explanation:-- the where parameter indicates the conditions for exporting the table

5. Only export the table structure but not the specific data

Mysqldump-uroot-p-- databases tsshop-- no-data > C:\ Users\ QuJian\ Desktop\ a.dmp

Explanation:-- the no-data parameter specifies that only the table structure is exported, not the table data

6. Note that we are operating on the client side, so for each previous export operation, whether it is a library or a table, we need to indicate the source server address and use the parameter-- host

Mysqldump-uroot-p-- host=10.0.0.47-- databases tsshop-- no-data > C:\ Users\ QuJian\ Desktop\ a.dmp

7. Export stored procedures and custom functions

Mysqldump-uroot-p-- host=10.0.0.47-- databases tsshop-- routines > C:\ Users\ QuJian\ Desktop\ a.dmp

Explanation:-- the routines parameter indicates that stored procedures and custom functions need to be exported, except that-- all-databases will back up stored procedures and custom functions by default, and back up the database separately. The table will not have stored procedures and custom functions.

8. Export event

Mysqldump-uroot-p-host=10.0.0.47-- databases tsshop--routines-- events > C:\ Users\ QuJian\ Desktop\ a.dmp

Explanation:-- events parameter indicates that events are also exported, except that-- all-databases will back up events by default, backup the database separately, and the table will not have events.

9.-add-drop-database

Mysqldump-uroot-p-host=10.0.0.47-- databases tsshop--routines-- events-- add-drop-database > C:\ Users\ QuJian\ Desktop\ a.dmp

Explanation:-- add-drop-database means to add a drop database statement to the generated sql statement, which is used for this user to already have this database. Delete the database with the same name before import, and then create a new database. However, I feel useless, because if you do not declare this parameter, the database with the same name will not be created in the sql statement, you can just delete the table and rebuild the table, and you can still import the data.

10.-add-drop-table

Mysqldump-uroot-p-host=10.0.0.47-- databases tsshop--routines-- events-- add-drop-table > C:\ Users\ QuJian\ Desktop\ a.dmp

Explanation:-- add-drop-table means to delete the table with the same name before importing it. This parameter is a default parameter. If you want to cancel this default parameter, you can use-- skip-add-drop-table.

11.-add-locks

Mysqldump-uroot-p-host=10.0.0.47-- databases tsshop--routines-- events-- add-locks > C:\ Users\ QuJian\ Desktop\ a.dmp

Explanation:-- add-locks means to lock each table before it is exported and then release the lock after it is exported. This is also turned on by default. Cancel this parameter and use-- skip-add-locks.

12.-comments

Mysqldump-uroot-p-host=10.0.0.47-- databases tsshop--routines-- events-- comments > C:\ Users\ QuJian\ Desktop\ a.dmp

Explanation:-- comments means to export comment information. If you cancel it, you can use-- skip-comments

13.-force

Mysqldump-uroot-p-host=10.0.0.47-- databases tsshop--routines-- events-- force > C:\ Users\ QuJian\ Desktop\ a.dmp

Explanation:-- force means to ignore sql errors in the export

14.-triggers

Mysqldump-uroot-p-host=10.0.0.47-- databases tsshop--routines-- events-- triggers > C:\ Users\ QuJian\ Desktop\ a.dmp

Explanation:-- triggers means export trigger, this option is enabled by default, unlike-- routines-- events will not be enabled by default, cancel the use of-- skip-triggers

3. Import data

There are two ways to import:

1. Import directly at the operating system level

Import the entire library: mysql-h 10.0.0.44-uroot-psource C:\ Users\ QuJian\ Desktop\ a.dmp

At this point, I believe you have a deeper understanding of "how to use mysql dump". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.