Several ways of Mysql Import and Export + View and modify Database character set method

three。 Export the database file from the database:

1. Export the database mydb to a file:

Open start-> run-> enter cmd to enter command line mode

C:\ > MySQLdump-h localhost-u root-p mydb > e:\ MySQL\ mydb.sql

Then enter the password and wait for a while for the export to be successful. You can check whether it is successful in the target file.

# Export multiple databases (data and structures)

> mysqldump-u root-p-B database A, database B, database C > xxxx.sql

two。 Export tables in the database to a file: do not add-tmurd is the derived structure and data

C:\ > MySQLdump-h localhost-u root-p password database name table1 table2 > e:\ MySQL\ mytable.sql

[root@BI-162 /] # mysqldump-uroot pacs pis_study_info > / mysql/pis_info.sql

3. Export the structure of the database mydb to a file:

C:\ > MySQLdump-h localhost-u root-p mydb-d-- add-drop-table database name > e:\ MySQL\ mydb_stru.sql

[root@BI-162 /] # mysqldump-uroot-d-add-drop-table pacs > / mysql/pacs_jg.sql

Mysqldump-uroot-d pacs > / mysql/pacs_jg2.sql

4. Export a table with only table structure-d only structure

Mysqldump-u user name-p password-d database name table name > exported file name

C:\ > mysqldump-uroot-p mysql-d database name table name > e:\ date_rec_drv.sql

[root@BI-162 /] # mysqldump-uroot-d pacs pis_study_info > / mysql/pis_jg.sql

-h localhost can be omitted and is generally used on virtual hosts

5. Export data does not export structure-t only data

Mysqldump-t database name-uroot-p > xxx.sql-- export the data of the library, that is, all table data

Mysqldump-t database name table name-uroot-p > xxx.sql-- Export specified table data

6. Export with language parameters

Mysqldump-uroot-p-- default-character-set=latin1-- set-charset=gbk-- skip-opt database_name > outfile_name.sql

Compressed backup

Mysqldump-uroot-pendant pwd'-B db1db2 | gzip > / db_back.sql.gz

Batch compressed backup

Mysqldump-uroot-pawd'-e "show databases;" | grep-Eiv "database | infor | perfor" | sed-r's # ^ ([Amurz]. * $)

# mysqldump-uroot-p 'pwd'-- event-B\ 1 | gzip > / opt/back/\ 1.sql.gzroomg' | bash

four。 Import data into the database from the external file MySQL:

Import the SQL statement from the file into the database from e:\ MySQL\ mydb2.sql:

1. Enter MySQL from the command line and create the database mydb2 with the command CREATE DATABASE mydb2;.

two。 You can enter the command exit; or quit to exit MySQL

3. Enter the following command in CMD:

C:\ > MySQL-h localhost-u root-p mydb2

< e:\MySQL\mydb2.sql 然后输入密码，就OK了。 导入数据3种方式： 1.mysql>

Source / etc/rc.d/init.d/pis_study_info.sql-Import data, executed on the mysql command line

2. [root @ BI-162 init.d] # mysql-u root-p pacs

3. To load data into a data table in text:

Mysql > LOAD DATA LOCAL INFILE "D:/mysql.txt" INTO TABLE database name. Table name

5. View and modify character set

Enter mysql to view the character set, client, database and server character set

Mysql > show variables like'% char%'

Mysql > status;-- View the current database character set

Vim / etc/my.cnf

[client]

Default-character-set=utf8

[mysqld]

Character-set-server=utf8

Service mysqld restart restart service

Finally, execute set names utf8

The execution of this sentence is equivalent to the simultaneous execution of the following three sentences:

SET character_set_client='utf8'

SET character_set_connection='utf8'

SET character_set_results='utf8'