首頁 > 文章 > 資料庫 > MySQL怎麼實現分庫分錶備份

MySQL怎麼實現分庫分錶備份

WBOY轉載: 2023-05-27 12:37:061047瀏覽

主題：MySQL分庫分錶備份

#腳本詳細內容

[root@db02 scripts]# cat /server/scripts/Store_backup.sh  #!/bin/sh  MYUSER=root

  MYPASS=xuliangwei

  SOCKET=/data/3306/mysql.sock

  MYLOGIN=”mysql -u$MYUSER -p$MYPASS -S $SOCKET”

  MYDUMP=”mysqldump -u$MYUSER -p$MYPASS -S$SOCKET -B”

  DATABASE=”$($MYLOGIN -e “show databases;”|egrep -vi “Data|_schema|mysql”)”

  for dbname in $DATABASE  do  MYDIR=/server/backup/$dbname  [ ! -d $MYDIR ] && mkdir -p $MYDIR  $MYDUMP $dbname|gzip >$MYDIR/${dbname}_$(date +%F).sql.gz

  done

執行後的結果

  [root@db02 /]# tree /server/backup/  /server/backup/

  ├── oldboy26

  │ ├──oldboy26_2015-12-29.sql.gz

  │ ├──oldboy26_2015-12-30.sql.gz

  │ └──oldboy26_2015-12-31.sql.gz

  ├── oldboy_xuliangwei26

  │ ├──oldboy_xuliangwei26_2015-12-29.sql.gz

  │ ├──oldboy_xuliangwei26_2015-12-30.sql.gz

  │ └──oldboy_xuliangwei26_2015-12-31.sql.gz

  └── xuliangwei26

  ├──xuliangwei26_2015-12-29.sql.gz

  ├──xuliangwei26_2015-12-30.sql.gz

  └──

  xuliangwei26_2015-12-31.sql.gz

  3 directories, 9 files

腳本詳細內容

  [root@db02 scripts]# cat mysql_table.sh #  #!/bin/sh  USER=root

  PASSWD=xuliangwei

  SOCKET=/data/3306/mysql.sock

  MYLOGIN=”mysql -u$USER -p$PASSWD -S$SOCKET”

  MYDUMP=”mysqldump -u$USER -p$PASSWD -S$SOCKET”

  DATEBASE=”$($MYLOGIN -e “show databases;”|egrep -vi “Data|_schema|mysql”)”

  for dbname in $DATEBASE  do  TABLE=”$($MYLOGIN -e “use $dbname;show tables;”|sed &lsquo;1d&rsquo;)”

  for tname in $TABLE  do  MYDIR=/server/backup/$dbname/${dbname}_$(date +%F)

  [ ! -d $MYDIR ] && mkdir -p $MYDIR  $MYDUMP $dbname $tname |gzip >$MYDIR/${dbname}_${tname}_$(date +%F).sql.gz

  done  done

查看執行後的結果

  [root@db02 scripts]# tree /server/backup/  /server/backup/

  ├── oldboy26

  │ ├──oldboy26_2015-12-30

  │ │├──oldboy26_oldboy_test_2015-12-30.sql.gz

  │ │└──oldboy26_oldboy_test2_2015-12-30.sql.gz

  │ └──oldboy26_2015-12-31

  │ ├── oldboy26_oldboy_test_2015-12-31.sql.gz

  │ └── oldboy26_oldboy_test2_2015-12-31.sql.gz

  ├── oldboy_xuliangwei26

  │ ├──oldboy_xuliangwei26_2015-12-30

  │ │├──oldboy_xuliangwei26_cloudstack_2015-12-30.sql.gz

  │ │├──oldboy_xuliangwei26_dashborad_2015-12-30.sql.gz

  │ │└── oldboy_xuliangwei26_student_2015-12-30.sql.gz

  │ └──oldboy_xuliangwei26_2015-12-31

  │ ├──oldboy_xuliangwei26_cloudstack_2015-12-31.sql.gz

  │ ├──oldboy_xuliangwei26_dashborad_2015-12-31.sql.gz

  │ └──oldboy_xuliangwei26_student_2015-12-31.sql.gz

  └── xuliangwei26

  ├──xuliangwei26_2015-12-30

  │├──xuliangwei26_openstack_2015-12-30.sql.gz

  │├──xuliangwei26_saltstack_2015-12-30.sql.gz

  │├──xuliangwei26_server_2015-12-30.sql.gz

  │└──xuliangwei26_zabbix_2015-12-30.sql.gz

  └──xuliangwei26_2015-12-31

  ├──xuliangwei26_openstack_2015-12-31.sql.gz

  ├──xuliangwei26_saltstack_2015-12-31.sql.gz

  ├──xuliangwei26_server_2015-12-31.sql.gz

  └──xuliangwei26_zabbix_2015-12-31.sql.gz

  9 directories, 18 files

以上是MySQL怎麼實現分庫分錶備份的詳細內容。更多資訊請關注PHP中文網其他相關文章！

mysql

陳述：

本文轉載於：yisu.com。如有侵權，請聯絡admin@php.cn刪除

上一篇：mysql查詢慢日誌怎麼開啟下一篇：mysql查詢慢日誌怎麼開啟

看更多