MySQL主从同步与读写分离

2020-11-18 13:19:13 MySQL

主从复制的作用

热备份，防止主数据库意外崩溃或数据丢失
提高数据库性能，采用主从复制、读写分离，将读和写分开，分摊数据库压力

主从复制的原理

当master（主库）发生了insert update delete 等操作的时候（主要指表操作，当然也包括create数据库或者表等命令），操作内容会被写入binlog
salve（从库）会连接master，而master会为每个salve创建一个binlog dump线程
当有内容被写入master的binlog之后，master会通知所有的salve，并且发送相关数据
salve 的io线程接受到数据后会写入relay log
随后salve的sql线程读取relay log内容，对数据库做相应的操作（与master数据库相同的操作）
以上，便实现了主从同步（或主从复制）

安装MySQL

首选，安装一下MySQL

sudo apt install -y mysql-server mysql-client

如果装完了发现，不知道密码，也无法登陆MySQL，莫慌，看这里：ubuntu安装MySQL后无法登陆也无法修改密码

配置主库Master

配置从库账号，其中 vien 和 password 换成你的用户名密码，用于从库连接主库，192.168.0.172是从库的IP地址

GRANT REPLICATION SLAVE ON *.* to 'vien'@'192.168.0.172' identified by 'password';

刷新权限

FLUSH PRIVILEGES;

修改配置文件

vim /etc/mysql/my.cnf

在文件末尾追加：

[mysqld]
#服务器唯一ID
server-id=1
#启用二进制日志
log-bin=mysql-bin
# 同步的数据库，不写默认全部
binlog-do-db=test_db
# binlog日志保留的天数，清除超过10天的日志 防止日志文件过大，导致磁盘空间不足
expire-logs-days=10
#不同步mysql系统数据库。
binlog-ignore-db=mysql

还有就是默认是不开启远程连接的，需要注释掉绑定127.0.0.1连接的配置项：

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

找到这一行并且注释

bind-address            = 127.0.0.1

改为

# bind-address            = 127.0.0.1

重启服务

service mysql restart

查看当前日志状态

mysql> show master status;

打印结果

+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              | mysql            |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

配置slave

修改配置文件

vim /etc/mysql/my.cnf

在文件末尾追加：

[mysqld]
#服务器唯一ID
server-id=100

重启mysql

service mysql restart

MySQL内执行：

停止slave同步进程

stop slave;

执行同步语句

change master to master_host='192.168.0.141',master_user='slave0',master_password='slave0',master_log_file='mysql-bin.000001' ,master_log_pos=154;

master_host 是主机IP，master_user 和 master_password 是之前在master主机上为slave创建的账户和密码，master_log_file 是master上的binlog日志名称，master_log_pos 是日志偏移量，上面show master status;里有相关信息, master_port 是端口号，默认3306。

开启slave同步进程

start slave;

查看slave同步信息

show slave status\G;

截取部分打印：

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.141
                  Master_User: slave0
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 154
               Relay_Log_File: hecs-x-xlarge-2-linux-20201117135522-relay-bin.000003
                Relay_Log_Pos: 367
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

通过Slave_IO_Running和Slave_SQL_Running可以看到IO进程和SQL进程状态都是Yes，Slave_IO_State 显示正在等待master发送event，这说明大功告成了，接着我们来试一下效果。

测试

在master上创建一个叫test的数据库

create database test;

然后查看一下：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

可以看到我们创建了一个叫test的数据库，然后去slave服务器看一下

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

可以发现，同样也创建了一个叫test的数据库，可喜可贺，可口可乐，完。

欢迎小伙伴们在下方评论区留言 ~ O(∩_∩)O
文章对我有帮助, 点此请博主吃包辣条 ~ O(∩_∩)O

AdSense Anaconda Android apache API apt Auth AWS B-tree Bandwagon Blog bower brew bytes Caffe Catalina certbot Charles cloudcone Composer conda CoreML CPU crontab CSS csv Cuda cv2 datetime Digitalocean DNS Docker Docker-Compose Eloquent Excel export Flask FTP GET Git GitHub GitLab Gmail GoDaddy Google GTM hash Homebrew Homestead HTML http HTTPS IDEA image imagemagick imagick imgick import InnoDB ios iou iPhone ISO8601 iTerm2 Java JavaScript JPG JS Keras Laravel Laravel-Admin lazyload Linux list Livewire lnmp load logs Lravel Mac Markdown matplotlib md5 mix MobileNet Mojave mongo MongoDB MySQL Namesilo Nginx Node npm numpy Nvidia Nvidia-Docker onevps OpenCV Openpose openpyxl oss Outline parse PayPal PHP php-fpm PhpStorm PHP扩展 PIL Pillow pip PNG POST Protobuf PyCharm pyenv pymongo Python Python,人工智能,机器学习,VOC,xml Queue Redis requests RGB Sanctum save selenium SEO Shadowsock Shadowsocks ShadowsocksR simplemde Spring Boot SQLServer ssd SSH ssl SSL证书 SSR str Sublime sudo swap Swift Tensorflow TensorflowLite Terminal Terminator timestamp Ubuntu urllib UTC v2ray Valet Validation Validator VienBlog virtualenvs VPN VPS Vultr Web Windows WordPress Xcode xlsx yaml YAPI YUV zip zmq zsh 上网下载图片主从同步云主机云存储云开发云服务器人工智能优化优惠码伪原创作弊与反作弊免费ss账号免费提现切片前端加密协议博客友链双击事件后台运行后端命令国内镜像源图标图片操作图片转换域名多身份认证大小写转换姿态检测安卓模拟器安装定时任务定时执行密码密钥导出导入小程序码延迟加载异常微信微信小程序快捷方式慢查询懒加载提现搜索引擎搬瓦工搭梯子教程数据库数据重复文件上传无法登录日志日期时区时间时间戳服务器机器学习权限梯子模拟浏览器港版支付宝漏洞爬虫生活服务用户管理病毒登录目标检测科学上网系统升级索引组件组件开发编辑器自动付款自定义组件英文伪原创计划任务计算机视觉订阅通知认证语法读写分离远程连接配置文件重定向错误异常错误提示队列阿里云香港香港手机号

亲情非友情链接