mig5.net

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;

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.