（二）mysql主从同步搭建步骤
时间：2021-07-01 10:21:17
帮助过：2人阅读
Slave_Server:192.168.159.11
1.测试远程账户登录
Master_Server:
#给backup用户授权
mysql> grant replication slave on *.* to ‘backup‘@‘192.168.159.11‘ identified by ‘123‘;
#检查 authentication_string 相当于mysql5.5之前的password
mysql> select host,user,authentication_string from mysql.user;
| host | user | authentication_string |
+----------------+------------------+------------------------------------------- +
| localhost | root | *3DB01AD4E54E8FE9429FA016DB24E4E725247513 |
| localhost | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | debian-sys-maint | *C9A3938D4184835A2096D4F76879FFCEA1383C22 |
| 192.168.159.11 | backup | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+----------------+------------------+------------------------------------------- +
Slave_Server:
mysql -ubackup -p -h‘192.168.159.10‘ #输入密码测试是否能登陆
=========================================
不能登陆时，问题排查：
1）检查Master_Server是否只监听本机3306端口
netstat -an|grep 3306
root@ubuntu:~# netstat -an|grep 3306
tcp6 0 0 :::3306 :::* LISTEN
tcp6 0 0 192.168.159.10:3306 192.168.159.11:53918 ESTABLISHED
#如果只出现127.0.0.1：3306 则修改my.cnf 解除ip绑定，
vim /etc/mysql/mysql.conf.d/mysqld.cnf
注释掉# bind-address = 127.0.0.1
测试能否成功
2）关闭防火墙/telnet 3306 检测端口能否访问
==========================================
2.开始配置主从
先确定mysql版本,mysql5.17后不支持将主从配置信息写在配置文件里面了，
1）Master_Server 配置文件
开启binary log: log_bin = /var/log/mysql/mysql-bin.log
进入mysql，show master status，记下file，positin
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 611 | | | |
+------------------+----------+--------------+------------------+-------------------+
2）Slave_Server 配置文件
修改server-id:server-id = 2
进入mysql
mysql>change master to master_host=‘192.168.159.10‘,
>master_user=‘backup‘,
>master_password=‘123‘,
>master_log_file=‘mysql-bin.000001‘,
>master_log_pos=611;
mysql>start slave;
3）检测是否成功
mysql> show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
==========================================
问题：配置完成Slave_IO_Running:No,查看error.log，
The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work
解决：因为我们虚拟机做了clone，导致主从启动时读取的uuid完全相同，查看
show variables like ‘server%id%‘;
修改UUID:vim /var/lib/mysql/auto.cnf #改成一样的就可以了
（二）mysql主从同步搭建步骤
标签：
