Shulou(Shulou.com)05/31 Report--

This article mainly introduces "data backup and recovery of Mysql". In daily operation, I believe many people have doubts about data backup and recovery of Mysql. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "data backup and recovery of Mysql". Next, please follow the editor to study!

Backup classification of Mysql:

Data backup for mysql:

Logical backup, physical backup

Mysql is classified by dataset:

Full backup

Incremental backup

Differential backup

Generally, these are used in combination. For example, a full backup can be carried out once a week, and incremental backup can be made.

Copies and differential backups can be backed up once a day, but it depends on the business logic of the enterprise, so

The right choice will minimize the loss when the data is recovered.

Mysql is classified by whether the server is down or not:

Cold backup: service stops completely

Warm backup: the service is not turned off, but at the time of backup, other transactions are only queried.

Hot backup: the service is online and does not affect read and write (but the non-transactional storage engine is difficult to implement hot backup, but

It is possible to use LVM to achieve almost hot backup)

What needs to be backed up:

1. Data file

two。 Log files (transaction logs, binary logs)

3. Stored procedure, stored function, trigger

4. Configuration file

Log classification:

1. Transaction log:

Conversion of random IO to sequential IO

Event replay (undo) provides the basis

two。 Event transaction:

Record historical time

Log file for MySQL

1.the error log error log

Error message, information generated by the start or stop of a server process from which the master adds replication

Wait, it will be recorded.

The main profile starts the error log

Log_error=/var/log/mysqld.err (make sure you have write access to mysqld)

2.the binary log binary log

Provide incremental backup

Point-in-time based recovery

Replication architecture provides the foundation

Log_bin=/data/logs/binary/changelog (default in data file)

Log_bin_index=/data/logs/relay/binarylog defines the index of binaries

Position

3.the general query log is usually recommended not to open.

General_log=ON | OFF

General_log_file=/PATH

4.the slow query log slow query log

Slow_query_log=ON

Slow_query_log_file=/PATH

Long_query_time=NUM defines the timeout

5.relay log Relay Lo

No logging is enabled by default Mysql, but error logging is enabled in the mysqld script (the

Error log)

Common backup tools for mysql:

Mysqldump

SELECT INTO OUTFILE

Mk-parallel-backup

Ibbackup

Mysqlhostcopy (almost cold backup of MyISAM storage engine, looks like a hot backup)

Next, I will demonstrate the process of backing up and restoring data with a simple example of mine:

1. Use dump to back up

The backup of mysqldump is a logical backup

The backup format is as follows

# mysqldump db_name [table_name] > / PATH

Of course, there are many options for mysqldump backup:

-- all-databases backs up all databases

-- events backup event

-- routines backup storage function

-- triggers backup trigger

-- insertion of extended-insert backup extension

-- skip-extended-insert turns off the insertion of backup extensions

-- lock-all-tables lock, this is necessary

-- it is also necessary for flush-logs to refresh log files to disk

-- master-data=n {0 | 1 | 2} is used for replication (2)

-- no-data only backs up the table structure in the database

-- where "WHERE CLAUSE" backup specified data

Mysql > CREATE DATABASE luowei

Mysql > SHOW DATABASES;// view all databases

+-+

| | Database |

+-+

| | information_schema |

| | luowei |

| | mysql |

| | ndodb |

| | test |

+-+

Mysql > USE luowei

Mysql > CREATE TABLE st (

-> ID INT UNIQUE AUTO_INCREMENT

-> Name VARCHAR (30)

Mysql > SHOW TABLES; / / View the table

+-+

| | Tables_in_luowei |

+-+

| | st |

+-+

Mysql > INSERT INTO st (Name) VALUE ('a'), ('b'), ('c')

Mysql > SELECT * FROM st; / / display the data in the table

+-+ +

| | ID | Name |

+-+ +

| | 1 | a |

| | 2 | b | |

| | 3 | c |

+-+ +

This is the original information in my database, because the database and tables will be deleted later, so take a look at it first.

The data in it will be verified later.

[root@nfs ~] # mysqldump-uroot-p luowei st > / root/st.sql / / start

Back up the table st, which does not use any parameter

Enter password:

OK, the backup is finished at this time. Because mysqldump uses logical backup, it is backed up.

Are some create, insert statements

We can open it and have a look.

[root@nfs ~] # cat / root/luoweidb | grep-v "^ /" | grep-v "^ $"

-- MySQL dump 10.11

--

-- Host: localhost Database: luowei

-

-- Server version 5.0.77

--

-- Current Database: `luowei`

--

CREATE DATABASE / *! 32312 IF NOT EXISTS*/ `luowei` / *! 40100 DEFAULT

CHARACTER SET latin1 * /

USE `luowei`

--

-- Table structure for table `st`

--

DROP TABLE IF EXISTS `st`

SET @ saved_cs_client = @ @ character_set_client

SET character_set_client = utf8

CREATE TABLE `st` (

`ID`int (11) NOT NULL auto_increment

`Name` varchar (30) default NULL

UNIQUE KEY `ID` (`ID`)

) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1

SET character_set_client = @ saved_cs_client

--

-- Dumping data for table `st`

--

LOCK TABLES `st` WRITE

INSERT INTO `st` VALUES (1), (2)), (3)

UNLOCK TABLES

-- Dump completed on 2011-09-05 8:00:08

See, this is the whole statement that I just did to create a database, create a table, and insert data.

Next, let's do a database recovery:

[root@nfs ~] # mysql-uroot-e "DROP DATABASE luowei;"-p / / delete

