MySql master-slave replication configuration example

MySql master-slave replication configuration example

A recent study of mysql, from the oracle official website of the latest 5.6 mysql, compressed version. Today, it took me a long time to try the Mysql replication function and share my experiences with you.

The database we used for development is deployed in the test environment. The mysql version in the test environment is 5.1.

1. master server configuration

Cd/etc, find my. cnf, and add the configuration to be copied in this configuration file.

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

A unique server id is required for the master server. log-bin indicates that binary logs are enabled, and binlog-do-db indicates the database for which logs are selected.

2. Slave Configuration

Open my. ini and add configurations similar to those on the server.

server-id=2log-bin=mysql-bin replicate-do-db=wx

3. Copy the wx database on the host to the slave. This step must be executed, or an error will be reported later. I ignored this step and failed to try it several times! Of course, this step can be done in any way. You can use a tool to execute it from the machine after mysqldump is used.

4. Create a copy account on the host

Run the following command on the mysql command line of the host to create an account and password for replication.

grant replication slave  on *.*  to 'replication'@'%' identified by '123'; 

Check the host status.

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. Associate the slave with the host

Execute commands for the associated host from the machine and restart the slave machine.

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;  start slave; 

6. Verify the operation

Find a table in the wx database on the host, insert a statement, and copy it from the host immediately.

View the slave status.

mysql> show slave status\G*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 192.168.146.120                  Master_User: replication                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mysql-bin.000003          Read_Master_Log_Pos: 708               Relay_Log_File: CDYJY-LVSHENG1-relay-bin.000004                Relay_Log_Pos: 265        Relay_Master_Log_File: mysql-bin.000003             Slave_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:           Master_SSL_CA_Path:              Master_SSL_Cert:            Master_SSL_Cipher:               Master_SSL_Key:        Seconds_Behind_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 has 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:           Retrieved_Gtid_Set:            Executed_Gtid_Set:                Auto_Position: 01 row in set (0.00 sec)

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.