Anonymous

Changes

From NixNet
m
add postgresql variant and reword the rest
== Database dumps ==
Refer to [[SQL Snippets]] for more quick commands regarding SQL databases.  === Setting MySQL up ===Add a new user with a complicated password and minimal permissions to all databases with the following SQL command.
<pre>
grant lock tables,show view,select on *.* to 'archive'@'localhost' identified by 'CHANGEMETOSOMETHINGSECURE';
</pre>
 
=== Setting PostgreSQL up ===
Same as above but PostgreSQL variant
<pre>
CREATE ROLE archive WITH LOGIN ENCRYPTED PASSWORD 'CHANGEMETOSOMETHINGSECURE';
\c dbname
GRANT USAGE ON SCHEMA public TO archive;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO archive;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO archive;
</pre>
 
=== Scripting the dumps ===
We store scripts in <code>/usr/local/scripts</code> but you can put them wherever. This one is named <code>/usr/local/scripts/pre-acts.sh</code>. It simply dumps all databases to a backup SQL file for tarsnap to ingest.
chown 0:0 $DUMPFILE
chmod 600 $DUMPFILE
 
# IF USING MYSQL
mysqldump -u archive -pCHANGEMETOSOMETHINGSECURE --all-databases > $DUMPFILE
 
# IF USING POSTGRES
 
</pre>