mysql主从同步数据初始化_mysql主从不同步处理--数据库初始化

问题处理借鉴至网上的内容

重新做主从，完全同步

在主库新建一张表后，在slave 段发现数据没有同步过去。

mysql version：5.6.10

os ：rhel 5.6

解决步骤如下：

1.主库进行锁表，防止数据写入

mysql> flush tables with read lock;

解锁命令：

Mysql> unlock tables;

2.进行数据备份 ，数据备份到mysql.sql文件

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

3.查看master 状态

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.把mysql备份文件传到从库机器，进行数据恢复

scp or ftp

5.停止从库的状态

mysql> stop slave;

6.从库导入数据备份

mysql> source /lixora/mysql.sql；

7.设置从库同步，注意该处的同步点，就是主库show master status信息里的| File| Position两项

change master to

master_host='10.80.18.241', -----主库IP地址

master_user='root',

master_password='mysql',

master_port=3306,

master_log_file='mysql-bin.000003',

master_log_pos=335;

其中MASTER_HOST是master机的ip，

MASTER_USER和MASTER_PASSWORD是在master上添加的用户，用来复制操作的用户

MASTER_LOG_FILE和MASTER_LOG_POS对应与show master status里的信息

8.重新开启从同步

mysql> start slave;

9.查看同步状态

mysql> show slave statusG

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

同步测试：

【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 statusG

*************************** 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 has 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)