Experimenting with a bunch of VMs and CouchDB for
scalable, replicated filesystem purposes.

I like the way it stores any object as a 'document' in its database, doesn't depend on another
database backend like MogileFS seems to.

I'm slightly wary of the fact that the replication isn't built as part of the daemon's
responsibility automatically on the fly, by way of which you could control via the config ini
file. Instead replication is a manually executed process either via the web interface or via
curl commands that one could at least build into bash scripts... and maybe via the API i.e it
could be done in the app itself that is pushing files up into the database?

Not sure if that's a good feature or not yet.. also the fact that it's uni-directional
replication, making it not so easy to get a master-master HA solution happening.. you can
replicate both ways but what happens when conflict occurs (and they seem to)..

Watch this space!

#!/bin/bash
DATE=`date +%Y%m%d`
FILENAME=$1/$1_daily_$DATE.sql.bz2
mkdir -p $1
mysqldump --user=root --password=changeme --host=localhost $1 | bzip2 -c > ${FILENAME}
scp -i /home/web/.ssh/password_less_ssh_key $FILENAME <a href="mailto:user@remote.server">user@remote.server</a>:/data/backup/dbbackups/$1/

mysql -p DATABASE

(enter password)

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

#!/bin/bash
DATE=`date +%H`
FILENAME=$1/$1_hourly_$DATE.sql.bz2
mkdir -p $1
mysqldump --user=root --password=changeme --host=localhost $1 | bzip2 -c > ${FILENAME}

/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

I seem to have to keep looking up notes whenever I need to set up a multi-master MySQL replication ring. I thought I'd put it all down in one place that I can find easily - on my own blog.

This is not a howto, it's just notes, I can't guarantee these are accurate or without faults.

1. Install MySQL server on Server A and B

apt-get install mysql-server

2. On Server A, grant replication privileges to a replication user

grant replication slave on *.* to 'replication'@'server_b' identified by 'slavepw';

3. MySQL config on Server A:

server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
binlog_do_db = testdb
binlog_ignore_db =mysql
relay-log=mysqld-relay-bin

Restart Server A MySQL

4) Edit Server B config:

server-id = 2
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
binlog_do_db = testdb
binlog_ignore_db =mysql
relay-log=mysqld-relay-bin
master-host = server_a
master-user = replication
master-password = slavepw
master-port = 3306

5) Restart MySQL on Server B, then:

start slave;
show slave status\G;

These should be both Yes, also check that the Master Host, binlog and positions all match (by comparing with 'show master status;' on Server A

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

At this point we've a master->slave relationship. It's time to make Server B a master of Server A as well.

6. Set the replication privileges on Server B to become a master of Server A

grant replication slave on *.* to 'replication'@'server_a' identified by 'slavepw';

7. On Server B, add the master info to my.cnf so it knows it's a slave of Server B

master-host = server_b
master-user = replication
master-password = slavepw
master-port = 3306

8. Restart Server B and then Server A

(not sure if it matters what order really)

9. On Server A:

start slave;
show slave status\G;

These should be both Yes, also check that the Master Host, binlog and positions all match (by comparing with 'show master status;' on Server B

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

10. start slave on Server B

(I guess, or was it already started when MySQL restarted? It should do it automatically, try to remember from 2008 what we did here to make sure it does)

Unless there's errors for either node connecting to each other, should be ok to create the database testdb on Primary or Secondary (the creation will replicate to the other node), and start creating tables/data on either node.

psql template1;

SELECT COUNT(*) FROM pg_stat_activity;

You can see max allowed connections with SHOW max_connections; - but this is just as easily read from the postgresql.conf in /etc/postgresql/(version).. etc

#!/bin/bash
DATE=`date +%Y%m%d`
FILENAME=$1/$1_daily_$DATE.sql
mkdir -p $1
pg_dump -cxO -f $FILENAME $1
gzip -f $FILENAME
echo $FILENAME
scp -i /home/user/.ssh/password_less_ssh_key $FILENAME.gz user@remote.backup:/data/backup/dbbackups/$1/

#!/bin/bash
DATE=`date +%H`
FILENAME=$1/$1_hourly_$DATE.sql
mkdir -p $1
pg_dump -cxO -f $FILENAME $1
gzip -f $FILENAME
echo $FILENAME

Tables:

SELECT pg_tables.tablename, pg_tables.schemaname, pg_size_pretty(pg_relation_size((pg_tables.schemaname::text || '.'::text) || pg_tables.tablename::text)) AS pg_size_pretty FROM pg_tables ORDER BY pg_relation_size((pg_tables.schemaname::text || '.'::text) || pg_tables.tablename::text) DESC;

Database entirely (I swear I had this sites statistics script somewhere that contained all this, including a MySQL version.. where did that go?):

select pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size from pg_database WHERE pg_database.datname = 'foobar';