mysql数据库备份

一、mysqldump导出数据

1、导出数据库表结构

 mysqldump  -uroot -proot  -d  test_db >  /home/test_db.sql

2、到出数据库表数据

mysqldump  -uroot -proot  -t   test_db >  /home/test_db.sql

3、导出数据库表结构和数据

 mysqldump  -uroot -proot   test_db >  /home/test_db.sql

4、导出单表结构

mysqldump -uroot -proot --set-gtid-purged=OFF -d test_db  t_user>'/home/t_user.sql'

5、导出单表数据

mysqldump  -uroot -proot  -t  test_db  t_user>/home/t_user.sql

6、导出单表结构和数据

mysqldump -uroot -proot --set-gtid-purged=OFF test_db  t_user>'/home/sys_user.sql'

二、select导出数据

  1、secure_file_priv

   查看secure_file_priv是否允许把数据导出到任何目录

SHOW  VARIABLES  LIKE	  '%secure_file_priv%';

    如果为NULL,则在my.cnf配置文件中配置想要导出的的目录即可

  2、导出表数据

 SELECT  * FROM  t_user INTO OUTFILE '/var/lib/mysql-files/t_user.sql';

 

 三、导入数据

1、方式一：load data

LOAD DATA LOCAL INFILE '/var/lib/mysql-files/t_user.sql' INTO TABLE t_user  LINES TERMINATED BY '\r\n';

2、方式二：mysql   

mysql -uroot -p test_db  < t_user.sql

3、方式三：insert into 

INSERT  INTO   t_user_back(id,name)  SELECT  id,name  FROM  t_user;

 

四、mysqldump参数

