mysql5.7主从

1：主mysql,创建复制用户

mysql> create user 'repl'@'172.xx.xx.%' identified by 'xxxx';
Query OK, 0 rows affected (0.01 sec)

mysql> grant replication slave on *.* to 'repl'@'172.xx.xx.%' identified by 'xxx';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges; 
Query OK, 0 rows affected (0.01 sec)

mysql> select user,host from mysql.user;
+---------------+-------------+
| user          | host        |
+---------------+-------------+
| jiangxin      | %           |
| jxgitea       | %           |
| repl          | 172.19.30.% |
| mysql.session | localhost   |
| mysql.sys     | localhost   |
| root          | localhost   |
+---------------+-------------+
6 rows in set (0.00 sec)

2：查看binlog

mysql> show master status;
+------------------+-----------+--------------+-------------------------------------------------+-------------------+
| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB                                | Executed_Gtid_Set |
+------------------+-----------+--------------+-------------------------------------------------+-------------------+
| mysql-bin.000039 | 212379497 |              | information_schema,performance_schema,mysql,sys |                   |
+------------------+-----------+--------------+-------------------------------------------------+-------------------+
1 row in set (0.00 sec)

mysql> show slave hosts;
Empty set (0.00 sec)

mysql> show slave hosts;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID                           |
+-----------+------+------+-----------+--------------------------------------+
|       223 |      | 3306 |        66 | f7378ba7-897d-11e8-88cd-00163e06c223 |
+-----------+------+------+-----------+--------------------------------------+
1 row in set (0.00 sec)

从配置成功，show slave hosts;才不为空

1:slave配置

stop slave;
change master to master_host='IP',master_user='xxx',master_password='xxxxxx',master_log_file='mysql-bin.000039',master_log_pos=212379497;
start slave;

------------------
stop slave;
set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
start slave;

配置过程中出现的问题

1：修改server-id

[root@izuf69ftqv2b857mlhbdvgz ~]# cat /etc/my.cnf
[mysqld]
server-id=223

2:The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

[root@iZbpycie06u04lZ ~]# cat /usr/local/mysql/data/auto.cnf
[auto]
server-uuid=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
[root@iZbpycie06u04lZ ~]# vi /usr/local/mysql/data/auto.cnf

3:Master command COM_REGISTER_SLAVE failed: Access denied for user 'root'@'%' (using password: YES) (Errno: 1045)

GRANT REPLICATION SLAVE ON *.* TO xxxxxx@'%' IDENTIFIED BY 'xxxxxx';
flush privileges;

 

4: Slave failed to initialize relay log info structure from the repository

mysql> start slave;
ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository
mysql> reset slave;
Query OK, 0 rows affected (0.01 sec)

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

slave reset执行候做了这样几件事：
1、删除slave_master_info ，slave_relay_log_info两个表中数据；
2、删除所有relay log文件，并重新创建新的relay log文件；