MySQL Master never synchronizes--database initialization

Problem-handling for reference to online content

Re-master from, fully synchronized

After you create a new table in the main library, you find that the data is not synchronized in the slave segment.

MySQL version:5.6.10

Os:rhel 5.6

The steps to resolve are as follows:

1. Lock table for main library, prevent data write

Mysql> flush tables with read lock;

Unlock command:

mysql> unlock tables;

2. Data backup, data backup to Mysql.sql file

#mysqldump-uroot-pmysql--all-databases > Mysql.sql

3. View Master Status

Mysql> Show master status;

+------------------+----------+--------------+------------------+-------------------+

| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |

+------------------+----------+--------------+------------------+-------------------+

|      mysql-bin.000003 |                  335 |test |                   | |

+------------------+----------+--------------+------------------+-------------------+

1 row in Set (0.00 sec)

4. Upload the MySQL backup file to the slave library for data recovery

SCP or FTP

5. Stop the state from the library

mysql> stop Slave;

6. Import data backup from library

Mysql> Source/lixora/mysql.sql;

7. Set synchronization from the library, note that the synchronization point is in the main library show Master status information | file| Position two items

Change Master to
Master_host= ' 10.80.18.241 ',-----Main Library IP Address
Master_user= ' Root ',
master_password= ' MySQL ',
master_port=3306,
Master_log_file= ' mysql-bin.000003 ',
master_log_pos=335;

Where Master_host is the IP of the master machine,

Master_user and Master_password are users added on master and used to copy operations

Master_log_file and Master_log_pos correspond to the information in the show master status

8. Re-open from sync

mysql> start slave;

9. View sync Status

Mysql> Show Slave Status\g

Slave_io_running:yes

Slave_sql_running:yes

Synchronization test:

"Master"

mysql> use test;
Database changed

Mysql> Show tables;
+----------------+
| Tables_in_test |
+----------------+
| Lixora |
| Max |
| Test |
+----------------+
3 Rows in Set (0.00 sec)


Mysql> CREATE TABLE lixora_2015_0317 as SELECT * from Mysql.user;
Query OK, 5 rows affected (0.68 sec)
Records:5 duplicates:0 warnings:0

Mysql> Show tables;
+------------------+
| Tables_in_test |
+------------------+
| Lixora |
| lixora_2015_0317 |
| Max |
| Test |
+------------------+
4 rows in Set (0.00 sec)


Mysql> select * from lixora_2015_0317;

。。。

5 rows in Set (0.00 sec)

"Slave"

mysql> Use test
Database changed
Mysql> Show tables;
+------------------+
| Tables_in_test |
+------------------+
| Lixora |
| lixora_2015_0317 |
| Max |
| Test |
+------------------+
4 rows in Set (0.00 sec)


Mysql> Select COUNT (*) from lixora_2015_0317;
+----------+
| COUNT (*) |
+----------+
| 5 |
+----------+
1 row in Set (0.06 sec)

Mysql> Show Slave Status\g
1. Row ***************************
Slave_io_state:waiting for Master to send event
master_host:10.80.18.241
Master_user:root
master_port:3306
Connect_retry:60
master_log_file:mysql-bin.000003
read_master_log_pos:1166
relay_log_file:oracle11g-dag-relay-bin.000002
relay_log_pos:1114
relay_master_log_file:mysql-bin.000003
Slave_io_running:yes
Slave_sql_running:yes
replicate_do_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:1166
relay_log_space:1295
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:
Replicate_ignore_server_ids:
Master_server_id:1
master_uuid:888a8d9e-cbb3-11e4-b87e-000c29d57584
Master_info_file:/var/lib/mysql/master.info
sql_delay:0
Sql_remaining_delay:null
Slave_sql_running_state:slave have read all relay log; Waiting for the slave I/O thread to update it
master_retry_count:86400
Master_bind:
Last_io_error_timestamp:
Last_sql_error_timestamp:
MASTER_SSL_CRL:
Master_ssl_crlpath:
Retrieved_gtid_set:
Executed_gtid_set:
auto_position:0
1 row in Set (0.00 sec)

MySQL Master never synchronizes--database initialization