How to use mysqldump in MySQL

This article introduces the knowledge about "how to use mysqldump in MySQL". In the actual case operation process, many people will encounter such difficulties. Next, let Xiaobian lead you to learn how to deal with these situations! I hope you can read carefully and learn something!

Logical backup is generally used for data migration or data export when the amount of data is small.

I. Backup process

Export all databases

mysqldump -uroot -p -q --single-transaction -A > all.sql

2. Export the test library

mysqldump -uroot -p -q --single-transaction test >test.sql

3. Export the individual tables of the test library

mysqldump -uroot -p -q --single-transaction test table1 table2 >test_table1_table2.sql

II. Recovery process

mysql -uroot -p /backup/$dateDIR/data/${i}_${dateDIR}.sql.gz

done

binlog_rm=`tail -n 1 $path/mysql-bin-3306.index | awk -F"/" '{print $4}'`

mysql -uroot -pmysql -e "purge binary logs to '$binlog_rm'"

Incremental backup:

Click here to fold or open

#!/ bin/bash

#MySQL Full Backup Script

cd /backup

dateDIR=`date +"%y-%m-%d"`

mkdir -p $dateDIR/data

path=/mysql/data

mysqladmin -uroot -pmysql flush-logs

binlog_cp=`head -n -1 $path/mysql-bin-3306.index | awk -F"/" '{print $4}'`

for i in $binlog_cp

do

mysql -uroot -pmysql -e "system cp -p $path/$i /backup/$dateDIR/data;"

done

binlog_rm=`tail -n 1 $path/mysql-bin-3306.index | awk -F"/" '{print $4}'`

mysql -uroot -pmysql -e "purge binary logs to '$binlog_rm'"

"MySQL mysqldump in the use of the method" content is introduced here, thank you for reading. If you want to know more about industry-related knowledge, you can pay attention to the website. Xiaobian will output more high-quality practical articles for everyone!