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

In this issue, the editor will bring you about the backup and recovery methods in MYSQL. The article is rich in content and analyzed and described from a professional point of view. I hope you can get something after reading this article.

1. Cold backup

Stop the library, backup

# which mysqladmin

# mysqladmin-uroot-pOracle123 shutdown

# ps-ef | grep mysql

Enter the location of the data file for physical cp or scp

two。 Hot backup 2.1 mysqldump backup

Its backup process is to find the data that needs to be backed up from buffer for backup, otherwise the data files will be cached in buff for backup.

Full database backup

# mysqldump-- single-transaction-uroot-pOracle123-A > all_20200310.sql

Full library recovery

# mysql-uroot-pOracle123

< all_20200310.sql 单库备份TEST # mysqldump --single-transaction -uroot -pOracle123 TEST >

TEST_20200310.sql

Single database recovery

# mysql-uroot-pOracle123 TEST

< TEST_20200310.sql 注：数据库TEST如果被删除了，需要先创建空库TEST 备份单表TEST.TEST # mysqldump --single-transaction -uroot -pOracle123 TEST TEST >

TEST.TEST_20200310.sql

Single table restore

# mysql-uroot-pOracle123 TEST

< TEST_20200310.sql 备份表结构TEST # mysqldump --single-transaction -uroot -pOracle123 TEST TEST -d >

TEST_META_20200310.sql

Backup table contents TEST

# mysqldump-- single-transaction-uroot-pOracle123 TEST TEST-t > TEST_DATA_20200310.sql

Back up TEST as required

# mysqldump-single-transaction-uroot-pOracle123 TEST TEST-where= "id > 3" > TEST_WHERE_20200310.sql

2.2 select... Into outfile

Note: first make sure that the secure_file_priv parameter is not NULL, otherwise set secure-file-priv= ""

Mysql > select * from TEST into outfile'/ tmp/test.sql'

Mysql > delete from TEST

Mysql > commit

Mysql > load data infile'/ tmp/test.sql' into table TEST

Mysql > COMMIT

Or

# mysql-uroot-pOracle123-e "load data infile'/ tmp/test.sql' into table TEST.TEST"

2.3 mydumper

Download address

Http://rpm.pbone.net/index.php3/stat/4/idpl/55203713/dir/redhat_el_7/com/mydumper-0.9.1-4.1.x86_64.rpm.html

# yum install glib2-devel mysql-devel zlib-devel pcre-devel openssl-devel cmake make

# cd / u01

# yum install-y mydumper-0.9.1-4.1.x86_64.rpm

# ln-s / tmp/mysql.sock / var/lib/mysql/mysql.sock

Back up the TEST database

# mydumper-u root-p Oracle123-B TEST-o / u01/bak

Restore TEST database

Mysql > drop database TEST

# myloader-u root-p Oracle-B TEST-d / u01/bak/

Back up the TEST table

# mydumper-u root-p Oracle123-B TEST-T TEST-o / u01/bak/

Restore the TEST table

# myloader-u root-p Oracle123-B TEST-o TEST-d / u01/bak/

This is what the backup and recovery methods in MYSQL shared by the editor are like. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

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.