· 8 years ago · Apr 04, 2018, 03:52 PM
1mysql> SELECT DISTINCT table_schema FROM information_schema.tables WHERE table_schema NOT IN ('information_schema','mysql')
2 -> ;
3ERROR 126 (HY000): Incorrect key file for table '/tmp/#sql_29ef_0.MYI'; try to repair it
4
5# df -h /tmp/
6Filesystem Size Used Avail Use% Mounted on
7tmpfs 2.0G 12K 2.0G 1% /tmp
8# df -i /tmp/
9Filesystem Inodes IUsed IFree IUse% Mounted on
10tmpfs 2041621 7 2041614 1% /tmp
11# mount | grep /tmp
12tmpfs on /tmp type tmpfs (rw,size=2048M)
13
14# cd /var/lib/mysql ; ls -lR | grep -c ".frm$"
151876765
16
17# cd /var/lib/mysql ; ls -lR | grep -c ".frm$"
1849514
19
20# ./backup.sh
21Backing up database number: 1
22Completed in: 0.016
23Backing up database number: 2
24Completed in: 0.078
25Backing up database number: 3
26Completed in: 0.074
27Backing up database number: 4
28Completed in: 0.068
29Backing up database number: 5
30Completed in: 0.071
31Backing up database number: 6
32Completed in: 0.060
33Backing up database number: 7
34Completed in: 0.067
35Backing up database number: 8
36Completed in: 0.070
37Backing up database number: 9
38Completed in: 0.065
39.....
40Backing up database number: 107
41Completed in: 10.749
42Backing up database number: 108
43Completed in: 12.125
44Backing up database number: 109
45Completed in: 11.313
46Backing up database number: 110
47Completed in: 11.572
48Backing up database number: 111
49Completed in: 11.371
50.....
51
52#!/usr/bin/env bash
53
54DATA_DIR="/tmp/mysqlbackup"
55LOCKFILE=/tmp/backup.lock
56NOW=$(date +%Y%m%d)
57COUNT=1
58
59if [ -f "$LOCKFILE" ]; then
60 echo "$(basename $0) is already running: PID $(cat $LOCKFILE)"
61 exit 0
62
63else
64
65 echo $$ > $LOCKFILE
66
67 if [ ! -d $DATA_DIR/$NOW ]; then
68 mkdir -m 700 -p $DATA_DIR/$NOW
69 fi
70
71 while read DB; do
72
73 (( COUNT++ ))
74
75 echo "Backing up database number: $COUNT"
76
77 START=$(date +%s.%N)
78
79 mydumper -e -o $DATA_DIR/$NOW/$DB -B "$DB"
80
81 ELAPSED=$(date +%s.%N)
82
83 printf "Completed in: %.3Fn" $(echo $ELAPSED - $START | bc)
84
85 done <<< "$(mysql -A -B -N -e "SHOW DATABASES" | egrep -v '(mysql|*_schema|log)')"
86
87 echo "Removing backup dir...";
88
89 rm -rf $DATA_DIR/$NOW
90
91 rm -f $LOCKFILE
92
93 exit 0
94
95fi
96
97mysql> show create table information_schema.tablesG
98*************************** 1. row ***************************
99 Table: TABLES
100Create Table: CREATE TEMPORARY TABLE `TABLES` (
101 `TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '',
102 `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
103 `TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
104 `TABLE_TYPE` varchar(64) NOT NULL DEFAULT '',
105 `ENGINE` varchar(64) DEFAULT NULL,
106 `VERSION` bigint(21) unsigned DEFAULT NULL,
107 `ROW_FORMAT` varchar(10) DEFAULT NULL,
108 `TABLE_ROWS` bigint(21) unsigned DEFAULT NULL,
109 `AVG_ROW_LENGTH` bigint(21) unsigned DEFAULT NULL,
110 `DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,
111 `MAX_DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,
112 `INDEX_LENGTH` bigint(21) unsigned DEFAULT NULL,
113 `DATA_FREE` bigint(21) unsigned DEFAULT NULL,
114 `AUTO_INCREMENT` bigint(21) unsigned DEFAULT NULL,
115 `CREATE_TIME` datetime DEFAULT NULL,
116 `UPDATE_TIME` datetime DEFAULT NULL,
117 `CHECK_TIME` datetime DEFAULT NULL,
118 `TABLE_COLLATION` varchar(32) DEFAULT NULL,
119 `CHECKSUM` bigint(21) unsigned DEFAULT NULL,
120 `CREATE_OPTIONS` varchar(255) DEFAULT NULL,
121 `TABLE_COMMENT` varchar(2048) NOT NULL DEFAULT ''
122) ENGINE=MEMORY DEFAULT CHARSET=utf8
1231 row in set (0.00 sec)
124
125mysql>
126
127DROP TABLE IF EXISTS mysql.dbtbcount;
128CREATE TABLE mysql.dbtbcount
129(
130 table_schema VARCHAR(64) NOT NULL,
131 table_count INT NOT NULL DEFAULT 0,
132 PRIMARY KEY (table_schema)
133) ENGINE=MEMORY;
134INSERT INTO mysql.dbtbcount (table_schema)
135SELECT schema_name FROM information_schema.schemata
136WHERE schema_name NOT IN ('information_schema','mysql');
137INSERT INTO mysql.dbtbcount (table_schema)
138SELECT table_schema FROM information_schema.tables
139ON DUPLICATE KEY UPDATE table_count = table_count + 1;
140SELECT * FROM mysql.dbtbcount;
141SELECT COUNT(1) DistinctDBs FROM mysql.dbtbcount WHERE table_count > 0;
142
143BACKUP_FOLDER=/any/path/but/tmp
144
145MYSQL_HOST=localhost
146MYSQL_USER=root
147MYSQL_PASS=rootpassword
148MYSQL_CONN="-h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS}"
149
150SQLSTMT="SELECT db FROM (SELECT table_schema db,SUM(data_length) datasize"
151SQLSTMT-"${SQLSTMT} FROM information_schema.tables"
152SQLSTMT-"${SQLSTMT} WHERE table_schema NOT ('mysql','log')"
153SQLSTMT-"${SQLSTMT} AND RIGHT(table_schema,7)<>'_schema'"
154SQLSTMT-"${SQLSTMT} GROUP BY table_schema) A"
155SQLSTMT-"${SQLSTMT} ORDER BY datasize DESC"
156mysql ${MYSQL_CONN} -ANe"${SQLSTMT}" > /tmp/ListOfDatabases.txt
157
158COMMIT_COUNT=0
159COMMIT_LIMIT=25
160MYSQLDUMP_OPTIONS="--hex-blob --routines --triggers"
161for DB in `cat /tmp/ListOfDatabases.txt`
162do
163 BACKUP_FILE=${BACKUP_FOLDER}/${DB}.sql.gz
164 mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} ${DB} | gzip > ${BACKUP_FILE} &
165 (( COMMIT_COUNT++ ))
166 if [ ${COMMIT_COUNT} -eq ${COMMIT_LIMIT} ]
167 then
168 COMMIT_COUNT=0
169 wait
170 fi
171done
172if [ ${COMMIT_COUNT} -gt 0 ]
173then
174 wait
175fi
176
177BACKUP_FOLDER=/any/path/but/tmp
178
179MYSQL_HOST=localhost
180MYSQL_USER=root
181MYSQL_PASS=rootpassword
182MYSQL_CONN="-h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS}"
183
184SQLSTMT-"${SQLSTMT} SELECT schema_name FROM information_schema.schemata"
185SQLSTMT-"${SQLSTMT} WHERE schema_name NOT ('mysql','log')"
186SQLSTMT-"${SQLSTMT} AND RIGHT(schema_name ,7)<>'_schema'"
187
188COMMIT_COUNT=0
189COMMIT_LIMIT=25
190MYSQLDUMP_OPTIONS="--hex-blob --routines --triggers"
191for DB in `cat /tmp/ListOfDatabases.txt`
192do
193 BACKUP_FILE=${BACKUP_FOLDER}/${DB}.sql.gz
194 mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} ${DB} | gzip > ${BACKUP_FILE} &
195 (( COMMIT_COUNT++ ))
196 if [ ${COMMIT_COUNT} -eq ${COMMIT_LIMIT} ]
197 then
198 COMMIT_COUNT=0
199 wait
200 fi
201done
202if [ ${COMMIT_COUNT} -gt 0 ]
203then
204 wait
205fi