MySql Backup Recovery and mysql Backup Recovery

Source: Internet
Author: User
Tags mysql backup pscp

MySql Backup Recovery and mysql Backup Recovery

Mysql backup restoration method

We generally perform the following operations to back up and restore mysql databases (generally from dozens to hundreds of GB) to windows machines, then, the files on the windows machine are restored to the mysql database of the linux system.

This document is mainly used to manipulate large databases to be backed up (if the database is small, there are many other simple methods ). In reality, we generally perform Step 1 at the site, and step 3 and Step 3 at the company.

1. the operation scenario in this article: Back up the mysql database pqss0215old on 192.168.1.207 (centos) to the local machine (win7), and then upload the backup file to 192.168.1.207 (centos ), then, restore the backed up database to pqss0215old2.

2. Required Software: pscp.exe (used to transfer files between linux and win7) and SecureCRT (used to remotely connect to the linux system, and the compressed package address of pscp and crt at the end of the document)

3. Procedure:

. The first step is to back up the mysql database:

3.1.1 run the 192.168.1.207.mysqldump command on the SecureCRT remote connection to back up data. The user name is root and the password is 123456. The database to be backed up is pqss02155old, which is backed up to the pqss0215old1. SQL file.

Mysqldump-uroot-p123456 pqss0215old>/usr/pqss0215old1. SQL

Switch to the backup folder and you can see the backup file pqss0215old1. SQL.

3.1.2 compress the database file backed up just now. It may take several minutes for gzip to compress. When the # sign is displayed, the compress is complete.

3. 2. Transfer the compressed file to the local device.

3.2.1 use the software pscp.exe to pass the compressed file to the local edisk: root is the username for logging on to 207, and enter the password for the root username.

After the task is completed, switch to the Elastic Block Storage (edisk) and you will see the uploaded files.

3.3 pass the compressed package to the linux System

3.3.1 transfer the file just passed on the elastic drive to 192.168.1.207

Modify the name of a file named pqss0215old2. SQL .gz to be unique to the original file.

Pscptransfers pqss0215old2. SQL .gz to/usr/of the 207 system.


In this case, check the/usr/Under 192.168.1.207 and you can see the file that was just passed in.


3.4 restore the backed up database to the new database pqss0215old2

3.4.1 create a database pqss0215old2

3.4.2 decompress the database file just passed in

3.4.2 restore pqss0215old2. SQL to the database pqss0215old2



Position of pscp and SecureCRT: http://download.csdn.net/download/luozuolincool/7984279



Mysql backup and recovery problems

You can copy. frm,. MYI, And. MYD files for MyISAM tables between different architectures that support the same floating point format. (MySQL focuses on all byte exchanges ). If you need to transfer databases between different architectures, you can use mysqldump to create files containing SQL statements. Then you can transfer the file to another machine and input it to the MySQL client. This is in mysql5.1 reference.

Mysqldump-u 'user'-p-R-'bdatabase'-h 'host'>/mysqldata/testdata080409. SQL

Mysql-u 'user'-p testdata </mysqldata/testdata090409. SQL
INNODB tables should not be backed up by directly copying physical files.
Mysqldump -- help to view parameter configuration
What's wrong?
Is there no such program?
Is it WINDOWS? Change the system variable.
Add the BIN directory of MYSQL
For details, see hi.baidu.com/..5.html.
If LINUX or UNIX is used, I want to see the error.
It should be-R-B 'database'. The preceding error occurs --
-The R parameter indicates the stored procedure of the database to be backed up. The function does not add to copy only tables.
-B is the database for backup.
If you only copy the database structure and add-d to it, the data will not be copied.

Mysql backup and recovery problems

You can copy. frm,. MYI, And. MYD files for MyISAM tables between different architectures that support the same floating point format. (MySQL focuses on all byte exchanges ). If you need to transfer databases between different architectures, you can use mysqldump to create files containing SQL statements. Then you can transfer the file to another machine and input it to the MySQL client. This is in mysql5.1 reference.

Mysqldump-u 'user'-p-R-'bdatabase'-h 'host'>/mysqldata/testdata080409. SQL

Mysql-u 'user'-p testdata </mysqldata/testdata090409. SQL
INNODB tables should not be backed up by directly copying physical files.
Mysqldump -- help to view parameter configuration
What's wrong?
Is there no such program?
Is it WINDOWS? Change the system variable.
Add the BIN directory of MYSQL
For details, see hi.baidu.com/..5.html.
If LINUX or UNIX is used, I want to see the error.
It should be-R-B 'database'. The preceding error occurs --
-The R parameter indicates the stored procedure of the database to be backed up. The function does not add to copy only tables.
-B is the database for backup.
If you only copy the database structure and add-d to it, the data will not be copied.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.