adric.net

MySQL_Replication
Login

Vendor docs: http://dev.mysql.com/doc/refman/4.1/en/replication.html

Overview

Mysql replication is per database and is master-slave. Two way replication is just two configs, one going the other way. These instructions are for one way master to one/many MyISAM setups on MySQL 4.1.x (Debian sarge). Like everything else in MySQL land, using InnoDB complicates matters and using 3.x makes things harder (grant privs for one).

MySQL replication masters must use bin logs. Master and all slaves must have a unique server-id assigned in my.cnf. Use id 1 for master. Slaves must be able to connect to the mysql port on the master (3306), so verify your network connection and firewall configs.

Although you have to do some configuration on the master, most of the work and configuration in MySQL replication in put on the slaves. Masters do not know how many slaves or what data they have. Slaves use the replication account to pull the binlogs by polling master. That is how they synch.

On Master:

On each slave:

Details:

in master my.cnf:

# The following can be used as easy to replay backup logs or for replication
server-id               

1 log-bin

/web/sites/data/mysql-bin.log #binlog-do-db

include_database_name #binlog-ignore-db

include_database_name

in slave my.cnf

# The following can be used as easy to replay backup logs or for replication
server-id               

35 replicate-do-db

dbname read-only #log-bin

/var/log/mysql/mysql-bin.log #binlog-do-db

include_database_name

on master:

mysql> grant replication slave on *.* to replication@'%' identified by 'goodpass';
mysql> flush tables with read lock;
mysql> show master status\G;
*************************** 1. row ***************************
            File: mysql-bin.000001
        Position: 460126
    Binlog_Do_DB: 
Binlog_Ignore_DB: 
1 row in set (0.00 sec)

ERROR: No query specified # dump, eg : mysqldump -u dbuser -p -Q --add-drop-table --databases dbname |gzip -c > /web/home/dbname.sql.gz mysql> unlock tables;

on slave:

# fetch the sql dump with wget: 
wget http://server/dbname.sql.gz
zcat dbname.sql.gz | mysql -u dbuser -p 
mysql>change master to master_host

'server', master_user='replication', master_password='goodpass', ->master_log_file='mysql-bin.000001', master_log_pos

1196; mysql> slave start; # or reload mysql mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: server Master_User: replication Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 480622 Relay_Log_File: server-relay-bin.000002 Relay_Log_Pos: 479473 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: dbname Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 480622 Relay_Log_Space: 479473 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 1 row in set (0.00 sec)

ERROR: No query specified