Mysql主从（一主双从）

主机ip

主  ip：192.168.88.138

从1 ip：192.168.88.139

从2 ip：192.168.88.140

1）确保从数据库与主数据库里的数据一致

方法1：

主
[root@localhost ~]# systemctl stop mysql

[root@localhost ~]# cd /usr/local/mysql/data/

[root@localhost data]# tar czf /tmp/data.tar.gz *

[root@localhost data]# scp /tmp/data.tar.gz 192.168.88.139:/tmp
The authenticity of host '192.168.88.139 (192.168.88.139)' can't be established.
ECDSA key fingerprint is SHA256:epUDTs55lr03jrstXvd0iwK6X2dT/feLfUGXCEKq9Ek.
ECDSA key fingerprint is MD5:a9:55:d5:71:f3:f5:8f:ab:ca:88:ed:1b:5d:0e:22:11.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.88.139' (ECDSA) to the list of known hosts.
root@192.168.88.139's password: 
data.tar.gz                                                                                         100% 1425KB  84.2MB/s   00:00

从
[root@localhost tmp]# systemctl stop mysql

[root@localhost tmp]# rm -rf /usr/local/mysql/data/*

[root@localhost tmp]# tar xf /tmp/data.tar.gz -C /usr/local/mysql/data/

[root@localhost data]# systemctl start mysql

 方法2：

主
锁表设置只读
mysql> flush tables with read lock;
提示：如果超过设置时间不操作会自动解锁。

主
mysqldump -uroot -p123456 -B 数据库1 数据库2 > /backup/db/db.sql
#复制的数据库是从没有的，要保证主从有的数据库完全一样

主
mysql> unlock tables;

主
主库备份数据上传到从库
 scp /backup/db/sb.sql 192.168.95.139:/tmp

从
mysql -uroot -p123456 < /tmp/db.sql

2）在主数据库里创建一个同步账户授权给从数据库使用

mysql [(none)]>grant replication slave on *.* to 'rep'@'192.168.88.%' 
identified by '123456';


mysql [(none)]>show grants for 'rep'@'192.168.88.%';

刷新权限列表
mysql [(none)]>flush privileges;
Query OK, 0 rows affected (0.00 sec)

3）配置主数据库（修改配置文件）

vim /etc/my.cnf
log-bin=mysql-bin
server_id=138

4）配置从数据库（修改配置文件）

#139从
vim /etc/my.cnf
log-bin=mysql-bin
server_id=139

#140从
vim /etc/my.cnf
log-bin=mysql-bin
server_id=140

5）设定主从同步

现在主上查看正在使用的bin-log日志

mysql [(none)]>show master status;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000007 |      487 |              |                  | eda16056-c646-11ed-8b10-000c298fd3ca:1-29 |
+------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)

139从

mysql [(none)]>change master to
    -> master_host="192.68.88.138",
    -> master_user="rep",
    -> master_password="123456",
    -> master_log_file="mysql-bin.000007",
    -> master_log_pos=487;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    2
Current database: *** NONE ***

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

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

mysql [(none)]>show slave status\G

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

140从

mysql [(none)]>change master to
    -> master_host="192.68.88.138",
    -> master_user="rep",
    -> master_password="123456",
    -> master_log_file="mysql-bin.000007",
    -> master_log_pos=487;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    2
Current database: *** NONE ***

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

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

mysql [(none)]>show slave status\G

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

 5）测试主从同步

在主库上创建一个数据库：

 从上检查（139）：