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

The following content mainly brings you mysql database backup tutorial analysis, the knowledge mentioned here, slightly different from books, are professional and technical personnel in contact with users, summed up, has a certain experience sharing value, hope to bring help to the majority of readers.

Mysql database backup is the most important thing for every data administrator, and it can help us in critical times. It is recommended that when backing up the database, table data, tablespaces, triggers, functions, and views are backed up separately, which can save backup time and is more convenient when restoring data.

The backup method adopted is to use shell and crontab to operate. In the case of a large number of databases, it is recommended to backup 5 to 6 libraries in each script.

A simple example.

mysql-adson database In Cloud Virtual Machine, there are three databases as follows: adson_inter,adson_adm,adson_hi

The backup method adopted is to backup table database, table space and trigger separately; create/mysqlbak to store backup data, and then create directories respectively: adson_inter,adson_adm,adson_hi

Create adsonbak_1.sh in/script/directory

#!/ bin/sh

rq=`date -d last-day +%Y%m%d`

#Backup table structure

/usr/bin/mysqldump -uroot -pagfigjky --single-transaction --database adson_inter --skip-triggers -d -R | gzip > /mysqlbak/adson_inter/adson_inter_$rq.tb_str.sql.gz

/usr/bin/mysqldump -uroot -pagfigjky --single-transaction --database adson_adm --skip-triggers -d -R | gzip > /mysqlbak/adson_adm/adson_adm_$rq.tb_str.sql.gz

/usr/bin/mysqldump -uroot -pagfigjky --single-transaction --database adson_hi --skip-triggers -d -R | gzip > /mysqlbak/adson_hi/adson_hi_$rq.tb_str.sql.gz

#Backup data

/usr/bin/mysqldump -uroot -pagfigjky --single-transaction --database adson_inter --skip-triggers -t -R | gzip > /mysqlbak/adson_inter/adson_inter_$rq.dt_thing.sql.gz

/usr/bin/mysqldump -uroot -pagfigjky --single-transaction --database adson_adm --skip-triggers -t -R | gzip > /mysqlbak/adson_adm/adson_adm_$rq.dt_thing.sql.gz

/usr/bin/mysqldump -uroot -pagfigjky --single-transaction --database adson_hi --skip-triggers -t -R | gzip > /mysqlbak/adson_hi/adson_hi_$rq.dt_thing.sql.gz

#Backup Trigger

/usr/bin/mysqldump -uroot -pagfigjky --single-transaction --database adson_inter --triggers -d -t -R | gzip > /mysqlbak/adson_inter/adson_inter_$rq.tr_thing.sql.gz

/usr/bin/mysqldump -uroot -pagfigjky --single-transaction --database adson_adm --triggers -d -t -R | gzip > /mysqlbak/adson_adm/adson_adm_$rq.tr_thing.sql.gz

/usr/bin/mysqldump -uroot -pagfigjky --single-transaction --database adson_hi --triggers -d -t -R | gzip > /mysqlbak/adson_hi/adson_hi_$rq.tr_thing.sql.gz

Execute permissions for adsonbak_1.sh and set crontab

# crontab -l

0 6 * * * /bin/bash /script/adsonbak_1.sh

Set to execute at 6:00 every day

Then you can set up to automatically retain backup data in combination with reality.

For the above analysis of mysql database backup tutorial, if you still need to know more, you can continue to pay attention to our industry promotion, if you need to get professional answers, you can contact the pre-sales and after-sales in the official website, I hope this article can bring you some knowledge updates.

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.