forked from microdevops-com/gitlab-ci-functions
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathmysql.sh
150 lines (133 loc) · 5.23 KB
/
mysql.sh
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
#!/bin/bash
function mysql_create_db () {
local NEW_DB_NAME="$1"
echo 'CREATE DATABASE IF NOT EXISTS `'$NEW_DB_NAME'`;' | mysql -vv -u "$MYSQL_USER" --password="$MYSQL_PASS" -h "$MYSQL_HOST" -P "$MYSQL_PORT"
}
function mysql_truncate_db () {
local DB_NAME="$1"
mysql -u "$MYSQL_USER" --password="$MYSQL_PASS" -h "$MYSQL_HOST" -P "$MYSQL_PORT" -Nse 'show tables' "$DB_NAME" | while read table; do mysql -vv -u "$MYSQL_USER" --password="$MYSQL_PASS" -h "$MYSQL_HOST" -P "$MYSQL_PORT" -e "DROP TABLE $table" "$DB_NAME"; done
}
function mysql_truncate_table () {
local DB_NAME="$1"
local TABLE="$2"
echo 'TRUNCATE TABLE `'$TABLE'`;' | mysql -vv -u "$MYSQL_USER" --password="$MYSQL_PASS" -h "$MYSQL_HOST" -P "$MYSQL_PORT" "$DB_NAME"
}
function mysql_query_from_file () {
local DB_NAME="$1"
local FILE="$2"
cat "$FILE" | mysql -vv -u "$MYSQL_USER" --password="$MYSQL_PASS" -h "$MYSQL_HOST" -P "$MYSQL_PORT" "$DB_NAME"
}
function mysql_query_from_file_no_verbose () {
local DB_NAME="$1"
local FILE="$2"
cat "$FILE" | mysql -u "$MYSQL_USER" --password="$MYSQL_PASS" -h "$MYSQL_HOST" -P "$MYSQL_PORT" "$DB_NAME"
}
function mysql_grant_all_privileges_on_db () {
local DB_NAME="$1"
local USER_NAME="$2"
echo 'GRANT ALL PRIVILEGES ON `'$DB_NAME'`.* TO `'$USER_NAME'`@`%`;' | mysql -vv -u "$MYSQL_USER" --password="$MYSQL_PASS" -h "$MYSQL_HOST" -P "$MYSQL_PORT"
}
function mysql_db_sanitize () {
if [ -z "$2" ]; then
local LENGTH="63"
else
local LENGTH="$2"
fi
echo $1 | tr "[:upper:]" "[:lower:]" | sed "s/[^a-zA-Z0-9-]/-/g" | head -c $LENGTH | sed "s/-$//g" | tr -d '\n' | tr -d '\r'
}
function mysql_delete_from_table_where () {
local DB_NAME="$1"
local TABLE="$2"
local WHERE="$3"
echo 'DELETE FROM `'$TABLE'` WHERE '$WHERE';' | mysql -vv -u "$MYSQL_USER" --password="$MYSQL_PASS" -h "$MYSQL_HOST" -P "$MYSQL_PORT" "$DB_NAME"
}
function mysql_select_count_from_table_where () {
local DB_NAME="$1"
local TABLE="$2"
local WHERE="$3"
echo 'SELECT COUNT(*) FROM `'$TABLE'` WHERE '$WHERE';' | mysql -s -r -u "$MYSQL_USER" --password="$MYSQL_PASS" -h "$MYSQL_HOST" -P "$MYSQL_PORT" "$DB_NAME"
}
# with connection counterparts
function mysql_create_db_with_connect () {
local NEW_DB_NAME="$1"
local MYSQL_USER="$2"
local MYSQL_PASS="$3"
local MYSQL_HOST="$4"
local MYSQL_PORT="$5"
echo 'CREATE DATABASE IF NOT EXISTS `'$NEW_DB_NAME'`;' | mysql -vv -u "$MYSQL_USER" --password="$MYSQL_PASS" -h "$MYSQL_HOST" -P "$MYSQL_PORT"
}
function mysql_truncate_db_with_connect () {
local DB_NAME="$1"
local MYSQL_USER="$2"
local MYSQL_PASS="$3"
local MYSQL_HOST="$4"
local MYSQL_PORT="$5"
mysql -u "$MYSQL_USER" --password="$MYSQL_PASS" -h "$MYSQL_HOST" -P "$MYSQL_PORT" -Nse 'show tables' "$DB_NAME" | while read table; do mysql -vv -u "$MYSQL_USER" --password="$MYSQL_PASS" -h "$MYSQL_HOST" -P "$MYSQL_PORT" -e "DROP TABLE $table" "$DB_NAME"; done
}
function mysql_truncate_table_with_connect () {
local DB_NAME="$1"
local TABLE="$2"
local MYSQL_USER="$3"
local MYSQL_PASS="$4"
local MYSQL_HOST="$5"
local MYSQL_PORT="$6"
echo 'TRUNCATE TABLE `'$TABLE'`;' | mysql -vv -u "$MYSQL_USER" --password="$MYSQL_PASS" -h "$MYSQL_HOST" -P "$MYSQL_PORT" "$DB_NAME"
}
function mysql_query_from_file_with_connect () {
local DB_NAME="$1"
local FILE="$2"
local MYSQL_USER="$3"
local MYSQL_PASS="$4"
local MYSQL_HOST="$5"
local MYSQL_PORT="$6"
cat "$FILE" | mysql -vv -u "$MYSQL_USER" --password="$MYSQL_PASS" -h "$MYSQL_HOST" -P "$MYSQL_PORT" "$DB_NAME"
}
function mysql_query_from_file_no_verbose_with_connect () {
local DB_NAME="$1"
local FILE="$2"
local MYSQL_USER="$3"
local MYSQL_PASS="$4"
local MYSQL_HOST="$5"
local MYSQL_PORT="$6"
cat "$FILE" | mysql -u "$MYSQL_USER" --password="$MYSQL_PASS" -h "$MYSQL_HOST" -P "$MYSQL_PORT" "$DB_NAME"
}
function mysql_grant_all_privileges_on_db_with_connect () {
local DB_NAME="$1"
local USER_NAME="$2"
local MYSQL_USER="$3"
local MYSQL_PASS="$4"
local MYSQL_HOST="$5"
local MYSQL_PORT="$6"
echo 'GRANT ALL PRIVILEGES ON `'$DB_NAME'`.* TO `'$USER_NAME'`@`%`;' | mysql -vv -u "$MYSQL_USER" --password="$MYSQL_PASS" -h "$MYSQL_HOST" -P "$MYSQL_PORT"
}
function mysql_delete_from_table_where_with_connect () {
local DB_NAME="$1"
local TABLE="$2"
local WHERE="$3"
local MYSQL_USER="$4"
local MYSQL_PASS="$5"
local MYSQL_HOST="$6"
local MYSQL_PORT="$7"
echo 'DELETE FROM `'$TABLE'` WHERE '$WHERE';' | mysql -vv -u "$MYSQL_USER" --password="$MYSQL_PASS" -h "$MYSQL_HOST" -P "$MYSQL_PORT" "$DB_NAME"
}
function mysql_select_count_from_table_where_with_connect () {
local DB_NAME="$1"
local TABLE="$2"
local WHERE="$3"
local MYSQL_USER="$4"
local MYSQL_PASS="$5"
local MYSQL_HOST="$6"
local MYSQL_PORT="$7"
echo 'SELECT COUNT(*) FROM `'$TABLE'` WHERE '$WHERE';' | mysql -s -r -u "$MYSQL_USER" --password="$MYSQL_PASS" -h "$MYSQL_HOST" -P "$MYSQL_PORT" "$DB_NAME"
}
function mysql_copy_table_to_local () {
local DB_NAME="$1"
local TABLE_NAME="$2"
local OPTIONS="$3"
echo CMD: Copy table ${TABLE_NAME} with options ${OPTIONS}
mysqldump --single-transaction -alv \
-h ${SYNC_MYSQL_STAGE_HOST} -u ${SYNC_MYSQL_STAGE_USER} -p${SYNC_MYSQL_STAGE_PASS} \
--databases "${DB_NAME}" --tables "${TABLE_NAME}" --where="${OPTIONS}" 2> /dev/stderr | mysql \
-h ${SYNC_MYSQL_LOCAL_HOST} -u ${SYNC_MYSQL_LOCAL_USER} -p${SYNC_MYSQL_LOCAL_PASS} \
"${DB_NAME}"
}