MySQL backup and restore details under Centos 6.4

MySQL backup and restore details under Centos 6.4

We described in the previous article in CentOS 6.4 installation and configuration of MySQL, after installation in order to ensure the security of data so we also have to learn to back up and restore, backup and restore operations in our real work environment is essential, due to database data backup, So today we'll introduce you to the backup and restore operations of MySQL under Centos 6.4. Our two articles are introduced for the following MySQL master-slave configuration to pave the way, so the environment with an article is the same, this environment is the previous article installed in the two cnetos. 6.4+mysql Server, Today's main presentation is to restore the DB1 on the 192.168.6.28 to the DB on the 192.168.6.38 server after the backup is ready

As we said earlier, MySQL data files are stored in

/var/lib/mysql under

650) this.width=650; "title=" clip_image001 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "clip_image001" src= "http://s3.51cto.com/wyfs02/M00/86/53/wKioL1e7-P2hqbBGAABwia4Vvgg079.png" height= "/>"

First, the general backup database:

Mysqldump-hlocalhost-uroot-p DB1 > DB1.SQLDB1 database name for backup

650) this.width=650; "title=" clip_image002 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "clip_image002" src= "http://s3.51cto.com/wyfs02/M00/86/53/wKioL1e7-P6A9G9SAAB5CatI3Fs919.png" height= "/>"

Restore the database:

Mysql-hlocalhost-uroot-p DB1 < db1.sqldb1 name of the restored database

650) this.width=650; "title=" clip_image003 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "clip_image003" src= "http://s3.51cto.com/wyfs02/M01/86/53/wKioL1e7-P6RBPqwAABezKr-0hs319.png" height= "/>"

Second, MySQL database compression backup

Mysqldump-hlocalhost-uroot-p DB1 | gzip > DB1.sql.gz

650) this.width=650; "title=" clip_image004 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "clip_image004" src= "Http://s3.51cto.com/wyfs02/M02/86/53/wKioL1e7-P-wjULuAAB9lhWGdrk908.png" height= "122"/>

Restore compressed MySQL database Gunzip < db12016-08-23.sql.gz | Mysql-uroot-p DB1

650) this.width=650; "title=" clip_image005 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "clip_image005" src= "http://s3.51cto.com/wyfs02/M00/86/54/wKiom1e7-QDy7hXxAABSR3gBzfo164.png" height= "/>"

Third, we then use the script to compress the backup

#!/bin/shdbname= "DB1" source= "/root/mysqldb_bak" date_str= ' date +%y-%m-%d_%h:%m:%s ' mysqldump -h  localhost -u root --password=beyondsoft -r -e -e --max_allowed_packet= 1048576 --net_buffer_length=16384  $DBName  | gzip >  $Source/$DBName $date_ str.sql.gzecho  "$ (date +%y-%m-%d_%h:%m:%s)  - Backup directory:DBName:  $DBName  to  $Source/$DBName. sql.gz is backup sucess " >>/var/log/$DBName. log#!/bin/ Shdbname= "DB1" source= "/root/mysqldb_bak" #date_str = ' date +%y-%m-%d-%h:%m:%s ' date_str= ' date +%y-%m-%d_ %h-%m-%s ' date_str= ' date +%y-%m-%d ' mysqldump -h localhost -u root --password= beyondsoft -r -e -e --max_allowed_packet=1048576 --net_buffer_length=16384 $ dbname | gzip >  $Source/$DBName $date_str.sql.gzecho  "$ (date +%y-%m-%d_%h:%m:% S)  - backup directory:dbname:  $DBName  to  $Source/$DBName. sql.gz is backup sucess " >>/ var/log/$DBName. Log

Log is added, and if successful, a log log will be generated in/var/log/db1.log

650) this.width=650; "title=" clip_image006 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "clip_image006" src= "http://s3.51cto.com/wyfs02/M00/86/53/wKioL1e7-QDQed2kAAC05VMzXJI307.png" height= "/>"

After execution, the backup succeeds

650) this.width=650; "title=" clip_image007 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "clip_image007" src= "Http://s3.51cto.com/wyfs02/M02/86/53/wKioL1e7-QHw66mlAABJwQ7GrZc528.png" height= "the"/>

Then remotely copy the backed up compressed file to the destination computer for restore

SCP db12016-08-22_16.46.17.sql.gz [Email protected]:/

650) this.width=650; "title=" clip_image008 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "clip_image008" src= "http://s3.51cto.com/wyfs02/M00/86/53/wKioL1e7-QLBwuNwAAAp8b5Ngio580.png" height= "/>"

Restore the database;

We need to note that our 192.168.6.38 server only has the MySQL service and configured the account and password, so we only need to restore the MySQL backup data on the 192.168.6.28 to 192.168.6.38;

After the copy data is finished, we use decompression to extract the compressed file to the database data file path strength;

We're on the 192.168.6.38, access the database data file.

Cd/var/lib/mysql

650) this.width=650; "title=" clip_image009 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "clip_image009" src= "Http://s3.51cto.com/wyfs02/M01/86/53/wKioL1e7-QODuA4gAAGaSCOfGFs675.png" height= "303"/>

To restore directly from a compressed file:

Gunzip < db12016-08-22_16.46.17.sql.gz | Mysql-u root-p <databasename>

If the target does not have a database name backed up, you need to create a library that is the same as the backup database;

650) this.width=650; "title=" clip_image010 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "clip_image010" src= "http://s3.51cto.com/wyfs02/M01/86/54/wKiom1e7-QOxGHyDAAAy2x-4Vwo016.png" height= "Si"/>

Note: DB1 is the database information that needs to be restored; restore file copy to database file path strength

ls/var/lib/mysql/

650) this.width=650; "title=" clip_image011 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "clip_image011" src= "Http://s3.51cto.com/wyfs02/M02/86/54/wKiom1e7-QSiiDCMAABouu_AUgE492.png" height= "94"/>

Gunzip < db12016-08-23.sql.gz | Mysql-uroot-p Db1gunzip < db12016-08-22_16.46.17.sql.gz | Mysql-u root-p <databasename>

650) this.width=650; "title=" clip_image012 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "clip_image012" src= "http://s3.51cto.com/wyfs02/M02/86/53/wKioL1e7-QSTZwQlAABSS9Yy9p0416.png" height= "/>"

Finally, let's check

SELECT * from info;

650) this.width=650; "title=" clip_image013 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px, "border=" 0 "alt=" clip_ image013 "src=" Http://s3.51cto.com/wyfs02/M00/86/53/wKioL1e7-QXiFr1HAAC3UCX4gWg219.png "height=" 292 "/>

This article from "Gao Wenrong" blog, declined reprint!

MySQL backup and restore details under Centos 6.4