主从搭建准备
1.主从数据库版本一致
2.主从数据库内数据保持一致
Master
1、修改mysql配置文件
vi /etc/my.cnf
添加以下内容
##master 中my.cnf需要添加的内容,master 192.168.0.23 取最后的23作为id
server-id=23
log-bin=mysql-bin
##生产环境:通常建议将 sync_binlog 设置为 1,以确保每个事务的二进制日志都被安全地写入磁盘。这可以最大限度地减少数据丢失的风险。
sync_binlog=1
##日志保存180天
expire_logs_days=180
重启
systemctl restart mysqld
2、创建授权用户
创建授权用户replicator
CREATE USER 'replicator'@'192.168.0.2' IDENTIFIED BY '你的密码';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'192.168.0.2';
FLUSH PRIVILEGES;
3、查看日志和主机master的状态
查看log_bin日志是否属于开启状态
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
1 row in set (0.00 sec)
查看当前操作的宿主机是否为master状态
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 157 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
这里要记住 file和position,后面配置slave会用到.
slave
修改server-id
##slave 中my.cnf需要添加的内容,slave 192.168.0.2 取最后的2作为id
server-id=2
重启
systemctl restart mysqld
进入mysql执行相关命令
先关掉slave
mysql> stop slave
-> ;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
在从服务器上配置复制:master_host为master的ip,master_password 为授权时设置的密码,master_log_file 分别为上文中需要你记住的file和position
CHANGE MASTER TO MASTER_HOST='192.168.0.23',MASTER_USER='replicator',MASTER_PASSWORD='你的密码',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=2573;
开启slave
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.03 sec)
查看同步状态:
SHOW SLAVE STATUS\G
检查Slave_IO_Running和Slave_SQL_Running是否都为Yes,以及Last_Error是否为空或无错误信息。
出现如下错误:
Last_IO_Error: Error connecting to source 'replicator@192.168.0.23:3306'. This was attempt 2/86400, with a delay of 60 seconds between attempts. Message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.
则在master执行如下操作:
USE mysql;
ALTER USER 'replicator'@'192.168.0.2' IDENTIFIED WITH mysql_native_password BY '你的密码';
FLUSH PRIVILEGES;
重启slave后,查看同步状态
SHOW SLAVE STATUS\G
特别说明,出现:
Last_IO_Error: Source command COM_REGISTER_REPLICA failed:
Access denied for user 'repl'@'%' (using password: YES) (Errno: 1045)
是因为mysql8的复制用户需指定IP。