MySQL Export database several methods

Source: Internet
Author: User
Tags command line mysql tutorial table name import database mysql command line mysql database mysql export database phpmyadmin

MySQL Tutorial export database tutorials several ways

Method One

CMD to the MySQL Bin directory with the following command mysqldump--opt-h192.168.0.156-uusername-ppassword--skip-lock-tables databasename> Database.sql

It's okay to change IP to localhost.

If it's navicate, it's easier. Connect the database First, select the database, and then choose to dump SQL.

Method Two

Enter cmd (note in OS cmd instead of MySQL)

===================

1. Export database (SQL script)

Mysqldump-u user name-p database name > exported file name

Mysqldump-u root-p db_name > Test_db.sql

2.mysql Export Database A table

Mysqldump-u user name-P database name Table name > exported file name

Mysqldump-u wcnc-p test_db users> test_users.sql (no semicolon at the end)

Method Three

Start the MySQL service

/etc/init.d/mysql start

Export the entire database

Mysqldump dbname > C:mydb.sql-u root-p

Import Database

SOURCE Mydb.sql

Mysql-u user name-p database name < database name. sql

tutorial on importing databases in more detail

2.1. Export SQL Script

On the original database server, you can export the SQL script using the PHP Tutorial MyAdmin tool, or the mysqldump command line (mysqldump command in the mysql/bin/directory).

2.1.1 with phpMyAdmin Tools

Export options, select export structure and data, and do not add drop database and drop table options.

Select the Save As File option to select the gzipped option if the data is more numerous.

Save the exported SQL file.

2.1.2 with mysqldump command line

Command format

Mysqldump-u user name-p database name > database name. sql

Example:

MYSQLDUMP-UROOT-P ABC > Abc.sql

(Export database ABC to abc.sql file)

When prompted for a password, enter the password for the database user name.

2.2. Create an empty database

Create a database from the Master interface/Control Panel. Suppose the database is named ABC and the database is Abc_f.

2.3. Import SQL script into execution

There are also two methods, one using the phpMyAdmin (MySQL database management) tool, or the MySQL command line.

2.3.1 with phpMyAdmin Tools

From the Control Panel, choose to create an empty database, click "Manage", and go to the admin tools page.

On the SQL menu, browse to select the SQL file you just exported and click "Execute" to download and execute.

Note: phpMyAdmin has limits on the size of uploaded files, and PHP itself has limits on the size of uploaded files, if the original SQL file

Relatively large, you can first use gzip to compress it, for SQL files such as text files, you can get 1:5 or higher compression rate.

Gzip Use method:

# gzip Xxxxx.sql

Get

xxxxx.sql.gz files.

2.3.2 with mysql command line

Command format

Mysql-u user name-p database name < database name. sql

Example:

MYSQL-UABC_F-P ABC < Abc.sql

(Import database ABC from Abc.sql file)

When prompted for a password, enter the password for the database user name.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.