Database

Enter password:

[root@nfs ~] # mysql-uroot-e "SHOW DATABASES;"-p / / View after deletion

Database of

Enter password:

+-+

| | Database |

+-+

| | information_schema |

| | mysql |

| | ndodb |

| | test |

+-+

I have deleted the luowei database and will now restore the database

[root@nfs] # mysql-uroot-p

< /root/luoweidb Enter password: [root@nfs ~]# mysql -e "SHOW DATABASES;" +--------------------+ | Database | +--------------------+ | information_schema | | hellodb | | luowei | | mysql | | ndodb | | test | +--------------------+ OK ，数据库是恢复过来了，接下来就是查看表中的信息是否完整了 [root@nfs ~]# mysql -e "SELECT * FROM luowei.st" -uroot -p Enter password: +----+------+ | ID | Name | +----+------+ | 1 | a | | 2 | b | | 3 | c | +----+------+ Ok,一起安好，呵呵，当然其他的参数就不做详细介绍了，需要用到的话， 可以使用HELP命令寻求帮助。 下面介绍一个本机不用输入Mysql密码的方法： #vim /root/.my.cnf [client] user = root password = redhat 保存起来，下次对数据库的操作就直接使用mysql命令了，不用在输入密码 了； 接下来的实验我也就不输入密码了； 2.使用SELECT INTO OUTFILE 的方法进行备份 还是用上面的表， [root@nfs ~]# mysql -e "SELECT * FROM luowei.st" +----+------+ | ID | Name | +----+------+ | 1 | a | | 2 | b | | 3 | c | +----+------+ 如果想把ID小于等于2的备份出来，就可以使用这种方法备份了 mysql>

SELECT * INTO OUTFILE'/ tmp/st.bak' FROM st WHERE ID CREATE TABLE mt LIKE st; / / I have created a new one based on the st table format

Table mt, and then restore the data just backed up to the new table mt, which is also similar to restoring to the original table, ah

Oh, the thief is lazy!

Mysql > LOAD DATA INFILE'/ tmp/st.bak' INTO TABLE mt

Mysql > SELECT * FROM mt

+-+ +

| | ID | Name |

+-+ +

| | 1 | a |

| | 2 | b | |

+-+ +

Ok, now let's summarize these two methods, first use the data backed up by mysqldump to make

In the way of logical backup, some statements for the operation of the database are backed up, which is easy to recover.

Use SELECT INTO OUTFILE to back up specified data and selectively back up data

But you have to be a little more troublesome in recovery and create tables.

3. LVM-based snapshot backups:

Procedure:

a. Make sure the data file is on the LV

b. Apply a read lock to the table in the database

c. Take a snapshot of the LV where the data is located

d. Release the read lock of the database

e. Mount snapshot volumes, back up data files (for InnoDB storage engine, and back up transaction logs

File)

f. Delete snapshot

The conditions for using LVM for snapshot backups are:

> SHOW VARIABLES LIKE 'log_binlog'; is in ON

> SHOW VARIABLES LIKE 'sync_binlog' changed to 1

> SHOW VARIABLES LIKE 'innodb_support_xa' is in the state of ON

LV snapshot backup

# mysql

> FLUSH TABLES WITH READ LOCK; / / plus read lock

# lvcreate-L 50m-n mysnap-s / dev/myvg/mydata / / create a snapshot

> UNLOCK TABLES; / / unlock

# mount / dev/myvg/mysnap / mnt / / Mount the snapshot volume

# cd / mnt/data

# tar-jcf / root/mysql.tar.bz2 luowei/ ibdata1 ib_logfile* mysql-

Bin.* / / back up data and compress

# umount / mnt / / unmount snapshot

# lvremove-- force / dev/myvg/mysnap / / remove a snapshot

LV recovery

Cd / usr/local/mysql

# chown-R mysql:mysql. / / modify permissions

# scripts/mysql_install_db-user=mysql-datadir=/mydata/data/ / /

Regenerate

# chown-R root.

# killall mysqld / / Kill all processes related to mysql

# cd / mydata/data

# tar xf / root/mysql.tar.bz2-C. / / restore the backup to the data file of mysql

Then when we log in to Mysql, we will find that the original data still exists.

4. Using binary log files to achieve point-in-time recovery

Show transaction files that are currently in use

Mysql > FLUSH TABLES WITH READ LOCK

Mysql > SHOW MASTER STATUS

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | mysql-bin.000006 | 330 | |

+-+

Mysql > FLUSH LOGS; / / scroll log file

Then look at the transaction file you are using, and then record the Position

Because the binary log file only records information about data modifications, each modification corresponds to the

Position will change.

Now I insert a row of data into the table

Mysql > INSERT INTO st (Name) VALUE ('d')

Mysql > INSERT INTO st (Name) VALUE ('E')

Mysql > SHOW MASTER STATUS

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | mysql-bin.000007 | 553 |

+-+

Since we have already made a full backup, we can now back up the

The data.

# mysqlbinlog-- start-position 330 mysql-bin.000007

> / root/mysql.incr.sql / / backup the added binaries

Then we log in to mysql at this time.

Mysql > DROP DATABASE luowei

Then restore the database

# service mysqld stop

# scripts/mysql_install_db-user=mysql-datadir=/mydata/data/

# cd / mydata/data/

# tar xf / root/mysql.incr.sql-C.

# mysql < / root/mysql.incr.sql

# service mysqld start

Log in to the database

You will find that some data have returned to normal.

At this point, the study of "data backup and recovery of Mysql" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.