没有合适的资源?快使用搜索试试~ 我知道了~
mysql5.5源码主从复制搭建(以两台机器的单实例为例)
需积分: 5 0 下载量 50 浏览量
2023-07-07
20:41:31
上传
评论
收藏 36KB DOCX 举报
温馨提示
试读
25页
mysql5.5源码主从复制搭建(以两台机器的单实例为例)
资源推荐
资源详情
资源评论
mysql5.5 源码主从复制搭建(以两台机器的单实例为例)
项目 1 配置 mysql 服务器的主从结构 (开始时主从数据不一致时需要找到临界点的主从同步,主库初始是单库时)
1.在 192.168.37.128(主)上
[root@localhost ~]# /etc/init.d/mysqld status
SUCCESS! MySQL running (61813)
[root@localhost ~]# netstat -anptu |grep 3306
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 61813/mysqld
1)主库上建立一些数据,模拟生产环境有数据
[root@localhost ~]# mysql -uroot -p123
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
mysql> create database ku;
mysql> use ku;
mysql> create table biao (id int(2),name varchar(8));
mysql> insert into biao values (1,'shi1');
mysql> insert into biao values (2,'shi2');
mysql> insert into biao values (3,'shi3');
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| ku |
| mysql |
| performance_schema |
| test |
+--------------------+
mysql> select * from ku.biao;
+------+------+
| id | name |
+------+------+
| 1 | shi1 |
| 2 | shi2 |
| 3 | shi3 |
+------+------+
mysql> quit
2)开启 binlog 日志
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
server-id = 1 #主从编号不能一致
log-bin=mysql-bin #开启 bin-log
wq
[root@localhost ~]# ls /usr/local/mysql/data/
ibdata1 ib_logfile1 localhost.localdomain.err mysql test
ib_logfile0 ku localhost.localdomain.pid performance_schema
[root@localhost ~]# mysql -uroot -p123 -e "show variables like 'log_bin'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | OFF |
+---------------+-------+
[root@localhost ~]# /etc/init.d/mysqld stop
[root@localhost ~]# /etc/init.d/mysqld start
[root@localhost ~]# ls /usr/local/mysql/data/
ibdata1 ib_logfile1 localhost.localdomain.err mysql mysql-bin.index test
ib_logfile0 ku localhost.localdomain.pid mysql-bin.000001 performance_schema
[root@localhost ~]# mysql -uroot -p123 -e "show variables like 'log_bin'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
3)授权一个用户,允许从库用此用户连接主库,然后锁表,查看 binlog 日志名称和位置点,准备全量备份(全量备份
所有的数据库时候,另开一个窗口,该窗口不能断)
[root@localhost ~]# mysql -uroot -p123
mysql> grant replication slave on *.* to 'rep'@'192.168.37.%' identified by '123';
mysql> flush privileges;
mysql> flush table with read lock; #锁表,此时不提供服务,重开窗口做备份,也可不锁,备份时指定参数-x 锁表备份
mysql> show master status; #查看位置点,也可不 show,直接在备份时加参数--master-data=2 找位置点
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 331 | | |
+------------------+----------+--------------+------------------+
此窗口不断,备份完成后,再将锁住的表解锁,因为主库要提供服务。见下面
此窗口如果关闭,则锁表失效,还有这两参数超时也失效:interactive_timeout = 60 wait_timeout = 60
注意:mysql5.5 和 5.1 锁表命令不一样:5.5 是:flush table with read lock;而 5.1 是:flush tables with read lock;
4)主库上另开一个窗口,做全备份数据库(主从复制可以不刷新 logbin,防止文件杂乱)
[root@localhost ~]# mysqldump -uroot -p123 -A -B --events --master-data=1/2 > /opt/rep.sql
#注意,上面操作的锁表和查看位置点也可不做,在备份时加参数-x 锁表,加参数--master-data=1/2 查看位置点也可实现。如果加参数--master-data=1/2,那么就会自动帮
你找位置点,从库 change master 时候,可以省略 master_log_file 和 master_log_pos 这两项,这两项可以不加,1:没注释,2:注释了,但 1 或 2 都行,尽量用 1 吧。
[root@localhost ~]# less /opt/rep.sql grep MASTER_LOG_POS 或 more,确认查看的位置点,注意:中间没有管道
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=331;
[root@localhost ~]# ls /usr/local/mysql/data/ #备份时没刷新 binlog
ibdata1 ib_logfile1 localhost.localdomain.err mysql mysql-bin.index test
ib_logfile0 ku localhost.localdomain.pid mysql-bin.000001 performance_schema
[root@localhost ~]# grep "mysql-bin.000001" /opt/rep.sql
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=331; 1 时:没注释
5)主库刚才锁表和查看位置点的窗口,查看位置点没变化(证明备份时已锁住表,正常),备份完后,然后解锁
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 331 | | |
+------------------+----------+--------------+------------------+
mysql> unlock tables; #表解锁,这时数据库就能提供服务了,不用担心从库追不上主库,只要位置点找对就没事
mysql> quit #表解锁了,就可以退出该窗口也可以了
6)主库上再增加点数据,模拟全备数据库后还有增量的数据
[root@localhost ~]# mysql -uroot -p123
mysql> insert into ku.biao values (4,'shi4');
mysql> insert into ku.biao values (5,'shi5');
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| ku |
| mysql |
| performance_schema |
| test |
+--------------------+
mysql> select * from ku.biao;
+------+------+
| id | name |
+------+------+
| 1 | shi1 |
| 2 | shi2 |
| 3 | shi3 |
| 4 | shi4 |
| 5 | shi5 |
+------+------+
mysql> quit
[root@localhost ~]# scp /opt/rep.sql [email protected]:/opt/
2.在 192.168.37.129(从)上:
[root@localhost ~]# /etc/init.d/mysqld status
SUCCESS! MySQL running (23311)
[root@localhost ~]# netstat -anptu |grep 3306
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 23311/mysqld
1)配置 server-id,和主的不能一样,log-bin 可根据情况开或不开,此处不开
[root@localhost ~]# vim /etc/my.cnf
server-id = 2
#log-bin=mysql-bin
[root@localhost ~]# /etc/init.d/mysqld stop
[root@localhost ~]# /etc/init.d/mysqld start
2)将主库的全量备份恢复到从库里(全量备份备的数据先补齐)
[root@localhost ~]# mysql -uroot -p123
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
mysql> quit
[root@localhost ~]# ls /opt/rep.sql
/opt/rep.sql
[root@localhost ~]# mysql -uroot -p123 < /opt/rep.sql #主从同步前,先恢复全量备份数据
[root@localhost ~]# mysql -uroot -p123
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| ku |
| mysql |
| performance_schema |
| test |
+--------------------+
mysql> select * from ku.biao;
+------+------+
| id | name |
+------+------+
| 1 | shi1 |
| 2 | shi2 |
| 3 | shi3 |
+------+------+
mysql> quit
剩余24页未读,继续阅读
资源评论
运维实战帮
- 粉丝: 78
- 资源: 419
上传资源 快速赚钱
- 我的内容管理 展开
- 我的资源 快来上传第一个资源
- 我的收益 登录查看自己的收益
- 我的积分 登录查看自己的积分
- 我的C币 登录后查看C币余额
- 我的收藏
- 我的下载
- 下载帮助
安全验证
文档复制为VIP权益,开通VIP直接复制
信息提交成功