Virtual Architects Support Wiki

User Tools

Site Tools

You are here: start » computing » linux » mysql_backup
Trace: • offline_files
computing:linux:mysql_backup

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
Next revision 		Previous revision
computing:linux:mysql_backup [2016/10/29 14:41]
gcooper 		computing:linux:mysql_backup [2018/10/01 14:33] (current)
gcooper
Line 1: Line 1:
 ====== MySQL and PostgreSQL Backup Script ====== ====== MySQL and PostgreSQL Backup Script ======
  
-You can modify this script to only backup MySQL or only PostgreSQL.+===== Reset Forgotten PostgreSQL Password ===== 
 + 
 +If necessary, as ''root'': 
 + 
 +<file> 
 +sudo su postgres && psql 
 + 
 +ALTER USER postgres with password 'newpostgresuserpassword'; 
 + 
 +\q 
 + 
 +exit 
 +</file> 
 + 
 +===== PostgreSQL Password ===== 
 + 
 +For scripts that run as ''root'': 
 + 
 +<file> 
 +touch ~/.pgpass && chmod 0600 ~/.pgpass && vim ~/.pgpass 
 +</file> 
 + 
 +and add: 
 + 
 +<file> 
 +#server:port:database:username:password 
 +localhost:*:*:postgres:yourpostgresuserpass 
 +</file>
  
 ===== db_backup.sh ===== ===== db_backup.sh =====
  
-:!: You must edit the script, at least for the MySQL root password.+:!: You must edit the script, at least for the MySQL 'root' password. 
 + 
 +You can easily modify this script to only backup MySQL or only PostgreSQL.  However, the script checks to see if either PostgreSQL or MySQL database is running, then only backs up if they are.
  
 <file> <file>
Line 18: Line 47:
  
 # Mysql password # Mysql password
-MYSQLPW="mysqlpassword" +MYSQLPW="password"
 # Mysql username # Mysql username
 MYSQLUSER="root" MYSQLUSER="root"
 +
 +# PostgreSQL backup username
 +PGSQLUSER="postgres"
  
 # Location of the backup logfile. # Location of the backup logfile.
Line 36: Line 67:
  
 # #
-# PostgreSQL+# Postgresql
 # #
  
-if pgrep postmaster > /dev/null; then+if pgrep postgres > /dev/null; then
  
 echo "Begin Backing up PostgreSQL Databases...         `date`" >> $logfile echo "Begin Backing up PostgreSQL Databases...         `date`" >> $logfile
Line 45: Line 76:
  
 # Dump all databases at once # Dump all databases at once
-/usr/bin/pg_dumpall --clean -h localhost -U postgres | gzip > "$BACKUP/postgresql-all-databases.sql.gz"+/usr/bin/pg_dumpall -w --clean -h localhost -U $PGSQLUSER | gzip > "$BACKUP/postgresql-all-databases.sql.gz"
 echo "Backup complete for all PostreSQL databases at once at `date +%H:%M`" >> $logfile echo "Backup complete for all PostreSQL databases at once at `date +%H:%M`" >> $logfile
 echo "" >> $logfile echo "" >> $logfile
  
 # Individual databases # Individual databases
-databases=`psql -h localhost -U postgres -q -c "\l" | sed -n 4,/\eof/p | grep -v rows\) | awk {'print $1'}`+LIST_PG_DB_QUERY="select datname from pg_database where not datistemplate and datallowconn order by datname;" 
 + 
 +databases=`psql -h localhost -U $PGSQLUSER -At -c "$LIST_PG_DB_QUERY" postgres`
 for i in $databases; do for i in $databases; do
    /usr/bin/vacuumdb -z -h localhost -U postgres $i >/dev/null 2>&1    /usr/bin/vacuumdb -z -h localhost -U postgres $i >/dev/null 2>&1
Line 94: Line 127:
 exit exit
 </file> </file>
 +
 +====== Restore A MySQL Backup ======
 +
 +One-liner to restore a gzipped MySQL backup:
 +
 +<file>
 +gunzip < mysql-database-name.sql.gz | mysql -p -h localhost mysql-database-name
 +</file>
 +
 +:!: You will need to enter the database password of the database on 'localhost' being restored to.
computing/linux/mysql_backup.1477773710.txt.gz · Last modified: 2016/10/29 14:41 by gcooper

Page Tools

Except where otherwise noted, content on this wiki is licensed under the following license: CC Attribution-Share Alike 4.0 International
CC Attribution-Share Alike 4.0 International Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki