MySQL Replication (ii) Master-slave replication practices

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.