Nightly commits /etc into SVN

Simple script committing changes in /etc/postfix and /etc/network into SVN repository. Can be scheduled every night - it checks for changes and in doesn't try to commit if system is untouched.

/etc -> SVN


#!/bin/bash

export LC_ALL=C
export EDITOR=/usr/bin/vim
tmpconf=/var/tmp/conf2svn
root=/etc

for srv in serverA serverB serverC
do
  for dir in network postfix
  do
    rm -rf ${tmpconf}/${srv} > /dev/null 2>&1
    mkdir -p ${tmpconf}/${srv} > /dev/null 2>&1
    cd ${tmpconf}/${srv}
    svn checkout --quiet svn://svn.mydomain.com/repos/conf/${srv}/${dir} ./${dir}
    scp -q -r ${srv}:${root}/${dir} .
    cd ${tmpconf}/${srv}/${dir}
    svn diff | grep "" > /dev/null
    if [[ $? -eq 0 ]]
    then
      svn commit -m "Commiting last changes in live servers' configuration (/etc)."
    fi
  done
done

rm -rf $tmpconf > /dev/null 2>&1


If you import /etc/something into SVN, delete /etc/something (be careful) and check something out back into /etc you won't need to make temporary working copy (SVN workspace) into /var/tmp/conf2svn

Script can't add new files. It only commits changes in existing ones.

MySQL schemas -> SVN


#!/bin/bash

export LC_ALL=C
export EDITOR=/usr/bin/vim
tmpconf=/var/tmp/schema2svn

rm -rf $tmpconf > /dev/null 2>&1
mkdir $tmpconf > /dev/null 2>&1

cd $tmpconf
svn checkout --quiet svn://svn.mydomain.com/repos/schemas .

for srv in dbA dbB
do
  mysqldump --host=$srv --password=nkjn39c134 --no-data --all-databases > ${tmpconf}/${srv}_schema_dump.txt
done

for file in *txt
do
  grep -v -E -- '(^--|ENGINE=MyISAM AUTO_INCREMENT)' $file > ${file}.new
  mv ${file}.new $file
done

svn diff | grep "" > /dev/null
if [[ $? -eq 0 ]]
then
  svn commit --quiet -m "Committing schema changes on live servers."
fi

rm -rf $tmpconf > /dev/null 2>&1


___
If you implemented your own schemas of backup/archiving, please let me know - send me an e-mail, drop a comment or meet me on IRC.

MySQL master-master replication

MANY articles about it is on the Net, but I try to show you, how quickly check what and why doesn't work in MySQL replication.

Initialization

In case of master-master replication, lets assume that we have server A, the origin of data, and server B, which is now a slave for A, but after transferring data on B we make it a master for A. That's why you:

1. hide server B (that one which you pass the data on), to avoid lost of data during initialization. Simply bind MySQL in my.cnf to 127.0.0.1 instead of 0.0.0.0


2. on server A list all databases you want to not replicate in my.cnf in ignore-bin-db directive


3. create a dump on server A using mysqldump with option --master-data. You can add --extended-insert or even --add-drop-database, but master-data is the most important option here. man mysqldump :-) My dump command is below


4. transfer the dump file on server B and stop slave (echo "STOP SLAVE" | mysql -p). You can RESET SLAVE if you wish and then import all to MySQL instance:
   mysql -p < ./mysql_dump.sql


5. While keeping the server B hidden on 127.0.0.1 start slave and watch: SHOW SLAVE STATUS\G. In case of problems check the error message and act accordingly. Check what databases you dumped, which of them are on destination machine (especially if it is not the first transfer of data) and so on


6. Because nobody make 'write' statements on hidden slave (server B), you can easily make it master for the server you took data off (server A).
   
   
   
   1. We want to make the new slave (server B) a master for server A. We start executing in mysql prompt SHOW MASTER STATUS and...
   
   
   2. ...writing that parameters on the server A in CHANGE MASTER TO command. This syntax is always confusing me, that's why I have to point you to reference site.
   
   
   3. When you entered it you can start slave on server A, but you can be sure that nothing will change (SHOW SLAVE STATUS\G). Until you have server B hidden, there no statement to transfer thru replication mechanism to server A.
      
   
   


7. Bind the hidden server B to 0.0.0.0 (edit my.cnf and restart)


8. Grab a piece of cake. You deserved ;-)

Dump on server A:

DATABASES=`echo "SHOW DATABASES" | \
  mysql -p | \
  grep -E '(db1|db2|db4)' | \
  grep -v db4_backup | xargs`
nice mysqldump -p --add-drop-database --extended-insert \
  --master-data --databases $DATABASES | \
  gzip -9 > /var/tmp/mysql_transfer.sql; \
  scp /var/tmp/mysql_transfer.sql.gz slaveserver:/var/tmp
rm /var/tmp/mysql_transfer.sql.gz

I write it as one long command with additional semicolons and slashes.
The dump is compressed because I had slow connection between servers. Usually you have pretty good link between them :-) In such case you can transfer data between servers without leaving the destination server (the server B):

DATABASES=`echo "SHOW DATABASES" | \
  mysql --host=serverA -p | \
  grep -E '(db1|db2|db4)' | \
  grep -v db4_backup | xargs`
nice mysqldump --host=serverA -p --add-drop-database --extended-insert \
  --master-data --databases $DATABASES | \
  gzip -9 > /var/tmp/mysql_transfer.sql

Note: if you have many instances, remember to which one you are connecting to. Add --host=www114.ibm.com --port=3305 (for example) to all mysql... commands.

Troubleshooting

Doesn't work? Ha! That's what tigers like most!
First of all check show slave status. What's the error?
If duplicating unique key, you're home. Simply forget --master-data, which blocks tables and adds two statements to dump: MASTER_LOG and MASTER_POS. Please imagine - mysqldump took care of freezing database state, built dump file including position on which it was frozen. Wow!
Another possibility to make similar mistake is when you prepare master-master replication and after start the first slave you allow to change data on both servers before you start the second one. I didn't try to freeze server B and I simply bind it to 127.0.0.1 making it invisible. But probably there is a simpler method.

Another problem is when you have functions in replicated databases, because they can try to write to databases absent on slaves. It's a problem. I solve it building dump with those databases, I add CREATE FUNCTION to the end of dump, but on master the databases are in ignore-bin-db directive in my.cnf and actually aren't not transferred on the network. How to retrieve code of functions creation? SHOW FUNCTION STATUS\G and then SHOW CREATE FUNCTION database_with_functions.YourFunctionName\G;

In case of deeper investigation you can use mysqlbinlog tool. It takes a binlog file (provided as an argument) and translate it to text for you. You can find whats going on in replication tracking the binary log.

DBA course

Good course for DBA should contain:

1. a briefing about history of databases


2. explanation of databases currently in use in business


3. guide how to develop not too complicate application

.
This is the beginning.

Update 2008-07-02
Then, according to audience needs, the following topics should be explained:

1. deep history of recent db engines (how MySQL can compete with others, where is PostgreSQL from, what happend to Berkeley DB, and what does it mean


2. current DB trends, e.g. the future of versioning


3. differences between MS SQL Srv, DB2, Oracle, Google's BigTable, MySQL, PostgreSQL


4. If not relational database, then what?