MySQL uses commands to import and export instances for. SQL file backup data operations

MySQL uses commands to import and export instances for. SQL file backup data operations

In order to pursue efficiency, various MySQL operations are basically completed through a graphical interface. A few mouse clicks, ALL over. However, in a specific environment, for example, when MySQL graphical management tools are not installed and cannot be downloaded, what should we do?

The author has encountered this situation. The following is an instance that imports and exports the. SQL file through the MySQL command line to complete database backup operations. Make a record and hope to help friends who need it.

1. First, use the cmd command to switch to the directory of the MySQL command tool.

For example, this time: cd D: \ MySQL \ lib

Ii. log on to MySQL using the cmd command line

1. D: \ MySQL \ lib> MySQL-h hostname-u username-p

Press ENTER, and then ENTER the password. Here, hostname is the name of the server, such as localhost and username is the username of MySQL, such as root.

After entering the command line, you can directly operate MySQL.

2. Here are some common commands for MySQL to view database information.

MySQL-> show databases; // displays the database information and available DATABASES.

MySQL-> USE dbname; // select a database

MySQL-> show tables; // displays the table information, available TABLES

MySQL-> DESCRIBE tablename; // display the information of the created table

3. Export Database files from the database through the command line

1. Export the database phpernote to the e: \ MySQL \ phpernote. SQL file:

C: \> MySQL dump-h localhost-u root-p phpernote> e: \ MySQL \ phpernote. SQL

Enter the password and wait for a moment until the export is successful. You can check whether the export is successful in the target file.

2. Export the mytable table in database phpernote to the e: \ MySQL \ mytable. SQL file:

C: \> MySQLdump-h localhost-u root-p phpernote mytable> e: \ MySQL \ mytable. SQL

3. Export the database phpernote structure to the e: \ MySQL \ phpernote_stru. SQL file:

C: \> MySQLdump-h localhost-u root-p phpernote -- add-drop-table> e: \ MySQL \ phpernote_stru. SQL

4. import data from the external file MySQL to the database

Import the SQL statements in the file into the database from e: \ MySQL \ phpernote2. SQL:

Enter the following command in CMD:

C: \> MySQL-h localhost-u root-p phpernote2 <e: \ MySQL \ phpernote2. SQL

Then enter the password, and then OK.

5. Next, let's talk about how to solve the import file size restriction problem.

By default, the size of the MySQL imported file is limited to 2 MB. Therefore, when the file is large, it cannot be imported directly. The solution to this problem is as follows:

1. Modify related parameters in php. ini:

There are three parameters that affect the size of the MySQL import file:

Memory_limit = 128 M, upload_max_filesize = 2 M, post_max_size = 8 M

Modify upload_max_filesize = 200 M. Modify the size that meets your needs.

You can modify two other memory_limit = 250 M post_max_size = 200 M at the same time.

In this way, MySQL can import the. SQL file below MB.

Articles you may be interested in

• How does mysql export data of a field in a table?
• The jquery setting button cannot be clicked to prevent repeated data submission
• Mysql converts unix timestamp to date format, and MySQL queries the current time
• Php reads the Directory and lists the functions that display the files in the directory.
• How to query data of mysql today, yesterday, last 7 days, last 30 days, last month, and last month
• Linux chmod (file or folder permission setting) command parameters and Usage Details
• Note the following when querying strings with single quotes and inserting strings with single quotes in Mysql:
• Prohibit web page right-click, copy, save as, View Source file and other functions to implement web page source code protection