本文共 15975 字，大约阅读时间需要 53 分钟。
首先需要两台机器，并且主从复制的前提是需要先做数据同步，先在我的master机器上用mysqldump 将所有的数据备份，然后scp传输到我的slave机器上，然后在slave机器上再将数据导入实现数据同步
然后master机器必须要开启二进制日志，主从服务器配置不同的server_id ,从服务器需要开启slave服务，保证我的I/O thread 和SQL thread 都是开启的，当我的主服务器上的数据改变时，首先会记录到二进制日志，主上的log dump 会向我的从服务器的io thread 发送消息，让它来拿我更新的二进制日志，然后slave的io线程会连接到master ，来读取master的二进制日志，写入到slave的中继日志relaylog中，然后Slave中的SQL线程读取中继日志relaylog进行SQL 的回放（replay）操作，完成主从复制，从而达到主从服务器上的数据一致性
环境准备：两台机器（master slave），编译安装mysql，且最好mysql版本一致
异步复制：
同步复制： 半同步复制：主从复制的前提：先做主从复制的数据同步
二进制日志的位置号：主的二进制位置号肯定比从的二进制号大，所以就会存在一个问题：主并不知道从哪个号码开始推送
I/O thread
SQL thread两台机器，master --二进制
在master主机上要进行的操作
1，开启mysql的二进制日志
[root@master backup]# cat /etc/my.cnf[mysqld_safe][client]socket=/data/mysql/mysql.sock[mysqld]socket=/data/mysql/mysql.sockport = 3306open_files_limit = 8192innodb_buffer_pool_size = 512Mcharacter-set-server=utf8#skip-grant-tables#binary logserver_id=1log_bin [mysql]auto-rehashprompt=\\u@\\d \\R:\\m mysql>#查看二进制日志是否开启root@(none) 09:18 mysql>show variables like "%log_bin%";+---------------------------------+----------------------------------------+| Variable_name | Value |+---------------------------------+----------------------------------------+| log_bin | ON || log_bin_basename | /usr/local/mysql/data/master-bin || log_bin_index | /usr/local/mysql/data/master-bin.index || log_bin_trust_function_creators | OFF || log_bin_use_v1_row_events | OFF || sql_log_bin | ON |+---------------------------------+----------------------------------------+6 rows in set (0.01 sec)
2，建立一个mysql新用户并授予复制权限
root@(none) 08:38 mysql>grant replication slave on *.* to 'xionghan'@'192.168.0.173' identified by '123456';Query OK, 0 rows affected, 1 warning (0.00 sec)root@(none) 08:38 mysql>show slave status\GEmpty set (0.00 sec)
3.备份所有的库
[root@master ~]# mysqldump -uroot -p'Sanchuang1234#' --all-databases >/backup/2020-10-23-all-db.sqlmysqldump: [Warning] Using a password on the command line interface can be insecure.
4，上传数据到从服务器
[root@master ~]# cd /backup/[root@master backup]# ls2020-10-22-all-db.sql 2020-10-23-all-db.sql[root@master backup]# scp 2020-10-23-all-db.sql root@192.168.0.173:/rootroot@192.168.0.173's password: 2020-10-23-all-db.sql 100% 834KB 24.3MB/s 00:00
5.查看当前的二进制日志的文件名和位置号
root@(none) 08:54 mysql>show master status;+-------------------+----------+--------------+------------------+-------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+-------------------+----------+--------------+------------------+-------------------+| master-bin.000002 | 453 | | | |+-------------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)
在从服务器上要做的操作
1.配置server_id（必须的）也开启二进制日志（二进制日志不是必须的）
[root@slave ~]# vim /etc/my.cnf[root@slave ~]# cat /etc/my.cnf[mysqld_safe][client]socket=/data/mysql/mysql.sock[mysqld]socket=/data/mysql/mysql.sockport = 3306open_files_limit = 8192innodb_buffer_pool_size = 512Mserver_id=2log_bin[mysql]auto-rehashprompt=\\u@\\d \\R:\\m mysql>[root@slave ~]# service mysqld restartShutting down MySQL.. SUCCESS! Starting MySQL. SUCCESS! [root@slave ~]# ps aux|grep mysqldroot 46275 0.1 0.0 11816 1592 pts/1 S 08:56 0:00 /bin/sh /usr/local/sc_mysql/bin/mysqld_safe --datadir=/data/mysql --pid-file=/data/mysql/slave.pidmysql 46443 1.5 5.2 2026404 203020 pts/1 Sl 08:56 0:00 /usr/local/sc_mysql/bin/mysqld --basedir=/usr/local/sc_mysql --datadir=/data/mysql --plugin-dir=/usr/local/sc_mysql/lib/plugin --user=mysql --log-error=slave.err --open-files-limit=8192 --pid-file=/data/mysql/slave.pid --socket=/data/mysql/mysql.sock --port=3306root 46475 0.0 0.0 112824 980 pts/1 S+ 08:56 0:00 grep --color=auto mysqld#查看二进制日志是否开启root@(none) 20:33 mysql>show variables like "%log_bin%";+---------------------------------+-----------------------------+| Variable_name | Value |+---------------------------------+-----------------------------+| log_bin | ON || log_bin_basename | /data/mysql/slave-bin || log_bin_index | /data/mysql/slave-bin.index || log_bin_trust_function_creators | OFF || log_bin_use_v1_row_events | OFF || sql_log_bin | ON |+---------------------------------+-----------------------------+6 rows in set (0.24 sec)
2，导入master上的数据
[root@slave ~]# mysql -uroot -p'Sanchuang1234#' < 2020-10-23-all-db.sqlmysql: [Warning] Using a password on the command line interface can be insecure.
3.在slave上配置master的信息和授权的用户名和密码
root@(none) 08:59 mysql>CHANGE MASTER TO MASTER_HOST='192.168.0.171', -> MASTER_USER='xionghan', -> MASTER_PASSWORD='123456', -> MASTER_PORT=3306, -> MASTER_LOG_FILE='master-bin.000002', -> MASTER_LOG_POS=453;Query OK, 0 rows affected, 2 warnings (0.01 sec)
5.查看slave的状态
root@(none) 09:00 mysql>show slave status\G*************************** 1. row *************************** Slave_IO_State: Master_Host: 192.168.0.171 Master_User: xionghan Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-bin.000002 Read_Master_Log_Pos: 453 Relay_Log_File: slave-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: master-bin.000002 Slave_IO_Running: No Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: #启动slave功能root@(none) 09:00 mysql>start slave;Query OK, 0 rows affected (0.00 sec)#再次查看```bashroot@(none) 09:01 mysql>show slave status\G*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.0.171 Master_User: xionghan Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-bin.000002 Read_Master_Log_Pos: 453 Relay_Log_File: slave-relay-bin.000002 Relay_Log_Pos: 321 Relay_Master_Log_File: master-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table:
7.验证主从复制的功能
1.在主上插入新的数据 2.在从上看是否有数据在主服务器上的操作
#新建库root@(none) 09:03 mysql>create database xh1;Query OK, 1 row affected (0.01 sec)root@(none) 09:03 mysql>use xh1;Database changed#新建表root@xh1 09:03 mysql>create table t1(id int);Query OK, 0 rows affected (0.03 sec)root@xh1 09:04 mysql>show tables;+---------------+| Tables_in_xh1 |+---------------+| t1 |+---------------+1 row in set (0.00 sec)#插入数据root@xh1 09:04 mysql>insert into t1(id) values(1),(2);Query OK, 2 rows affected (0.00 sec)Records: 2 Duplicates: 0 Warnings: 0
在从服务器上查看数据是否一致
root@(none) 09:01 mysql>show databases;+--------------------+| Database |+--------------------+| information_schema || aliwangwang || chenran || mysql || performance_schema || sanchuang || sc || sys || wangtc || xh1 || xionghan || zwx |+--------------------+12 rows in set (0.00 sec)root@(none) 09:04 mysql>use xh1;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedroot@xh1 09:04 mysql>show tables;+---------------+| Tables_in_xh1 |+---------------+| t1 |+---------------+1 row in set (0.00 sec)root@xh1 09:09 mysql>select * from t1;+------+| id |+------+| 1 || 2 |+------+2 rows in set (0.00 sec)
上述是一主一从，可不可以一主多从？答案是可以的
一主多从的优点： 1，互相备份 2，流量分流，读写分离，写只往主服务器上写，读主从服务器都可以读，达到一个冗余和负载均衡的效果异步主从复制的缺点：数据有延迟，如何解决？ —引出半同步
日志首先在内存产生—》再写到磁盘 —二进制日志的优点：方便数据恢复
如果slave迟迟没有回复，会自动切换为异步模式
半同步复制的优点：减小数据延迟，master在写数据时会先向从服务器发送消息，主从服务器一起写数据
日志文件 表文件
1，在master上的配置
root@(none) 19:39 mysql>install plugin rpl_semi_sync_master soname 'semisync_master.so';Query OK, 0 rows affected (0.05 sec)
#查看有哪些插件root@(none) 19:41 mysql>show plugins;+----------------------------+----------+--------------------+--------------------+---------+| Name | Status | Type | Library | License |+----------------------------+----------+--------------------+--------------------+---------+| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL || mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL || sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL || InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL || INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_CMP_PER_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_CMP_PER_INDEX_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_TEMP_TABLE_INFO | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_METRICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_FT_DEFAULT_STOPWORD | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_FT_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_FT_BEING_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_FT_CONFIG | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_FT_INDEX_CACHE | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_FT_INDEX_TABLE | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_FIELDS | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_FOREIGN | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_FOREIGN_COLS | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_DATAFILES | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_VIRTUAL | ACTIVE | INFORMATION SCHEMA | NULL | GPL || MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL || MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL || MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL || CSV | ACTIVE | STORAGE ENGINE | NULL | GPL || PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL || BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL || partition | ACTIVE | STORAGE ENGINE | NULL | GPL || ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL || FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL || ngram | ACTIVE | FTPARSER | NULL | GPL || rpl_semi_sync_master | ACTIVE | REPLICATION | semisync_master.so | GPL |+----------------------------+----------+--------------------+--------------------+---------+45 rows in set (0.00 sec)
开启相应的服务
root@(none) 19:43 mysql>set global rpl_semi_sync_master_enabled=1;Query OK, 0 rows affected (0.00 sec)
查看一下
root@(none) 19:44 mysql>show global variables like '%rpl_semi%';+-------------------------------------------+------------+| Variable_name | Value |+-------------------------------------------+------------+| rpl_semi_sync_master_enabled | ON || rpl_semi_sync_master_timeout | 10000 || rpl_semi_sync_master_trace_level | 32 || rpl_semi_sync_master_wait_for_slave_count | 1 || rpl_semi_sync_master_wait_no_slave | ON || rpl_semi_sync_master_wait_point | AFTER_SYNC |+-------------------------------------------+------------+6 rows in set (0.01 sec)
rpl_semi_sync_master_wait_point | AFTER_SYNC
在master 同步了二进制日志后，在从服务器上安装插件
root@(none) 19:51 mysql>install plugin rpl_semi_sync_slave soname 'semisync_slave.so';Query OK, 0 rows affected (0.02 sec)
开启相应的服务
root@(none) 19:51 mysql>set global rpl_semi_sync_slave_enabled=1;Query OK, 0 rows affected (0.00 sec)
查看一下
root@(none) 19:52 mysql>show global variables like '%rpl_semi%'; +---------------------------------+-------+| Variable_name | Value |+---------------------------------+-------+| rpl_semi_sync_slave_enabled | ON || rpl_semi_sync_slave_trace_level | 32 |+---------------------------------+-------+2 rows in set (0.00 sec)
半同步配置成功
[root@slave mysql]# cd /data/mysql[root@slave mysql]# lsaliwangwang relay-log.infoauto.cnf sanchuangca-key.pem scca.pem server-cert.pemchenran server-key.pemclient-cert.pem slave-bin.000001client-key.pem slave-bin.000002ib_buffer_pool slave-bin.000003ibdata1 slave-bin.indexib_logfile0 slave.errib_logfile1 slave.pidibtmp1 slave-relay-bin.000001localhost.localdomain.err slave-relay-bin.000002localhost.localdomain.pid slave-relay-bin.indexmaster.info sysmysql wangtcmysql.sock wangwangmysql.sock.lock wangwang1node2-db.err xh1performance_schema xionghanprivate_key.pem zwxpublic_key.pem[root@slave mysql]# cat master.info25master-bin.0000021370192.168.0.171xionghan1234563306600030.00003b416199-07a7-11eb-83b5-000c29ab471a864000[root@slave mysql]# cat relay-log.info 7./slave-relay-bin.0000021238master-bin.0000021370001
转载地址：http://cqlzi.baihongyu.com/