Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ MySQL database master never solve the synchronization method     - Linux iptables firewall and vsftpd to resolve the issue (Linux)

- Python configuration tortuous road of third-party libraries Numpy and matplotlib (Programming)

- Oracle table space rename and delete table space (Database)

- Java filter (Programming)

- How wifi-linux AP signal strength detection (Linux)

- MyEclipse configuration necessary (Linux)

- MySQL optimization of the relevant Group By (Database)

- Python 2.7.9 Installation on Linux CentOS 6.6 (Linux)

- NAT (network address translation) Realization (Linux)

- GNU / Linux enable Intel Rapid Start (Linux)

- To add the Oracle JDBC driver in Maven repository (Linux)

- ORA-00845: MEMORY_TARGET not supported on this system Problem (Database)

- Android code performance optimization tips (Programming)

- Simple security measures to reinforce the Linux kernel (Linux)

- Python substring format (Programming)

- The most concise Systemd tutorial, just ten minutes (Linux)

- Getting Started with Linux system to learn: how to install the Shrew Soft IPsec VPN on Linux (Linux)

- Github with .gitignore ignore specified file (Linux)

- A process of how to get the current traffic in GNU Linux (Linux)

- Java synchronization mechanism: synchronized, wait, notify (Programming)

 
         
  MySQL database master never solve the synchronization method
     
  Add Date : 2017-04-13      
         
         
         
  Fault description:

Enter from the database

mysql> show slave status \ G;

The results are as follows:

...

Slave_IO_Running: Yes

Slave_SQL_Running: No

Last_Errno: 1062

...

the reason:

1. The program may be a write operation on the slave

2. After the slave machine may be restarted, things caused rollback

Most are caused by the second

Solution one: Continue to ignore the error after synchronization

The method is applicable to the main data from the library or less, or require data may not be completely uniform, the data requirements are not stringent conditions

mysql> slave stop;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
mysql> slave start;

Solution two: Mandatory start synchronization from a certain point

This method will be some loss of data is not synchronized, delete records on a subsequent synchronization master server will have some error messages, it will not affect the use of

View the host to the primary server status:
Record File Position and the corresponding value.
mysql> show master status;
+ ------------------ + ----------- + -------------- + --- --------------- +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ------------------ + ----------- + -------------- + --- --------------- +
| Mysql-bin.000021 | 135617782 | | |
+ ------------------ + ----------- + -------------- + --- --------------- +
1 row in set (0.00 sec)

3. to the slave server to perform manual synchronization:
mysql> change master to
> Master_host = 'master_ip',
> Master_user = 'user',
> Master_password = 'pwd',
> Master_port = 3307,
> Master_log_file = 'mysql-bin.000021',
> Master_log_pos = 135617782;
1 row in set (0.00 sec)
mysql> slave start;
1 row in set (0.00 sec)
See slave status again found:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...
Seconds_Behind_Master: 0

Solution three: Re-shots from fully synchronized

The method is applicable to the case of a larger difference from the main library data, or require a fully integrated data

1. Go to the main library, data backup


mysqldump -u root -p --opt --master-data --single-transaction -B> mysql.bak.sql

2. Backup files to mysql from the library, import from library

mysql> stop slave;

mysql -u root -p < mysql.bak.sql

3. Locate the file and position from the backup files

grep -i "CHANGE MASTER TO" mysql.bak.sql will be similar to the following contents:

CHANGE MASTER TO MASTER_LOG_FILE = 'mysql-bin.000021', MASTER_LOG_POS = 135617782;

4. Set the synchronization from the library

mysql> change master to
> Master_host = 'master_ip',
> Master_user = 'user',
> Master_password = 'pwd',
> Master_port = 3307,
> Master_log_file = 'mysql-bin.000021',
> Master_log_pos = 135617782;

mysql> slave start;
1 row in set (0.00 sec)
See slave status again found:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...
Seconds_Behind_Master: 0
     
         
         
         
  More:      
 
- learning Linux ls command examples (Linux)
- Oracle Data File Management (Database)
- Depth understanding of C language (Programming)
- ImageMagick Tutorial: How to cut images in Linux command line (Linux)
- Hibernate Performance Optimization of EHCache cache (Programming)
- MySQL simple operation notes under Linux (Database)
- Bash common several configuration files (Linux)
- Oracle SQL statement to retrieve data paging table (Database)
- iOS GCD multithreading simple to use (Programming)
- Install the latest development version of Wine on RedHat and Debian-based systems (Linux)
- PostgreSQL using the system cache to improve operational efficiency (Database)
- OpenNMS separate database (Server)
- How to use the TF / SD card making Exynos 4412 u-boot boot disk in Mac (Linux)
- Spring Data MongoDB combat (Database)
- Shell Scripting Interview Questions (Programming)
- Linux basic introductory tutorial ---- Software Installation under Linux (Linux)
- Java recognize simple codes (Programming)
- Configuring Eclipse Note Templates (Linux)
- Linux five security TIPS (Linux)
- Variables Python variables (Programming)
     
           
     
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.