MySQL master-slave replication configuration (dual-machine master slave)

Objective: To enable two MySQL servers to provide synchronization services from each other.

Advantages:

1. The main advantage of MySQL's master-slave replication is that it synchronizes "backup" and the database on the slave is equivalent to a (basic real-time) backup repository.

2. On the basis of master-slave replication, through the mysqlproxy can be read and write separation, from the slave to share some query pressure.

3. Do a two-way master-slave replication, two machines to each other for the host slave, so that in any one machine library write, will be "real-time" synchronization to another machine, two-way advantage is that when a host failure, another host can quickly switch over to continue service.

Steps:

1. Add an account to be accessed from the machine on both machines, giving replication slave permissions.

GRANT REPLICATION SLAVE on * * to ' SLAVE ' @ '% ' identified by ' SLAVE ';

The slave user is given replication slave permissions on any table in any database, which can be accessed anywhere in the network and marked with a password slave when accessed.

When using Ubuntu, it is important to note that the/ETC/MYSQL/MY.CNF configuration file bind-address = 127.0.0.1 This line requires comments, otherwise the slave is not connected to the request. (My is Ubuntu, other version of Linux do not know will be the same)

In order to ensure that the work of the detailed steps, can be configured after the user related information, on another machine with the assigned user password to connect once, can be successful can ensure that the current step is correct.

2. Configure the server number to turn on Bin-log

Edit MySQL config file, Linux:/etc/mysql/my.cnf, Windows:c:/program files/mysql/mysql 5.0/my.ini

Find [mysqld] this label,

There are two lines underneath it.

#server-id = 1
#log_bin =/var/log/mysql/mysql-bin.log

Open the comments on both lines, note that the Server-id here is the server number, so the values on both servers are set differently. Like 1 and 2.

3. Make configuration changes to Server-id and log-bin effective:

Sudo/etc/init.d/mysql restart

or restart the MySQL service in the service under Windows

4. Lock MySQL for two database servers

In MySQL command mode:

FLUSH TABLES with READ LOCK;

SHOW MASTER STATUS;

At this point, ensure that the MySQL console executing these two commands does not exit.

5. Reopen a MySQL console, respectively, to configure the host

Change MASTER to

Master_host = ' HOST ', #另一台机器的地址

Master_port = 3306, #另一台机器的端口

Master_user = ' slave ', #另一台机器上第一步分配的用户名

Master_password = ' slave ', #另一台机器上第一步分配的密码

Master_log_file = ' mysql-bin.000001 ', #另一台机器上执行SHOW the file name obtained by the MASTER status

Master_log_pos = 192; Offset from #另一台机器上执行SHOW MASTER status

6. Turn on Sync

START SLAVE;

MySQL master-slave replication configuration (dual-machine master slave)