Mysql主从模式

Master:

1> 授权用户相应权限

mysql> grant replication slave on *.* to 'copy'@'192.168.174.%' identified by 'mima23333232';
Query OK, 0 rows affected (0.00 sec)

2> 查看master状态值，用于slave库上同步

mysql> show master status\G;
*************************** 1. row ***************************
              File: mysql-bin.000001
       Position: 329
     Binlog_Do_DB:
     Binlog_Ignore_DB:
     Executed_Gtid_Set:
1 row in set (0.00 sec)

Slave1：

1> 使用change master语句，确定master库的ip、用户、密码等信息

mysql> change master to
    -> master_host='192.168.174.164',
    -> master_user='lxq',
    -> master_password='123456',
    -> master_log_file='mysql-bin.000001', 
    -> master_log_pos=329;

Query OK, 0 rows affected, 2 warnings (0.00 sec)

以下两项是从master status中获取的File和Position与下列相对应

master_log_file='mysql-bin.000001'

master_log_pos=329

2> 开启同步

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

3> 验证Slave_IO_Running、Slave_SQL_Running是否是YES，两个都是YES表示mysql主从复制配置成功了。

mysql> show slave status\G;
*************************** 1. row ***************************
      Slave_IO_State: Waiting for master to send event
        Master_Host: 192.168.174.164
        Master_User: lxq
        Master_Port: 3306
       Connect_Retry: 60
      Master_Log_File: mysql-bin.000001
    Read_Master_Log_Pos: 329
       Relay_Log_File: relay-bin.000002
       Relay_Log_Pos: 283
   Relay_Master_Log_File: mysql-bin.000001
      Slave_IO_Running: Yes
     Slave_SQL_Running: Yes

Slave_IO_Running: Yes(负责从库去主库读取二进制日志，并写入到从库的中继日志)

Slave_SQL_Running: Yes(负责将中继日志准换成SQL语句后执行)

备注：Slave2库重复一下Slave1的操作即可完成一主多从

3.3 验证能否成功同步

测试主从是否同步（master数据库上执行Sql、Slave上面去验证）

Master:

mysql> create database lxqdb;