MySQL Backup and restore Series II: CP Cold Backup and Mysqldump

if you want to back up, make sure that MySQL opens Log-bin, with Binarylog,mysql to do full recovery when necessary, or point-in-time recovery, or location-based recovery.

My data storage directory is:

mysql> SHOW VARIABLES like '%datadir% '; +---------------+-----------------+| variable_name | Value |+---------------+-----------------+| DataDir | /var/lib/mysql/|+---------------+-----------------+

1. Set up a directory to store binary logs

[Email protected] ~]# mkdir/mybinlog[[email protected] ~]# chown Mysql:mysql/mybinlog

2. Edit the configuration file, enable the binary log

[[email protected] ~]# vi/etc/my.cnf### binary log directory and filename prefix log-bin =/mybinlog/mysql-bin## then restart mysqld service [[email protected] ~]# Service mysqld Restart

3. View the generated Binlog log

[Email protected] ~]# ls/mybinlog/mysql-bin.000001 Mysql-bin.index

4. Prepare test data

mysql> create database mydb;mysql> use mydb;###  Create  myisam  Engine Table Mysql> create table myisam_tbl (    ->      id INT NOT NULL AUTO_INCREMENT,    ->      name varchar (+),     ->     primary key (ID)     -> )  engine=myisam default charset=utf8;mysql> insert  INTO MYISAM_TBL (name)     -> values (' One '), (' Joy '), (' Li '), (' Tom '), (' Jerry '), (' Hello ');###  a good way to build data     mysql> insert into myisam_tbl (name )     -> select name from myisam_tbl;    ###   Create a table mysql> create table innodb_tbl for the InnoDB engine (    ->      id INT Not null auto_increment,    ->     name varchar (+),     ->     primary key (ID)     - > )  engine = innodb DEFAULT charset=utf8;#  myisam_tbl data to Innodb_ TBL Table MYSQL> INSERT INTO INNODB_TBL (name)      -> SELECT  name from myisam_tbl;

Direct copy of database file (file system Backup tool CP), physical backup (suitable for small database)

Standard process : Lock table, refresh table to disk, stop service, copy file, unlock

Cold Backup steps:

Backup:

1. Stop the MySQL service and back up the MySQL data files at the operating system level.

2. Restart the MySQL service and back up the binlog that were generated after the reboot.

Recovery:

1. Stop the MySQL service and restore the MySQL data file at the operating system level.

2. Restart the MySQL service and use Mysqlbinlog to recover the binlog since the backup.

1, in the Terminal 1

# refresh, open read lock mysql> FLUSH TABLES with read lock; Query OK, 0 rows Affected (0.00 sec)

2, in the Terminal 2

# Create backup directory [[email protected] ~]# mkdir/mnt/mysql-$ (date +%f) # Copy all data files in archive mode [[email protected] ~]# cp-a/var/lib/mysql/*/ mnt/mysql-2014-07-21/

3. Back to the first terminal

# unlock mysql> UNLOCK TABLES; Query OK, 0 rows affected (0.10 sec)

4, the Simulation database corruption, delete all the original data files

[[email protected] ~]# rm -rf /var/lib/mysql/*#  then close the MySQL service [[email  protected] ~]# service mysqld stop###  I was installed via Yum MySQL, it is possible to stop the service smoothly. #  If you can't stop  error! mysql server pid file could not be found!  ,  so [[email protected] ~]# ps -ef | grep mysql[[email  protected] ~]# killall mysqld## #进行数据库初始化 #1  compiled and installed &nbsp, please specify parameters according to the actual situation [[email protected]  ~]# /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql/ --datadir=/ Mydata/data/ --user=mysql#2 yum installed [[email protected] ~]# /usr/bin/mysql_install_db#   After initialization, our data has been lost mysql> show databases;+--------------------+| database            |+--------------------+| information_schema | |  mysql              | |  test               |+-------- ------------+3 rows in set  (0.00 SEC)

5, the recovery process, copy the fully backed up data file into the data directory

# \cp   escapes the alias, or the copy is always reminded whether to overwrite [[email protected] ~]# \cp -a /mnt/ mysql-2014-07-21/* /var/lib/mysql/#  Start Mysql[[email protected] ~]# service mysqld  startmysql> show databases;+--------------------+| database            |+--------------------+| information_schema | |  mydb               | |  mysql              | |  test               |+-------- ------------+mysql> use mydb;database changedmysql> show tables;+-------------- ---+| tables_in_mydb  |+-----------------+| innodb_tbl       ||  myisam_tbl      | | test_myisam_tbl |+-----------------+3 rows in set  (0.00 sec) 

