Mysqlhotcopy backup usage instructions in MySQL

1 installation. MySQL is installed in the/usr/local/mysql directory.

The code is as follows	Copy Code
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 Test Make install

2 Backing up a database

The code is as follows	Copy Code
Mkdir-p/data/word/ /usr/local/mysql/bin/mysqlhotcopy–user=root–password=123456 Word/data/word

Can be packaged, if packaged name is word.tar.gz

3 recovery

Stop MySQL Database

Modify Permissions. Start MySQL

example, Mysqlhotcopy + binlog to achieve MySQL incremental backup

Mysqlhotcopy is simply a process of caching writes and file copying, consuming resources and backing up much faster than mysqldump. Especially suitable for large databases, which use lock tables, FLUSH tables, and CP or SCP to quickly back up databases. It is the quickest way to back up a database or a single table, running only on the same machine as the database directory.

Note: Mysqlhotcopy only supports the MyISAM engine.

1. Install Dependency Pack

Mysqlhotcopy is written in the Perl language, so you need to install Perl's connection to MySQL driver:

The code is as follows	Copy Code
# yum Install perl-dbi.x86_64 # yum Install perl-dbd-mysql.x86_64

2 mysqlhotcopy Common parameters:

-–allowold if the target exists and does not exit (plus a _old suffix to rename it)
-–addtodest incremental backup, the new backup automatically overwrites the original. 荩? llowold
-–checkpoint=db_name.tbl_name inserts a checkpoint entry in the specified database, table.
-–debug enable debug output.
-–dryrun,-n report actions without executing them.
Refresh the log after all tables have been locked-–flushlog.
The previously (renamed) target is not deleted after the-–keepold is completed.
-–method=command Replication method (CP or SCP).
All index files are not included in the-–noindices backup. This makes backup smaller and faster. You can reconstruct the index later with MYISAMCHK-RQ.
-–user=user_name,-u user_name the MySQL user name to use when connecting to the server.
-–password=password,-p password The password to use when connecting to the server. Please note that the password value for this option is not optional, unlike other MySQL programs.
-–port=port_num,-p port_num The TCP/IP port number that is used when connecting to the local server.
-–quiet,-q keep silent except when there are errors.
-–regexp=expr Copies all database names that match the regular expression given.
-–socket=path,-s path is used for connecting UNIX socket files.
-–suffix=str the suffix of the database name that is replicated.
-–tmpdir=path Temp directory (instead of/tmp).
Reset binary log after –resetmaster all table locks
–resetslave All tables are locked and reset Master.info
–record_log_pos=db.table specifies the table that records slave and master information

3. Create a table that records slave and master information

The

code is as follows

copy code

