MySQL mysqldump data backup and mysqlmysqldump

MySQL mysqldump data backup and mysqlmysqldump
1. mysqldump command tool description

 

Parameter annotation: mysqldump adopts an SQL-level backup mechanism. It imports data tables into SQL script files and is suitable for upgrading between different MySQL versions. This is also the most common backup method. Here are some main parameters of mysqldump: -- Compatible = nameIt tells mysqldump that the exported data will be compatible with the database or the old MySQL server. The values can be ansi, mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb, no_key_options, no_tables_options, and no_field_options. Separate them with commas. Of course, it does not guarantee full compatibility, but is as compatible as possible. -- Complete-insert-C exports data using the complete INSERT method containing the field name, that is, to write all the values in a row. This can improve the insertion efficiency, but may be affected by the max_allowed_packet parameter, resulting in insertion failure. Therefore, you need to use this parameter with caution. At least I do not recommend this parameter. -- Default-character-set = charsetSpecifies the character set used for data export. If the data table does not use the default latin1 character set, this option must be specified during data export. Otherwise, garbled characters will occur after data is imported again. -- Disable-keysTell mysqldump to add/* at the beginning and end of the INSERT statement /*! 40000 alter table table disable keys */; And /*! 40000 alter table table enable keys */; Statement, which greatly improves the speed of the insert statement because it re-creates the index after all data is inserted. This option is only applicable to MyISAM tables. -- Extended-insert = true | falseBy default, mysqldump enables the -- complete-insert mode, so if you do not want to use it, use this option to set its value to false. -- Hex-blobExport binary string fields in hexadecimal format. This option is required if binary data exists. The affected field types include BINARY, VARBINARY, and BLOB. -- Lock-all-tables,-X before starting the export, 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 disabled. -- Lock-tablesIt is similar to -- lock-all-tables, but instead of locking all tables in the database. This option is only applicable to MyISAM tables. For Innodb tables, you can use the -- single-transaction option. -- No-create-info,-T only exports data without adding the create table statement. -- No-data,-D. Only the database table structure is exported without exporting any data. -- OptThis is just a quick option, it is equivalent to adding the -- add-drop-tables -- add-locking -- create-option -- disable-keys -- extended-insert -- lock-tables -- quick -- set-charset option at the same time. This option allows mysqldump to export data quickly and export data back quickly. This option is enabled by default, but can be disabled with -- skip-opt. Note: If the -- quick or -- opt option is not specified when running mysqldump, the entire result set is stored in the memory. If you export a large database, problems may occur. -- Quick,-Q this option is useful when exporting large tables. It forces mysqldump to directly output records from server queries rather than cache all records into memory. -- Routines,-R: export the stored procedure and user-defined functions. -- Single-transactionThis option submits a begin SQL statement before exporting data. BEGIN does not block any applications and ensures Database Consistency during export. It is only applicable to transaction tables, such as InnoDB and BDB. This option and the -- lock-tables option are mutually exclusive, Because lock tables will implicitly commit any pending transactions. To export a large table, use the -- quick option.

-- TriggersExport the trigger at the same time. This option is enabled by default. Use -- skip-triggers to disable it.

 

 

2. Example

1. Export the entire database (-hex-blob is made of blob data to prevent garbled characters and import failures)

Mysqldump-u username-p Database Name> exported file name
Mysqldump-u root-p -- default-character-set = gbk -- hex-blob i5a6> i5a6. SQL

2. Export a table
Mysqldump-u user name-p database name Table Name> exported file name
Mysqldump-u root-p i5a6 users> i5a6. SQL

3. Export a database structure
Mysqldump-u root-p-d -- add-drop-table i5a6> d:/i5a6. SQL
-D no data-add-drop-table add a drop table before each create statement

4. Import the database
Common source commands
Go to the MySQL Database Console,
For example, MySQL-u root-p
Mysql> use Database

Then run the source command. The following parameter is the script file (for example,. SQL used here)
Mysql> source d:/i5a6. SQL

 

5. Shell scripts

The above are relatively scattered code. Let's look at the shell code.

First, I will bring you the complete script. The script will be described later. I suppose you already know shell scripting, mysqldump, and crontab.

Database Export CodeThe Code is as follows:

#! /Bin/bash

#1. database information Definition
Mysql_host = "192.168.1.1"
Mysql_user = "root"
Mysql_passwd = "root"
# SQL Backup Directory
Root_dir = "/backup"
Back_dir = "/backup/databases"
Data_dir = "databases"
Store_dir = "database"
If [! -D $ back_dir]; then
Mkdir-p $ back_dir
Fi
# Backup database Array
Db_arr = $ (echo "show databases;" | mysql-u $ mysql_user-p $ mysql_passwd-h $ mysql_host)
# Single-instance databases that do not require backup
Nodeldb = "test1"
# Current date
Date = $ (date-d '+ 0 days' + % Y % m % d)
# Zip package Password
Zippasswd = "passwd"
Zipname = "lczh _" $ date ". zip"

