Mysql export
Several Methods of Database

 

Methods a

Mysql> mysqldump — opt-h192.168.0.156-uusername-ppassword –skip-lock-tables databasename> The database. SQL

Change the IP address to localhost and you can use www.2cto.com

If Navicate is installed, then it is even easier to connect to the database, select the database, and then dump the SQL

Method 2

Enter CMD (note in OS CMD and not in MySQL)

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

1. Export database (SQL script)

Mysqldump -u username -p database name> Export filename

mysqldump -u root -p db_name > test_db.sql

Mysql> export a table from mysql

Mysql> mysqldump -u -p; Export filename

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

Methods three

Mysql> start mysql

/etc/init.d/mysql start

Export the entire database

mysqldump dbname > C: mydb. SQL -u root -p

Import the database

source mydb.sql

Mysql -u username -p database name < Database name.sql

More detailed database import tutorial

www.2cto.com

2.1. Export SQL Scripts

From the original database server, you can export SQL scripts using the PHP tutorial myadmin tool or the mysqldump command line (mysqldump command is located in the mysql/bin/ directory).

2.1.1 Use the phpMyAdmin tool

In the export options, select export “structure” and “data” and do not add “drop database” and “drop table” options.

Select the “save as file” option, or if you have a lot of data, select the “gzipped” option.

Save the exported SQL file.

2.1.2 Use the mysqldump command line

The command format

Mysqldump -u username -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 user name of the database.

2.2. Create an empty database

From the master interface/control panel, create a database. Assume that the database name is ABC and the database full user is ABC_F.

2.3. Import SQL script for execution

There are also two methods, one using phpMyAdmin (my)
SQL database management tools, or
MySQL command line.

2.3.1 Use the phpMyAdmin tool

From the control panel, select the empty database to create, and click “Manage” to go to the admin tools page.

In the “SQL” menu, browse and select the exported SQL file, and click “Execute” to upload and execute.

Note: phpMyAdmin has a limit on the size of uploaded files, and PHP itself has a limit on the size of uploaded files, if the original SQL file is used

It is relatively large, you can use gzip to compress it first, for text files such as SQL files, you can get 1:5 or more compression ratio.

How to use gzip: www.2cto.com

# gzip xxxxx.sql

get

XXXXX. SQL. Gz file.

2.3.2 Use the MySQL command line

The command format

Mysql -u username -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 user name of the database.

Usually, you may use a lot of MySQL import and export methods, today I will introduce you several import and export MySQL data methods, convenient for you to use in work.
Here’s what Baidu got:

Mysql> import files or data or execute related SQL

1

Mysql -h host address -u user name -p user password mysql -h host address -u user name -p user password (shell command line)
mysql -u root -p dbname < filename.sql

2

Execute an SQL
directly on the command line (shell command line)
mysql -hhostname -uusername -p dbname -e ‘select * from tbname limit 1’
After execution, the command line will prompt for the database password.

3

echo ‘select id from dbname.tbname where id = 1; ‘ | mysql -hhostname -ureadonly -preadonly dbname > xxxx.sql

4

Enter the MySQL database (where the SQL file is executed)
> source xxx.sql

END

Mysqldump = mysqldump;

1

Mysqldump -u user name -p password -h host database a-w “SQL condition” — lock-all-tables>; Path
mysqldump -hhostname -uusername -p dbname tbname> xxxx.sql

2

** Export the contents of the database table on the specified criteria. (-w option — where)
mysqldump -hhostname -uusername-p dbname tbname -w’id > = 1 and id< = 10000′–skip-lock-tables > xxxx.sql

3

Or the next line is
mysqldump -hhostname -uusername -p dbname tbname –where=’unit_id > = 1 and unit_id < = 10000′> ~/xxxx.sql


END

Mysqldump Export Database Table

1

mysqldump -u username -p database name> Export file name
> mysqldump -u breezelark-p mydb > mydb.sql


2

mysqldump -u username -p database name>
mysqldump -u username -p database name> Export file name
mysqldump -u lingxi -p mydb mytb> mytb.sql


3

Export a database structure (no data only structure)
mysqldump -u lingxi -p -d –add-drop-table mydb > mydb.sql
– d no data – add – drop – table before each the create statement adds a drop table
of course this is just one way of import and export, you can feel the choice suits own method.