mysql

Backup script to find and backup all databases (postgres and mysql)

#!/bin/bash
 
today=$(date +%y%m%d)
 
# local dir where the backups go
myDir='/data/dbbackups'
# remote dir where we'll send them offsite
myRemoteDir='/data/backup/li35-166/dbbackups'
 
 
# this is for PostgreSQL. If you don't need it, you
# could leave it here, but remove the 'backup_pgsql'
# function call at the end of the script
 
function backup_pgsql {
for db in `su postgres -c "psql -U postgres -qAtc '\l'" | cut -f1 -d\|
| grep -v '^template[01]'`; do
mkdir -p $myDir/${db};

/etc/init.d/mysql stop
mysqld_safe --skip-grant-tables &
mysql -u root

mysql> use mysql;
mysql> update user set password=PASSWORD("newrootpassword") where user='root';
mysql> flush privileges;
mysql> quit

/etc/init.d/mysql stop
/etc/init.d/mysql start
mysql -u root -p

mysql -p DATABASE

(enter password)

SELECT * INTO OUTFILE "/tmp/table.txt" FROM TABLE;