#2. Go to the backup directory
Cd $ back_dir

#3. Cyclic backup
For dbname in $ {db_arr}
Do
If [$ dbname! = $ Nodeldb]; then
Sqlfile = $ dbname-$ date ". SQL"
Mysqldump-u $ mysql_user-p $ mysql_passwd-h $ mysql_host $ dbname> $ sqlfile
Fi
Done

#4.tar package all SQL files
Tar-zcPpf $ root_dir/$ store_dir/$ zipname -- directory/& nbsp; $ root_dir/$ data_dir
# Deleting an SQL file after successful Packaging
If [$? = 0]; then
Rm-r $ data_dir
Fi

Regular data cleanup scripts

 
Function
Regularly clear backup files 14 days ago
 
The Code is as follows:

#! /Bin/bash-
#1. parameter configuration
# Mysql file backup directory
Backup_dir1 = "/backup/test1 /"
Backup_dir2 = "/backup/test2 /"
Backdir_arr = ($ backup_dir1 $ backup_dir2)
# Expiration time
Keep_time = 14
# For the current week, crontab is executed on week 7 with an odd number
Week = $ (date + % W)
Flag = 'expr $ week % 2'
 
#2. Clear expired files and only execute the command in seven days of the odd week
If [$ flag-eq 1]; then
For dir in $ {backdir_arr [*]}
Do
If [-d $ dir]; then
# Search for file data beyond 14 days
Clean_arr = 'Find $ dir-type f-mtime + $ keep_time-exec ls {};'
For cleanfile in $ {clean_arr}
Do
Rm $ cleanfile
Done
Fi
Done
Fi

Crontab Configuration
 

The Code is as follows:
0 5 ** 7 run the cleanup script

 

4. Others

Recently, When I backed up and restored mysql, I found that the view was restored and an error occurred while creating the view. I checked the information online and found the following information:

1. If the backup database contains a view, you must change character-set in my. ini to latin1 to restore the view. 2. After restoration, change latin1 to gb2312. Otherwise, the stored procedure cannot be used. 3. The stored procedure cannot be backed up along with the database. for restoration of the stored procedure, you must manually copy the SQL statement and execute it in the QUERY. I felt a little troublesome, so I looked for other methods. I used the following methods to back up the mysql database and then restore it. No error was reported, and the view was also restored: # mysql backup and restoration in windows, you do not need to modify my. ini to restore the view (verified) mysqldump-uroot-p123 -- default-character-set = gbk -- opt -- extended-insert = false -- triggers-R -- hex-blob-x db_name> f: \ db. SQL mysql-uroot-p123-f db_name <f: \ db. SQL linux: SQL to back up the MyISAM Table:/usr/local/mysql/bin/mysqldump-uroot-p123 -- Default-character-set = utf8-- Opt -- extended-insert = false -- triggers-R -- Hex-blob-X db_name> db_name. SQL: /usr/local/mysql/bin/mysqldump-uroot-p123 -- default-character-set = utf8 -- opt -- extended-insert = false -- triggers-R -- hex-blob -- single -transaction db_name> db_name. SQL

 

Time added:

Mysqldump-uroot -- default-character-set = utf8 -- hex-blob-p "kEy 31kG _" gameonline | gzip>/data/mysqlback/gameonline'Date + % Y-% m-% d _ % H % M % s'. SQL .gz

 

Recently, When I backed up and restored mysql, I found that an error occurred while restoring the view. I checked the information online and found the following information: 1. If the backup database contains a view, during restoration. change character-set in ini to latin1 to restore the view. 2. After restoration, change latin1 to gb2312. Otherwise, the stored procedure cannot be used. 3. The stored procedure cannot be backed up along with the database. for restoration of the stored procedure, you must manually copy the SQL statement and execute it in the QUERY. I felt a little troublesome, so I looked for other methods. I used the following methods to back up the mysql database and then restore it. No error was reported, and the view was also restored: # mysql backup and restoration in windows, you do not need to modify my. ini to restore the view (verified) mysqldump-uroot-p123 -- default-character-set = gbk -- opt -- extended-insert = false -- triggers-R -- hex-blob-x db_name> f: \ db. SQL mysql-uroot-p123-f db_name <f: \ db. SQL linux: SQL to back up the MyISAM table: /usr/local/mysql/bin/mysqldump-uroot-p123 -- default-character-set = utf8 -- opt -- extended-insert = false -- triggers-R -- hex-blob-x db_name> db_name. SQL uses the following SQL to back up the Innodb table: /usr/local/mysql/bin/mysqldump-uroot-p123 -- default-character-set = utf8 -- opt -- extended-insert = false -- triggers-R -- hex-blob -- single -transaction db_name> db_name. SQL