The main role of the mysql master-slave program:read and write separation,Enable the database to support greater concurrency.This is especially important in reports.Because some report SQL statements are very slow,Causes the table to lock,Affects front desk services.If the foreground uses master and the report uses slave, then the report sql will not cause a foreground lock.Guaranteed foreground speed.

Researched an open source project today,The database is mysql, and the script data needs to be backed up.Since my machine is mac pro, and the database connection tools under mac are not very easy to use,Just thinking about how to use the database connection tool under windows,And make related backups,In addition, the sqlyog tool under the windows system is still very powerful.Recommend it here.

So in order to use the connection tools under windows systems such as sqlyog,Began a day of tossing.

First two ideas,One is to use another Acer laptop,Install sqlyog directly on it and use it through wireless LAN connection,This way is too cumbersome,And it ’s cumbersome to switch computers back and forth,Remote connection usage is also slow.The second is to install a virtual machine.Install the connection tool on the virtual machine.

As my mac pro has installed the mysql database, and has installed the virtualbox virtual machine and installed the windows7 system, and also installed mysql in the windows7 system.

1. Install sqlyog in the virtual machine win7 system, connect directly to the mysql of the mac system, and export the backup,This is simple.(Of course, note that the network connection between the virtual machine and the mac machine is open)

1) First set the virtual machine network card settings.Select bridge mode,The concept of the bridge mode of virtual machines such as virtualbox is that the virtual machine is a separate machine in the network.Has its own ip, and the ip in the LAN is figured out,

Set the bridge mode of virtualbox as shown

According to the settings in the figure,The virtual machine root hosts can access each other.

2) Connect using sqlyog

By the way, one more thing to note is thatSince the LAN IP may not be fixed,I happen to be using a Xiaomi router.Has the function of binding the device IP, so the IP is fixed

As can be seen from the figure above,The host IP is independent of the virtual machine IP.

2.mysql master-slave backup method,mac pro is the master and the virtual machine is the slave

1) First, after the mysql client installation on the mac computer is completed,By default, there is no my.cnf file, even if the relevant configuration of/usr/local/mysql/is modified, it is useless.This requires custom mysql configuration. Copy the my-default.cnf file in/usr/local/mysql/support-files/to the/etc directory. Of course, you also encounter problems here.Service cannot start

This requires adding explicit_defaults_for_timestamp=true to the configuration file

2) modify the configuration file,As follows

3) modify my.cnf in the mysql directory in the virtual machine

Modify the mysqld node

4) Restart master and slave and check the status

View master status

View slave status

When you see the two pictures above, it proves that the configuration is successful.Of course, I was not so smooth.Encountered many problems halfway through

One:got fatal error 1236 from master when reading data from binary log

According to most online solutions, this problem is,First check the status of the master and record the relevant information

For example, the information is:

To record the file:mysql-bin.000002 position:16093

And execute in slave:

This approach should be fine.If this doesn't work,Try this:I tried it should be no problem,

(This way I may be because I did not follow the specifications when I configured,(Cause of inconsistency between master and slave)

Second:slave can not handle replication events with the checksum that master is configured to log

error This error usually occurs in master5.6, slave in the lower version.This is because 5.6 uses crc32 for binlog checksum. In addition to changing the master setting from crc32 to none

Binlog-checksum=none [my.cnf]

Third:The main reason I spend a lot of time today is this.I see that the slave status in the virtual machine is correct.

But through the connection tool of the virtual machine,Added a table to the master database and inserted data,But it is not always synchronized to slave mysql, so check the process

From mysql process

master process

This problem is a problem that has been tangled for a day,Of course, no specific reason was found,Look at the tip, what seems to be the cause of the process,But why did n’t I understand sleep?Checking the slave hosts did not find it

Of course in the end,no solution anymore,In the case of tangling for a long time and no reason is found,I insert data directly through the terminal on the mac machine,It was a success.. . Speechless. . But it was at least a little gratifying to finally be able to sync.As for what happened, find time to find out why.

  • Previous Parsing text boxes and text areas in Java graphical programming
  • Next JS achieve light blue simple vertical Tab click switching effect