Table of Contents
1.冷备
cp
2.热备
mysqldump
二进制(binlog)
mysqldump全备 + binlog增备" >mysqldump全备 + binlog增备
mysqlhotcopy" >mysqlhotcopy
Home Database Mysql Tutorial Mysql备份_MySQL

Mysql备份_MySQL

Jun 08, 2016 am 08:50 AM
binlog mysql

 

1.冷备

冷备份发生在数据库已经正常关闭的情况下,当正常关闭时会提供给我们一个完整的数据库。冷备份是将关键性文件拷贝到另外位置的一种说法。对于备份数据库信息而言,冷备份是最快和最安全的方法。

只能提供到“某个时间点”的恢复;不能按表和用户恢复;工作是需要关闭数据库;恢复对版本有很高要求

备份速度取决于存储引擎类型

cp

1.找到数据目录存放位置

#mysql -uroot -pmypasswd
mysql> show variables like ‘%datadir%’;

2.把内存中的数据都刷新到磁盘中,同时锁定数据表,以保证拷贝过程中不会有新的数据写入

#mysql -uroot -pmypasswd
mysql> FLUSH TABLES WITH READ LOCK;
mysql> FLUSH LOGS;

3.关闭mysql服务器

#service mysqld stop

4.备份

#tar -zcvf mysql.tar.gz /var/lib/mysql
#cp mysql.tar.gz mysql_bck

注意,对于 Innodb 类型表来说,还需要备份其日志文件,即 ib_logfile* 文件。因为当 Innodb 表损坏时,就可以依靠这些日志文件来恢复。

2.热备

热备份是在数据库运行的情况下,备份数据库操作的sql语句。

mysqldump

mysqldump 是采用SQL级别的备份机制(逻辑备份),它将数据表导成 SQL 脚本文件,在不同的 MySQL 版本之间升级时相对比较合适,这也是最常用的备份方法

Mysqldump命令的工作原理很简单,它先查出需要备份的表的结构,再在文本文件中生成一个CREATE语句。然后,将表中的所有记录转换为一条INSTERT语句。这些CREATE语句和INSTERT语句都是还原时使用的。还原数据时就可以使用其中的CREATE语句来创建表。使用其中的INSERT语句来还原数据。它可以实现整个服务器备份,也可以实现单个或部分数据库、单个或部分表、表中的某些行、存储过程、存储函数、触发器的备份;并且能自动记录备份时刻的二进制日志文件及相应的位置。对于InnoDB存储引擎来讲支持基于单事务模式实现热备,对于MyISAM则最多支持温备。

MySQL数据库压缩备份

#mysqldump -hhostname -uusername -pmypasswd db_name | gzip > backupfile.sql.gz

仅备份数据库结构

#mysqldump –no-data –databases db_name1 db_name2 > backupfile.sql

备份服务器上所有数据库

#mysqldump –all-databases > allbackupfile.sql

还原需要先手动创建一个数据库 db_name
还原MySQL数据库的命令

#mysql -hhostname -uusername -pmypasswd db_name

还原压缩的MySQL数据库

#gunzip

二进制(binlog)

二进制日志包含了所有更新了数据或者已经潜在更新了数据(例如,没有匹配任何行的一个DELETE)的所有语句。

启用binlog。

#vim /etc/my.cnf

<code class="hljs lasso">server-id = 1
log-bin = binlog
log-bin-index = binlog.index</code>
Copy after login

#service mysqld restart

1.滚动日志复制文件<br> 终止对当前 binlog 的写入

#mysql -uroot -pmypasswd<br> mysql> FLUSH LOGS;

复制导出二进制文件

cp mysql-bin.000001 /mysql_bck/mysql-bin.000001

2.mysqlbinlog导出二进制日志文件内容

# mysqlbinlog mysql-bin.000001 > binlog_date +%F.sql

3.恢复

#mysqlbinlog /mysql_bck/binlog.000001 | mysql -uroot -pmypasswd db_name

ps:如果是备份复制系统中的从服务器,还应该备份 master.info 和 relay-log.info 文件。

mysqldump全备 + binlog增备

#mysqldump -uroot -pmypasswd –lock-all-tables –master-data=2 –events<br> –routines–all-databases > /mysql_bck/database_date +%F.sql<br> tips:–lock-all-tables表示为所有表施加读锁;–master-data=2表示在备份文件中记录当前二进制日志的位置;–events表示备份数据的同时备份时间调度器代码;–routines表示备份数据的同时备份存储过程和存储函数;–all-databases表示备份所有库。<br> # mysqlbinlog –start-position= –stop-position= mysql-bin.000001 > /mysql_bck/binlog_date +%F_%H.sql

