Database management snippets

database_snippets.md

Postgres Snippets

Backup a django Postgres database

cd /codedata/code/django-study/mysite
# Stop the django surver so database will not be updated
# DBNAME=django_link
DBNAME=databasename
dumpfile=$DBNAME.pg_dump.$(date +%Y-%m-%d-%H:%M:%S).sql
pg_dump $DBNAME > ../databak/$dumpfile

Create a database

DBNAME=databasename
# dropdb $DBNAME
createdb -U $USER --locale=en_US.utf-8 -E utf-8 -O $USER $DBNAME -T template0

# Check database created
psql
greg=# \l

Restore backup into new database

# check target database
echo $DBNAME
echo $dumpfile
psql $DBNAME < $dumpfile

# Check database
psql $DBNAME
linkapp2=# \d

# restart django server

MYSQL Snippets

Backup a MySQL database

# add a space to start of command to keep command out of bash history
 export DBNAME=database_name
 export DBUSER=database_user
 export PASSWORD=password
export DUMPFILE=$DBNAME.mysql_dump.$(date +%Y-%m-%d-%H:%M:%S).sql
mysqldump -u $DBUSER -p$PASSWORD $DBNAME > $DUMPFILE

Create a database

tbd

Restore backup into new database

# check target database
echo $DBNAME
echo $dumpfile
mysql -u root -prootpass $DBNAME < $dumpfile

MYSQL Snippets

Backup a MySQL database

DBNAME=databasename
dumpfile=$DBNAME.pg_dump.$(date +%Y-%m-%d-%H:%M:%S).sql
mysqldump -u root -prootpass dbname > /path/to/databak/$dumpfile
# mysqldump -u root -p $DBNAME > /path/to/databak/$dumpfile

Create a database

tbd

Restore backup into new database

# check target database
echo $DBNAME
echo $dumpfile
mysql -u root -prootpass $DBNAME < $dumpfile