I have two different Linux servers, and wanted to test if one where faster than the other. I used mysqlslap together with the “time” command in a bash shell. # time mysqlslap –user=root –password=$(cat /root/mysql) –host=localhost concurrency=50 –iterations=100 –number-int-cols=5 –number-char-cols=20 –auto-generate-sql –verbose In this way I could get out different values from the server which […]
We have a Linux server (Redhat Enterprise 6) running a multi-site installation of Mediawiki. In total, we have today 120 unique wiki’s, each having its one Postgresql 9.1 database. Suddenly the server stopped responding. In Apache webserver, the total number concurrent apache processes went up to Apache’s max-client setting in httpd.conf, and people couldn’t log […]
We have a Postgresql server open to many different people at our organization. In order to add a ldap authentication you can add these 3 lines to your current pg_hba.conf file:
|
1 2 3 |
host all username 127.0.0.1/32 ldap ldapserver="ourserver.uib.no" ldapbasedn="ou=people,dc=uib,dc=no" host all username 129.177.XXX.YYY/32 ldap ldapserver="ourserver.uib.no" ldapbasedn="ou=people,dc=uib,dc=no" local all username ldap ldapserver="ourserver.uib.no" ldapbasedn="ou=people,dc=uib,dc=no" |
where the first one will allow users that access the server from localhost (either from a shell or 127.0.0.1 connections). The second line […]
I upgraded our Open Journal Systems from 2.2.2 to 2.4.2 and afterwards we saw the email logs were gone. This was related to: http://pkp.sfu.ca/support/forum/viewtopic.php?f=8&t=9140 My solution was this: Established a new Postgresql server in a virtual enviroment (vmware, test-machine), and import the old and the new (upgraded) database.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
# Log in to Prod server ssh prodserver # Dump the related and upgraded database to a sql file pg_dump -U postgres -f /tmp/ojs_new.sql ojs_new # Copy the file to a Test server where you can play around scp ojs_new.sql username@my_vmware_test_server # Log in to your Test server ssh my_vmware_test_server # Become Root, the system super user sudo su - # Become Postgres user, so that you can do all Postgresql stuff su - postgres # Create a user named "ojs" createuser ojs # Create the databases createdb -O ojs ojs_new createdb -O ojs ojs_old # Import the databases to Postgresql on the Test Vmware server psql -f ojs_old.sql ojs_old psql -f ojs_new.sql ojs_new |
ojs_old: was the original 2.2.2 database […]
I tried to upgrade Moodle 2.2.1 to 2.6.2+ and got this error message:
|
1 2 3 |
Default exception handler: DDL sql execution error Debug: Data truncated for column 'institution' at row 999 ALTER TABLE mdl_user MODIFY COLUMN institution VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' after phone2 Error code: ddlexecuteerror |
The solution was to run these sql’s at the Mysql database:
|
1 2 3 |
update mdl_user set institution="" where institution is NULL; update mdl_user set department="" where department is NULL; update mdl_user set address="" where address is NULL; |
This issue seems to be related to: https://tracker.moodle.org/browse/MDL-43147 https://moodle.org/mod/forum/discuss.php?d=217512 https://tracker.moodle.org/browse/MDL-37016
We wrote an improved postgresql dump bash script for Postgresql version 9.1. This one will save each dump file with the name: database_name_DAYNAME.sql.bz2 In this way, we would only have 7 backups at any time, because each file will be overwritten after seven days. Since our backup system (TSM) saves 7 versions of each file, […]
If you just need a quick way to get some data from a mysql database in your shell (bash), you could do something like this in one line:
|
1 |
mysql -h your.server.edu -u db_username -p`cat /path/to/your/homedir/secretpasswordfile` -e ";use databasename; SELECT tablename.columnname FROM tablename where id like '421111' and something like '1' and option like '23';"; > /tmp/datayouwant.txt; while read i; do echo ";$i";; done < /tmp/datayourwant.txt | sort | uniq |
If you don’t like to scroll: -bash-3.2$ mysql -h your.server.edu -u db_username -pcat /path/to/your/homedir/secretpasswordfile -e “use databasename; SELECT tablename.columnname FROM tablename where id like ‘421111’ and […]
We had a lot of spam users in our multisite wordpress system. This was because we had self-registration enabled for a period. Not a smart thing to do… anyway, I wrote a bash script in order to find which users id’s from the Mysql database that could potentially be spam users. With this list of […]
I had to send emails to all the Blog admins in our WordPress Multisite installation. This is how I did it: [code lang=”php”] <?php # Alert Blog Admin php script # connect to blog db, select from, find all blog admin email adresses # get subject, body from pre-created local files, send the email # […]
I’ve wrote a simple bash script to dump databases from a Postgresql 8.1 database server which uses the pg_dump and pg_dumpall.
|
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 |
#!/bin/bash ## This scripts dumps all the databases in a Postgres 8.1 server, localhost ## 2 dump files are made for each database. One with Inserts another without. ## some variables, change them to fit yours LOGFILE="/var/lib/pgsql/data/pg_log/pgsql_dump.log" HOSTNAME=`hostname` MAILLIST="name1@something.com,name2@something.com" BACKUP_DIR="/backup/postgresql_dumps" # Date and time variables DATE=`date +%Y-%m-%d` TIME=`date +%k:%M:%S` TODAY=$DATE"T"$TIME # only postgres can run this script! if [ `whoami` != "postgres" ]; then echo "pgsql_dump tried to run, but user is not postgres!" >> $LOGFILE echo "You are not postgres, can not run." echo "Try: su -c ./pgsql_dump.sh postgres" exit; fi # clean up old dumps! (find all types which are files, with the name that ends with .sql, # their date older than 7 days, and execute the command "rm" (remove) ) find $BACKUP_DIR -type f -name '*.sql' -mtime +7 -exec rm {} \; # Check if there any backup files. # Action: find in folder BACKUP_DIR all files with file-extension .sql. # Count the number of files with wc (word count, option -l, which counts the numbers of lines. # If this number is 0, then there are no files, and clearly something is wrong, # because you don't have any backups! if [ `find $BACKUP_DIR -type f -name '*.sql' | wc -l` -eq 0 ]; then echo "There are no pgsql dumps for the last 2 days at $HOSTNAME. Something is wrong!" | mail -s "[PGSQLDUMP ERROR] $HOSTNAME" $MAILLIST fi # Create the log-file if it doesn't exist if [ ! -e $LOGFILE ]; then touch $LOGFILE fi # Find which databases you have in your Postgresql server # Action: list out all the databases, remove unwanted lines and characters, extract wanted line with awk (line 1), # strip away white empty lines with the command 'sed': DATABASES=`psql -q -c "\l" | sed -n 4,/\eof/p | grep -v rows | grep -v template0 | awk {'print $1'} | sed -e '/^$/d'` # Dump the databases in individual files for i in $DATABASES; do FILENAME="$i-$TODAY.sql" pg_dump -D $i > /backup/postgresql_dumps/"$FILENAME" done # we also like a dump with copy statements for i in $DATABASES; do FILENAME="$i-cp-$TODAY.sql" pg_dump $i > /backup/postgresql_dumps/"$FILENAME" done # full backup is also necessary pg_dumpall -D > /backup/postgresql_dumps/"all-databases $TODAY".sql pg_dumpall > /backup/postgresql_dumps/"all-databases $TODAY".sql # Finally vacuum the database vacuumdb -a -f -z -q |