MYSQL Master server Configuration

MySQL server master and slave configuration, originally is a very simple thing, helpless not from scratch, always in someone else already installed MySQL server above, this will involve, MySQL version, startup files, and so on some problems.

But don't worry, just ask two.

1. Location of MySQL configuration file my.cnf

2, how to start, stop MySQL, find good boot file

Assuming there are two machines, MySQL is already installed (try the same version as possible, and the same network as the two machines can ping through)

A friend said: "From the server, can not be lower than the version of the master server," but I was below, there is no problem.

Host a:192.168.1.100

Slave machine b:192.168.1.101

Can have more than one slave machine

1. Log in to host A first

Mysql>grant REPLICATION SLAVE on * * to ' backup ' @ ' 192.168.1.101 ' identified by ' 123456 ';

Given slave permissions, there are more than one cluster machine that executes multiple times

2. Open the my.cnf of host A, enter

Server-id = 1 #主机标示, integer
Log_bin =/var/log/mysql/mysql-bin.log #确保此文件可写
Read-only =0 #主机, read and write all can
Binlog-do-db =test #需要备份数据, more than one write multiple lines
Binlog-ignore-db=mysql #不需要备份的数据库, more than one write multiple lines

3. Open the my.cnf from machine B, enter

Server-id = 2
Log_bin =/var/log/mysql/mysql-bin.log
Master-host =192.168.1.100
Master-user =backup
Master-pass =123456
Master-port =3306
Master-connect-retry=60 #如果从服务器发现主服务器断掉, reconnection time difference (seconds)
Replicate-do-db =test #只复制某个库
Replicate-ignore-db=mysql #不复制某个库

4. Synchronizing the database

There are many ways, I say the stupidest kind, first mysqldump export host A's data test to Test.sql

Then, set up the database from machine B test,mysql import test.sql into the test library

5, restart the host a MySQL, and then restart from machine b MySQL

6. Verification

In host A, mysql>show master status\g;

In slave B, mysql>show slave status\g;

Can see roughly this content

file:mysql-bin.000001
position:1374
Binlog_do_db:test
Binlog_ignore_db:mysql

Can be in host A, do some insert, UPDATE, DELETE operation, see Host B, whether has been modified

Here's a reference.

Http://www.ningoo.net/html/2007/mysql_replication_configuration.html

http://leftleg.hzpub.com/post/645/

http://blog.zhangjianfeng.com/article/705

MYSQL Master server Configuration