Shulou(Shulou.com)06/01 Report--

The following together to understand how to backup and restore mysql data, I believe we will certainly benefit from reading, the text is not more refined, I hope how to backup and restore mysql data this short content is what you want.

Different categories of backups:

Hot, warm and cold backups

Hot backup: reading and writing are not affected;

Warm backup: only read operations can be performed;

Cold backup: offline backup; read and write operations are suspended;

Physical and logical backups

Physical Backup: Copy data files; backup what the original data looks like on the operating system, applicable

For scenarios that are large, important, and need to be quickly recovered in case of failure

Logical backup: backup sql statement, execute the sql statement of backup once during recovery, more flexible, but faster recovery

Slow for small, easy to recover scenarios on other Cloud Virtual Machine

Full, incremental and differential backups;

Full backup: backup all data;

Incremental backup: backup only the data changed since the last full backup or incremental backup;

Differential backup: Backs up only data that has changed since the last full backup;

Backup tools:

1. Create a backup by copying object-related files

Generally applicable to myisam engine tables, as long as the.frm,.MYD,.MYI files copied and saved is equivalent to a backup

Steps:

mysql>FLUSH TABLES tb_name WITH READ LOCK #Read only lock tables before backup [root@localhost /]cp /the/path/to/data /the/path/to/backup mysql>UNLOCK TABLES Release the lock

This backup method is not applicable to Innodb tables. When innodb_file_per_table is set to 1, copying the related file.ibd of the table object cannot achieve the purpose of backup, because the table structure, database and other information still exist in Innodb's system table space.

2. Use SQL statements for backup

mysql>SELECT * INTO OUTFILE '/path/to/somefile.txt' FROM tb_name [WHERE clause];

3. Use mysqldump for logical backup

Backup a single database/table

mysqldump -uUSERNAME -p PASSWORD db_name [tb_name]> /path/to/backup/db_name.sql

Backup multiple databases

mysqldump -uUSERNAME -p PASSWORD --databases db1 db2 > /path/to/backup/db1_db2.sql

Use--tab to output txt files and sql files to separate data from table creation statements

mysqldump -uUSERNAME -p PASSWORD --tab=/path/to/backup db_name [tb_name] #Export all database objects or specific tables to backup directory

Example 1: Backup tutors table under database jiaowu

View/tmp/test2 directory

Example 2: Backup the entire database

Then look at the/tmp/test directory

It can be found that each table in the database generates two files with the same name,.txt stores the actual data in the table, and.sql stores sql statements (table creation statements).

Check out tutors.txt

As you can see, each record is output on a row, and the column values are separated by tabs. If you want to customize the output format, you can use the following options

--fields-terminated-by=','

Column values are separated by commas

--fields-enclosed-by='"' column values enclosed in double quotes--lines-terminated-by='\' specifies\as line terminator, defaults to newline

As you can see, the exported tutors.txt column values are separated by commas and enclosed in double quotes, which makes it easier to import the data back into the database later.

Points to note:

When mysqldump does not use--tab, its exported sql file has both operation statements and table creation statements; when--tab is used, the txt file stores data, and the sql file only has table creation statements.

Database restoration

1. mysqldump does not use--tab option

mysql>use db_namemysql>source /path/to/backup/tb.sql #This can be imported

2. mysqldump uses the--tab option

1. Use the.sql file to create the table

mysql -uUSERNAME -p PASSWORD db_name

LOAD DATA INFILE '/path/to/tb.txt' INTO TABLE tb_name [parameter]

Create a cold backup

1. View binary logs in use

mysql>show master status;

2. Binary log scrolling

mysql>flush logs

3. Close mysql, backup the data directory, and open mysql.

Create incremental backups

1. Binary log scrolling

2. Use mysqlbinlog for backup

mysqlbinlog /path/to/binlog/mysql-bin.0000xx > /path/to/backup/0000xx.sql

(When restoring, you can directly source or mysql command (mentioned above when restoring))

To put it bluntly, incremental backup is to use mysqlbinlog to back up the newly generated binary log after the last backup to the specified directory

After reading this article on how to backup and restore mysql data, many readers will definitely want to know more about the relevant content. If you need more industry information, you can pay attention to our industry information column.

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.