brief introduction
Recently, there is a new requirement to make MySQL backup into a visual interface for operation, use code to call MySQL dump program process backup, and use MySQL program process recovery.
Because a lot of instructions are used, we refer to the information found on the Internet and hereby record it
If there is more time in the future, post the solution ideas and codes for MySQL visual backup. Let's talk about it later
mysql dump client can be used to dump database or collect database for backup or transfer data to another sql Server (not necessarily a mysql server). Dump contains sql statements that create tables and / or load tables.
- If you are backing up on the server and the tables are all myisam tables, you should consider using mysqlhotcopy because you can backup and restore faster.
preface
This paper introduces mysql data backup and recovery from three parts:
- First, the basic knowledge of mysql;
- Second, an example of data backup and recovery;
- Third, the specific parameters of mysqldump are described.
1, Basic knowledge of MySql
1. Connect to mysql
$ Format: mysql -h Host address -u user name －p User password
- Connect to MYSQL on this computer
# Press enter to prompt you to enter your password. If MYSQL has just been installed, the super user root does not have a password, so press enter to enter MYSQL. The prompt of MYSQL is: MYSQL > $ mysql -uroot -p
- Connect to MYSQL on remote host
# Suppose the IP address of the remote host is 127.0.0.1, the user name is root and the password is 12356. Type the following command: $ mysql -h127.0.0.1 -uroot -p12356 # (Note: u and root do not need to add spaces, and others are the same)
- Exit MYSQL command
# Exit command $ exit && \q
2. Common commands:
1,Display database list: $ show databases; # At the beginning, there were only two databases: MySQL and test. MySQL library is very important. It contains MySQL system information. We actually use this library to change passwords and add users. 2,Display data tables in the Library: $ use mysql; // Open library # Show all tables $ show tables; 3,Displays the structure of the data table: $ describe Table name; # Abbreviation: desc table name 4,Database creation: $ create database Library name; 5,Table creation: use Library name; $ create table Table name (Field setting list); 6,Delete database and delete table: $ drop database Library name; $ drop table Table name; 7,Clear the records in the table: $ delete from Table name; 8,Show records in table: $ select * from Table name; 9,mysqldump Program help command $ mysqldump --help
2, Data backup and recovery
1. Backup: exporting data from database:
$ mysqldump -h link ip -P(Capitalize)port -u user name -p Password database name > xxxx.sql(route)
Example: mysqldump - h127.0.0.1 - p3307 - uroot - p8888 test > test.sql;
2. Backup export example:
1. Export data and table structure - return all the data and table structure and data in a specific database and a specific table
$ mysqldump -uroot -h127.0.0.1 -padmin -P3306 test up_subjects > test.sql
2. Export table structure without exporting table data - only return the table structure of a specific table in a specific database, do not return data, and add "- d" command parameter
$ mysqldump -uroot -h127.0.0.1 -padmin -P3306 -d test up_subjects > test.sql
3. Export table structure and table data meeting the top conditions - only the table structure and data meeting the specific conditions of a specific table in a specific database are returned
$ mysqldump -uroot -h127.0.0.1 -padmin -P3306 test up_subjects --where="ctime>'2017-01-01' and ctime<'2017-06-30'" >test.sql
4. Export data without exporting table structure - only return the data of a specific table in a specific database, do not return table structure, and add "- t" command parameter
$ mysqldump -uroot -h127.0.0.1 -padmin -t -P3306 test up_subjects >test.sql
5. Export the table structure and data of all tables in a specific database, and add the "– databases" command parameter
$ mysqldump -uroot -h127.0.0.1 -padmin -P3306 --databases test > test.sql
3. Restore imported database data:
Imports the exported local file into the specified database
1. System command line
# Format: mysql -h link IP - p (in uppercase) port - u user name - p password database name < D: xx.sql (Lu Jin) $ mysql -uusername -ppassword test < test.sql
2. Or mysql command line
mysql> # Select database user test; # Import sql source test.sql;
3. How to restore the entire database:
$ mysql -uroot -h127.0.0.1 -padmin -P3306 < test.sql
4. Specific recovery examples:
1. Log in to the MySQL server first and switch the data to be imported. The specific commands are as follows:
mysql> use test; mysql> source /home/test/test.sql Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 9 rows affected (0.00 sec) Records: 9 Duplicates: 0 Warnings: 0
Note: there are 9 lines of affected records, 0 lines of duplicate records and 0 warning records
2. Use the system command line directly
$ mysql -uroot -h127.0.0.1 -padmin -P3306 test < /home/test/test.sql
3, mysqldump parameter description
- mysqldump character set settings
$ mysqldump -uusername -ppassword --default-character-set=gb2312 db1 table1 > tb1.sql
mysql dump client can be used to dump database or collect database for backup or transfer data to another sql Server (not necessarily a mysql server). Dump contains sql statements that create tables and / or load tables.
If you are backing up on the server and the tables are myisam tables, you should consider using mysqlhotcopy , because backup and recovery can be done faster.
- You can use mysqldump – help to view all parameters
$ mysqldump --help
1,–all-databases , -A
Export all databases.
$ mysqldump -uroot -p --all-databases
2,–all-tablespaces , -Y
Export all tablespaces.
$ mysqldump -uroot -p --all-databases --all-tablespaces
3,–no-tablespaces , -y
No tablespace information is exported.
$ mysqldump -uroot -p --all-databases --no-tablespaces
4,–add-drop-database
Add a drop database statement before each database is created.
$ mysqldump -uroot -p --all-databases --add-drop-database
5,–add-drop-table
Add a drop data table statement before each data table is created. (it is on by default. Use -- skip add drop table to cancel the option)
# (add drop statement by default) $ mysqldump -uroot -p --all-databases # (cancel drop statement) $ mysqldump -uroot -p --all-databases –skip-add-drop-table
6,–add-locks
Before each table is exported, add LOCK TABLES and then UNLOCK TABLE. (it is on by default. Use -- skip add locks to cancel the option)
# (add LOCK statement by default) $ mysqldump -uroot -p --all-databases # (cancel LOCK statement) $ mysqldump -uroot -p --all-databases –skip-add-locks
7,–allow-keywords
Allows the creation of column names that are keywords. This is done by prefixing the table name to each column name.
$ mysqldump -uroot -p --all-databases --allow-keywords
8,–apply-slave-statements
Add 'STOP SLAVE' before 'CHANGE MASTER' and 'START SLAVE' at the end of the export.
$ mysqldump -uroot -p --all-databases --apply-slave-statements
9,-character-sets-dir
Directory of character set files
$ mysqldump -uroot -p --all-databases --character-sets-dir=/usr/local/mysql/share/mysql/charsets
10,–comments
Additional comment information. It is on by default and can be cancelled with -- skip comments
# (default record comment) $ mysqldump -uroot -p --all-databases # (uncomment) $ mysqldump -uroot -p --all-databases --skip-comments
11,–compatible
The exported data will be compatible with other databases or older versions of MySQL. The values can be ansi, mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb and no_key_options,no_tables_options,no_field_options, etc. to use several values, separate them with commas. It is not guaranteed to be fully compatible, but as far as possible.
$ mysqldump -uroot -p --all-databases --compatible=ansi
12,–compact
Export less output information (for debugging). Remove comments, head and tail structures. You can use options: - skip add drop table -- skip add locks -- skip comments -- skip disable keys
$ mysqldump -uroot -p --all-databases --compact
13,–complete-insert, -c
Use the full insert statement (including the column name). This can improve the insertion efficiency, but it may be affected by max_ allowed_ The insertion failed due to the influence of the packet parameter.
$ mysqldump -uroot -p --all-databases --complete-insert
14,–compress, -C
Enable compression to pass all information between the client and the server
$ mysqldump -uroot -p --all-databases --compress
15,–create-options, -a
Include all MySQL attribute options in the CREATE TABLE statement. (on by default)
$ mysqldump -uroot -p --all-databases
16,–databases, -B
Export several databases. All parameters after the parameter are treated as database names.
$ mysqldump -uroot -p --databases test mysql
17,–debug
Output debug information for debugging. The default value is: d:t,/tmp/mysqldump.trace
$ mysqldump -uroot -p --all-databases --debug # Default value $ mysqldump -uroot -p --all-databases --debug=" d:t,/tmp/debug.trace"
18,--debug-check
Check the memory and open file instructions and exit.
$ mysqldump -uroot -p --all-databases --debug-check
19,--debug-info
Output debugging information and exit
$ mysqldump -uroot -p --all-databases --debug-info
20,--default-character-set
Set the default character set. The default value is utf8
$ mysqldump -uroot -p --all-databases --default-character-set=utf8
21,–delayed-insert
Data is exported in INSERT DELAYED mode
$ mysqldump -uroot -p --all-databases --delayed-insert
22,–delete-master-logs
Delete logs after master backup. This parameter will automatically activate -- master data.
$ mysqldump -uroot -p --all-databases --delete-master-logs
23,–disable-keys
For each table, use / *! 40000 ALTER TABLE tbl_name DISABLE KEYS /; And /! 40000ALTER TABLE tbl_name ENABLE KEYS */; Statement references an INSERT statement. This allows you to import the dump file faster because it creates the index after inserting all rows. This option is only applicable to MyISAM table. It is open by default.
$ mysqldump -uroot -p --all-databases
24,–dump-slave
This option appends the binlog location and file name of the primary to the file of the exported data (show slave status). When set to 1, it will be output to the data file with the CHANGE MASTER command; When set to 2, a comment is added before change. This option will turn on -- lock all tables unless -- single transaction is specified. This option automatically turns off the -- lock tables option. The default value is 0.
$ mysqldump -uroot -p --all-databases --dump-slave=1 $ mysqldump -uroot -p --all-databases --dump-slave=2
25,–master-data
This option appends the location and file name of the binlog of the current server to the output file (show master status). If it is 1, the CHANGE MASTER command will be output; If it is 2, add annotation information before the output CHANGE MASTER command. This option will turn on the -- lock all tables option unless -- single transaction is also specified (in this case, the global read lock obtains a short time at the beginning of export; for other contents, refer to the -- single transaction option below). This option automatically turns off the -- lock tables option.
$ mysqldump -uroot -p --host=localhost --all-databases --master-data=1; $ mysqldump -uroot -p --host=localhost --all-databases --master-data=2;
26,–events, -E
Export events.
$ mysqldump -uroot -p --all-databases --events
27,–extended-insert, -e
Use INSERT syntax with multiple VALUES columns. This makes the export file smaller and speeds up the import. The default is on. Use -- skip extended INSERT to cancel the option.
$ mysqldump -uroot -p --all-databases # (cancel option) $ mysqldump -uroot -p --all-databases--skip-extended-insert
28,–fields-terminated-by
The given field is ignored in the export file. Used with the -- tab option and cannot be used with the -- databases and -- all databases options
$ mysqldump -uroot -p test test --tab="/home/mysql" --fields-terminated-by="#"
29,–fields-enclosed-by
Each field in the output file is wrapped with the given character. Used with the -- tab option and cannot be used with the -- databases and -- all databases options
$ mysqldump -uroot -p test test --tab="/home/mysql" --fields-enclosed-by="#"
30,–fields-optionally-enclosed-by
Each field in the output file is selectively wrapped with a given character. Used with the -- tab option and cannot be used with the -- databases and -- all databases options
$ mysqldump -uroot -p test test --tab="/home/mysql" --fields-enclosed-by="#" --fields-optionally-enclosed-by ="#"
31,–fields-escaped-by
Each field in the output file ignores the given character. Used with the -- tab option and cannot be used with the -- databases and -- all databases options
$ mysqldump -uroot -p mysql user --tab="/home/mysql" --fields-escaped-by="#"
32,–flush-logs
Refresh the log before starting the export. Please note: if you export multiple databases at a time (using the option -- databases or -- all databases), the logs will be refreshed one by one. In addition to using -- lock all tables or -- master data. In this case, the log will be refreshed once, and the corresponding tables will be locked at the same time. Therefore, if you intend to export and refresh logs at the same time, you should use -- lock all tables or -- master data and -- flush logs.
$ mysqldump -uroot -p --all-databases --flush-logs
33,–flush-privileges
After exporting the mysql database, issue a FLUSH PRIVILEGES statement. For proper recovery, this option should be used at any time when exporting mysql database and relying on mysql database data.
$ mysqldump -uroot -p --all-databases --flush-privileges
34,–force
Ignore SQL errors during export.
$ mysqldump -uroot -p --all-databases --force
36,–help
Display help and exit.
$ mysqldump --help
37,–hex-blob
Export BINARY string fields in hexadecimal format. You must use this option if you have BINARY data. The affected field types include BINARY, VARBINARY and BLOB.
$ mysqldump -uroot -p --all-databases --hex-blob
38,–host, -h
Host information to be exported
$ mysqldump -uroot -p --host=localhost --all-databases
39,–ignore-table
The specified table is not exported. When you specify to ignore multiple tables, you need to repeat one table at a time. Each table must specify both a database and a table name. For example: - ignore table = database.table1 -- ignore table = database.table2
$ mysqldump -uroot -p --host=localhost --all-databases --ignore-table=mysql.user
40,–include-master-host-port
Add 'master' to the 'CHANGE MASTER TO..' statement generated by -- dump slave_ HOST=<host>，MASTER_ PORT=<port>‘
$ mysqldump -uroot -p --host=localhost --all-databases --include-master-host-port
41,–insert-ignore
Use the INSERT IGNORE statement when inserting rows
$ mysqldump -uroot -p --host=localhost --all-databases --insert-ignore
42,–lines-terminated-by
Each line of the output file is divided by a given string. Used with the -- tab option and cannot be used with the -- databases and -- all databases options.
$ mysqldump -uroot -p --host=localhost test test --tab="/tmp/mysql" --lines-terminated-by="##"
43,–lock-all-tables, -x
Submit a request to lock all tables in all databases to ensure data consistency. This is a global read lock, and the -- single transaction and -- lock tables options are automatically turned off.
$ mysqldump -uroot -p --host=localhost --all-databases --lock-all-tables
44,–lock-tables, -l
Lock all tables before starting export. Lock the table with READ LOCAL to allow parallel insertion of MyISAM table. For tables that support transactions, such as InnoDB and BDB, - single transaction is a better choice because it does not need to lock the table at all. Note that when exporting multiple databases, - lock tables locks tables for each database separately. Therefore, this option does not guarantee the logical consistency between the tables in the exported file and the database. The export status of different database tables can be completely different.
$ mysqldump -uroot -p --host=localhost --all-databases --lock-tables
45,–log-error
Attach warning and error information to the given file
$ mysqldump -uroot -p --host=localhost --all-databases --log-error=/tmp/mysqldump_error_log.err
46,–max_allowed_packet
The maximum packet length sent and accepted by the server.
$ mysqldump -uroot -p --host=localhost --all-databases --max_allowed_packet=10240
47,–net_buffer_length
Cache size of TCP/IP and socket connections.
$ mysqldump -uroot -p --host=localhost --all-databases --net_buffer_length=1024
48,–no-autocommit
Wrap the table with autocommit/commit statements.
$ mysqldump -uroot -p --host=localhost --all-databases --no-autocommit
49,–no-create-db, -n
Only export data without adding a CREATE DATABASE statement.
$ mysqldump -uroot -p --host=localhost --all-databases --no-create-db
50,–no-create-info, -t
Export only data without adding a CREATE TABLE statement.
$ mysqldump -uroot -p --host=localhost --all-databases --no-create-info
51,–no-data, -d
No data is exported, only the database table structure is exported.
$ mysqldump -uroot -p --host=localhost --all-databases --no-data
52,–no-set-names, -N
Equivalent to -- skip set charset
$ mysqldump -uroot -p --host=localhost --all-databases --no-set-names
53,–opt
Equivalent to -- add drop table, - add locks, - create options, - quick, - extended insert, - lock tables, - set charset, - Disable keys. This option is enabled by default and can be disabled with -- skip opt
$ mysqldump -uroot -p --host=localhost --all-databases --opt
54,–order-by-primary
If there is a primary key or the first unique key, the records of each table are sorted. It is valid when exporting MyISAM table to InnoDB table, but it will take a long time to export.
$ mysqldump -uroot -p --host=localhost --all-databases --order-by-primary
55,–password, -p
Connection database password
56. – pipe (available for Windows system)
Connecting mysql using named pipes
$ mysqldump -uroot -p --host=localhost --all-databases --pipe
57,–port, -P
Connection database port number
# Note capital P $ mysqldump -uroot -Plocalhost -ppassword
58,–protocol
The connection protocols used include: tcp, socket, pipe, memory
$ mysqldump -uroot -p --host=localhost --all-databases --protocol=tcp
59,–quick, -q
The query is not buffered, but directly exported to standard output. The default is on. Use -- skip quick to cancel this option.
$ mysqldump -uroot -p --host=localhost --all-databases $ mysqldump -uroot -p --host=localhost --all-databases --skip-quick
60,–quote-names,-Q
Use () to cause table and column names. The default is on. Use – skip quote names ` to cancel this option.
$ mysqldump -uroot -p --host=localhost --all-databases $ mysqldump -uroot -p --host=localhost --all-databases --skip-quote-names
61,–replace
Replace INSERT INTO with REPLACE INTO
$ mysqldump -uroot -p --host=localhost --all-databases --replace
62,–result-file, -r
Output directly to the specified file. This option should be used on systems that use carriage return line feed pairs (\ \ r\n) to wrap lines (for example, DOS, Windows). This option ensures that only one row is used.
$ mysqldump -uroot -p --host=localhost --all-databases--result-file=/tmp/mysqldump_result_file.txt
63,–routines, -R
Export stored procedures and custom functions.
$ mysqldump -uroot -p --host=localhost --all-databases --routines
64,–set-charset
Add 'set names default'_ character_ Set 'to the output file. The default is on. Use -- skip set charset to turn off the option.
$ mysqldump -uroot -p --host=localhost --all-databases $ mysqldump -uroot -p --host=localhost --all-databases --skip-set-charset
65,–single-transaction
This option submits a BEGIN SQL statement before exporting data. BEGIN will not block any applications and can ensure the consistency of the database during export. It is only applicable to multi version storage engines, only InnoDB. This option and the -- LOCK TABLES option are mutually exclusive, because LOCK TABLES will make any pending transactions implicitly committed. To export large tables, use the -- quick option in combination.
$ mysqldump -uroot -p --host=localhost --all-databases --single-transaction
66,–dump-date
Add the export time to the output file. The default is on. Use -- skip dump date to turn off the option.
$ mysqldump -uroot -p --host=localhost --all-databases $ mysqldump -uroot -p --host=localhost --all-databases --skip-dump-date
67,–skip-opt
Disable the – opt option
$ mysqldump -uroot -p --host=localhost --all-databases --skip-opt
68,–socket,-S
Specify the socket file location for connecting to MySQL, and the default path is / TMP / mysql.socket
$ mysqldump -uroot -p --host=localhost --all-databases --socket=/tmp/mysqld.sock
69,–tab,-T
Create a tab split text file for each table at the given path. Note: only for mysqldump and mysqld servers running on the same machine. Note: the -- databases parameter cannot be specified when using -- tab
$ mysqldump -uroot -p --host=localhost test test --tab="/home/mysql"
70,–tables
Override the -- databases (-B) parameter to specify the table name to be exported. In later versions, table will be used instead of tables.
$ mysqldump -uroot -p --host=localhost --databases test --tables test
71,–triggers
Export trigger. This option is enabled by default and disabled with -- skip triggers.
$ mysqldump -uroot -p --host=localhost --all-databases --triggers
72,–tz-utc
Set the time zone time at the top of the export_ Zone = '+ 00:00', to ensure the correctness of TIMESTAMP data exported in different time zones or when the data is moved to other time zones.
$ mysqldump -uroot -p --host=localhost --all-databases --tz-utc
73,–user, -u
Specifies the user name for the connection.
74,–verbose, –v
Output various platform information.
75,–version, -V
Output mysqldump version information and exit
76,–where, -w
Dump only the records selected by the given WHERE condition. Note that if the condition contains spaces or characters specific to the command interpreter, be sure to reference the condition.
$ mysqldump -uroot -p --host=localhost --all-databases --where=" user='root'"
77,–xml, -X
Export XML format
$ mysqldump -uroot -p --host=localhost --all-databases --xml
78,–plugin_dir
Directory of client plug-ins for compatibility with different plug-in versions.
$ mysqldump -uroot -p --host=localhost --all-databases--plugin_dir="/usr/local/lib/plugin"
79,–default_auth
Default permissions for client plug-ins.
$ mysqldump -uroot -p --host=localhost --all-databases--default-auth="/usr/local/lib/plugin/<PLUGIN>"
Reference article link:
MySql database backup and recovery -- Summary of import and export methods using mysqldump_ helloxiaozhe's blog - CSDN blog_ Mysqldump export database MySQL :: MySQL Backup and Recovery :: 1.4.1 Dumping Data in SQL Format with mysqldump