mysql5.5源码主从复制搭建（以两台机器的单实例为例）
如果对运维课程感兴趣，可以在b站上、csdn或微信视频号 上搜索我的账号： 运维实战课程，可以关注我，学习更多免费的运维实战技术视频
项目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
3)连接主库，从找到的binlog日志位置点往下恢复增量的数据（找位置点，打开同步开关）
[root@localhost ~]# mysql -uroot -p123
mysql> change master to master_host='192.168.37.128',
-> master_port=3306,
-> master_user='rep',
-> master_password='123',
-> master_log_file='mysql-bin.000001',
-> master_log_pos=331;
mysql> start slave; #开启增量同步数据的开关
mysql> show slave
标签：+--------------------+,主从复制,为例,源码,master,mysql,root,localhost,schema From： https://blog.csdn.net/qq_34953582/article/details/142186876