OK, we see that the data has been restored. CP Command, the backup, fast, restore speed is almost the fastest, but the flexibility is very low, can cross the system, but the cross-platform ability is poor, suitable for small database backup!

Second, mysqldump logical backup DATABASE (full backup + increase backup, relatively slow, suitable for small database) (MyISAM is warm backup, InnoDB is hot backup)

Logical backup steps:

Backup:

1. Choose to use Mysqldump–f (flush-logs) to back up data when the system is idle, such as at night

Library.

# mysqldump–u root–p*** pointcard–f > Pointcard.sql

2. And back up the binlog generated after mysqldump started.

Recovery:

1. Stop the application and perform the MySQL import backup file.

Mysql–u root–p*** Pointcard < Pointcard.sql

2. Use Mysqlbinlog to recover Binlog since mysqldump backup.

Mysqlbinlog $HOME/data/mysql-bin.123456 | Mysql-u root–p***

Mysqldump-u-h-p--all-databases--databases <dbname>--events #备份事件--flush-logs #在数据库导出之前先执行FLUSH Logs--lock-all-tables #锁定所有表--lock-tables #锁定某些表--master-data=n # (Specify backup point: Log fiel name and position)--opt # (P ITR accurate recovery, point-in-time recovery)--routines # (Backup stored procedure, storage function)--triggers # (Backup trigger)--single-transaction# (provides backup for transactional database)--flush-logs # Roll Dynamic Log--where # (Specify filter criteria, only back up qualifying data)

Because the mysqldump differs for different engines, it is important to understand the engine type

Win Bei:

In using the MyISAM engine, only warm backups are used, which prevents the data from being written, so the read lock is added first. You can enter the database manually to read the lock. This is more troublesome, there is a lock option directly in the Mysqldump tool

# mysqldump--databases mydb--lock-all-tables--flush-logs>/tmp/backup-' Date +%f-%h-%m '. sql### Back up all libraries (full backup) # Mysqldump--all-databases--lock-all-tables--routines--triggers--events--master-data=2--flush-logs >/root/ mybackup/' Date +%f-%h-%m '. Full.sql

If you are backing up a table, just add the table name after the database name.

# mysqldump-uroot-p MyDB Course >/root/mydb.sql Backup table course# mysqldump-uroot-p--databases mydb dnsdata >/root/ Mydb.sql Backup of two databases simultaneously

Note here that to achieve a point-in-time recovery, plus the--flush-logs option, after restoring using the backup file, and then making a point-in-time recovery based on the binary log

Hot Spare
If you are using the InnoDB engine, you do not have to perform a lock on the database, plus an option to do hot backup:--single-transaction

# mysqldump--databases mydb--single-transaction--flush-logs--master-data=2 >/tmp/backup-' Date +%f-%h-%m '. sql

Experiment:

1. Binlog Log before backup

Mysql> Show Master status;+------------------+----------+--------------+------------------+| File | Position | binlog_do_db | binlog_ignore_db |+------------------+----------+--------------+------------------+|      mysql-bin.000005 |              106 |                  | |+------------------+----------+--------------+------------------+

2. Back up all libraries ( full backup )

[Email protected] ~]# mysqldump-uroot-p--all-databases--lock-all-tables--routines--triggers--events--master-data =2--flush-logs >/root/mybackup/$ (Date +%f-%h-%m). Full.sql[[email protected] ~]# ls mybackup/2014-07-21-16-53. Full.sql

3, check the Binlog after the backup, mainly the--flush-logs option

Mysql> Show Master status;+------------------+----------+--------------+------------------+| File | Position | binlog_do_db | binlog_ignore_db |+------------------+----------+--------------+------------------+|      mysql-bin.000006 |              106 |                  | |+------------------+----------+--------------+------------------+

4, we insert a few new data to the MYISAM_TBL table

Mysql> INSERT into MYISAM_TBL (name), VALUES (' new-1 '), (' New-2 '), (' new-3 '); # view binlogmysql> Show Master Stat us;+------------------+----------+--------------+------------------+| File | Position | binlog_do_db | binlog_ignore_db |+------------------+----------+--------------+------------------+|      mysql-bin.000006 |              263 |                  | |+------------------+----------+--------------+------------------+

5. Backup Binlog ( incremental backup )

