Share a MySQL database shard backup script (original) and a mysql database shard backup script

Share a MySQL database shard backup script (original) and a mysql database shard backup script

Share a MySQL database shard backup script (original)

Development ideas:

1. path: Specify the backup location and define the path (first determine whether a directory exists and no directory exists). My path:/mysql/backup. Each backup is compressed to improve efficiency, convenient sorting with time

2. database fetch: capture the database name. The awk and grep I use are used together to obtain the database name (if you want to back up the database according to the table, you can refine it). Be sure to use the mysql-e Option to create a script.

3. system environment variables: because the function is used, it is best to use the./etc/profile command in the script to pass the current environment variables of the system.

4. Implementation Method: Use the mysqldump command + for loop to implement database shard backup

5. Backup check: if the size of the backed up file is smaller than 0, it is regarded as successful and success is returned. Otherwise, failed is returned. If it is placed in a scheduled task, it is recommended to output it to the log file for future viewing.

6. Pay attention to the difference between single quotation marks ''and double quotation marks" "when defining variables. Otherwise, an error may occur.

 1 #!/bin/bash 2 #define var 3 user="root" 4 pass="1314520" 5 path="/mysql/backup" 6 cmd="mysql -u${user} -p${pass}" 7 dump="mysqldump -u${user} -p${pass} -B --events -x --master-data=2" 8 #system function 9 . /etc/init.d/functions10 . /etc/profile11 #judge dir12 function jdir(){13 if [ ! -e $path ];then14   mkdir $path -p15 fi16 }17 #dump database18 function bk(){19 for dbname in `$cmd -e 'show databases;'|awk 'NR>1{print $0}'|grep -v "performance_schema"`20 do21   $dump $dbname|gzip >${path}/${dbname}_$(date +%F).sql.gz22   sleep 123   if [ -s ${path}/${dbname}_$(date +%F).sql.gz ];then24      action "dump $dbname success!" /bin/true25   else26      action "dump $dbname failed" /bin/false27   fi28 done
29 }30 function main(){31 jdir32 bk33 }34 main

What I want to share with you is the development idea and the technology is very basic.

Tests can be completed. If any errors occur, please correct them.