主从搭建的方法:
a.准备两台服务器
系统版本 | 主机ip | 切换前角色 | 切换后角色 | 备注 |
ubuntu20.5 | 10.41.17.10 | master | slave | |
ubuntu20.5 | 10.41.17.11 | slave | master |
b.安装mysql数据库
* 数据下载(mysql 8.0.34)
root@mysql-master-test1:~# wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-server_8.0.34-1ubuntu20.04_amd64.deb-bundle.tar
root@mysql-master-test1:~# tar xf mysql-server_8.0.34-1ubuntu20.04_amd64.deb-bundle.tar
root@mysql-master-test1:~# chmod +x mysql-*
root@mysql-master-test1:~# dpkg -i mysql-community-server_8.0.34-1ubuntu20.04_amd64.deb
root@mysql-master-test1:~# dpkg -i mysql*
#解决依赖问题
root@mysql-master-test1:~# apt --fix-broken install
root@mysql-master-test1:~# dpkg -i mysql* //安装完成
root@mysql-master-test1:~# service mysql status
- 配置数据主库
添加配置文件
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
log-error = /var/log/mysql/error.log
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
read-only = 0
binlog_expire_logs_seconds = 2592000
max_binlog_size = 100M
binlog_do_db = test
"/etc/mysql/mysql.conf.d/mysqld.cnf" 42L, 1602C
#登录数据库执行以下命令
mysql -u root -p
#创建用户 我这里用户名为copyuser,注意这里的ip是从库服务器的ip
CREATE USER 'copyuser'@'10.41.17.11' IDENTIFIED WITH mysql_native_password BY '123456';
#给主从复制账号授权
grant replication slave on *.* to 'copyuser'@'10.41.17.11';
重启数据库
service mysql restart
查看状态
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 157 | test | mysql,sys | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql>
配置从数据库
添加配置如下
server-id = 2 #服务器 id,随意,但要唯一
log_bin = /var/log/mysql/mysql-bin.log #二进制文件存放路径
read-only = 1 #[可选] 0(默认)表示读写(主机),1表示只读(从机)
binlog_expire_logs_seconds = 2592000 #设置日志文件保留的时长,单位是秒
max_binlog_size = 100M #控制单个二进制日志大小。此参数的最大和默认值是1GB
replicate_do_db = test #待同步的数据库日志
replicate_ignore_db = mysql,sys #不同步的数据库日志
重启数据库
service mysql restart
实现主从同步
#1,先登录mysql,然后执行后续代码
mysql -u root -p密码
#2,关闭从库
stop slave;
#3,设置同步,注意这里是主库ip,日志名称和位置是我们之前上图中看到的名称和位置
change master to master_host='192.168.1.10',master_user='copyuser',master_password='123456',master_log_file='mysql-bin.000004',master_log_pos=157;
#4,开启从库
start slave;
# 5,检查服务器状态
show slave status \G;
看待同步相关线程ok及配置主从成功