centos7上mysql5.6版本主从复制

 

做主从复制实验：

第一步：主服务器上操作

1、修改主服务器master:

?

1 2 3

[root@localhost ~]# vim /etc/my.cnf server_id = 1  //[必须]服务器唯一ID，默认是1 log-bin=mysql-bin //[必须]启用二进制日志

 2、重启主数据库

?

1	[root@localhost ~]# systemctl restart mysqld

3、在主服务器上建立帐户并授权slave:

?

1 2 3 4 5

mysql> grant replication slave on *.* to 'zhangsan'@'%' identified by '123456'; Query OK, 0 rows affected (0.00 sec)   mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)

 4、登录主服务器的mysql，查询master的状态

?

1 2 3 4 5 6 7

mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 |      401 |              |                  |                   | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)

  注：执行完此步骤后不要再操作主服务器MYSQL，防止主服务器状态值变化

第二步：从服务器操作

1、修改从服务器slave:

?

1 2 3

[root@localhost ~]# vim /etc/my.cnf  server_id = 2 log-bin=mysql-bin

 2、重启从数据库

?

1	[root@localhost ~]# systemctl restart mysqld

 3、配置从服务器Slave：

?

1 2 3 4 5

mysql> change master to master_host='192.168.35.131',master_user='zhangsan',master_password='123456', master_log_file='mysql-bin.000001',master_log_pos=401; Query OK, 0 rows affected, 2 warnings (0.02 sec)   mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)

4、启动从服务器复制功能并检查从服务器复制功能状态

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63

mysql> start slave; Query OK, 0 rows affected (0.13 sec)   mysql> show slave status \G; *************************** 1. row ***************************                Slave_IO_State: Waiting for master to send event                   Master_Host: 192.168.35.131  //主服务器地址                   Master_User: zhangsan     //授权帐户名，尽量避免使用root                   Master_Port: 3306                 Connect_Retry: 60               Master_Log_File: mysql-bin.000001           Read_Master_Log_Pos: 401   //#同步读取二进制日志的位置，大于等于Exec_Master_Log_Pos                Relay_Log_File: localhost-relay-bin.000002                 Relay_Log_Pos: 283         Relay_Master_Log_File: mysql-bin.000001              Slave_IO_Running: Yes    //此状态必须YES             Slave_SQL_Running: Yes    //此状态必须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: 401               Relay_Log_Space: 460               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: 31f8646b-484e-11e8-b503-000c298f5b19              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

 注：Slave_IO及Slave_SQL进程必须正常运行，即YES状态，否则都是错误的状态(如：其中一个NO均属错误)。

第三步：检测主从配置

1、在主数据库中创建一个库data，查看从库中是否有

主库中：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

mysql> show databases; +--------------------+ | Database           | +--------------------+ | information_schema | | mysql              | | performance_schema | | test               | +--------------------+ 4 rows in set (0.00 sec)   mysql> create database data; Query OK, 1 row affected (0.00 sec)   mysql> show databases; +--------------------+ | Database           | +--------------------+ | information_schema | | data               | | mysql              | | performance_schema | | test               | +--------------------+ 5 rows in set (0.00 sec)

 2、从库中查看：

?

1 2 3 4 5 6 7 8 9 10 11

mysql> show databases; +--------------------+ | Database           | +--------------------+ | information_schema | | data               | | mysql              | | performance_schema | | test               | +--------------------+ 5 rows in set (0.00 sec)

 能够看到从库中是有data库的，说明主从复制配置完成，但是要注意，从表里不能做更新，删除等操作，只能使用查询，所以要慎重。

 

总结：完成以上操作过程，主从服务器配置完成。