[Email protected] ~]# cp/mybinlog/mysql-bin.000006/root/mybackup/$ (date +%f-%h-%m). binlog.000006

6, Analog error operation (delete myisam_tbl table)

mysql> drop table myisam_tbl; Query OK, 0 rows affected (0.01 sec)

7. Recovery

Turn off binary logging of the recovery process before recovering, because it is meaningless to record the recovery statement

mysql> set sql_log_bin=0;        --  Close Binlogquery  OK, 0 rows affected  (0.00 sec) mysql> \. /root/mybackup/ 2014-07-21-16-53.full.sqlmysql> use mydb;mysql> show tables;+-----------------+|  tables_in_mydb  |+-----------------+| innodb_tbl      | |  myisam_tbl      | |  test_myisam_tbl |+-----------------+mysql> select *           from myisam_tbl         order  by id desc         limit 5;+----+-------+|  id | name  |+----+-------+| 12 | hello | |  11 | jerry | |  10 | tom   | |   9 | li    | |   8 | joy   |+----+-------+

OK, it is now back to the full backup state, but the last three data we inserted is not recovered.

[Email protected] ~]# mysqlbinlog/root/mybackup/2014-07-21-17-02.binlog.000006 | Mysql-uroot-p mydbenter password:mysqlbinlog:unknown variable ' Default-character-set=utf8 '

Mysqlbinlog Error:unknown variable ' Default-character-set=utf8 '

it ' s because inside the my.cnf got
Default-character-set=utf8

The default-character-set is deprecated in 5.5. We should use instead:

Character-set-server = UTF8

or add --no-defaults

mysqlbinlog --no-defaults -v logbin-log.000003 > logbin003.sql

==============end=================

[Email protected] ~]# Mysqlbinlog--no-defaults/root/mybackup/2014-07-21-17-02.binlog.000006 |        Mysql-uroot-p mydbmysql> set sql_log_bin=1; --Open Binlogquery OK, 0 rows Affected (0.00 sec) mysql> SELECT * from MYISAM_TBL ORDER by ID DESC LIMIT 5;+----+-------+ | ID | Name |+----+-------+| 15 | new-3 | | 14 | new-2 | | 13 | new-1 | | 12 | Hello | | 11 | Jerry |+----+-------+

OK, restore success.

Point-in-time recovery:

1. If an error occurs at 10 o'clock in the morning today, you can use the following statement to restore the data to a backup and Binglog

Before the barrier:

Mysqlbinlog--no-defaults--stop-date= "2014-07-24 9:59:59"/var/log/mysql/bin.123456 | Mysql-u root–pmypwd

2. Skip the point in time of failure, proceed to the back Binlog, complete the recovery

Mysqlbinlog--no-defaults--start-date= "2014-07-24 10:01:00"/var/log/mysql/bin.123456 | Mysql-u root-pmypwd

Location Recovery:

Similar to point-in-time recovery, but more precise, the steps are as follows:

Mysqlbinlog--no-defaults--start-date= "2014-07-24 9:55:00"--stop-date= "2014-07-2410:05:00"/var/log/mysql/ bin.123456 >/tmp/mysql_restore.sql

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/41/08/wKioL1PQg5ODXDqEAAHBOfJYF9M271.jpg "title=" 9.png " alt= "Wkiol1pqg5odxdqeaahbofjyf9m271.jpg"/>

This command creates a small text file in the/tmp directory, edits the file, and locates the position number before and after the error statement.

For example, the front and rear position numbers are 368312 and 368315 respectively. After restoring the previous backup file, you should start from the command

Line, enter the following:

Mysqlbinlog--no-defaults--stop-position= "368312"/var/log/mysql/bin.123456 | Mysql-u root-pmypwdmysqlbinlog--no-defaults--start-position= "368315"/var/log/mysql/bin.123456 | Mysql-u root-pmypwd

The 1th row above reverts to all transactions until the stop location. The next line resumes from the given starting position

All transactions until the binary log ends. Because the output of Mysqlbinlog includes each SQL statement record

Before the SET TIMESTAMP statement, the recovered data and the associated MySQL log will react to the original time of the transaction execution

Room

Watch out.
Restore the moment to close the binary log

Mysql>set sql_log_bin=0;

Because this is based on a logical backup, SQL statements are executed to insert data when the log is restored, and the log that is inserted into the data during recovery is meaningless.

This article is from the "Share Your Knowledge" blog, so be sure to keep this source http://skypegnu1.blog.51cto.com/8991766/1529586