http://www.bitsCN.com/article/74613.htm<br> http://www.bitsCN.com/article/22727.htm

mysqlhotcopy

mysqlhotcopy 是一个 PERL 程序,最初由Tim Bunce编写。它使用 LOCK TABLES、FLUSH TABLES 和 cp 或 scp 来快速备份数据库。它是备份数据库或单个表的最快的途径,但它只能运行在数据库文件(包括数据表定义文件、数据文件、索引文件)所在的机器上。mysqlhotcopy 只能用于备份 MyISAM,并且只能运行在 类Unix 和 NetWare 系统上。

安装

#yum -y install perl perl-DBI<br> #wget http://file.111cn.net/upload/2013/12/DBD-mysql-3.0002.tar.gz<br> #tar zxvf DBD-mysql-3.0002.tar.gz<br> #cd DBD-mysql-3.0002<br> #perl Makefile.PL –mysql_config=/usr/local/mysql/bin/mysql_config<br> #make<br>  #make install

备份

#mysqlhotcopy -uroot -pmypasswd db_name /mysql_bck

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
3 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
R.E.P.O. Best Graphic Settings
3 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
R.E.P.O. How to Fix Audio if You Can't Hear Anyone
3 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

PHP's big data structure processing skills PHP's big data structure processing skills May 08, 2024 am 10:24 AM

Big data structure processing skills: Chunking: Break down the data set and process it in chunks to reduce memory consumption. Generator: Generate data items one by one without loading the entire data set, suitable for unlimited data sets. Streaming: Read files or query results line by line, suitable for large files or remote data. External storage: For very large data sets, store the data in a database or NoSQL.

How to use MySQL backup and restore in PHP? How to use MySQL backup and restore in PHP? Jun 03, 2024 pm 12:19 PM

Backing up and restoring a MySQL database in PHP can be achieved by following these steps: Back up the database: Use the mysqldump command to dump the database into a SQL file. Restore database: Use the mysql command to restore the database from SQL files.

How to optimize MySQL query performance in PHP? How to optimize MySQL query performance in PHP? Jun 03, 2024 pm 08:11 PM

MySQL query performance can be optimized by building indexes that reduce lookup time from linear complexity to logarithmic complexity. Use PreparedStatements to prevent SQL injection and improve query performance. Limit query results and reduce the amount of data processed by the server. Optimize join queries, including using appropriate join types, creating indexes, and considering using subqueries. Analyze queries to identify bottlenecks; use caching to reduce database load; optimize PHP code to minimize overhead.

How to insert data into a MySQL table using PHP? How to insert data into a MySQL table using PHP? Jun 02, 2024 pm 02:26 PM

How to insert data into MySQL table? Connect to the database: Use mysqli to establish a connection to the database. Prepare the SQL query: Write an INSERT statement to specify the columns and values ​​to be inserted. Execute query: Use the query() method to execute the insertion query. If successful, a confirmation message will be output.

How to use MySQL stored procedures in PHP? How to use MySQL stored procedures in PHP? Jun 02, 2024 pm 02:13 PM

To use MySQL stored procedures in PHP: Use PDO or the MySQLi extension to connect to a MySQL database. Prepare the statement to call the stored procedure. Execute the stored procedure. Process the result set (if the stored procedure returns results). Close the database connection.

How to create a MySQL table using PHP? How to create a MySQL table using PHP? Jun 04, 2024 pm 01:57 PM

Creating a MySQL table using PHP requires the following steps: Connect to the database. Create the database if it does not exist. Select a database. Create table. Execute the query. Close the connection.

How to fix mysql_native_password not loaded errors on MySQL 8.4 How to fix mysql_native_password not loaded errors on MySQL 8.4 Dec 09, 2024 am 11:42 AM

One of the major changes introduced in MySQL 8.4 (the latest LTS release as of 2024) is that the &quot;MySQL Native Password&quot; plugin is no longer enabled by default. Further, MySQL 9.0 removes this plugin completely. This change affects PHP and other app

The difference between oracle database and mysql The difference between oracle database and mysql May 10, 2024 am 01:54 AM

Oracle database and MySQL are both databases based on the relational model, but Oracle is superior in terms of compatibility, scalability, data types and security; while MySQL focuses on speed and flexibility and is more suitable for small to medium-sized data sets. . ① Oracle provides a wide range of data types, ② provides advanced security features, ③ is suitable for enterprise-level applications; ① MySQL supports NoSQL data types, ② has fewer security measures, and ③ is suitable for small to medium-sized applications.

See all articles