首先需要两台机器，并且主从复制的前提是需要先做数据同步，先在我的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.sock port = 3306 open_files_limit = 8192 innodb_buffer_pool_size = 512M character-set-server=utf8 #skip-grant-tables #binary log server_id=1 log_bin [mysql] auto-rehash prompt=\\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\G Empty set (0.00 sec)3.备份所有的库
[root@master ~]# mysqldump -uroot -p'Sanchuang1234#' --all-databases >/backup/2020-10-23-all-db.sql mysqldump: [Warning] Using a password on the command line interface can be insecure.4，上传数据到从服务器
[root@master ~]# cd /backup/ [root@master backup]# ls 2020-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:/root root@192.168.0.173's password: 2020-10-23-all-db.sql 100% 834KB 24.3MB/s 00:005.查看当前的二进制日志的文件名和位置号
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.sock port = 3306 open_files_limit = 8192 innodb_buffer_pool_size = 512M server_id=2 log_bin [mysql] auto-rehash prompt=\\u@\\d \\R:\\m mysql> [root@slave ~]# service mysqld restart Shutting down MySQL.. SUCCESS! Starting MySQL. SUCCESS! [root@slave ~]# ps aux|grep mysqld root 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.pid mysql 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=3306 root 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.sql mysql: [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) #再次查看 ```bash root@(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 names You can turn off this feature to get a quicker startup with -A Database changed root@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)半同步配置成功
