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 status\G  

 

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