A little remember a Mysql master-slave configuration solution _mysql

Today, the study of an open source project, the database is MySQL, the script data needs to be backed up, because my machine Mac Pro, and Mac database connection tools are not easy to use, think of how to take advantage of Windows database connection tools used, and do related backup, In addition, Windows system under the SQLyog tool is very powerful, recommended here.

So in order to use SQLYOG and other Windows systems under the Connection tool, it began a day of toss.

The first two ideas, one is the use of another Acer laptop, directly on its installation of SQLyog use, through the wireless LAN to connect to use, this way is too cumbersome, and to change the computer is cumbersome, remote connection use is very slow. The second is to install the virtual machine and install the Connection tool on the virtual machine.

Since my Mac Pro has installed the MySQL database, and has installed the VirtualBox virtual machine and installed the Windows7 system, and in the Windows7 system also installed MySQL.

1. Install SQLyog in the virtual machine win7 system, directly connect to the MAC system MySQL, and export backup, this is very simple. (Note, of course, that the virtual machine is connected to the Mac machine's network)

1 first set the virtual Machine network card settings, select bridging Mode, VirtualBox the concept of the bridge mode of such a virtual machine is a virtual machine as a separate machine in the network, has its own IP, and the LAN in the IP is figured out,

Set the VirtualBox bridge mode as shown in the figure

!

　　　　　　

This allows virtual machine root hosts to access each other as they are set in the diagram.

2) using SQLyog to connect

　　

There is also a point to note is that, because the LAN IP may not be fixed, just I use the millet router, binding device IP functions, so that IP is fixed

From the above figure can be seen, host IP and virtual machine IP is independent of it.

2.mysql master-slave backup way, Mac Pro Main master, virtual machine for from slave

1 First, because the Mac computer MySQL client installation is complete, the default is no my.cnf file, even if modified/usr/local/mysql/related configuration is also useless, this requires a custom MySQL configuration, will/usr/local/mysql/ The my-default.cnf file in support-files/is copied to the/etc directory, of course, there are also problems, the service can not start

This requires adding explicit_defaults_for_timestamp=true to the configuration file

2) Modify the configuration file as shown below

3 Modify the MY.CNF in the MySQL directory in the virtual machine

Modifying the MYSQLD node

4 reboot master and slave and view status

View Master Status

View slave status

When you see the above two pictures of the time to prove that the configuration was successful, of course, I did not so smooth, midway encountered a lot of problems

One: Got fatal error 1236 from master when reading the data from binary log
The problem with most of the online solution is to first view the status of master and record the relevant information
For example, the information is:

To record the file:mysql-bin.000002 position:16093
and execute in Slave:

There should be no problem in this way. If the above way is not good, you can try the following this, I tried to try should be no problem,

(This way I may be because I have not been configured in accordance with the norms, resulting in the main reason for the inconsistency)

Second: Slave can not handle replication events with the checksum, that master is configured to log
This error generally occurs in the case of Master5.6,slave in the lower version. This is because 5.6 uses the CRC32 to do binlog checksum. In addition to changing master's settings from CRC32 to None
Binlog-checksum = none [My.cnf]

Third: I spent a lot of time today the main reason is this, I in the virtual machine to view the slave status display is correct,

But I've added a table to the master database and inserted the data through the virtual machine's connection tool, but it's always not synchronized to from MySQL, so view the process
From the MySQL process

Master Process

This problem is a tangle of the day's problems, of course, did not find out the specific reasons why the reason seems to be why the process has been sleeping, but why sleep did not understand, see slave hosts also did not find

Of course, finally, no way, tangled for a long time also did not find any reason, I directly in the Mac machine directly through the terminal insert data, incredibly successful ... No words. But at least it's comforting to be able to sync at the end. As for what appears this problem to find the time to find out why.