CREATE TABLE ' Mysqlhotcopy_log_pos ' (   ' host ' varchar (a) not NULL,   ' time _stamp ' timestamp not NULL DEFAULT current_timestamp on UPDATE current_timestamp,   ' log_file ' varchar T null,   ' log_pos ' int (one) default null,   ' master_host ' varchar () default NULL,   ' master_l Og_file ' varchar default NULL,   ' master_log_pos ' int (one) default null,   ' relay_log_file ' varchar ( Default NULL,   ' relog_log_pos ' int (one) default null,  primary KEY (' host ') ) Engine=myisam DE FAULT Charset=utf8

Note: The mysqlhotcopy script does not record relay_log_file and Relog_log_pos values, and it does not write the information to the file. The following records relay log and POS value information and writes to the file because I changed the mysqlhotcopy script content.

4. Dedicated User rights

The code is as follows	Copy Code
Grant SELECT, Reload, lock tables on *.* to ' mysqlbackup ' @ ' localhost ' identified by ' www.111cn.net '; Grant SELECT, delete, UPDATE, insert on Mysql.mysqlhotcopy_log_pos to ' mysqlbackup ' @ ' localhost ' identified by ' WWW.111CN. NET ';

5. Regular use

If you only want to heat up some of the data, you may use the regular.

5.1 Database name matching, such as: Back up the library beginning with Ttlsa, you can use:

The code is as follows	Copy Code
# mysqlhotcopy--flushlog-u= ' mysqlbackup '-p= ' www.111cn.net '--regexp= ^ttlsa/backup/mysqlback

To back up a library that starts with [a-f], you can use:

The code is as follows	Copy Code
# mysqlhotcopy--flushlog-u= ' mysqlbackup '-p= ' www.111cn.net '--regexp=^[a-f]/backup/mysqlback

5.2 Back up some of the tables in a database:

To back up a table that starts with user Ttlsa_com library:

The code is as follows	Copy Code
# mysqlhotcopy--flushlog-u= ' mysqlbackup '-p= ' www.111cn.net ' ttlsa_com./^user//backup/mysqlback

Back up the ttlsa_com library except for the table at the beginning of User_log:

The code is as follows	Copy Code
# mysqlhotcopy--flushlog-u= ' mysqlbackup '-p= ' www.111cn.net ' ttlsa_com./~^user_log//backup/mysqlback

Back up the table with the Ttlsa_com library starting with User_0,user_1,user_2......,user_9:

The code is as follows	Copy Code
# mysqlhotcopy--flushlog-u= ' mysqlbackup '-p= ' www.111cn.net ' ttlsa_com./^ (user_[0-9])//backup/mysqlback

6. Record Slave and master information

The code is as follows	Copy Code
# perl./mysqlhotcopy-u mysqlbackup-p Www.111cn.net-S/tmp/mysql.sock--record_log_pos=mysql.mysqlhotcopy_log_pos- Keepold--record_log_pos2file--flushlog--regexp= "[a-za-z0-9_-]"/backup/mysqlback/mysqlhotcopy_20131114_041307

Note: The –record_log_pos2file parameter is modified by my mysqlhotcopy plus.

7. Incremental backup implementation

Incremental backups are implemented based on the master, slave information records above 6.

The code is as follows	Copy Code
# Mysqlbinlog--start-position=pos Bin_log_file

8. Simple backup Script

The code is as follows

Copy Code

#!/bin/bash Mysqlhotcopy= "/usr/local/mysql/bin/mysqlhotcopy2" User= "Mysqlbackup" Password= "Www.111cn.net" socket= "/tmp/mysql.sock" Backupdir= "/backup/mysqlback" Datadir= "Mysqlhotcopy_ ' Date +%y%m%d_%i%m%s '" Echo $datadir target= "$backupdir/$datadir" Retention_days_local= "5" Status= ($ (Mysql-u$user-p${password}-S $socket-e "show slave statusg"--skip-column-names | egrep "Slave_io_running| Slave_sql_running "| awk ' {print $} ') If ["${status[0]}" = = "Yes"] && ["${status[1]}" = "yes"]; Then Mkdir-p $target $mysqlhotcopy-u $user-P $password-S $socket--record_log_pos=mysql.mysqlhotcopy_log_pos--keepold--record_log_ Pos2file--flushlog--regexp= "[a-za-z0-9_-]" $target Find $backupdir-name "^mysqlhotcopy_*_*"-type d-mtime +${retention_days_local} | Xargs RM-RF Else echo "Slave Error" Fi

Compare with mysqldump:

1, the former is a fast file in the sense of copy, the latter is a database-side SQL statement collection.
2, the former can only run in the database directory on the machine, the latter may be used in remote clients, but the backup files are still stored on the server.
3, the same place is the online execution of LOCK tables and UNLOCK tables
4, the former restore only need copy backup files to the source directory coverage can be, the latter need to import SQL files to the original library. (source or MySQL < Bakfile.sql)
5, the former only applies to the MyISAM engine, and then can be used at the same time in the MyISAM engine and INODDB engine.
6, the former must install the Perl-dbd-mysql package before using, but the latter does not need.

Tar zxvf word.tar.gz