mysql replication hints

[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
innodb_flush_log_at_trx_commit=1
sync_binlog=1

For the greatest possible durability and consistency in a replication setup using InnoDB with transactions, you should also specify the innodb_flush_log_at_trx_commit=1, sync_binlog=1 options

CREATE USER ‘replication_user’@’%’ IDENTIFIED BY ‘password’;
GRANT REPLICATION SLAVE ON *.* TO ‘replication_user’@’%’;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

It should be interesting for you file and position fields.

UNLOCK TABLES;

mysqldump –all-databases –master-data > /var/lib/mysql/master.db

use rsync to move you database to slave.

On MySQL slave you should do this:
mysql -p < ./master.db

CHANGE MASTER TO
MASTER_HOST=’master.mydomain.com’,
MASTER_USER=’replication_user’,
MASTER_PASSWORD=’password’,
MASTER_LOG_FILE=”file”,
MASTER_LOG_POS=”position”;

SHOW SLAVE STATUS\G

config should look kike this:

[mysqld]
server-id=2
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin

Testing replication:
create database testdb;
create table testdb.simples (id int not null primary key) engine=ndb;
insert into testdb.simples values (999),(1),(2),(3);
select * from testdb.simples;

Leave a Reply

Your email address will not be published. Required fields are marked *