Beckham _ mysql master-slave replication function and case _ MySQL

Mysql master-slave replication: 1. mysql User authorization 2. mysqlbin-log 3. mysql master-slave replication 1. mysql User authorization 1. Command 2. role: permission control 3. example: (Note: allow xiaobei to access mysql from localhost, and password authentication is required. Mysql master-slave replication Brief:I. mysql User authorization II. mysql bin-log III. mysql master-slave replication

I. mysql User authorization

1. commands

2. Role: permission control

3. example:

(Note: allow xiaobei to access mysql from localhost, and password verification is required. the password is xiaobei)

4. view the mysql User permission table

II. bin-log

1. check whether bin-log is enabled.

2. enable bin-log

Modify configuration file

[Mysqld]

Log-bin = mysql-bin

3. restart the mysql server and verify whether the server is enabled.

4. Physical structure

5. refresh bin-log-related logs

5.1. flush logs

Generate a latest bin-log

5.2. show master status;

View last bin day

5.3. reset master

Clear all bin-log logs

6. what statements will be recorded in the bin-log?

A. before execution, start position of bin-log

B. execute the query

C. execute the modification

D. execute insert

E. execute delete

(Conclusion: bin-log only records the SQL statements added, deleted, and modified .)

7. view the bin-log content

Use mysqlbinlog tool

III. mysql master-slave replication

1. role:

A. If a problem occurs on the master server, you can quickly switch to the service provided by the slave server.

B. query operations can be performed on the slave server to reduce the access pressure on the master server.

C. backup can be performed on the slave server to avoid affecting the services of the master server during the backup.

(Note: Generally, only infrequently updated data or less demanding real-time data can be queried from the server. data with high real-time requirements still need to be obtained from the master server)

2. configuration

2.1 modify the configuration file of the primary database

Restart service

2.2 go to the mysql client and view the binlog

2.3 Create an authorized user from the service

2.4 modify the slave server configuration file

2.5 After the service is restarted, enter the client and establish master-slave replication

2.6 start the slave server

2.7 View slave server information

(Yes is displayed in red, indicating that the configuration is successful)

2.8 verification

A. The master and slave servers are all in the same database.

B. run

C. View on the slave server

In this case, the master-slave database is successfully copied.

Summary: Steps for master-slave replication

A. create an authorized user

B. modify the master/slave server configuration

C. specify the master server on the slave server

D. start master-slave replication on the slave server.

FAQs:

1. the problem of Device eth0 does not seem to bepresent occurs when vmware copies the centos virtual machine.

Because cluster testing is required, multiple virtual machines are required as testing machines. If every virtual machine needs to install the system, it is very troublesome. Fortunately, vmare can directly generate a new virtual machine by copying the physical file of the virtual machine. The network of the copied virtual machine fails. when the network server is restarted, the following error is prompted:

Device eth0 does not seem to bepresent, delaying initialization.

Found only after searching on the InternetCause: After vmware copies a VM, a new Nic device is automatically generated for the VM. for example, if the original Nic is eth0, eth1 is added after the VM is copied. But because no corresponding/etc/sysconfig/network-scripts/ifcfg-eth1 file is generated, leading to network connection failure, this should be a bug of vmware.

View the Nic configuration file/etc/udev/rules. d/70-persistent-net.rules. you can see an additional eth1 configuration.

Solution

Edit the Nic configuration file/etc/udev/rules. d/70-persistent-net.rules, change the Nic address of eth0 to the NIC address of eth1, and then delete eth1.

Edit the nic ip address profile/etc/sysconfig/network-scripts/ifcfg-eth0 and change the Nic physical address HWADDR to the NIC address above eth0.

After the modification, restart the system to enable the network service.

2. Fatal error: The slave I/O thread stops because masterand slave have equal MySQL server UUIDs; these UIDs must be different forreplication to work.

Solution:

Modify auto. cnf in the database data directory