MySQL5.6 数据库主从同步安装与配置详解(Master/Slave)02

 

此时,查看数据库的报错信息(数据库的目录, /data/mysqldb/VM_128_194_centos.err ),可以看到:

2016-05-06 13:12:04 13345 [Note] InnoDB: Waiting for purge to start

2016-05-06 13:12:04 13345 [Note] InnoDB: 5.6.27 started; log sequence number 2850211

2016-05-06 13:12:04 13345 [ERROR] /data/home/server/mysql-5.6.27/bin/mysqld: unknown variable ‘master-host=192.168.1.1’

2016-05-06 13:12:04 13345 [ERROR] Aborting

可以看出master-host 被检测数是一个未知的变量,因此会出现错误。

5、在5.6以及后续版本的配置如下:

修改完/etc/my.cnf 文件之后,重启一下MySQL(service mysqld restart)

进入Slave mysql控制台,执行:

mysql> stop slave; #关闭Slave

mysql> change master to master_host=’192.168.1.1′,master_user=’root’,master_password=’123456′,master_log_file=’mysql-bin.000004′, master_log_pos=28125;

 
 

mysql> start slave; #开启Slave

在这里指定Master的信息,master_log_file是在配置Master的时候的File选项, master_log_pos是在配置Master的Position 选项,这里要进行对应。

然后可以通过mysql> show slave status; 查看配置的信息:

mysql> show slave status \G;

*************************** 1. row ***************************

        Slave_IO_State: Waiting for master to send event

         Master_Host: 192.167.1.1

         Master_User: root

         Master_Port: 3306

        Connect_Retry: 60

       Master_Log_File: mysql-bin.000004

     Read_Master_Log_Pos: 28125

        Relay_Log_File: VM_128_194_centos-relay-bin.000004

        Relay_Log_Pos: 26111

    Relay_Master_Log_File: mysql-bin.000004

       Slave_IO_Running: Yes

      Slave_SQL_Running: Yes

       Replicate_Do_DB: ufind_db

     Replicate_Ignore_DB: mysql

      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: 28125

       Relay_Log_Space: 26296

       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: 2

         Master_UUID: 8ac3066a-9680-11e5-a2ec-5254007529fd

       Master_Info_File: /data/mysqldb/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)

 
 

ERROR:

No query specified

 
 

mysql>

可以看到,已经配置成功。

添加需要同步的从库Slave

由于种种原因,测试的时候使用test库,这里我按照上述的方式,修改Master的my.cnf的配置文件,新增同步的数据库test,重启MySQL,执行Master的:show master status如下:

相应的,要修改Slave从库的信息在my.cnf 增加 replicate-do-db=test,重启Mysql,根据上述的show master status,在Slave从库中执行下边的内容:

>stop slave

>change master to master_host=’192.168.1.1′,master_user=’root’,master_password=’123456′,master_log_file=’mysql-bin.000005′, master_log_pos=120;

>start slave

然后使用:show slave status;

mysql> show slave status\G;

*************************** 1. row ***************************

        Slave_IO_State: Waiting for master to send event

         Master_Host: 192.168.1.1

         Master_User: root

         Master_Port: 3306

        Connect_Retry: 60

       Master_Log_File: mysql-bin.000005

     Read_Master_Log_Pos: 1422

        Relay_Log_File: VM_128_194_centos-relay-bin.000004

        Relay_Log_Pos: 283

    Relay_Master_Log_File: mysql-bin.000005

       Slave_IO_Running: Yes

      Slave_SQL_Running: Yes

       Replicate_Do_DB: ufind_db,test

     Replicate_Ignore_DB: mysql

      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: 1422

       Relay_Log_Space: 468

       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: 2

         Master_UUID: 8ac3066a-9680-11e5-a2ec-5254007529fd

       Master_Info_File: /data/mysqldb/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)

 
 

ERROR:

No query specified

 
 

mysql>

已经新增加了test。

真正的测试

     

如果这文章对你有帮助,请扫左上角微信支付-支付宝,给于打赏,以助博客运营

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注