MySQL master-slave Replication Configuration Example

Recently researched MySQL, from the Oracle website under a new 5.6 MySQL, compressed version. Today, it took a long time to try out MySQL's copy function and share the experience.

With our development library, deployed in the test environment, the MySQL version of the test environment is actually 5.1.

1. Primary server Configuration

Cd/etc, find my.cnf, and then this configuration file plus the configuration to use for replication

Server-id       = 1log-bin=mysql-binbinlog-do-db=wx

The primary server requires a unique server Id,log-bin to enable the binary log, binlog-do-db to select the database that logs the log

2. Slave configuration

Open My.ini, plus configuration similar to server

Server-id=2log-bin=mysql-bin REPLICATE-DO-DB=WX

3. Copy the WX database on the host machine to the slave.This step must be performed, or you will get an error later. I just ignored this step, leading to a few unsuccessful attempts! Of course, this step can be done in any way, with a tool that can be executed with mysqldump after the slave.

4. Copy account on the host

The host's MySQL command line executes the following command, building a copy of the account, and password

Grant replication Slave on  * *  

And check the status of the mainframe.

Mysql> Show Master status\g*************************** 1. Row ***************************            file:mysql-bin.000003        position:256    binlog_do_db:wxbinlog_ignore_db: 1 row in Set (0.00 sec)

Both position and file are useful later.

5. Operating from an organ-linked host

Executes the command of the associated host from the machine and restarts the slave.

Change Master to  master_host = ' 192.168.146.120 ',  master_user = ' replication ',  Master_password = ' 123 ',  master_log_file = ' mysql-bin.000003 ',  master_log_pos = 256;  

6. Verify the operation

The WX library on the host to find a table, insert a statement, from the machine immediately copied over.

View the slave status.

Mysql> Show slave status\g*************************** 1. Row *************************** slave_io_state:waiting for master to send event Master_hos t:192.168.146.120 master_user:replication master_port:3306 Connect_ret Ry:60 master_log_file:mysql-bin.000003 read_master_log_pos:708 Relay_log_file:cdyj y-lvsheng1-relay-bin.000004 relay_log_pos:265 relay_master_log_file:mysql-bin.000003 S           Lave_io_running:yes Slave_sql_running:yes replicate_do_db:wx replicate_ignore_db:                   Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:              last_errno:0 last_error:skip_counter:0 exec_master_log_pos:708 relay_log_space:588 Until_condition:none Until_log_file:until_log_pos:0 Master_ssl_allowed:no Master_ssl_ca_file:mast Er_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_B               Ehind_master:0master_ssl_verify_server_cert:no last_io_errno:0 Last_io_error:                  last_sql_errno:0 last_sql_error:replicate_ignore_server_ids:master_server_id:1                    Master_uuid:master_info_file:d:\developtools\db\mysql-5.6.26-winx64\data\master.info sql_delay:0 Sql_remaining_delay:null Slave_sql_running_state:slave have read all relay log; Waiting for the slave I/O thread to update it master_retry_count:86400 Master_bind:last_ IO_Error_Timestamp:Last_SQL_Error_Timestamp:Master_SSL_Crl:Master_SSL_Crlpath:Re Trieved_gtid_set:execuTed_gtid_set:auto_position:01 row in Set (0.00 sec) 

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

MySQL master-slave Replication Configuration Example