mysql 数据库主从复制搭建

MySQL 主从复制主要用于实现高可用性和备份。在主从复制中，一个 MySQL 实例（称为主节点）将其数据更改复制到至少一个其他 MySQL 实例（称为从节点）上。主要借助于数据库二进制日志binlog进行数据的复制。
主从数据库对应的操作系统、数据库版本要一致。

1、主库配置

设定一个唯一的server ID，开启二进制日志。serverId默认值是1，最大可取值2^32-1。

配置文件：my.cnf

[mysqld]
#配置serverId
server_id=1
# 开启 binlog
log-bin=mysql-bin 
#配置不需要同步的库
binlog-ignore-db = mysql
binlog-ignore-db = sys
binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema

重启服务

systemctl restart mysqld

2、从库配置

配置文件my.cnf

[mysqld]
server-id=21
log-bin=mysql-bin
read_only=1

read_only设置数据库为只读，注意如果用户有super权限还是可以修改数据的。

3、创建复制用户

mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'repl';
mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repl'@'%';

数据复制只需要REPLICATION SLAVE权限就可以了，REPLICATION CLIENT权限是为了能查看复制状态。如下面的show master status命令就需要该权限。

4、获取二进制日志坐标

mysql> SHOW MASTER STATUS \G;
*************************** 1. row ***************************File: mysql-bin.000003Position: 11199Binlog_Do_DB: Binlog_Ignore_DB: 
Executed_Gtid_Set:

File是当前的binlog日志文件，position是当前位置。

5、创建数据快照

如果要同步的数据库有存量数据，先进行同步数据

主库卸数

mysqldump -uroot -p --master-data db_test > db_test.sql

db_test是要同步的数据库名称，也可以不指定数据库，使用–all-databases来dump所有的数据库。

–master-data用来在从库自动生成一条设置master信息的语句。会设置对应的MASTER_LOG_FILE和MASTER_LOG_POS。这样可以达到不停机备份同步的效果。

例：

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=11199;

在高版本的mysql中，已经过时使用–source-data替代。

备库导入初始数据

首先要创建对应的数据库：create database db_test;

mysql -u root -p  db_test < db_test.sql

如果是已经命令行登录，可以在命令行直接执行：source 数据文件来导入初始数据。

6、开启复制

CHANGE MASTER TO MASTER_HOST='192.168.1.101',MASTER_USER='repl',MASTER_PASSWORD='repl',MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=9026;

这里几个参数都很好理解，host，port是主库的连接地址，user,passowrd是上面主库上创建的复制用户。

master_log_file是主库当前binlog日志文件，master_log_pos指定从哪个位置开始复制。可以通过上面的show master status获取后面两个参数。

另外上面在导出主库数据时也说了，如果mysqldump加上–master-data参数在备份数据文件会自动生成设置后面两个参数的语句。这里可以不用CHANGE MASTER命令配置了就。

CHANGE MASTER命令在高版本（8.0.23）中也替换成了CHANGE REPLICATION SOURCE命令。

开始复制

>start slave;

查看复制状态

mysql> show slave status \G;
*************************** 1. row ***************************Slave_IO_State: Waiting for source to send eventMaster_Host: 192.168.1.100Master_User: replMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000003Read_Master_Log_Pos: 11199Relay_Log_File: 50-relay-bin.000004Relay_Log_Pos: 1273Relay_Master_Log_File: mysql-bin.000003Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 11199Relay_Log_Space: 2246Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1Master_UUID: ceeb46c0-c925-11ed-8e74-fa163efb83e8Master_Info_File: mysql.slave_master_infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Replica has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0Network_Namespace:

复制状态后很多信息，这里可以核对下CHANGE MASTER配置的master基本信息是否正确。Slave_IO_Running和Slave_SQL_Running可以查看当前复制线程状态。

Read_Master_Log_Pos和Exec_Master_Log_Pos对比，SQL_Delay数量可以查看当前复制进度是否偶延迟。也可以通过Slave_SQL_Running_State描述信息来看当前复制状态。

如果复制过程中有错误，Last_SQL_Errno和Last_SQL_Error会有对应的错误信息，更详细的信息记录在performance_schema.replication_applier_status_by_worker表中。

跳过错误

如果从库在复制过程中发生了错误(某条语句在从库上执行失败)，如手动修改了从库，导致复制语句无法执行，在经过具体错误信息分析后，可以通过sql_slave_skip_counter参数来设置跳过当前错误。

>stop slave;
>set global sql_slave_skip_counter = 1;
>start slave;

sql_slave_skip_counter参数设置跳过数量，start slave开启复制使用过该变量后会将该参数重新归0.