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
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
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.
- MySQL database master-slave configuration tutorial under windows
- MySQL57 master-slave configuration instance analysis
- Docker mysql master and slave configuration details and examples
- Using MySQL master-slave configuration to achieve read-write separation to reduce database pressure
- Linux-based mysql master-slave configuration record
- python 3x - typeerror: 'method' object is not subscriptable
- python - you may need to restart the kernel to use updated packages error
- xcode - pod install [!] no `podfile 'found in the project directory
- android studio - unresolved reference comes out in kotlin
- vuejs - [vuetify] unable to locate target [data-app] i want to unit test to avoid warning
- android studio - emulator: dsound: could not initialize about the error message directsoundcapture
- django - oserror: [winerror 123] the file name, directory name, or volume label syntax is incorrect : '<frozen importlib_boot
- mysql startup failed [error] innodb: the innodb_system data file 'ibdata1' must be writable
- python - importerror: cannot import name md5 error cannot be resolved