· 9 years ago · Apr 15, 2017, 09:12 PM
1mysqldump dbname -u root -p > dbname.sql
2tar czvf dbname.sql.tgz dbname.sql
3rm dbname.sql
4
5mysqldump dbname -u root -p > some wizardry > dbname.sql.tgz
6
7mysqldump dbname -u root -p > send dbname.sql.tgz to user@host
8
9mysqldump --opt <database> | gzip -c | ssh user@wherever 'cat > /tmp/yourfile.sql.gz'
10
11mysqldump -u userName -p (passwordPrompt) yourDatabaseName | gzip -v > output.gz
12
13mkfifo mysql_pipe
14gzip -9 -c < mysql_pipe > name_of_dump.gz &
15mysqldump database > mysql_pipe
16rm mysql_pipe
17
18ssh -C user@host "mysqldump --opt --compress database <table> | gzip -9 -c" > outputfile.sql.gz
19
20mysqldump prod_db -h dbslave | pv | gzip -c > prod_2012_08_20.dump.tgz
21
22mysqldump prod_db -h dbslave | pv -s 3g | gzip -c > prod_2012_08_20.dump.tgz
23
24#!/bin/bash
25#set -x
26
27#REQUIRED VARS
28SOURCE_USER=root #MySQL user
29SOURCE_HOST=localhost
30SOURCE_PASSWORD=yourmysqlpass #optional
31SOURCE_DBNAME=yourdbname
32TARGET_HOST=192.168.1.2
33TARGET_DBNAME=yourdbname
34TARGET_SSHUSER=root
35TARGET_SSHPASSWORD=yoursshpass #optional
36TABLES='table1 table2 table3 table4'
37TARGET_DIR="/data/dumpfiles"
38EXEC_ACTION_TEXT[0]='Reimport TABLES directly into remote MySQL database'
39EXEC_ACTION_TEXT[1]='Backup gzipped data to TARGED_DIR on remote TARGET_HOST'
40EXEC_ACTION=0
41
42#print config
43echo "---------------------------------"
44echo " SOURCE_USER: $SOURCE_USER (MySQL)"
45if [ "SOURCE_PASSWORD" != "" ]; then
46echo " SOURCE_PASSWORD:<present> "; else
47echo " SOURCE_PASSWORD:<to be asked> "
48fi
49echo " SOURCE_HOST: $SOURCE_HOST "
50echo " SOURCE_DBNAME: $SOURCE_DBNAME "
51echo " TARGET_HOST: $TARGET_HOST "
52echo " TARGET_DBNAME: $TARGET_DBNAME "
53echo " TARGET_SSHUSER: $TARGET_SSHUSER "
54if [ "TARGET_SSHPASSWORD" != "" ]; then
55echo " TARGET_SSHPASS: <present> "; else
56echo " TARGET_SSHPASS: <to be asked> "
57fi
58echo " TABLES: $TABLES "
59echo " EXEC_ACTION: $EXEC_ACTION - ${EXEC_ACTION_TEXT[$EXEC_ACTION]}"
60echo " TARGET_DIR: $TARGET_DIR (only for action 1)"
61echo "---------------------------------"
62echo "PRESS <ENTER> to continue..."; read; echo
63
64#read the mysql password from command-line (SOURCE and TARGET uses the same password)
65if [ "$SOURCE_PASSWORD" == "" ]; then
66 echo -n "Type $SOURCE_USER password for MySQL servers: "; read -s SOURCE_PASSWORD; echo
67fi
68echo "Creating database $TARGET_DBNAME on $TARGET_HOST if not exists ... "
69mysql
70--user=$SOURCE_USER
71--password=$SOURCE_PASSWORD
72--host=$TARGET_HOST
73--execute "create database if not exists $TARGET_DBNAME;"
74
75echo '--------------------------------------------------------------------------------------'
76echo "**** ATTENTION ****: execute this command on mysql server at $TARGET_HOST :"
77echo "GRANT ALL PRIVILEGES ON $TARGET_DBNAME.* TO '$SOURCE_USER'@'%' IDENTIFIED BY 'yourpass';"
78echo '--------------------------------------------------------------------------------------'
79echo "PRESS <ENTER> to continue..."; read; echo
80
81#read the password from command-line
82if [ "$TARGET_SSHPASSWORD" == "" ]; then
83 echo -n "Type the password for remote SSH Server (TARGET) ['$TARGET_SSHUSER'@'$TARGET_HOST']: "; read -s TARGET_SSHPASSWORD; echo
84fi
85
86for thistable in $TABLES
87do
88 case "$EXEC_ACTION" in
89 0)
90 thisaction="gunzip | mysql --user=$SOURCE_USER --password=$SOURCE_PASSWORD -D $TARGET_DBNAME"
91 endmessage='remote reimporting has finished'
92 ;;
93 1)
94 thisaction="cat > $TARGET_DIR/`date +%Y.%m.%d`-"$thistable".gz"
95 endmessage="$thisaction has finished"
96 ;;
97 *) echo "EXEC_ACTION=$EXEC_ACTION not supported" && exit 1
98 esac
99
100 echo "---------------------------------------------------------------------"
101 echo "-- table $thistable"
102 echo "---------------------------------------------------------------------"
103 (
104 echo -n "-- setting variables... " > /dev/stderr #talk to user via stderr
105 echo "SET AUTOCOMMIT=0; SET UNIQUE_CHECKS=0; SET FOREIGN_KEY_CHECKS=0;"
106 echo -n "starting mysqldump... " > /dev/stderr
107 mysqldump --opt --user=$SOURCE_USER --password=$SOURCE_PASSWORD --host=$SOURCE_HOST $SOURCE_DBNAME $thistable
108 echo -n "done mysqldump, reseting variables... " > /dev/stderr
109 echo "SET FOREIGN_KEY_CHECKS=1; SET UNIQUE_CHECKS=1; SET AUTOCOMMIT=1;"
110 echo -n "commiting... " > /dev/stderr
111 echo "COMMIT;"
112 echo "done!" > /dev/stderr
113 ) |
114 gzip -c -2 |
115 pv |
116 sshpass -p $TARGET_SSHPASSWORD ssh $TARGET_SSHUSER'@'$TARGET_HOST $thisaction
117 echo $endmessage ' with exit status '$?
118done