MySQL Master never synchronizes problems

First, the primary database (after the stop service) is copied directly into the database from.

If the amount of data is too large, you can back up from the primary database to synchronize the data (from the database missing data), the following example to back up some of the data in a single table

Primary database:

1, mysqldump-uroot-p password--single-transaction-t database name indicates--where= "id>100" >/var/www/data_back/system_db_cms_item_ News_1214.sql

--where= "id>100" in the ID >100 is a condition in SQL, can be customized such as name= "John Doe"

/var/www/data_back/beifen.sql the path of the backup file, customize it

From the database:

1, the Beifen.sql retransmission only from the database on the server, I uploaded to the/var/www/data_source/beifen.sql

Log on to MySQL on the server

Use DB1; Select your own database

Executive Source/var/www/data_source/beifen.sql

Import the database. Data synchronization is complete.

Two

Resets the log for the primary database, resets the log from the database, and re-establishes the synchronization

1. Stop all Synchronization Services from the database

Stop slave;

2. Primary database reset log

Reset Master;

and view the status show master status; Shown below

+-------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+-------------     +----------+--------------+------------------+
|     data.000001 | 106 |                  SYSTEM_DB1 | |
+-------------+----------+--------------+------------------+

3. resetting the log from the database

RESET SLAVE;

4. Set the location to read the primary database log from the database

Change Master to master_host= ' 129.90.1.1,master_user= ' root ', master_password= ' 123456 ', master_log_file= ' data.000001 ', master_log_pos=106;

This refers to the status value of the primary database, such as the 2nd step.

Start slave; Turn on Sync server

To see if the values from the database state are consistent,
Show Slave Status\g

As follows

Slave_io_state:waiting for Master to send event
master_host:129.90.1.1
Master_user:root
master_port:3306
Connect_retry:60
master_log_file:data.000001
read_master_log_pos:106
relay_log_file:mysql-relay-bin.000002
relay_log_pos:246
relay_master_log_file:data.000001
Slave_io_running:yes
Slave_sql_running:yes
replicate_do_db:system_db
replicate_ignore_db:
Replicate_do_table:
Replicate_ignore_table:
Replicate_wild_do_table:
Replicate_wild_ignore_table:
last_errno:0
Last_error:
skip_counter:0
exec_master_log_pos:106
relay_log_space:401
Until_condition:none
Until_log_file:
until_log_pos:0
Master_ssl_allowed:no
Master_ssl_ca_file:
Master_ssl_ca_path:
Master_ssl_cert:
Master_ssl_cipher:
Master_ssl_key:
seconds_behind_master:0
Master_ssl_verify_server_cert:no
last_io_errno:0
Last_io_error:
last_sql_errno:0
Last_sql_error:

View the Red font section

The rest will test the master and slave data synchronously.

MySQL Master never synchronizes problems