MySQL Replication (ii) Master-slave replication practices

1. Preparatory work

First, you need to prepare 2 machines and install MySQL separately.

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

Linux System for CentOS

The machines were:

master:192.168.1.227

slave:192.168.1.225

Scenario: Both master and slave are the databases that were just created, i.e. the data is in a consistent scenario

2. Configure replication

The approximate steps are as follows:

1) Create a copy account on each server

2) Configure Master and slave

3) Slave connection master starts copying

2.1 Create a copy account on Master

Create an account on master slave, password Slave, and give replication slave permissions

Mysql>grant REPLICATION SLAVE on * * to ' SLAVE ' @ ' 192.168.1.225 ' identified by ' SLAVE ';

2.2 Configuring the Master

# VI/ETC/MY.CNF

Make sure that the following configuration is 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 = sort_buffer_size = 512K Net_buffer_length = 8K Read_buffer_size = 256K Read_rnd_buffer_size = 512K myisam_sort_buffer_size = 8M binlog_for Mat=row log-slave-updates sync_binlog=1 auto_increment_increment=2 auto_increment_offset=1

SERVER_ID: Must be configured and cannot be the same as slave server_id

Log_bin: Open Binary Log

Save and exit, restart Master

Service mysqld Restart

Go to the SQL console, run the command show Master status or show Master Status\g

2.3 Configuring Slave

On the slave machine.

# VI/ETC/MY.CNF

[mysqld] Port = 3306server-id = 2 Log_bin = Mysql-binrelay_log         = mysql-relay-binlog_slave_updates = 1read_only         = 1

SERVER_ID: Must be configured and cannot be the same as slave server_id

Log_bin: Open Binary Log

Relay_log Configuring the Trunk log, log_slave_updates indicates that slave writes the copy event into its own binary log (see what it does later)

Save and restart Slave

2.4 Start copying

Let slave connect 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=106;

Note the red part

Master_log_file file corresponding to master

Master_log_pos corresponding to master position

Run command: mysql>show slave status\g

Slave_io_state, slave_io_running, and slave_sql_running indicate that Slave has not started the replication process. The location of the log is 4 instead of 0, because 0 is only the start of the log file, not the log location. In fact, MySQL knows the location of the first event is 4.

Start copying, Run command:mysql> start SLAVE;

Slave_io_running, and slave_sql_running both are yes, indicating a successful start

Perform SQL operations on master

MySQL>  use test;mysql>createtableint  varchar); MySQL>inserttest1 values (1,' 2 ');

View on slave

Indicates that replication was successful.

MySQL Replication (ii) Master-slave replication practices