主机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):