Mysql backup _ MySQL

Cold backup occurs when the database is shut down normally. When the database is shut down normally, it will provide us with a complete database. Cold backup is a way to copy key files to another location. Cold backup is the fastest and safest way to back up database information. Only available to 1. Cold backup

Cold backup occurs when the database is shut down normally. When the database is shut down normally, it will provide us with a complete database. Cold backup is a way to copy key files to another location. Cold backup is the fastest and safest way to back up database information.

Only recovery at "a certain point in time" can be provided; recovery by table and user is not allowed; database needs to be closed to work; recovery has high requirements on the version

The backup speed depends on the storage engine type.

Cp

1. Locate the data directory storage location

# Mysql-uroot-pmypasswd
Mysql> show variables like '% datadir % ';

2. Refresh all data in the memory to the disk and lock the data table to ensure that no new data is written during the copy process.

# Mysql-uroot-pmypasswd
Mysql> flush tables with read lock;
Mysql> flush logs;

3. Disable the mysql server

# Service mysqld stop

4. Backup

# Tar-zcvf mysql.tar.gz/var/lib/mysql
# Cp mysql.tar.gz mysql_bck

Note: For an Innodb table, you also need to back up its log file, that is, the ib_logfile * file. Because when the Innodb table is corrupted, these log files can be recovered.

2. Hot Standby

Hot Backup is the SQL statement used to back up the database when the database is running.

Mysqldump

Mysqldump adopts an SQL-level backup mechanism (logical backup). It imports data tables into SQL script files and is suitable for upgrading between different MySQL versions. This is also the most common backup method.

The Mysqldump command works very easily. It first detects the structure of the table to be backed up, and then generates a CREATE statement in a text file. Then, convert all the records in the table into an INSTERT statement. These CREATE statements and INSTERT statements are used for restoration. When restoring data, you can use the CREATE statement to CREATE a table. Use the INSERT statement to restore data. It can be used to back up the entire server, or to back up certain rows, stored procedures, stored functions, and triggers in a single or partial database, single or partial table, or table; it can automatically record the binary log files and their corresponding locations at the backup time. The InnoDB Storage engine supports Hot Backup Based on the single transaction mode, while MyISAM supports hot backup at most.

MySQL database compression and backup

# Mysqldump-hhostname-uusername-pmypasswd db_name | gzip> backupfile. SQL .gz

Back up database structures only

# Mysqldump-no-data-databases db_name1 db_name2> backupfile. SQL

Back up all databases on the server

# Mysqldump-all-databases> allbackupfile. SQL

To restore a database, you must manually create a database db_name.
Command for restoring MySQL database

# Mysql-hhostname-uusername-pmypasswd db_name <backupfile. SQL

Restore a compressed MySQL database

# Gunzip <backupfile. SQL .gz | mysql-uusername-pmypasswd db_name

Binary (binlog)

The binary log contains all statements that update data or have potentially updated data (for example, no DELETE matching any row.

Enable binlog.

# Vim/etc/my. cnf

server-id = 1log-bin = binloglog-bin-index = binlog.index

#service mysqld restart

1. copy a file by rolling logs
Terminate writing to the current binlog

#mysql -uroot -pmypasswd
mysql> FLUSH LOGS;

Copy and export a binary file

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

2. mysqlbinlog export binary log file content

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

3. Restore

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

Ps: for slave servers in the backup replication system, you should also back up master.info and relay-log.info files.

Mysqldump full backup + binlog Incremental Backup

# Mysqldump-uroot-pmypasswd-lock-all-tables-master-data = 2-events
-Routines-all-databases>/mysql_bck/database_date + % F. SQL
Tips:-lock-all-tables indicates that the read lock is applied to all tables;-master-data = 2 indicates that the current binary log location is recorded in the backup file; -events indicates the code of the Time Scheduler for backing up data at the same time;-routines indicates the stored procedure and storage function for backing up data at the same time;-all-databases indicates backing up all databases.
# Mysqlbinlog-start-position =-stop-position = mysql-bin.000001>/mysql_bck/binlog_date + % F _ % H. SQL

http://www.bitsCN.com/article/74613.htm
http://www.bitsCN.com/article/22727.htm

mysqlhotcopy

Mysqlhotcopy is a PERL program originally written by Tim Bunce. It uses lock tables, flush tables, and cp or scp to quickly back up databases. It is the fastest way to back up a database or a single table, but it can only run on the machine where the database file (including the data table definition file, data file, and index file) is located. Mysqlhotcopy can only be used to back up MyISAM and can only run on Unix-like and NetWare systems.

Install

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

Backup

#mysqlhotcopy -uroot -pmypasswd db_name /mysql_bck