Using mysqldump in linux to back up a mysql database as an SQL File

Using mysqldump in linux to back up a mysql database as an SQL File

Phase 1: Completely backup mysqldump-h IP address-uusername-ppassword-A> ~ /Name. SQL in this case, the entire database (structure and data) is exported and backed up into an SQL file -------------- Phase 2: to adjust the parameters to achieve different backup effects, let's first help mysqldump -- help because there are too many help outputs, so we will not list them one by one. Using remote connection to backup-h requires the backup server address-u allows remote connection. connection account-p allow remote connection account password-A description of common parameters for full backup-, -- all-databases backup all databases -- add-drop-database adds a drop database before each create database statement. -- Add-drop-table: add a drop table before each create table statement. -- Add-locks adds lock tables before each TABLE is exported and then unlock table. (To make it faster to insert data to MySQL) -- default-character-set = name: set the character set of the exported data. -- opt is the same as -- quick -- add-drop-table -- add-locks -- extended-insert -- lock-tables. You should be given the fastest export possible for reading a MySQL server. -E, -- extended-insert use the new multiline INSERT syntax. Provides more compact and faster insert statements ). The generated file insert statement contains multiple tasks, which increases the import speed -- hex-blob exports binary string fields in hexadecimal format. This option is required if binary data exists. The affected field types include BINARY, VARBINARY, and BLOB. -- Quick,-q this option is useful when exporting large tables. It forces mysqldump to retrieve records from the Server query and output them directly, instead of caching all records into memory-t, -- no-create-info only exports data-d, -- no-data only exports structure --------------- Stage 3: back up and define a database and a database table mysqldump-h IP address-uusername-ppassword databasename> ~ /Name. sqlmysqldump-h IP address-uusername-ppassword databasename tablename> ~ /Name. SQL statement ------------- stage 4.1: the database is backed up in different databases by reading text. For example, we have db1 dbdb3 db4 db5, which is a database ...... Db100 when the data volume in each database is not small, we need to back up the data separately by database. We cannot write 100 backup scripts in 100 databases to run them separately, in this way, we need to combine the loop statement in the shell script to operate cat filename.txt | while read ido backup script, for example, mysqldump-h IP address-uusername-ppassword $ I> ~ /Name.sqldoneits filename.txt stores the database name, one in each row. Mongodb2.txt db3.txt db4.txt db5.txt ....... Db100.txt performs database/table sharding backup using two cycles: cat filename.txt | while read idomkdir $ icat polici.txt | while read ado backup script, for example: mysqldump-h IP address-uusername-ppassword $ I $ a> $ I/$. sqldonedone, of course, sometimes we need to record the backup time fn =$ (date + "% Y-% m-% d _ % H: % M ") cat filename.txt | while read idomkdir $ icat polici.txt | while read ado backup script, for example, mysqldump-h IP address-uusername-ppassword $ I $ a> $ I/$ a $ fn. sqldonedone -------------------------------------------------- Phase 5: after Phase 5, our backups are more detailed, and we will recover the data that has gone wrong (although the granularity is still a bit rough) however, do we think that the backup in the 4th stage is still very complicated? For example, the database name and the indicated file are a huge project, to create a new database, we have to add a new table to the database file. We can add a new table to the table file under a database. This is tedious and error-prone, and is easy to forget, it is difficult to maintain. Now we have a way to let him automatically perform mysql-e "show databases" in the Database and the table mysql-e under the Database to get the following results + ---------------------- + | Database | + -------------------- + | information_schema | mysql | performance_schema | test | + -------------------- + but this result cannot be used, mysql-e "show databases" | sed '1d 'information _ schemamysqlperformance_schematest for db in 'mysql-e "show databases" | sed '1d'' do backup script, for example, mysqldump-h IP address-uusername-ppassword $ db> name. sqldone can also back up mysql databasename-e "show tables" | sed '1d 'fn = $ (date + "% Y-% m-% d _ % H: % M ") for db in 'mysql-e "show databases" | sed '1d ''domkdir $ dbfor tables in 'mysql $ db-e" show tables "| sed '1d'' do backup script, for example, mysqldump-h IP address-uusername-ppassword $ db $ tables> $ db/$ tables $ fn. sqldonedone has been completed. please correct me more. If you have other better ways, please leave a message and make progress together.

This article is from the "tianma xingkong" blog, please be sure to keep this source http://zhongnan83.blog.51cto.com/5988158/1137166