MySQL replication (2) Master-Slave replication practices, mysqlmaster-slave

MySQL replication (2) Master-Slave replication practices, mysqlmaster-slave
1. Preparations

First, prepare two machines to install mysql

My version here is 5.1.73, mysql database installation refer to: http://www.cnblogs.com/tangyanbo/p/4289753.html

Centos for linux

The machines are:

Master: 192.168.1.227

Slave: 192.168.1.225

Scenario: master and slave are both newly created databases, that is, when the data is consistent

2. Configure Replication

The procedure is as follows:

1) create a copy account on each server

2) Configure Master and Slave

3) The Server Load balancer connects to the Master node to start replication.

2.1 create a replication account on the Master

Create an account slave and password slave on the master and grant the replication slave permission.

Mysql> grant replication slave on *. * TO 'slave '@ '192. 168.1.225' identified by 'slave ';

2.2 configure Master

# Vi/etc/my. cnf

Make sure that the following configurations are available under mysqld:

[mysqld] port = 3306server-id = 1

log_bin = mysql-bin

socket = /tmp/mysql.sock skip-locking key_buffer_size = 16M max_allowed_packet = 1M table_open_cache = 64 sort_buffer_size = 512K net_buffer_length = 8K read_buffer_size = 256K read_rnd_buffer_size = 512K myisam_sort_buffer_size = 8M binlog_format=ROW log-slave-updates sync_binlog=1 auto_increment_increment=2 auto_increment_offset=1

Server_id: It must be configured and cannot be the same as the server_id of slave.

Log_bin: Enable binary log

Save and exit, restart master

Service mysqld restart

Go to the SQL console and run the command SHOW MASTER STATUS or show master status \ G.

[Mysqld] port = 3306server-id = 2log_bin = mysql-binrelay_log = mysql-relay-binlog_slave_updates = 1read_only = 1

Server_id: It must be configured and cannot be the same as the server_id of slave.

Log_bin: Enable binary log

Relay_log: configure the relay log. log_slave_updates indicates that slave writes the replication event into its own binary log (which will be useful later)

Save and restart slave

2.4 Start copying

Connect slave to master

Mysql> change master to MASTER_HOST = '192. 168.1.227 ', MASTER_USER = 'slave', MASTER_PASSWORD = 'slave ', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 192;

Note the red part

MASTER_LOG_FILE corresponds to the master's file

MASTER_LOG_POS corresponds to the Position of the master

Run the command: mysql> show slave status \ G

Mysql> use test; mysql> create table test1 (id int, name varchar (20); mysql> insert into test1 values (1, '2 ');

View on slave

The copy is successful.