snippets / mysql
All snippets tagged mysql (5)
-
MySQL random password string
Random string (like password).
1 SELECT CONV(FLOOR(RAND() * 99999999999999), 10, 36);
-
A sample config for MySQL Connector/J 5.1 for Tomcat 5.5
Replace path and docBase and etc. with your applications settings. This file assumes that this file is named apps-myapp.xml and is located under Tomcats configuration directory. It is also assumed that the application which is going to use MyConnection JDBC connection is called myapp and is accessed as http://yourdomain.tld/myapp and is located under Tomcats webapps directory. Database name is assumed my_database. Replace these with your actual values
1 <?xml version="1.0" encoding="ISO-8859-1"?>
2 <webapps>
3 <Context path="/myapp" docBase="webapps/myapp" debug="0" reloadable="true">
4 <!-- Replace path and docBase and etc. with your applications settings.
5 This file assumes that this file is named apps-myapp.xml and is
6 located under Tomcats configuration directory. It is also assumed that
7 the application which is going to use MyConnection JDBC connection is
8 called myapp and is accessed as http://yourdomain.tld/myapp and is
9 located under Tomcats webapps directory. Database name is assumed
10 my_database. Replace these with your actual values. -->
11
12
13 <!-- This is a sample XML config file for an Apache Tomcat 5.5 server to
14 setup MySQL Connector/J 5.1 for JDBC connectivity. Information are
15 derived from sources available in the Internet. -->
16
17
18 <!-- Resource name and ResourceParams name, must be the same and
19 The connection pool will be bound into JNDI with that name
20 Eg: "java:/comp/env/jdbc/MyConnection". Replace MyConnection in both
21 places with the name you want for the connection. -->
22 <Resource name="jdbc/MyConnection" auth="Container" type="javax.sql.DataSource"/>
23 <ResourceParams name="jdbc/MyConnection">
24
25
26 <parameter>
27 <name>factory</name>
28 <value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
29 </parameter>
30
31
32 <!-- Don't set this higher than max_connections on your MySQL server,
33 usually this should be a 10 or a few 10's of connections, not
34 hundreds or thousands -->
35 <parameter>
36 <name>maxActive</name>
37 <value>10</value>
38 </parameter>
39
40
41 <!-- Just allow only as many idle connections as you require. Too much
42 is bad. -->
43 <parameter>
44 <name>maxIdle</name>
45 <value>5</value>
46 </parameter>
47
48
49 <!-- Don't use autoReconnect=true, it's going away eventually and it's a
50 crutch for older connection pools that couldn't test connections.
51 You need to decide whether your application is supposed to deal with
52 SQLExceptions (hint, it should), and how much of a performance
53 penalty you're willing to pay to ensure 'freshness' of the connection -->
54 <parameter>
55 <name>validationQuery</name>
56 <value>SELECT 1</value>
57 </parameter>
58
59
60 <!-- The most conservative approach is to test connections before they're
61 given to your application. For most applications this is okay, the
62 query used above is very small and takes no real server resources to
63 process, other than the time used to traverse the network. If you have
64 a high-load application you'll need to rely on something else. -->
65 <parameter>
66 <name>testOnBorrow</name>
67 <value>true</value>
68 </parameter>
69
70
71 <!-- Otherwise, or in addition to testOnBorrow, you can test while
72 connections are sitting idle -->
73 <parameter>
74 <name>testWhileIdle</name>
75 <value>true</value>
76 </parameter>
77
78
79 <!-- You have to set this value, otherwise even though you've asked
80 connections to be tested while idle,the idle evicter thread
81 will never run -->
82 <parameter>
83 <name>timeBetweenEvictionRunsMillis</name>
84 <value>10000</value>
85 </parameter>
86
87
88 <!-- Don't set this too high. A few minutes or even fraction of a minute
89 is sometimes okay here, it depends on your application and how much
90 spikey load it will see -->
91 <parameter>
92 <name>minEvictableIdleTimeMillis</name>
93 <value>60000</value>
94 </parameter>
95
96
97 <!-- Username and password used when connecting to MySQL. Replace myuser
98 and mypassword with your username/password for the database user. -->
99 <parameter>
100 <name>username</name>
101 <value>myuser</value>
102 </parameter>
103 <parameter>
104 <name>password</name>
105 <value>mypassword</value>
106 </parameter>
107
108
109 <!-- Class name for the Connector/J driver -->
110 <parameter>
111 <name>driverClassName</name>
112 <value>com.mysql.jdbc.Driver</value>
113 </parameter>
114
115
116 <!-- The JDBC connection url for connecting to MySQL, notice that if
117 you want to pass any other MySQL-specific parameters you should
118 pass them here in the URL, setting them using the parameter
119 tags above will have no effect, you will also need to use &
120 to separate parameter values as the ampersand is a reserved
121 character in XML. Replace my_database with your database name. -->
122 <parameter>
123 <name>url</name>
124 <value>jdbc:mysql://yourdomain.tld:3306/my_database</value>
125 </parameter>
126
127
128 </ResourceParams>
129 </Context>
130 </webapps> -
Rotate backup files (mysql - mysqlhotcopy)
Makes backup named 1_dump.tar.gz, 2_dump.tar.gz... You'll get as many backup as $ROT_PERIOD. In this example, you have backup history for 15 days. Date is stored via extracted tgz dir name.
1 #!/bin/bash
2
3 ################################################
4 # author : Alexandre BULTE - alexandre[at]bulte[dot]net
5 # license : GPL v2
6 ################################################
7
8 BACKUP_PATH='/home/xxx/backup'
9 # db names
10 DBS='db1 db2 db3'
11 MYSQL_USER='root'
12 MYSQL_PASSWD='xxxxxx'
13 ROT_PERIOD=15
14 NEXT_ID=0
15 DATE_TODAY=$( date +%Y%m%d-%H%M%S )
16
17 # rep sauvegarde
18 REP=$BACKUP_PATH/$DATE_TODAY
19 if [ ! -d $REP ];
20 then
21 mkdir $REP
22 fi
23
24 # fetch biggest id in dir
25 cd $BACKUP_PATH
26 BIG_ID=$( ls -1 *_dump.tar.gz | tail -n 1 | cut -d '_' -f 1 ) &> /dev/null
27
28 if [ ! $BIG_ID ];
29 then
30 BIG_ID=0
31 fi
32
33 # rotation if at least 1_dump.tar.gz
34 for i in $( seq 1 $BIG_ID );
35 do
36 NEXT_ID=$( expr $i + 1 )
37 NEXT_FILENAME=$NEXT_ID'_dump.tar.gz'
38 FILENAME=$i'_dump.tar.gz'
39 if [ -e $FILENAME ];
40 then
41 echo "$FILENAME exists"
42 if [ $i = $ROT_PERIOD ];
43 then
44 echo "Removing oldest archive..."
45 rm $i'_dump.tar.gz'
46 else
47 echo "Rotating $i..."
48 cp $FILENAME $NEXT_FILENAME
49 fi
50 fi
51 done
52
53 # sauvegarde
54 mysqlhotcopy -q -u $MYSQL_USER -p $MYSQL_PASSWD $DBS $REP
55
56 # compression
57 tar cfz 1_dump.tar.gz $DATE_TODAY
58
59 # suppression
60 rm -rf $DATE_TODAY -
connection a une base de donnée MySQL en C
Code en C permettant l'accès a une base de donnée MySQL C code wich permit MySQL access
1 /*********************************************************
2 **Attention il est impératif de compiler ce code avec l'option de compilation -lmysqlclient
3 **********************************************************/
4
5 #include <stdio.h>
6 #include <errno.h>
7 #include <stdlib.h>
8 #include <iostream.h>
9 #include <fstream>
10 #include <mysql/mysql.h>
11 #include <sys/types.h>
12 #include <sys/wait.h>
13
14 #define MY_SERVER_HOST "localhost"
15 #define MY_SERVER_PORT 0
16 #define MY_ACCOUNT "root"
17 #define MY_PASS "test"
18 #define MY_DB_NAME "itest"
19 #define MY_TABLE_NAME "test_tbl"
20 #define MY_UX_SOCK NULL
21 #define MY_CLIENT_FLAG 0
22
23 void main()
24 {
25 MYSQL *mysql;
26 MYSQL_RES *res;
27 MYSQL_ROW row;
28 MYSQL_ROW rowchamps;
29 const char *query;
30 string qutmp;
31
32 int t,f,tt,ff;
33
34 mysql=mysql_init(NULL);
35
36 if (!mysql_real_connect(mysql,MY_SERVER_HOST,MY_ACCOUNT,MY_PASS,bd,MY_SERVER_PORT,MY_UX_SOCK,MY_CLIENT_FLAG)) {
37 printf( "Erreur de connexion : %s\n",mysql_error(mysql));
38 }
39 else {
40 qutmp = "SHOW FULL COLUMNS FROM TABLE ";
41 query = qutmp.c_str();
42 t=mysql_real_query(mysql, query, (unsigned int) strlen(query));
43 if((res=mysql_use_result(mysql))) {
44 f=mysql_num_fields(res);
45 while((row=mysql_fetch_row(res))) {
46 for(f=0;f<t;f++){
47 cout<<row[f]<<endl;
48 }
49 }
50 mysql_free_result(res);
51 }
52 }
53 mysql_close(mysql);
54 } -
new_site
Ce script permet de créer un site complet sur un serveur (dossiers, logs, stats, base MySQL, etc...). Il nécessite une certaine structure dans la gestion des espaces web. Il nécessite aussi : a2ensite, pwgen et un fichier /root/.my.cnf pour ne pas avoir à rentrer les mot de passe MySQL de l'utilisateur root.
1 #!/bin/bash
2 ###############################################################################
3 #
4 # new_site : Permet de créer un site web sur un serveur
5 #
6 # Requiert :
7 # - a2ensite
8 # - pwgen
9 # - Fichier /root/.my.cnf bien configuré ;-)
10 #
11 # by Guillaume Kulakowski a.k.a LLaumgui <guillaume at llaumgui dot com>
12 # Version 1.0
13 #
14 ###############################################################################
15 # This program is free software; you can redistribute it and/or
16 # modify it under the terms of the GNU General Public License
17 # as published by the Free Software Foundation; either version 2
18 # of the License, or (at your option) any later version.
19 #
20 # This program is distributed in the hope that it will be useful,
21 # but WITHOUT ANY WARRANTY; without even the implied warranty of
22 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
23 # GNU General Public License for more details.
24 #
25 # You should have received a copy of the GNU General Public License
26 # along with this program; if not,
27 # - write to the Free Software
28 # Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
29 # - See http://www.gnu.org/licenses/gpl.html
30 ###############################################################################
31
32
33 # Varibles
34 VHOST_MODEL='/etc/httpd/vhost.model.conf' # Chemin vers le modèle pour les vhost
35 VHOST_PATH='/etc/httpd/sites-available' # Chemin vers les fichiers vhost
36
37 AWSTATS_MODEL='/etc/httpd/awstats.model.conf' # Chemin vers le modèle awstats
38 AWSTATS_PATH='/etc/awstats' # Chemin vers les fichiers de conf awstats
39 AWSTATS=1 # Activer Awstats
40 MAIL_ADMIN=root # Mail de l'administrateur
41
42 # Valeurs par défaut
43 DEF_WWW_PATH="/home/llaumgui/public_html" # Chemin par défaut pour les sites
44 DEF_SERVER_ADMIN="spb-box@llaumgui.com" # Valeur par défaut pour ServerAdmin
45
46
47
48
49
50 ##
51 ## Affichage du récapitulatif
52 ##
53 function recap(){
54
55 echo "Récapitulatif"
56 echo "================================"
57 echo -e "\nConfiguration du site"
58 echo "-----------------------------"
59 echo " Adresse du site : http://$WWW_URL"
60 echo " Chemin vers le site : $WWW_PATH"
61 if [ "$PERM" != "" ]; then
62 echo " Permissions données à : $PERM"
63 fi
64
65 echo -e "\nConfiguration d'apache"
66 echo "-----------------------------"
67 echo " ServerAdmin : $SERVER_ADMIN"
68
69 echo -e "\nConfiguration de MySQL"
70 echo "-----------------------------"
71 if [ "$DB_NAME" != "" ]; then
72 echo " Base de données : $DB_NAME"
73 echo " Nom de l'utilisateur : $DB_LOGIN"
74 if [ "$1" = 0 ]; then
75 echo " Mot de passe MySQL : $DB_PASSWD"
76 else
77 echo " Mot de passe MySQL : ********"
78 fi
79 else
80 echo " *** Pas de base de données ***"
81 fi
82
83 echo -e "\nConfiguration d'Awstats"
84 echo "-----------------------------"
85 if [ $AWSTATS = 0 ]; then
86 echo " Stats Awstats : Activé"
87 else
88 echo " Stats Awstats : Désactivé"
89 fi
90 }
91
92
93
94 ##
95 ## Valider la récapitulation
96 ##
97 function recap_valid() {
98
99 echo -n "Est-ce correct ? (o/[n]) "; read CORRECT
100 if [ "$CORRECT" = "" ]; then
101 recap_valid
102 fi
103 if [ "$CORRECT" != "o" -a "$CORRECT" != "O" -a "$CORRECT" != "y" -a "$CORRECT" != "Y" ]; then
104 exit 1
105 fi
106
107 return 0
108 }
109
110
111
112
113
114 ###################################
115 ##
116 ## Définition des paramètres
117 ##
118 ###################################
119
120 ##
121 ## Défini l'url du site :
122 ##
123 function set_url() {
124
125 echo -n "URL principale du site (ex: www.google.fr) ? "; read WWW_URL
126
127 if [ "$WWW_URL" = "" ]; then
128 echo -e "\n\nErreur ! Veuillez renseigner une URL pour le site !";
129 set_url
130 fi
131
132 if [ -f "$VHOST_PATH/$WWW_URL" ]; then
133 echo -e "\n\nErreur ! Le site existe déjà !";
134 set_url
135 fi
136
137 return 0
138 }
139
140
141
142 ##
143 ## Défini le chemin vers le site
144 ## @TODO : Mettre en place la vérification du chemin.
145 function set_path() {
146
147 echo -n "Répertoire du site [$DEF_WWW_PATH/$WWW_URL] ? "; read WWW_PATH
148
149 if [ "$WWW_PATH" = "" ]; then
150 WWW_PATH="$DEF_WWW_PATH/$WWW_URL";
151 fi
152
153 if [ -d "$WWW_PATH" ]; then
154 echo -e "\n\nErreur ! Le site existe déjà !";
155 set_path
156 fi
157
158 echo -n "Donner les permissions sur $WWW_PATH/$WWW_URL/www à l'utilisateur UNIX [] ? "; read PERM
159
160 return 0
161 }
162
163
164
165 ##
166 ## Défini le paramètre ServerAdmin du vhost
167 ##
168 function set_serveradmin() {
169
170 echo -n "ServerAdmin [$DEF_SERVER_ADMIN] ? "; read SERVER_ADMIN
171
172 if [ "$SERVER_ADMIN" = "" ]; then
173 SERVER_ADMIN="$DEF_SERVER_ADMIN";
174 fi
175
176 return 0
177 }
178
179
180
181 ##
182 ## Défini le nom de la base
183 ##
184 function set_db_name() {
185
186 echo -n "Nom de la base de données [] ? "; read DB_NAME
187
188 if [ "$DB_NAME" = "" ]; then
189 return 1
190 fi
191
192 mysqlshow -u root "$DB_NAME" &> /dev/null
193 if [ $? -eq 0 ] ; then
194 echo -e "\n\nLa base de données $DB_NAME existe déjà !";
195 set_db_name
196 fi
197
198 return 0
199 }
200
201
202
203 ##
204 ## Défini du login pour la connection à la base
205 ## @TODO : Mettre en place la vérification de l'user.
206 ##
207 function set_db_login() {
208
209 echo -n "Login pour la connection à la base de données ? "; read DB_LOGIN
210
211 return 0
212 }
213
214
215
216
217 ##
218 ## Définition du mot de passe BD
219 ##function set_db_password() {
220
221 stty -echo;
222 echo -n "Mot de passe pour la base de données [] ? "; read DB_PASSWD1;
223
224 if [ "$DB_PASSWD1" = "" ]; then
225 stty echo;
226 echo -e "\n\nGénération d'un mot de passe avec pwgen..."
227 DB_PASSWD=`pwgen 16 1`
228 echo "$DB_PASSWD"
229 return 0
230 fi
231 echo -ne "\nVeuillez retaper le mot de passe [] ? "; read DB_PASSWD2;
232 if [ $DB_PASSWD1 != $DB_PASSWD2 ]; then
233 echo -e "\n\nLes 2 mots de passe saisis sont dissérents !";
234 set_db_password
235 fi
236 stty echo;
237 DB_PASSWD=$DB_PASSWD1
238
239 return 0
240 }
241
242
243
244 ##
245 ## Définition des stats
246 ##
247 function set_awstats() {
248
249 echo -n "Activer les stats Awstats ? (o/[n]) "; read TMP_AWSTATS
250 if [ "$TMP_AWSTATS" = "o" -o "$TMP_AWSTATS" = "O" -o "$TMP_AWSTATS" = "y" -o "$TMP_AWSTATS" = "Y" ]; then
251 AWSTATS=0
252 else
253 AWSTATS=1
254 fi
255
256 return 0
257 }
258
259
260
261 ##
262 ## Définition du login htaccess pour awstats
263 ##
264 function set_awstats_login() {
265
266 echo -n "Login htaccess pour Awstats ? "; read AW_LOGIN
267
268 if [ "$AW_LOGIN" = "" ]; then
269 set_awstats_login
270 fi
271
272 return 0
273 }
274
275
276
277 ##
278 ## Définition du mot de passe htaccess pour awstats
279 ##
280 function set_awstats_password() {
281
282 stty -echo;
283 echo -n "Mot de passe htaccess pour Awstats [] ? "; read AW_PASSWD1;
284
285 if [ "$AW_PASSWD1" = "" ]; then
286 stty echo;
287 echo -e "\n\nGénération d'un mot de passe avec pwgen..."
288 AW_PASSWD=`pwgen 16 1`
289 echo "$AW_PASSWD"
290 return 0
291 fi
292 echo -ne "\nVeuillez retaper le mot de passe [] ? "; read AW_PASSWD2;
293 if [ $AW_PASSWD1 != $AW_PASSWD2 ]; then
294 echo -e "\n\nLes 2 mots de passe saisis sont dissérents !";
295 set_awstats_password
296 fi
297 stty echo;
298 AW_PASSWD=$AW_PASSWD1
299
300 return 0
301 }
302
303
304
305
306
307 ###################################
308 ##
309 ## Action de création du domaine
310 ##
311 ###################################
312
313 ##
314 ## Création des répertoires web
315 ##
316 function new_www() {
317
318 mkdir "$WWW_PATH"; echo "Création du répertoire $WWW_PATH"
319 mkdir "$WWW_PATH/www"; echo "Création du répertoire $WWW_PATH/www"
320 mkdir "$WWW_PATH/logs"; echo "Création du répertoire $WWW_PATH/logs"
321 mkdir "$WWW_PATH/awstats"; echo "Création du répertoire $WWW_PATH/awstats"
322
323 if [ "$PERM" != "" ]; then
324 chown "$PERM":"$PERM" "$WWW_PATH/www"; echo "Attribution des droits sur le répertoire www"
325 fi
326
327 return 0
328 }
329
330
331
332 ##
333 ## Configuration d'apache
334 ##
335 function new_vhost() {
336
337 new_vhost=`cat $VHOST_MODEL | \
338 sed "s,@@ServerAdmin@@,$SERVER_ADMIN,g" | \
339 sed -e "s,@@DocumentRoot@@,$WWW_PATH,g" | \
340 sed "s,@@ServerName@@,$WWW_URL,g"`
341
342 if [ $AWSTATS = 0 ]; then
343 new_vhost=`echo "$new_vhost"|sed "s,#@@,,g"`
344 fi
345
346 echo "$new_vhost" > "$VHOST_PATH/$WWW_URL"; echo "Configuration du VirtualHost"
347
348 vi "$VHOST_PATH/$WWW_URL"
349 return 0
350 }
351
352
353
354 ##
355 ## Configuration d'Awstats
356 ##function new_awstats() {
357 cat $AWSTATS_MODEL | \
358 sed -e "s,@@DocumentRoot@@,$WWW_PATH,g" | \
359 sed "s,@@ServerName@@,$WWW_URL,g" \
360 > "$AWSTATS_PATH/awstats.$WWW_URL.conf"; echo "Configuration des stats"
361
362 vi "$AWSTATS_PATH/awstats.$WWW_URL.conf"
363
364 htpasswd -cb $WWW_PATH/.htpasswdstats $AW_LOGIN $AW_PASSWD
365 return 0
366 }
367
368
369
370 ##
371 ## Création de la base de données
372 ##
373 function new_db() {
374 mysqladmin -u root create "$DB_NAME"
375 if [ $? -eq 0 ] ; then
376 echo "Base de données $DB_NAME créée avec succès"
377 else
378 echo "Erreur lors de la création de la base de données $DB_NAME"
379 return 1
380 fi
381
382 mysql -u root mysql -e "GRANT ALL ON \`$DB_NAME\`.* TO \"$DB_LOGIN\"@\"localhost\" IDENTIFIED BY \"$DB_PASSWD\";"
383 if [ $? -eq 0 ] ; then
384 echo "Utilisateur $DB_LOGIN créé avec succès"
385 else
386 echo "Erreur lors de la création de l'utilisateur $DB_LOGIN"
387 return 1
388 fi
389 return 0
390 }
391
392
393
394
395
396 ###################################
397 ##
398 ## Code brut
399 ##
400 ###################################
401
402 # Questions
403 echo -e "\nConfiguration du site"
404 echo -e "================================\n"
405 set_url
406 set_path
407
408 echo -e "\n\nConfiguration d'apache"
409 echo -e "================================\n"
410 set_serveradmin
411
412 echo -e "\n\nConfiguration de MySQL"
413 echo -e "================================\n"
414 set_db_name
415 if [ "$DB_NAME" != "" ]; then
416 set_db_login
417 set_db_password
418 fi
419
420 echo -e "\n\nConfiguration d'Awstats"
421 echo -e "================================\n"
422 set_awstats
423 if [ "$AWSTATS" = 0 ]; then
424 set_awstats_login
425 set_awstats_password
426 fi
427 echo -e "\n\n\n\n"
428
429
430 # Récapitulatif
431 recap
432 recap_valid 1
433
434
435 # All is good !
436 echo -e "\n\n"
437 new_www
438 new_vhost
439 if [ "$DB_NAME" != "" ]; then new_db; fi
440 if [ "$AWSTATS" = 0 ]; then new_awstats; fi
441
442 if [ "$MAIL_ADMIN" != "" ]; then
443 recap 0 | mail -s "Création d'un site sur le serveur `hostname`" to-addr "$MAIL_ADMIN"
444 fi
445
446 /root/bin/a2ensite $WWW_URL
Pages : 1
Flux RSS friendsnippetLatest snippets
- c++ QT saatin yaninda... | 4 months, 3 weeks
- src/css/styles.css | 4 months, 3 weeks
- src/charts/Node.as | 4 months, 3 weeks
- src/charts/NodeSkin.mxml | 4 months, 3 weeks
- src/programmaticSkinClass... | 4 months, 3 weeks
