In linux, use mysqldump to back up A mysql database into an SQL file. Phase 1: create A full backup mysqldump-hip address-uusername-ppassword-~ Name. SQL exports the entire database (structure and data) to the slave database in this case.
In linux, use mysqldump to back up A mysql database into an SQL file. Phase 1: create A full backup mysqldump-hip address-uusername-ppassword-~ /Name. SQL in this case, the entire database (structure and data) is exported for backup.
Using mysqldump in linux to back up a mysql database as an SQL File
Phase 1: Silly full 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.
Certificate -----------------------------------------------------------------------------------------------------------------------------------------------
Phase 2: adjust parameters appropriately to achieve different backup Effects
Let's first help
Mysqldump -- help
Too many help outputs will not be listed one by one.
Remote connection and backup
-H: the address of the backup server.
-U: accounts that allow remote connection
-P: password of the remote connection account
-A full backup
Common Parameters
-A, -- all-databases: Back up all databases
-- Add-drop-database: add 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. (A more compact and faster insert statement is provided ). Multiple insert statements are generated to increase 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 directly output records from server queries rather than cache all records into memory.
-T, -- no-create-info only exports data
-D, -- no-data only exports the structure
Certificate ----------------------------------------------------------------------------------------------------------------------------------------------
Phase 3: Create a database and a table for the database
Mysqldump-h IP address-uusername-ppassword databasename> ~ /Name. SQL
Mysqldump-h IP address-uusername-ppassword databasename tablename> ~ /Name. SQL
Certificate ----------------------------------------------------------------------------------------------------------------------------------------------
Phase 4.1:
By reading text, database shards are used to back up databases into different SQL files.
For example, we have db1 db2 db3 db4 db5 ...... Db100 when the data volume in each database is not small, we need to back up data separately by database,
It is impossible for us to write 100 backup scripts in 100 databases separately, so we need to combine the loop statements in the shell script to operate.
Cat filename.txt | while read I
Do
Backup script, for example, mysqldump-h IP address-uusername-ppassword $ I> ~ /Name. SQL
Done
In this example, the name of the database is saved in filename.txt, with one row.
In combination with the linux crond service, we can automatically back up data.
Phase 4.2
If you need database/table sharding, what should you do?
In this case, the table file generated for each database is named db1.txt db2.txt db3.txt db4.txt db5.txt ....... Db100.txt
Back up database/table sharding through two cycles
Cat filename.txt | while read I
Do
Mkdir $ I
Cat polici.txt | while read
Do
Backup script, for example, mysqldump-h IP address-uusername-ppassword $ I $ a> $ I/$ a. SQL
Done
Done
Of course, we sometimes need to record the backup time
Fn = $ (date + "% Y-% m-% d _ % H: % M ")
Cat filename.txt | while read I
Do
Mkdir $ I
Cat polici.txt | while read
Do
Backup script, for example, mysqldump-h IP address-uusername-ppassword $ I $ a> $ I/$ a $ fn. SQL
Done
Done
Certificate --------------------------------------------------------------------------------------------------------------------------------------------------
Phase 5:
After the first phase, our backups are more detailed. If there is a problem with that part of the data, we will restore that part (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. On the U.S. server, we have to add a row to the database file to create a new database.
When creating a new table, we can add one to the table file under a database. This is tedious, error-prone, and easy to forget, which makes maintenance very troublesome.
Now we have a way for him to automatically list the warehouse and tables in the database.
Mysql-e
Run
Mysql-e "show databases"
The following result is displayed.
+ -------------------- +
| Database |
+ -------------------- +
| Information_schema |
| Mysql |
| Performance_schema |
| Test |
+ -------------------- +
However, we cannot use such a result. We need to remove things from the perimeter.
Mysql-e "show databases" | sed '1d'
Information_schema
Mysql
Performance_schema
Test
For db in 'mysql-e "show databases" | sed '1d''
Do
Backup script, for example, mysqldump-h IP address-uusername-ppassword $ db> name. SQL
Done
With this reference, we can also back up database/table sharding.
Mysql databasename-e "show tables" | sed '1d'
Fn = $ (date + "% Y-% m-% d _ % H: % M ")
For db in 'mysql-e "show databases" | sed '1d''
Do
Mkdir $ db
For 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. SQL
Done
Done
Please correct me for the time being. If you have other better ways, please leave a message and make progress together.
This article is from the "Tian Ma xingkong" blog. Please keep this source