Small shellfish _mysql master-slave copy function and case

MySQL master-slave replication Brief:One, MySQL user authorization two, MySQL bin-log log three, MySQL master-slave copy

One, MySQL user authorization

1. Command

2. Function: Control of authority

3. Example:

(Note: Allow user Xiaobei to access MySQL from localhost, and password Authentication required, password is Xiaobei)

4. View MySQL User Rights table

Second, bin-log log

1. See if Bin-log is open

2. Open Bin-log Log

Modifying a configuration file

[Mysqld]

Log-bin=mysql-bin

3. Restart the MySQL server to verify that it is turned on

4. Physical Structure

5. Log refresh related to Bin-log

5.1. Flush logs

generate an up-to-date bin-log log

5.2, show master status;

View Last bin days

5.3. Reset Master

Clear all the Bin-log logs

6, what kind of statement will be recorded in the Bin-log log

A, before execution, the starting position of the Bin-log

B, execute the query

C. Implementation of changes

D. Perform insert

E. Execute Delete

(Summary: Bin-log only records additions and deletions of SQL statements.) ）

7. View Bin-log Log Contents

using the Mysqlbinlog tool

Third, MySQL master-slave replication

1. Function:

A. If there is a problem with the primary server, you can quickly switch to the service provided from the server

b, you can perform query operations from the server to reduce the access pressure on the primary server

C. Backups can be performed from the server to avoid services that affect the primary server during backup

(Note: Notice that only infrequently updated data or the real-time requirements of the data can be queried from the server, real-time requirements of high-quality data still need to be obtained from the primary server)

2. Configuration

2.1. Modify the master database configuration file

Restart Service

2.2. Go to MySQL client and view Binlog log

2.3, the establishment from the service authorized user

2.4. Modify the configuration file from the server

2.5, restart the service, enter the client, establish master-slave replication

2.6. Start from the server

2.7. Viewing from server information

(The red area is displayed: Yes, indicating the configuration was successful)

2.8. Verification

A, the master and slave servers are in the same database

b, on the primary server execution

c, viewing from the server

At this point, the master-slave database replication succeeds.

Summary: Steps for master-slave replication

a , establish authorized users

b , modifying the master-slave server configuration

C , specifying the primary server from the server

D , starting master-slave replication from the server

Problems:

1 ,vmware replication CentOS virtual machine encounters device eth0 does not seem to bepresent the problem

because of the need to do cluster testing, more than one virtual machine as a test machine. It would be cumbersome to install the system on every virtual machine, but fortunately vmare can generate a new virtual machine directly by replicating the physical files of the virtual machine. Just copy the virtual machine network will fail, restart the network server will be prompted by the following error:

Device eth0 does not seem to bepresent,delaying initialization.

Online Search only to find the reason : Originally VMware after replicating the virtual machine will automatically generate a new network card device for the virtual machine to use, such as the original network card is eth0, after the virtual machine will be added eth1. However, the network connection failed because the corresponding/etc/sysconfig/network-scripts/ifcfg-eth1 file was not generated, which should be a bug for VMware.

looking at the NIC profile/etc/udev/rules.d/70-persistent-net.rules, you can see that there is an extra eth1 configuration.

Solutions

Edit the NIC profile/etc/udev/rules.d/70-persistent-net.rules, modify the ETH0 network card address to ETH1 's network card address, and then delete the eth1 line.

Edit the network card IP address profile/etc/sysconfig/network-scripts/ifcfg-eth0, modify the network card Physical address hwaddr for the above eth0 network card address.

After the modification, restart the system, you can start the network service normally.

2 , Fatal error:the slave I/O thread stops because Masterand slave has equal MySQL server UUIDs; These uuids must be dif Ferent forreplication to work.

Workaround :

Modify Database Data under the directory auto.cnf

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

Small shellfish _mysql master-slave copy function and case