MySQL replication

This is mostly a note for myself, so I don’t have to do research again. Bunch of usefull links:

Servers for hackers – replication

Exporting / Importing data in a UTF8 safe way

 

Replication was easy in my case, the real issue was with handling huge backup files. Here are the commands I used for export, transfer and import of data.

Also, remember to check master status (log position) and to lock the database before you start.

mysqldump -u root -p --routines --triggers --all-databases -r database.dump

sudo rsync -vP --inplace utf8.dump user@server.com:/home/user/

mysql -uroot -p --default-character-set=utf8
mysql> SOURCE database.dump;

In case you’re getting binary logging errors (“Statement based binlogging does not work in isolation level READ UNCOMMITTED and READ COMMITTED since the necessary locks cannot be taken.” or so), make sure this line is present in your MySQL config:

binlog_format=row

I hate WordPress editor.