Option Name	Description	Introduced	Deprecated
--add-drop-database	Add DROP DATABASE statement before each CREATE DATABASE statement
--add-drop-table	Add DROP TABLE statement before each CREATE TABLE statement
--add-drop-trigger	Add DROP TRIGGER statement before each CREATE TRIGGER statement
--add-locks	Surround each table dump with LOCK TABLES and UNLOCK TABLES statements
--all-databases	Dump all tables in all databases
--allow-keywords	Allow creation of column names that are keywords
--apply-replica-statements	Include STOP REPLICA prior to CHANGE REPLICATION SOURCE TO statement and START REPLICA at end of output	8.0.26
--apply-slave-statements	Include STOP SLAVE prior to CHANGE MASTER statement and START SLAVE at end of output		8.0.26
--bind-address	Use specified network interface to connect to MySQL Server
--character-sets-dir	Directory where character sets are installed
--column-statistics	Write ANALYZE TABLE statements to generate statistics histograms
--comments	Add comments to dump file
--compact	Produce more compact output
--compatible	Produce output that is more compatible with other database systems or with older MySQL servers
--complete-insert	Use complete INSERT statements that include column names
--compress	Compress all information sent between client and server		8.0.18
--compression-algorithms	Permitted compression algorithms for connections to server	8.0.18
--create-options	Include all MySQL-specific table options in CREATE TABLE statements
--databases	Interpret all name arguments as database names
--debug	Write debugging log
--debug-check	Print debugging information when program exits
--debug-info	Print debugging information, memory, and CPU statistics when program exits
--default-auth	Authentication plugin to use
--default-character-set	Specify default character set
--defaults-extra-file	Read named option file in addition to usual option files
--defaults-file	Read only named option file
--defaults-group-suffix	Option group suffix value
--delete-master-logs	On a replication source server, delete the binary logs after performing the dump operation		8.0.26
--delete-source-logs	On a replication source server, delete the binary logs after performing the dump operation	8.0.26
--disable-keys	For each table, surround INSERT statements with statements to disable and enable keys
--dump-date	Include dump date as "Dump completed on" comment if --comments is given
--dump-replica	Include CHANGE REPLICATION SOURCE TO statement that lists binary log coordinates of replica's source	8.0.26
--dump-slave	Include CHANGE MASTER statement that lists binary log coordinates of replica's source		8.0.26
--enable-cleartext-plugin	Enable cleartext authentication plugin
--events	Dump events from dumped databases
--extended-insert	Use multiple-row INSERT syntax
--fields-enclosed-by	This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA
--fields-escaped-by	This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA
--fields-optionally-enclosed-by	This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA
--fields-terminated-by	This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA
--flush-logs	Flush MySQL server log files before starting dump
--flush-privileges	Emit a FLUSH PRIVILEGES statement after dumping mysql database
--force	Continue even if an SQL error occurs during a table dump
--get-server-public-key	Request RSA public key from server
--help	Display help message and exit
--hex-blob	Dump binary columns using hexadecimal notation
--host	Host on which MySQL server is located
--ignore-error	Ignore specified errors
--ignore-table	Do not dump given table
--include-master-host-port	Include MASTER_HOST/MASTER_PORT options in CHANGE MASTER statement produced with --dump-slave		8.0.26
--include-source-host-port	Include SOURCE_HOST and SOURCE_PORT options in CHANGE REPLICATION SOURCE TO statement produced with --dump-replica	8.0.26
--insert-ignore	Write INSERT IGNORE rather than INSERT statements
--lines-terminated-by	This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA
--lock-all-tables	Lock all tables across all databases
--lock-tables	Lock all tables before dumping them
--log-error	Append warnings and errors to named file
--login-path	Read login path options from .mylogin.cnf
--master-data	Write the binary log file name and position to the output		8.0.26
--max-allowed-packet	Maximum packet length to send to or receive from server
--mysqld-long-query-time	Session value for slow query threshold	8.0.30
--net-buffer-length	Buffer size for TCP/IP and socket communication
--network-timeout	Increase network timeouts to permit larger table dumps
--no-autocommit	Enclose the INSERT statements for each dumped table within SET autocommit = 0 and COMMIT statements
--no-create-db	Do not write CREATE DATABASE statements
--no-create-info	Do not write CREATE TABLE statements that re-create each dumped table
--no-data	Do not dump table contents
--no-defaults	Read no option files
--no-set-names	Same as --skip-set-charset
--no-tablespaces	Do not write any CREATE LOGFILE GROUP or CREATE TABLESPACE statements in output
--opt	Shorthand for --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset
--order-by-primary	Dump each table's rows sorted by its primary key, or by its first unique index
--password	Password to use when connecting to server
--password1	First multifactor authentication password to use when connecting to server	8.0.27
--password2	Second multifactor authentication password to use when connecting to server	8.0.27
--password3	Third multifactor authentication password to use when connecting to server	8.0.27
--pipe	Connect to server using named pipe (Windows only)
--plugin-authentication-kerberos-client-mode	Permit GSSAPI pluggable authentication through the MIT Kerberos library on Windows	8.0.32
--plugin-dir	Directory where plugins are installed
--port	TCP/IP port number for connection
--print-defaults	Print default options
--protocol	Transport protocol to use
--quick	Retrieve rows for a table from the server a row at a time
--quote-names	Quote identifiers within backtick characters
--replace	Write REPLACE statements rather than INSERT statements
--result-file	Direct output to a given file
--routines	Dump stored routines (procedures and functions) from dumped databases
--server-public-key-path	Path name to file containing RSA public key
--set-charset	Add SET NAMES default_character_set to output
--set-gtid-purged	Whether to add SET @@GLOBAL.GTID_PURGED to output
--shared-memory-base-name	Shared-memory name for shared-memory connections (Windows only)
--show-create-skip-secondary-engine	Exclude SECONDARY ENGINE clause from CREATE TABLE statements	8.0.18
--single-transaction	Issue a BEGIN SQL statement before dumping data from server
--skip-add-drop-table	Do not add a DROP TABLE statement before each CREATE TABLE statement
--skip-add-locks	Do not add locks
--skip-comments	Do not add comments to dump file
--skip-compact	Do not produce more compact output
--skip-disable-keys	Do not disable keys
--skip-extended-insert	Turn off extended-insert
--skip-generated-invisible-primary-key	Do not include generated invisible primary keys in dump file	8.0.30
--skip-opt	Turn off options set by --opt
--skip-quick	Do not retrieve rows for a table from the server a row at a time
--skip-quote-names	Do not quote identifiers
--skip-set-charset	Do not write SET NAMES statement
--skip-triggers	Do not dump triggers
--skip-tz-utc	Turn off tz-utc
--socket	Unix socket file or Windows named pipe to use
--source-data	Write the binary log file name and position to the output	8.0.26
--ssl-ca	File that contains list of trusted SSL Certificate Authorities
--ssl-capath	Directory that contains trusted SSL Certificate Authority certificate files
--ssl-cert	File that contains X.509 certificate
--ssl-cipher	Permissible ciphers for connection encryption
--ssl-crl	File that contains certificate revocation lists
--ssl-crlpath	Directory that contains certificate revocation-list files
--ssl-fips-mode	Whether to enable FIPS mode on client side		8.0.34
--ssl-key	File that contains X.509 key
--ssl-mode	Desired security state of connection to server
--ssl-session-data	File that contains SSL session data	8.0.29
--ssl-session-data-continue-on-failed-reuse	Whether to establish connections if session reuse fails	8.0.29
--tab	Produce tab-separated data files
--tables	Override --databases or -B option
--tls-ciphersuites	Permissible TLSv1.3 ciphersuites for encrypted connections	8.0.16
--tls-version	Permissible TLS protocols for encrypted connections
--triggers	Dump triggers for each dumped table
--tz-utc	Add SET TIME_ZONE='+00:00' to dump file
--user	MySQL user name to use when connecting to server
--verbose	Verbose mode
--version	Display version information and exit
--where	Dump only rows selected by given WHERE condition
--xml	Produce XML output
--zstd-compression-level	Compression level for connections to server that use zstd compression	8.0.18